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