Note
Click here to download the full example code
Compute length on insertΒΆ
It is possible to insert a geometry and ask PostgreSQL to compute its length at the same time. This example uses SQLAlchemy core queries.
9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | from sqlalchemy import Column from sqlalchemy import Float from sqlalchemy import Integer from sqlalchemy import MetaData from sqlalchemy import Table from sqlalchemy import bindparam from sqlalchemy import func from geoalchemy2 import Geometry from geoalchemy2.shape import to_shape # Tests imports from tests import select from tests import test_only_with_dialects metadata = MetaData() table = Table( "inserts", metadata, Column("id", Integer, primary_key=True), Column("geom", Geometry("LINESTRING", 4326)), Column("distance", Float), ) @test_only_with_dialects("postgresql") class TestLengthAtInsert(): def test_query(self, conn): metadata.drop_all(conn, checkfirst=True) metadata.create_all(conn) # Define geometries to insert values = [ {"ewkt": "SRID=4326;LINESTRING(0 0, 1 0)"}, {"ewkt": "SRID=4326;LINESTRING(0 0, 0 1)"} ] # Define the query to compute distance (without spheroid) distance = func.ST_Length(func.ST_GeomFromText(bindparam("ewkt")), False) i = table.insert() i = i.values(geom=bindparam("ewkt"), distance=distance) # Execute the query with values as parameters conn.execute(i, values) # Check the result q = select([table]) res = conn.execute(q).fetchall() # Check results assert len(res) == 2 r1 = res[0] assert r1[0] == 1 assert r1[1].srid == 4326 assert to_shape(r1[1]).wkt == "LINESTRING (0 0, 1 0)" assert round(r1[2]) == 111195 r2 = res[1] assert r2[0] == 2 assert r2[1].srid == 4326 assert to_shape(r2[1]).wkt == "LINESTRING (0 0, 0 1)" assert round(r2[2]) == 111195 |