Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 to specify the SRID in the GeoJSON? How have the GeoJSON 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
share|improve this question
    
I think you need to specify that the table has srid:4326, it looks that your table has srid:3857 but your geojson has long/latitude (ie. srid:4326 or WGS84) –  Gery May 16 '13 at 10:44
    
I want to use 3857. How does the GeoJSON have to look like then? –  danijar May 16 '13 at 10:46

3 Answers 3

up vote 8 down vote accepted

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"}}
}
share|improve this answer

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.

share|improve this answer
    
Why do you think the coordinates are not in EPSG:3857? –  danijar May 17 '13 at 9:30
1  
Because the units of EPSG:3857 are (pseudo)meters, and the origin is in the Atlantic Ocean. You wouldn't have 6 decimal accuracy with meters, and this data would lie in the Atlantic Ocean near the coast of Africa. –  Devdatta Tengshe May 17 '13 at 10:59
    
The coordinates are from input on a map and have a lot of decimals. For testing I drew an area in the Atlantic Ocean near Africa. But thanks to you I can improve the map to round the coordinates to whole meters. –  danijar May 17 '13 at 11:13
    
@danijar: Then it's all right. If these coordinates were in EPSG:4326, then it would lie over the eastern states of the US. –  Devdatta Tengshe May 17 '13 at 11:40

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));
share|improve this answer
    
So you suggest to convert the SRID over 4326 to 3857? Then I could directly try ST_Transform(ST_SetSRID(..., 4326), 3857), couldn't I? Why would this extra transformation step be necessary? –  danijar May 16 '13 at 11:18
    
I think you should test what you're asking, probably what you suggest is the only step you need, try it and post what you got –  Gery May 16 '13 at 11:50
    
This is what I get. ERROR: transform: couldn't project point (9.25253e-302 6.08985e+159 1.18576e-322): latitude or longitude exceeded limits (-14) –  danijar May 16 '13 at 12:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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