0

Some PostGIS functions behave differently based on the arguments types.

Example: ST_Distance will calculate projected units distances for geometry arguments and meters distances for geography arguments.

In PostgreSQL you can easily cast with the :: syntax, but what if you are using sqlalchemy objects or geoalchemy?

1

Here are two ways of casting in python, when a table has a Geometry column.

Given

from geoalchemy2 import Geometry
from sqlalchemy import create_engine
from sqlalchemy import (Column, Integer)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SRS_WGS_84 = 4326

ClassBase = declarative_base()

class MyTable(ClassBase):
    id = Column(Integer, primary_key=True)
    geometry = Column(Geometry(srid=SRS_WGS_84, management=True))

LOCAL_TEST_DB = "postgresql+psycopg2:///my_test_db"
DB_ECHO = False

engine = create_engine(LOCAL_TEST_DB, echo=DB_ECHO)
Session = sessionmaker(bind=engine)

1. Casting with Geoalchemy

from geoalchemy2.types import Geography
from sqlalchemy.sql import cast

record1_geography = session.query(cast(MyTable.geometry, Geography(srid=SRS_WGS_84))
                                 ).filter(MyTable.id == 1).one()

2. Casting with PostGIS support function

from sqlalchemy.sql import func

cast(models.Task.geometry, Geography(srid=SRID))

# ref: https://postgis.net/docs/ST_GeogFromWKB.html
record1_geography_2 = session.query(func.ST_GeogFromWKB(MyTable.geometry)
                                   ).filter(MyTable.id == 1).one()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.