[GIS] How to insert a GeoJSON polygon into a PostGIS table

geojsonpolygonpostgissrid

I need to insert a polygon from GeoJSON to my PostGIS table. This is how the SQL query looks like.

INSERT INTO tablename (name, polygon)
VALUES (
    'Name',
    ST_GeomFromGeoJSON(
        '{
            "type": "Polygon",
            "coordinates": [
                [7.734375,51.835777520452],
                [3.8671875,48.341646172375],
                [7.20703125,43.580390855608],
                [18.6328125,43.834526782237],
                [17.9296875,50.289339253292],
                [13.7109375,54.059387886624],
                [7.734375,51.835777520452]
            ]
        }'
    )
)

Unfortunately, I get an error message.

ERROR:  Geometry SRID (0) does not match column SRID (3857)

The GeoJSON is already in the right reference system. But this isn't specified. How do I specify the SRID in the GeoJSON? What does the GeoJSON need to look like?

Update: When I wrap the geometry created by ST_GeomFromGeoJSON with ST_SetSRID(..., 3857) it throws another error. In my view it doesn't seem that the geometry has a Z dimension.

ERROR:  Geometry has Z dimension but column does not

Best Answer

Taking a look at the source code of PostGIS I found out how it parses SRIDs. Here is the correct way to specify the SRID in GeoJSON.

The GeoJSON specification says that the coordinates of a polygon are an array of line strings. Therefore I had to wrap them with additional brackets.

{
    "type":"Polygon",
    "coordinates":
    [
        [
            [-91.23046875,45.460130637921],
            [-79.8046875,49.837982453085],
            [-69.08203125,43.452918893555],
            [-88.2421875,32.694865977875],
            [-91.23046875,45.460130637921]
        ]
    ],
    "crs":{"type":"name","properties":{"name":"EPSG:3857"}}
}

Related Solutions

[GIS] How to insert a bounding box or polygon in a PostGIS geometry field

You can use St_GeomFromText to add geometries in WKT format

UPDATE sites
SET  geometry = st_geomfromtext('POLYGON((0 0, 1 1, 2 2, 3 3, 0 0))') 
WHERE ID = 1123

PostGIS GeoJSON – Storing GeoJSON FeatureCollection in PostgreSQL

Assuming you have at least PostgreSQL version 9.3, you can use a few JSON functions and operators to extract the relevant parts of the GeoJSON specification required by ST_GeomFromGeoJSON to create geometries.

Try the following, where you can replace the JSON in the top part:

WITH data AS (SELECT '{ "type": "FeatureCollection",
    "features": [
      { "type": "Feature",
        "geometry": {"type": "Point", "coordinates": [102.0, 0.5]},
        "properties": {"prop0": "value0"}
        },
      { "type": "Feature",
        "geometry": {
          "type": "LineString",
          "coordinates": [
            [102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0]
            ]
          },
        "properties": {
          "prop0": "value0",
          "prop1": 0.0
          }
        },
      { "type": "Feature",
         "geometry": {
           "type": "Polygon",
           "coordinates": [
             [ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0],
               [100.0, 1.0], [100.0, 0.0] ]
             ]
         },
         "properties": {
           "prop0": "value0",
           "prop1": {"this": "that"}
           }
         }
       ]
     }'::json AS fc)

SELECT
  row_number() OVER () AS gid,
  ST_AsText(ST_GeomFromGeoJSON(feat->>'geometry')) AS geom,
  feat->'properties' AS properties
FROM (
  SELECT json_array_elements(fc->'features') AS feat
  FROM data
) AS f;

Finds three geometries. The geom column has the geometry object, and the gid is the feature number. The ST_AsText function shows the WKT equivalent of each geometry. I've also included the properties or attributes that can be defined for each geometry, as is shown in the specification.

 gid |                   geom                   |              properties
-----+------------------------------------------+--------------------------------------
   1 | POINT(102 0.5)                           | {"prop0": "value0"}
   2 | LINESTRING(102 0,103 1,104 0,105 1)      | {                                   +
     |                                          |           "prop0": "value0",        +
     |                                          |           "prop1": 0.0              +
     |                                          |           }
   3 | POLYGON((100 0,101 0,101 1,100 1,100 0)) | {                                   +
     |                                          |            "prop0": "value0",       +
     |                                          |            "prop1": {"this": "that"}+
     |                                          |            }
(3 rows)

You should assign an SRID for the geometry, using ST_SetSRID.

Or if you simply need a single heterogeneous GEOMETRYCOLLECTION, you can make it compact like this:

SELECT ST_AsText(ST_Collect(ST_GeomFromGeoJSON(feat->>'geometry')))
FROM (
  SELECT json_array_elements('{ ... put JSON here ... }'::json->'features') AS feat
) AS f;

GEOMETRYCOLLECTION(POINT(2565453.18267219 -3835048.65976031),LINESTRING(2727584.72197102 -3713449.19424187,2732476.69178127 -3992291.47342619),POLYGON((2442627.90254053 -3705499.95430853,2425506.00820465 -3886502.83728783,2555143.20817631 -3910962.68633909,2442627.90254053 -3705499.95430853)))

See also Creating GeoJSON Feature Collections with JSON and PostGIS functions from the Postgres OnLine Journal, which does the opposite.

Best Answer

Related Solutions

[GIS] How to insert a bounding box or polygon in a PostGIS geometry field

PostGIS GeoJSON – Storing GeoJSON FeatureCollection in PostgreSQL

Related Question