[GIS] Error parsing GeoJson – parse error – invalid geometry

postgispostgis-2.2

I'm trying to parse GeoJSON but getting a parse error. I've validated the GeoJSON using http://geojsonlint.com/ and http://geojson.io/.

Using PostGIS 2.2.2 r14797, this is my SQL command:

SELECT st_asgeojson('
{
"type": "Feature",
"properties": {
     "GEO_ID": "0500000US01001",
     "STATE": "01",
     "COUNTY": "001",
     "NAME": "Autauga",
     "LSAD": "County",
     "CENSUSAREA": 594.436000
     },
"geometry":
      {
           "type": "Polygon",
           "coordinates": [
                [
                     [ -86.917595, 32.664169 ],
                     [ -86.816574, 32.660117 ],
                     [ -86.713390, 32.661732 ],
                     [ -86.714219, 32.705694 ],
                     [ -86.413116, 32.707386 ],
                     [ -86.411172, 32.409937 ],
                     [ -86.444721, 32.399841 ],
                     [ -86.455616, 32.405807 ],
                     [ -86.462497, 32.378135 ],
                     [ -86.492772, 32.361587 ],
                     [ -86.496774, 32.344437 ],
                     [ -86.532531, 32.338775 ],
                     [ -86.542537, 32.363517 ],
                     [ -86.581873, 32.375019 ],
                     [ -86.595335, 32.361345 ],
                     [ -86.614841, 32.374266 ],
                     [ -86.613453, 32.398584 ],
                     [ -86.619812, 32.406474 ],
                     [ -86.653419, 32.397247 ],
                     [ -86.655597, 32.376147 ],
                     [ -86.683537, 32.353395 ],
                     [ -86.711337, 32.360767 ],
                     [ -86.719028, 32.372059 ],
                     [ -86.717897, 32.402814 ],
                     [ -86.727181, 32.404497 ],
                     [ -86.749981, 32.389105 ],
                     [ -86.778365, 32.394601 ],
                     [ -86.780447, 32.368600 ],
                     [ -86.773163, 32.340728 ],
                     [ -86.798268, 32.308632 ],
                     [ -86.816107, 32.309970 ],
                     [ -86.814912, 32.340803 ],
                     [ -86.812714, 32.365354 ],
                     [ -86.840846, 32.396434 ],
                     [ -86.838731, 32.419437 ],
                     [ -86.843503, 32.433117 ],
                     [ -86.857402, 32.438654 ],
                     [ -86.864856, 32.444280 ],
                     [ -86.870960, 32.465716 ],
                     [ -86.881754, 32.480556 ],
                     [ -86.882694, 32.491883 ],
                     [ -86.890037, 32.508091 ],
                     [ -86.901357, 32.528950 ],
                     [ -86.906106, 32.544756 ],
                     [ -86.899138, 32.564242 ],
                     [ -86.903935, 32.587564 ],
                     [ -86.909512, 32.609588 ],
                     [ -86.911149, 32.627764 ],
                     [ -86.917970, 32.645370 ],
                     [ -86.920236, 32.658868 ],
                     [ -86.917595, 32.664169 ]
                ]
           ]
       }
    }
');

I'm receiving the following error.

ERROR: parse error - invalid geometry
  Hint: "
     {
" <-- parse error at position 8 within geometry
  Where: SQL function "st_asgeojson" statement 1

Any idea what's wrong with our GeoJSON?

Best Answer

Firstly, you're using ST_AsGeoJSON and giving it a GeoJSON feature as input. ST_AsGeoJSON produces GeoJSON geometry from a binary geometry. What you probably want is ST_GeomFromGeoJSON, which takes a string representing GeoJSON geometry, and produces a binary geometry.

Note that ST_GeomFromGeoJSON only accepts the geometry part of the GeoJSON feature: the properties are another matter.

SELECT st_geomfromgeojson('{
    "type": "Polygon",
    "coordinates": [
        [
            [ -86.917595, 32.664169 ],
            [ -86.816574, 32.660117 ],
            [ -86.713390, 32.661732 ],
            [ -86.714219, 32.705694 ],
            [ -86.413116, 32.707386 ],
            [ -86.411172, 32.409937 ],
            [ -86.444721, 32.399841 ],
            [ -86.455616, 32.405807 ],
            [ -86.462497, 32.378135 ],
            [ -86.492772, 32.361587 ],
            [ -86.496774, 32.344437 ],
            [ -86.532531, 32.338775 ],
            [ -86.542537, 32.363517 ],
            [ -86.581873, 32.375019 ],
            [ -86.595335, 32.361345 ],
            [ -86.614841, 32.374266 ],
            [ -86.613453, 32.398584 ],
            [ -86.619812, 32.406474 ],
            [ -86.653419, 32.397247 ],
            [ -86.655597, 32.376147 ],
            [ -86.683537, 32.353395 ],
            [ -86.711337, 32.360767 ],
            [ -86.719028, 32.372059 ],
            [ -86.717897, 32.402814 ],
            [ -86.727181, 32.404497 ],
            [ -86.749981, 32.389105 ],
            [ -86.778365, 32.394601 ],
            [ -86.780447, 32.368600 ],
            [ -86.773163, 32.340728 ],
            [ -86.798268, 32.308632 ],
            [ -86.816107, 32.309970 ],
            [ -86.814912, 32.340803 ],
            [ -86.812714, 32.365354 ],
            [ -86.840846, 32.396434 ],
            [ -86.838731, 32.419437 ],
            [ -86.843503, 32.433117 ],
            [ -86.857402, 32.438654 ],
            [ -86.864856, 32.444280 ],
            [ -86.870960, 32.465716 ],
            [ -86.881754, 32.480556 ],
            [ -86.882694, 32.491883 ],
            [ -86.890037, 32.508091 ],
            [ -86.901357, 32.528950 ],
            [ -86.906106, 32.544756 ],
            [ -86.899138, 32.564242 ],
            [ -86.903935, 32.587564 ],
            [ -86.909512, 32.609588 ],
            [ -86.911149, 32.627764 ],
            [ -86.917970, 32.645370 ],
            [ -86.920236, 32.658868 ],
            [ -86.917595, 32.664169 ]
        ]
    ]
}');

Related Solutions

PostGIS – ERROR: Parse Error Invalid Geometry

I have no idea how you got the [] round what appears to be a textual representation of a LINESTRING, and there is a fairly strong clue in the error message. To remove them:

use regexp_replace to remove the superfluous [ and ] around your text string.
Use the Postgres concat operator to add a leading LINESTRING, so that you have a valid textual representation of a geometry.
Use ST_GeomFromText to convert to a geometry. You can either wrap this with ST_SetSRID, as you have, or use the second form, adding in the SRID to the text string -- it makes no practical difference.
Combine the ALTER TABLE statement with USING, as you have. This basically says change the data type in place, using, the updated geometry type in place of the existing text type.

For example, using a simplified example of what you have above, [(1 1, 2 2, 3 3)], as I am too lazy to type out yours:

SELECT ST_GeomFromText(concat('LINESTRING',
            regexp_replace('[(1 1, 2 2, 3 3)]', '(\[|\])','', 'g')));

returns a linestring -- if you wrap this in ST_AsText, you get,

LINESTRING(1 1,2 2,3 3)

Note, the (a|b) syntax means match a and/or b the \ before the [ and ] is an escape character, as [ and ] have special a meaning in regular expressions, and g is a global flag, meaning replace everywhere.

Putting this altogether:

ALTER TABLE area_grid_01 ALTER COLUMN coordinates TYPE Geometry(LINESTRING, 4326) 
USING ST_SetSRID(ST_GeomFromText(concat('LINESTRING', 
           regexp_replace(coordinates, '(\[|\])','', 'g'))), 4326);

ought to do what you want, using your coordinates column instead of hard-coded numbers as above. The last bit is untested, so there may be an extra/missing ( or ) somewhere.

Best Answer

Related Solutions

PostGIS – ERROR: Parse Error Invalid Geometry

Related Question