Retrieve PostGIS geometry data as WKT using GeoAlchemy without making extra queries

337 views Asked by At

I'm using a FastAPI backend to connect to a PostGIS server.
I'm using SQLAlchemy with GeoAlchemy2 as an extension to map the db tables to objects.
One of the tables is called "consortiums" and has a column of type Geometry.
What I'd like is: to retrieve data from this column under WKT format.

The relevant part of the table:

class Consortium(Base):
    __tablename__ = "consortiums"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    geometry = mapped_column(Geometry)

For now, I have found 2 methods that work:

  1. making an explicit query (in this case for just 1 element):
geometry_wkt = session.scalars(select(new_c.geometry.ST_AsText())).first()
  1. using a Computed column:
# declared within the model, right below the geometry column
geometry_wkt: Mapped[str] = mapped_column(Computed("ST_AsText(geometry)"))

Another solution I tried is:

from geoalchemy2.functions import ST_AsText

   # added on model
   @hybrid_property
    def geometry_wkt(self):
        return self.geometry.ST_AsText()

but when I try to print a Consortium model, this seems to just return the function itself instead of the value

print(new_c) # outputs: ST_AsText(ST_GeomFromEWKB(:ST_GeomFromEWKB_1))

Right now I have opted for solution 2 which I'm ok with, I'm just wondering if it is the correct approach for what I'm trying to do or if there is a more efficient or something like a "best practice".

1

There are 1 answers

0
John Moutafis On

GeoAlchemy2 has a method called to_shape from its shapely integration. This is used as follows:

consortium = Session.query(Consortium).first()
shp = to_shape(consortium.geometry)

shp will be a shapely geometry.
Using shapely's to_wkt method you can get the WKT representation of that geometry without a second query to the DB:

to_wkt(shp)

Keep in mind that I am not suggesting that your approach is wrong :)
I am just presenting an alternative with a single DB query that is then transformed in the code side of your app!