[FIXED] Flask-SQLAlchemy ORM/GeoAlchemy2 results to a dictionary and ultimately JSON

Issue

I am using Flask/SQLAlchemy to create a web app with a map in it, so naturally I'm using a PostGIS database. The geom column requires an ST_Transform and somehow I need to turn this column and all others into JSON. The general structure of the database is:

from app import login, db
from datetime import datetime
from geoalchemy2 import Geometry
from time import time
from flask import current_app
from sqlalchemy import func

class Streets(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    street = db.Column(db.String(50))
    geom = db.Column(Geometry(geometry_type='LINESTRING'))

    def to_dict(self):
        data = {
            'id': self.id,
            'street': self.street,
            '_geom': func.ST_AsGeoJSON(func.ST_Transform(self.geom, 4326))
        }
    return data

My api route turns this result into an api:

return jsonify(Streets.query.get_or_404(id).to_dict())

But I keep getting this error: NameError: name 'ST_AsGeoJSON' is not defined

I also tried to create my _geom value like this:

    data['_geom'] = db.session.query(func.ST_AsGeoJSON(func.ST_Transform(self.geom, 4326)))

The error message is: TypeError: Object of type 'BaseQuery' is not JSON serializable

Finally, I tried an api route like this:

    data = Streets.to_dict(
        db.session.query(
            func.ST_AsGeoJSON(
                func.ST_Transform(
                    Streets.geom, 4326
                )
            )
        )
        .filter(Streets.id==id))

    return jsonify(data)

And I get a different error: AttributeError: 'BaseQuery' object has no attribute 'id'

If I run this in flask shell it works:

streets = db.session.query(
        Streets.id,
        Streets.street,
        func.ST_AsGeoJSON(func.ST_Transform(Streets.geom, 4326)))

How can I perform ST_Transform and get JSON to my api route?

UPDATE

I found this in the SQLALchemy documentation that got me some progress: "orm.column_property() can be used to map a SQL expression". So I tried adding this to my class Streets(db.Model):

coords = db.column_property(func.ST_AsGeoJSON(func.ST_Transform(geom, 4326)))

Then I add it to data like this:

def to_dict(self):
    data = {
        'id': self.id,
        'street': self.street,
        'coords': self.coords
        }
    return data

But now I'm double encoding my results, once into GeoJSON and then I jsonify it:

return jsonify(Streets.query.get_or_404(id).to_dict())

So my api inserts \'s:

{"coords": "{\"type\":\"MultiLineString\",\"coordinates\":[[[-80.8357132798193,35.2260689001034],[-80.8347602582754,35.2252424284259]]]}"}

And using ST_AsText just turns it into text:

{"coords": "MULTILINESTRING((-80.8357132798193 35.2260689001034,-80.8347602582754 35.2252424284259))"}

I think I'm close with this update, but does anyone have a suggestion for getting correct GeoJSON with the JSON of the other fields of my database?

Solution

The first error

NameError: name 'ST_AsGeoJSON' is not defined

means that your example code is not what you were actually using. You had forgot to access it through func. It would not work after fixing that either, since you'd be mixing the SQL world and the Python world. func.ST_AsGeoJSON(...) creates an SQL function expression object that is supposed to be compiled to SQL and sent to the DB in a query, not passed to jsonify().

The second error

TypeError: Object of type 'BaseQuery' is not JSON serializable

should be somewhat obvious.

data['_geom'] = db.session.query(func.ST_AsGeoJSON(func.ST_Transform(self.geom, 4326)))

creates a Query, and a too broad query at that, since you've not limited it to fetch data of the current object. The Query object is not JSON serializable.

In

data = Streets.to_dict(db.session.query(...)...)

you pass the Query object as self to Streets.to_dict(), which then tries to access its id attribute in

'id': self.id,

which fails for obvious reasons – namely passing an unrelated object as the instance to a method.

The column_property() approach produces the doubly encoded JSON because SQLAlchemy does not by default expect ST_AsGeoJSON to return JSON and treats it as text instead, which it actually returns. Try decoding in between manually:

def to_dict(self):
    data = {
        'id': self.id,
        'street': self.street,
        'coords': json.loads(self.coords)
        }
    return data

Answered By - Ilja Everilä