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:
- making an explicit query (in this case for just 1 element):
geometry_wkt = session.scalars(select(new_c.geometry.ST_AsText())).first()
- 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".
GeoAlchemy2 has a method called
to_shape
from its shapely integration. This is used as follows: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: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!