3

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?

3
  • Do you want to store the geometry as a PostGIS geometry type, or just as text? – alphabetasoup Dec 22 '16 at 1:37
  • as either as long as I can retrieve it the same way I've put it in – Jad Freyha Dec 22 '16 at 18:54
  • @JadFreyha provide more input or pick an answer? – Evan Carroll Jan 4 '17 at 5:24
4

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)

0

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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