fastapi: mapping sqlalchemy database model to pydantic geojson feature

Viewed 1419

I just started playing with fastapi/sqlalchemy/pydantic and I'm trying to build a simple API endpoint to return the rows in a postgis table as a geojson feature collection.

This is the sqlalchemy model:

class Poi(Base):
__tablename__ = 'poi'

id = Column(Integer, primary_key=True)
name = Column(Text, nullable=False)
type_id = Column(Integer)
geometry = Column(Geometry('POINT', 4326, from_text='ST_GeomFromEWKT'), nullable=False)

using geojson_pydantic the relevant pydantic models are:

from geojson_pydantic.features import Feature, FeatureCollection
from geojson_pydantic.geometries import Point
from typing import List

class PoiProperties(BaseModel):
    name: str
    type_id: int

class PoiFeature(Feature):
    id: int
    geometry: Point
    properties: PoiProperties

class PoiCollection(FeatureCollection):
    features: List[PoiFeature]

Ideally I'd like to be able to retrieve and return the database records like so:

def get_pois(db: Session, skip: int = 0, limit: int = 100):
    return db.query(Poi).offset(skip).limit(limit).all()


@app.get("/geojson", response_model=PoiCollection)
def read_geojson(skip: int = 0, limit: int = 100,  db: Session = Depends(get_db)):
    return get_pois(db, skip=skip, limit=limit)

Still I'm trying to figure out how to map the name and type_id columns from the db model to the PoiProperties in the PoiFeature object

0 Answers
Related