geodjango 从原始 SQL 返回 geojson

Question

有这个 django 视图：我知道我可以使用它来将 postgres 表序列化为 geojson

def mun_datasets(request):
mun = serialize('geojson', Municipalities.objects.all())
return HttpResponse(mun, content_type='json')

url.py

url(r'^mun_data/$',mun_datasets, name = 'mun'),

但是我有一个非常复杂的查询，需要在原始SQL中，所以我试图弄清楚如何从原始SQL查询中返回geojson(下面的查询非常简单，但实际查询很复杂，所以我只想知道如何从任何sql查询返回geojson以将该概念应用于更难的查询(

def mun_datasets(request):
cur = conn.cursor()
qry='''select json_object_agg(namelsad,geom) from reporter_municipalities'''
cur.execute(qry)
row=cur.fetchone()
mun = serialize('geojson', row)
return HttpResponse(mun, content_type='json')

这让我的mun_dataURL 出错

AttributeError at /mun_data/
'dict' object has no attribute '_meta'
Request Method: GET
Request URL:    http://127.0.0.1:8000/mun_data/
Django Version: 1.11
Exception Type: AttributeError
Exception Value:    
'dict' object has no attribute '_meta'
Exception Location: /usr/local/lib/python3.6/dist-packages/django/contrib/gis/serializers/geojson.py in start_object, line 38
Python Executable:  /usr/bin/python3
Python Version: 3.6.5
Python Path:    
['/home/ralph/Desktop/geo',
'/usr/lib/python36.zip',
'/usr/lib/python3.6',
'/usr/lib/python3.6/lib-dynload',
'/home/ralph/.local/lib/python3.6/site-packages',
'/usr/local/lib/python3.6/dist-packages',
'/usr/lib/python3/dist-packages']
Server time:    Fri, 12 Oct 2018 14:00:44 +0000

创建了此查询

SELECT row_to_json(fc)
FROM
( SELECT 'FeatureCollection' AS TYPE,
array_to_json(array_agg(f)) AS features
FROM
(SELECT 'Feature' AS TYPE,
ST_AsGeoJSON(g.geom)::JSON AS geometry,            
row_to_json(
(SELECT p
FROM
( SELECT namelsad,geom) AS p)) AS properties
FROM reporter_municipalities AS g
) AS f) AS fc;

这产生

{"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"MultiPolygon","coordinates":[[[[-75.100728,40.638361],[-75.100497,40.638426],[-75.097664,40.639218],[-75.09661,40.639512],[-75.09135,40.640982],[-75.090844,40.641214],[-75.088537 (...)
row=cur.fetchone()
#mun = serialize('geojson', row[0])
return HttpResponse(row[0], content_type='json')

mun_data不会抛出错误，它只是返回这个

typefeatures

Answer 1

想通了

def mun_datasets(request):
cur = conn.cursor()
qry='''SELECT row_to_json(fc)
FROM
( SELECT 'FeatureCollection' AS TYPE,
array_to_json(array_agg(f)) AS features
FROM
(SELECT 'Feature' AS TYPE,
ST_AsGeoJSON(g.geom)::JSON AS geometry,            
row_to_json(
(SELECT p
FROM
( SELECT namelsad,geom) AS p)) AS properties
FROM reporter_municipalities AS g
) AS f) AS fc;
'''
cur.execute(qry)
row=cur.fetchone()
return JsonResponse(row[0])