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