Note

Go to the end 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 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