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

Gallery generated by Sphinx-Gallery