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 75 76 77 78 79 80 81 | from sqlalchemy import bindparam
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Float
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import select
from sqlalchemy import Table
from geoalchemy2 import Geometry
from geoalchemy2.shape import to_shape
engine = create_engine('postgresql://gis:gis@localhost/gis', echo=True)
metadata = MetaData(engine)
table = Table(
"inserts",
metadata,
Column("id", Integer, primary_key=True),
Column("geom", Geometry("LINESTRING", 4326)),
Column("distance", Float),
)
class TestLengthAtInsert():
def setup(self):
self.conn = engine.connect()
metadata.drop_all(checkfirst=True)
metadata.create_all()
def teardown(self):
self.conn.close()
metadata.drop_all()
def test_query(self):
conn = self.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
|
Total running time of the script: ( 0 minutes 0.000 seconds)