1

I have the following SQL on a PostGIS Database ($properties contains a list of fields, including osm_id)

        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(ST_Intersection(lg.way, bbox.geom))::json As geometry, 
                    (
                       SELECT row_to_json(t) 
                         FROM 
                         (
                              SELECT $properties
                         ) t
                    )
             As properties
          FROM 
              planet_osm_polygon As lg, 
                ( 
                    SELECT ST_Transform ( ST_SetSRID(ST_MakeBox2d( ST_MakePoint( $topLon,$topLat) , ST_MakePoint( $botLon , $botLat) ), $srid) , 900913 ) as geom
                ) as bbox 

                WHERE "building" is NOT NULL AND ST_Intersects(lg.way, bbox.geom)
                    ) As f 
                ) fc;

that returns GeoJson in this format

"type": "FeatureCollection",
"features": [{
    "type": "Feature",
    "geometry": {
        "type": "MultiPolygon",
        "coordinates": [
            [
            ],
     "properties": {
        "osm_id": 278325855,
        "height": "80.4"
    }
},  

I need to return it in THIS format, note the extra id field at the properties and geometry level. The value to be returned is the same as the osm_id in the properties.

"type": "FeatureCollection",
"features": [{
    "type": "Feature",
    "id": 3698396,
    "properties": {
        "height": 54
    },
    "geometry": {
        "type": "Polygon",
        "coordinates": 
            [
            ]
        ]
    }
},                  

How can I do this in a Query, or would it be more efficient to parse the Json and add it later?

1 Answer 1

2

Try something like this, sorry had to replace your variables with real values so I could test, but should be easy enough for you to replace back with what you had. Also note I replace your ST_MakeBox2D/ST_SetSRID with ST_MakeEnvelope which is shorter and generally more accurate.

WITH c AS (SELECT -88.505 As topLon, 41.8046 As topLat, -88.405 As botLon, 41.906 As botLat, 4326 As srid)  ,
bbox AS (SELECT ST_Transform ( ST_MakeEnvelope( topLon, topLat , botLon , botLat , srid) , 900913 ) As geom
    FROM c )
  SELECT row_to_json(fc)
        FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
        FROM (SELECT 'Feature' As type, osm_id As id,

            (
                   SELECT row_to_json(t) 
                     FROM 
                     (
                          SELECT  tags->'height' As height
                     ) t
                )
         As properties,
        ST_AsGeoJSON(ST_Intersection(lg.way, bbox.geom))::json As geometry 

      FROM 
          planet_osm_polygon As lg, 
            bbox

            WHERE building is NOT NULL AND ST_Intersects(lg.way, bbox.geom)
                ) As f 
            ) fc;
1
  • Excellent, well done, nothing close to what I was doing .. Thanks for the ST_MakeEnvelope tip as well .. Jan 16, 2015 at 3:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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