How to insert a GeoJSON polygon into a PostGIS table?

Question

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

Answer 1

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"}}
}

Answer 2

There are a couple of problems with your JSON.

1. Firstly, the coordinates should be an array of arrays.
2. Secondly, looking at the coordinates, it looks like the values are Latlong in a Geographic coordinate system, most probably EPSG:4326. That then needs to be transformed to EPSG:3857.

Once you correct these two things, you can insert the row, using the following SQL Query:

INSERT INTO "Parcels"("Name", the_geom)
    VALUES ('Corrected_Shape', 
    ST_TRANSFORM(ST_GeomFromGeoJSON('{
    "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:4326"}}
}'),3857));

If this does not work, (i.e. you are still getting the error with Z diemsnion), please update the question with the PostGis version, and the Create Statement of your table.

Answer 3

your geojson must have UTM values instead, you could transform that with Proj or other online tools, but you can do it easily and directly with postgis before inserting it into your table, try this (untested):

SELECT ST_AsText(ST_Transform(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]
            ]
        }
    ),4326),3857));

Answer 4

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]
            ]
        }'
    )
)

missing "'"