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 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 @test_only_with_dialects("postgresql")
36 class TestLengthAtInsert:
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_GeomFromText(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