postgis – Fix Invalid GeoJSON When Inserting Data to CartoDB Table from Leaflet

cartogeojsonleafletpostgis

I've been following along to this tutorial from CartoDB.

I create the GeoJSON using: 

var drawing = "'"+JSON.stringify(layer.toGeoJSON())+"'";

and then inserting using a custom SQL function which includes: 

_the_geom := ST_SetSRID(ST_GeomFromGeoJSON(_geojson),4326);

I get the error: Invalid GeoJSON representation. I've tried entering the sql my javascript produces directly into the CartoDB console and get the same error. Example below.

SELECT insert_data(
'{"type":"Feature","properties":{},
"geometry":{"type":"Point",
"coordinates":[-71.11295700073242,42.37896312335581]}}')

Reading the documentation on the function I read "ST_GeomFromGeoJSON works only for JSON Geometry fragments. It throws an error if you try to use it on a whole JSON document." Could the error I get be because of the empty properties object?

Best Answer

The ST_GeomFromGeoJSON really only expects to be fed the geometry portion of the object, so this works:

select st_astext(st_geomfromgeojson(
       '{"type": "Point",
         "coordinates": [-71.11295700073242,42.37896312335581]}'
));

So you'd want something more like this in your JS:

var drawing = "'"+JSON.stringify(layer.toGeoJSON().geometry)+"'";
Related Question