Wednesday 22 April 2015

How to insert a GeoJSON polygon into a PostGIS table?


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



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

No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...