Note
Go to the end 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.
10 from sqlalchemy import Column
11 from sqlalchemy import Float
12 from sqlalchemy import Integer
13 from sqlalchemy import MetaData
14 from sqlalchemy import Table
15 from sqlalchemy import bindparam
16 from sqlalchemy import func
17
18 from geoalchemy2 import Geometry
19 from geoalchemy2.shape import to_shape
20
21 # Tests imports
22 from tests import select
23 from tests import test_only_with_dialects
24
25 metadata = MetaData()
26
27 table = Table(
28 "inserts",
29 metadata,
30 Column("id", Integer, primary_key=True),
31 Column("geom", Geometry("LINESTRING", 4326)),
32 Column("distance", Float),
33 )
34
35
36 class TestLengthAtInsert:
37 @test_only_with_dialects("postgresql", "sqlite")
38 def test_query(self, conn):
39 metadata.drop_all(conn, checkfirst=True)
40 metadata.create_all(conn)
41
42 # Define geometries to insert
43 values = [
44 {"ewkt": "SRID=4326;LINESTRING(0 0, 1 0)"},
45 {"ewkt": "SRID=4326;LINESTRING(0 0, 0 1)"},
46 ]
47
48 # Define the query to compute distance (without spheroid)
49 distance = func.ST_Length(func.ST_GeomFromEWKT(bindparam("ewkt")), False)
50
51 i = table.insert()
52 i = i.values(geom=bindparam("ewkt"), distance=distance)
53
54 # Execute the query with values as parameters
55 conn.execute(i, values)
56
57 # Check the result
58 q = select([table])
59 res = conn.execute(q).fetchall()
60
61 # Check results
62 assert len(res) == 2
63
64 r1 = res[0]
65 assert r1[0] == 1
66 assert r1[1].srid == 4326
67 assert to_shape(r1[1]).wkt == "LINESTRING (0 0, 1 0)"
68 assert round(r1[2]) == 111195
69
70 r2 = res[1]
71 assert r2[0] == 2
72 assert r2[1].srid == 4326
73 assert to_shape(r2[1]).wkt == "LINESTRING (0 0, 0 1)"
74 assert round(r2[2]) == 111195
