When you query the postgis database, you will get a number of row of record, how to write a sql query to get a valid geojson object instead of rows of record?

up vote 9 down vote accepted
SELECT json_build_object(
'type', 'FeatureCollection',

'features', json_agg(
    json_build_object(
        'type',       'Feature',
        'id',         gid,
        'geometry',   ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
        'properties', jsonb_set(row_to_json(city_parks)::jsonb,'{geom}','0',false)
    )
)
)
FROM city_parks
WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));

or

SELECT json_build_object(
'type', 'FeatureCollection',

'features', json_agg(
    json_build_object(
        'type',       'Feature',
        'id',         gid,
        'geometry',   ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
        'properties', jsonb_set(row_to_json(city_parks)::jsonb,'{geom}','0',false)
    )
)
)
FROM city_parks
WHERE ST_Intersects(st_transform(city_parcels.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995))'));

find all the city parks, which within or touch the rectangular bound(polygon(lng,lat)..)

above sql will output geojson, but not yet, assume'result" is whatever postgresql output, the real valid geojson is in result.rows[0].json_build_object

for example you use node.js the response send should be: res.send(result.rows[0].json_build_object);


more explain about the sql:

::json means cast to json type ::jsonb means cast to jsonb type( json binary type)

jsonb_set() because the row_to_json() will convert all column include geom to properties list, which you do not want geom column, you want to remove it, but so far not remove method provided by postgresql json operation method, so I use jsonb_set to reset geom to '0', here you can't use null, otherwise, all properties list will be null.

city_parks is the table name in postgresql, geom is the geometry column name.

geojson's SRID is always 4326

city_parks 's SRID is 2230

ST_ForceRHR() is used because geojson polygon follow right hand rule, this will fore polgyon vertice follow right hand rule

when you use st_intersects(a, b), a and b both must be geometry or both must be geography type.

  st_interesects(geometry, geography)  // invalid

city parks geom is geometry type with SRID=2230,

Bounding box polygon((lng,lat),(lng,lat)...) is geography type with SRID=4326,

st_transform() was used to either convert from SRID(2230) to SRID(4326) or like wise

row_to_json() will convert all column in this row to json

     select row_to_json(city_parks) from city_parks;

     {"id":6013,"column_name_1":"value1","column_name_2":"value2",... etc}

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.