Storing entire GeoJSON document in PostgreSQL/PostGIS?

Question

I am trying to insert GeoJSON document in my PostgreSQL, I cannot just simply use the data type Geometry and use the expression ST_GEOMFROMGEOJSON because my geoJSON looks like this, and I need to store it all, what do you suggest?

GeoJSON Document

{
"type": "FeatureCollection",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:EPSG::26916" } },
"features": [
{ "type": "Feature", "properties": { "h": -0.400000, "mean": null }, "geometry": { "type": "MultiPolygon", "coordinates": [ [ [ [ 337120.0, 4659412.460953020490706 ], [ 337121.0, 4659412.271286088041961 ], [ 337121.32677364279516, 4659412.0 ], [ 337121.581764082075097, 4659411.0 ], [ 337121.0, 4659410.470878113992512 ], [ 337120.0, 4659410.360407529398799 ], [ 337119.0, 4659410.916399526409805 ], [ 337118.937826662964653, 4659411.0 ], [ 337119.0, 4659411.488602316007018 ], [ 337119.127736570662819, 4659412.0 ], [ 337120.0, 4659412.460953020490706 ] ] ] ] } }
]}

There are more lines but I've cut it down. I have tried inserting it as text only, but then when I wanted to export the result it offered me CSV, XHTML,tabbed or XML and they all start putting random " marks. so I am guessing Text data type is not the way. I am sure someone out there tried inserting complete GeoJSON not only the geometry part.

bytea the .geojson file?

• alphabetasoup: Do you want to store the geometry as a PostGIS geometry type, or just as text?
• Jad Freyha: as either as long as I can retrieve it the same way I've put it in
• Evan Carroll: @JadFreyha provide more input or pick an answer?

Answer 1

You can't store this as GeoJSON, because there is no GeoJSON type. For reference, PostgreSQL native types, and then PostGIS provides a half dozen more. If you actually need to store the entire GeoJSON, I would use a jsonb type. (sample jacked from http://geojson.org/)

CREATE TABLE foo AS SELECT $$
 {
 "type": "Feature",
 "geometry": {
 "type": "Point",
 "coordinates": [125.6, 10.1]
 },
 "properties": {
 "name": "Dinagat Islands"
 }
 }
$$::jsonb AS geojson;

ST_GeomFromGeoJSON doesn't do what you want. It only takes the geometry object in the GeoJSON document: you say you have to store the entire thing. From there you can use the normal Postgres JSON functions to get to the geometry object, and even call ST_GeomFromGeoJSON on that object (rather than the whole document).

SELECT ST_AsEWKT(
 ST_GeomFromGeoJSON((geojson->'geometry')::text)
) FROM foo;

Note we do a little more work here casting the object to text, because ST_GeomFromGeoJSON doesn't currently read jsonb (the type that the -> operator returns)

Answer 2

I think you are wanting to convert GeoJSON data to PostGIS data? (not "store GeoJSON in PostGIS" - which would just store GeoJSON as plain text in a PostGIS database)

One way would be to use the ogr2ogr utility from GDAL/OGR. Eg,

ogr2ogr -f "PostgreSQL" PG:"dbname=aDBName user=postgres" "someJSONData.json"