烧瓶-SQLAlchemy/GeoAlchemy2 2结果为字典，最终为JSON

Question

我正在使用Flask/SQLAlchemy创建一个带有地图的web应用程序，所以我自然地使用了一个PostGIS数据库。geom列需要一个ST_Transform，我需要将这个列和所有其他列转换为JSON。数据库的一般结构是：

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

我的api路由将此结果转换为api：

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

但是我一直收到这样的错误：NameError: name 'ST_AsGeoJSON' is not defined

我还试图像这样创建我的_geom值：

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

错误消息是：TypeError: Object of type 'BaseQuery' is not JSON serializable

最后，我尝试了这样的api路由：

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

    return jsonify(data)

我得到了一个不同的错误：AttributeError: 'BaseQuery' object has no attribute 'id'

如果我在flask shell中运行这个程序，它可以工作：

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

我如何执行ST_Transform并将JSON带到我的api路由？

更新

我在SQLALchemy文档中发现了这一点，这给我带来了一些进步："orm.column_property()可以用来映射一个SQL表达式“。所以我试着把这个添加到我的class Streets(db.Model)中

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

然后我将它添加到data中，如下所示：

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

但是现在我要对我的结果进行双重编码，一次是GeoJSON，然后是jsonify：

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

所以我的api插入了\的：

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

使用ST_AsText只会将其转化为文本：

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

我想我已经接近这个更新了，但是有没有人建议用我数据库中其他字段的GeoJSON来获得正确的JSON呢？

Answer 1

第一个错误

NameError: name 'ST_AsGeoJSON' is not defined

意味着您的示例代码不是您实际使用的代码。你忘了通过func访问它。修复之后，它也无法工作，因为您将混合SQL世界和Python世界。func.ST_AsGeoJSON(...)创建一个SQL函数表达式对象，该对象应该编译到SQL中，并在查询中发送到DB，而不是传递给jsonify()。

第二错误

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

应该有点明显。

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

创建一个Query，并在此创建一个过于宽泛的查询，因为您并没有限制它获取当前对象的数据。Query对象不能被JSON序列化。

在……里面

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

将Query对象作为self传递给Streets.to_dict()，然后Streets.to_dict()尝试访问它的id属性

'id': self.id,

由于明显的原因而失败--即将不相关的对象作为实例传递给方法。

column_property()方法生成双重编码的JSON，因为在默认情况下，SQLAlchemy不会期望ST_AsGeoJSON返回JSON，而是将其作为文本来处理，而实际上它会返回文本。尝试在中间手动解码：

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