Fastapi: [QUESTION] Integration with POSTGIS / Geometries / GeoJson?

[KoduIsGreat]

Description
First of all i want to say @tiangolo congrats on making something really great. I haven't dove in too much but from what i've seen so far i'm really impressed.

How can I Integrate pydantic models with ORM models (specifically sqlalchemy) that have a geometry column to get all of the sweet benefits of the autogen docs and validation?
Are there any examples doing this, I looked around but it wasn't clear to me if this was possible? if not I would be happy to take a crack at it and perhaps submit a PR.

Thanks again for making a great library.

[Sieboldianus]

I just did this myself, running Postgres 11 with PostGis, this worked for me:

I db_models using Geometry from Geoalchemy2:

from geoalchemy2.types import Geometry

class User(LBSNBase):
    __tablename__ = 'user'

    user_guid = Column(Text, primary_key=True, nullable=False)
    user_name = Column(Text)
    coordinates = Column(Geometry('POINT', 4326))

In app models I followed the usual patterns, but there were two challenges that turned out quite difficult because PostGis uses extended WKT Format, which doesn't seem very popular in Python packages (even Shapely doesn't support it):

1) Allowing the use of WKTElement, for which no pydantic validator exists
2) Submission and retrieval of Geometry, and conversion to human readable Coordinates

I finally ended up doing this for handling of Coordinates/Geometry in the api-model:

class UserCreate(UserBase):
    """Properties to receive on User creation"""
    user_guid: str
    user_coordinates: Coordinates = None

class UserInDB(UserBaseInDB):
    """Properties properties stored in DB"""
    user_geom: str = f'SRID=4326;POINT(0 0)'

(For the Coordinates validation and custom class, see here

The Conversion back and forth is done with:

from shapely import wkt

def get_geom_from_coordinates(
        coordinates: Coordinates) -> str:
    geom_wkte = f'SRID=4326;POINT({coordinates.lng} {coordinates.lat})'
    return geom_wkte

def get_coordinates_from_geom(
        geom: str) -> Coordinates:
    """Convert EWKT representation (with srid) to geometry

    Note:
    Shapely has no support for handling SRID (projection). The
    approach used here is a shortcut. TODO: This should be replaced
    by proper EWKT handling using a package, e.g.
    django.contrib.gis.geos or django.contrib.gis.geometry, see:
    https://docs.huihoo.com/django/1.11/ref/contrib/gis/geos.html
    """
    geom = geom.replace("SRID=4326;", "")
    shply_geom = wkt.loads(geom)
    coordinates = Coordinates(
        lng=shply_geom.x, lat=shply_geom.y)  # pylint: disable=no-member
    return coordinates

Don't be confused with the gist I linked (here). Using WKTElement from geoalchemy2 seemed like the correct solution, but I couldn't get it to work. The gist may still be helpful because it shows how to use ORM models, such as WKTElement, in pydantic with:

class Config(BaseConfig):
    arbitrary_types_allowed = True

However, when I used explicit WKTElement, the final INSERT psql statement would always include a call to ST_GeomFromText(), which expects an EWKT string (instead, the WKTElement was converted to some complex array). Therefore I ended up using a simple string as shown above. It is easy to create such string, but the conversion back to a shapely Geometry object that I am using above is far from ideal (and only works if you're working in WGS1984).

When I have more time I will look into the GEOS API from Django. This would allow moving between WKT and EWKT and Geometries, e.g.:

>>> from django.contrib.gis.geos import GEOSGeometry
>>> GEOSGeometry('POINT EMPTY', srid=4326).ewkt
'SRID=4326;POINT EMPTY'
>>> GEOSGeometry('SRID=4326;POINT EMPTY', srid=4326).ewkt
'SRID=4326;POINT EMPTY'
>>> GEOSGeometry('SRID=1;POINT EMPTY', srid=4326)
Traceback (most recent call last):
...
ValueError: Input geometry already has SRID: 1.

I don't have much time to compile everything in a thorough example atm, sorry.

[KoduIsGreat]

Thanks!
I'll look over this and provide my thoughts.

[tiangolo]

First of all i want to say @tiangolo congrats on making something really great. I haven't dove in too much but from what i've seen so far i'm really impressed.

I'm glad to hear that! :tada: :rocket:

---

There's new and improved support for ORMs with Pydantic, using its ORM mode.

You can check the tutorial about how to use SQLAlchemy and Pydantic with ORM mode together: https://fastapi.tiangolo.com/tutorial/sql-databases/

It is part of the latest FastAPI version 0.30.0 (released today) :tada:

[github-actions[bot]]

Assuming the original issue was solved, it will be automatically closed now. But feel free to add more comments or create new issues.