ERROR: invalid GeoJson representation

Question

the GeoJSON Spec show,

{
  "type": "Feature",
  "geometry": {
    "type": "Point",
    "coordinates": [125.6, 10.1]
  },
  "properties": {
    "name": "Dinagat Islands"
  }
}

But when I wrap that in a call to ST_GeomFromGeoJSON, like this,

SELECT ST_GeomFromGeoJSON($${                                                                                                                          
  "type": "Feature",                                                                                                                                   
  "geometry": {                                                                                                                                        
    "type": "Point",                                                                                                                                   
    "coordinates": [125.6, 10.1]                                                                                                                       
  },                                                                                                                                                   
  "properties": {                                                                                                                                      
    "name": "Dinagat Islands"                                                                                                                          
  }                                                                                                                                                    
}$$);

I get the error,

ERROR: invalid GeoJson representation

Answer 1

From the docs on ST_GeomFromGeoJSON

ST_GeomFromGeoJSON works only for JSON Geometry fragments. It throws an error if you try to use it on a whole JSON document.

This means you'll want to cut out just the value from the geometry in your GeoJSON document,

SELECT ST_AsText(geom)
FROM ST_GeomFromGeoJSON($${                                                                                                                          
  "type": "Point",                                                                                                                                   
  "coordinates": [125.6, 10.1]                                                                                                                       
}$$) AS geom;

     st_astext     
-------------------
 POINT(125.6 10.1)

ℹ ST_GeomFromGeoJSON is the inverse of ST_AsGeoJSON (which only produces the geometry section of GeoJSON document.)

Answer 2

Modern PostgreSQL + PostGIS accepts JSONB, the best datatype for this job.

WITH json_load AS (
 SELECT $${
  "type": "Feature",
  "geometry": {
    "type": "Point",
    "coordinates": [125.6, 10.1]
  },
  "properties": {
    "name": "Dinagat Islands"
  }
 }$$::JSONB j
)

SELECT 
   j->>'type' AS type,
   ST_GeomFromGeoJSON(j->'geometry') geom,
   j->'properties' AS props,
   (j->geometry->coordinates->1)::real AS longitude
FROM json_load;

You need only the geom column, all other columns are illustrative of the richness of JSONb expressions, and how is easy to cast to text datatype, to real, etc.

---

Note. At server-side, in the json_load part, you can use pg_read_file('/tmp/myFile.geojson')::jsonb to read files. Use something like chmod 666 /tmp/myFile.geojson before. A GeoJSON collenction of geometries can be exploded by jsonb_array_elements function.