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)

Gallery generated by Sphinx-Gallery