TransWikia.com

How to get geometry in GeoJSON format using ST_GeomFromGeoJSON

Geographic Information Systems Asked on July 12, 2021

I am using PostgreSQL/PostGIS. I have the following JSON format

"{'type': 'Polygon', 
'coordinates': 
[[[6.69223420722764, 51.1329165124371], [6.69222819326484, 51.1329081193748], [6.69221180776184, 51.1329013203209], [6.69219149698336, 51.1329073049609],
  [6.69217949340067, 51.1329244260894], [6.69217956546811, 51.1329430447612], [6.69218526127669, 51.1329578171713], [6.692200130301, 51.1329606289999],
  [6.69220511560768, 51.1329581192089], [6.69221372090576, 51.1329518135339], [6.69222707128915, 51.132939215911], [6.69223420722764, 51.1329165124371]]]}"
  

and I would like to apply the function ST_GeomFromJSON() as shown below in the code, but I am getting the error mentioned in the error section below.
How can I fix this?

Code:

SELECT ST_GeomFromGeoJSON(
"{'type': 'Polygon', 
'coordinates': 
[[[6.69223420722764, 51.1329165124371], [6.69222819326484, 51.1329081193748], [6.69221180776184, 51.1329013203209], [6.69219149698336, 51.1329073049609],
  [6.69217949340067, 51.1329244260894], [6.69217956546811, 51.1329430447612], [6.69218526127669, 51.1329578171713], [6.692200130301, 51.1329606289999],
  [6.69220511560768, 51.1329581192089], [6.69221372090576, 51.1329518135339], [6.69222707128915, 51.132939215911], [6.69223420722764, 51.1329165124371]]]}")
  

Error:

NOTE: Identifier "{'type': 'polygon', 
    'coordinates': 
    [[[6.69223420722764, 51.1329165124371], [6.69222819326484, 51.1329081193748], [6.69221180776184, 51.1329013203209], [6.69219149698336, 51.1329073049609],
      [6.69217949340067, 51.1329244260894], [6.69217956546811, 51.1329430447612], [6.69218526127669, 51.1329578171713], [6.692200130301, 51.1329606289999],
      [6.69220511560768, 51.1329581192089], [6.69221372090576, 51.1329518135339], [6.69222707128915, 51.132939215911], [6.69223420722764, 51.1329165124371]]}" is changed to "{'type': 'polygon', 
    'coordinates': 
    [[[6.69223420722764, 51."]]}" will be shortened to "}".

ERROR: ERROR: Column "{'type': 'polygon', 
    'coordinates': 
    [[[6.69223420722764, 51."" does not exist.
LINE 2: "{'type': 'polygon', 

One Answer

PostgreSQL assumes strings wrapped in " to be object identifiers - names for tables etc., while string literals should either be wrapped in ', or *dollar-quoted ($$string$$) to allow for easy escaping of ' in the literal.

So your GeoJSON text should look like this:

SELECT ST_GeomFromGeoJSON(
  '{"type": "Polygon", 
    "coordinates": [[[6.69223420722764, 51.1329165124371], [6.69222819326484, 51.1329081193748], [6.69221180776184, 51.1329013203209], [6.69219149698336, 51.1329073049609],
  [6.69217949340067, 51.1329244260894], [6.69217956546811, 51.1329430447612], [6.69218526127669, 51.1329578171713], [6.692200130301, 51.1329606289999],
  [6.69220511560768, 51.1329581192089], [6.69221372090576, 51.1329518135339], [6.69222707128915, 51.132939215911], [6.69223420722764, 51.1329165124371]]]}
  ')
  

Correct answer by geozelot on July 12, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP