How to add a polygon from PostGIS ST_AsGeoJSON into Leaflet?

Question

I try to use the following function to add polygons to my Leaflet map.

function polygon(geojson)
{
    alert(geojson); // alert for testing
    layer = L.geoJson(geojson);
    layer.addTo(map);
}

When I call the above function with the GeoJSON string I fetched from a PostGIS table using ST_AsGeoJSON(), the following string is alterted.

{"type":"Polygon","coordinates":[[[10,370],[20,380],[30,410],[-10,395],[10,370]]]}

But no polygon shows up on the map. As far as I can say, there is no error message. And I can definitely say that a polygon with the points 10 370, 20 380, 30 410, 10 395 shows up on the map, if I insert it using L.polygon.

What's wrong?

Answer 1

you can do it using this SQL code

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(lg.geom)::json As geometry, 
    (
    select row_to_json(t) 
    from (select FIELD_1, FIELD_2, FIELD_N) t
    )
    As properties
   FROM MY_TABLE_OR_VIEW As lg   ) As f )  As fc;

Easy and will work!!!

Answer 2

According to the docs, ST_AsGeoJSON() returns the geometry as a GeoJSON element. It does not return a whole GeoJSON object. Here is an example of a full GeoJSON object:

{
    "type": "Feature",
    "id": "OpenLayers.Feature.Vector_314",
    "properties": {},
    "geometry": {
        "type": "Point",
        "coordinates": [
            97.03125,
            39.7265625
        ]
    },
    "crs": {
        "type": "name",
        "properties": {
            "name": "urn:ogc:def:crs:OGC:1.3:CRS84"
        }
    }
}

The .geoJson method expects GeoJSON whereas ST_AsGeoJSON() only returns the geometry element and not the whole GeoJSON, that's why the method fails and nothing is plotted.

Answer 3

Adding on what R.K. is saying, your best bet is to refine your postgis query to build the entire GeoJSON object.

This should explain how: http://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html