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