Write GeoJSON As Feature Object in Postgis

Question

I'm trying to create a geofence/circle from a lat/long set and I'm close except that the GeoJSON that PostGIS creates differs in structure from the client library that our front-end uses. Here is my python to create the geofence:

session = DatabaseSessionService().create_session()
METERS_IN_HALF_MILE = 805
longitude = 21.304116745663165
latitude = 38.68607570952619
geom = func.ST_Buffer(func.ST_MakePoint(longitude, latitude), METERS_IN_HALF_MILE)
geofence = Geofence(
 geom=geom,
 geojson=cast(func.ST_AsGeoJSON(geom), JSON),
 company_id=188,
 name='Test Fence 1'
)
session.add(geofence)
session.commit()

This will create a record with geojson set to:

{
 "type":"Polygon",
 "coordinates":[
 [
 [
 826.304116745663,
 38.6860757095262
 ],
 ...
 ]
 ]
}

However I'd like to maintain the convention set previously and have the GeoJSON written as a Feature Object like this:

{
 "type":"Feature",
 "properties":{
 },
 "geometry":{
 "type":"Polygon",
 "coordinates":[
 [
 [
 -92.29188859462738,
 38.913722221672636
 ],
 ...
 ]
 ]
 }
}

I've looked into using ogr2ogr but I'd prefer that this be an automated process that happens on creation as opposed to being a post-creation task.

• user30184: As you probably noticed ST_AsGeoJSON gives only the geometry part. This postgresonline.com/journal/archives/… may help you further.
• dbaston: See also gis.stackexchange.com/q/112057/18189

Answer 1

I ended up doing an INSERT and then UPDATE to use the python libs geojson and shapely to generate the geoJSON. So my code now looks like this.

import geojson
from models import Geofence
from geoalchemy2.shape import to_shape
from sqlalchemy import func
from services.DatabaseSessionService import DatabaseSessionService
session = DatabaseSessionService().create_session()
longitude = -92.339292
latitude = 39.005994
geom = func.ST_Buffer(func.ST_MakePoint(longitude, latitude), 0.5)
geofence = Geofence(
 geom=geom,
 company_id=188,
 name='Test Fence 1',
 alert_enter=False,
 alert_exit=False
)
session.add(geofence)
session.commit()
geometry = to_shape(geofence.geom)
geofence.geojson = geojson.Feature(geometry=geometry, properties={})
session.add(geofence)
session.commit()

• Michal Zimmermann: Could this be of any help? It describes the way to create valid GeoJSON within the PostGIS database.