3

Assuming that if I run a query in my database like this 

select coordinate_json from location

It will return coordinate_json column with value like this (example for one row):

"{\"type\": \"Point\", \"coordinates\": [96.25518281124721, 4.335420234501272]}"

The problem is, every time I run this query to convert coordinate_json to geometry column :

select ST_GeomFromGeoJSON(coordinate_json) from location

I will get this error message:

ERROR:  function st_geomfromgeojson(json) does not exist

Maybe coordinate_json format, as I wrote above, is not match with GeoJSON format remind that there're backslash, so how can I convert that to Geometry Column?

Improve this question
8
  • Where did you get coordinate_json from. Usually, when you see slashes in front of the quotes it is for escaping for the purposes of sending down the wire. But, you certainly, don't want it to be saved in the db like that. – John Powell Nov 16 '16 at 12:13
  • 1
    This also works, select st_geomfromgeojson(replace('{\"type\": \"Point\", \"coordinates\": [96.25518281124721, 4.335420234501272]}', '\','')); , but is a bit hacky. You could run an update to get rid of all the back slashes on the column and then st_geomfromgeojson will work as expected. – John Powell Nov 16 '16 at 12:20
  • Long story, i got it from some python manipulating to return variable and for some reason, that variable was saved as geojson to databases.. – Faizalprabowo Nov 16 '16 at 12:22
  • Ok, so, if you run the replace function to remove the \, then your problem is solved? – John Powell Nov 16 '16 at 12:25
  • 1
    replace(coordinate_json, '"{', '{'); etc – John Powell Nov 16 '16 at 13:28
2

I'm unclear of exactly your problem so I'll cover both bases..

If you're working with literals

Use $$ dollar-quoted string literals. When this returns text..

SELECT $${"type": "Point", "coordinates": [96.25518281124721, 4.335420234501272]}$$;

And this returns JSON,

SELECT $${"type": "Point", "coordinates": [96.25518281124721, 4.335420234501272]}$$::json;

Then this will work,

SELECT ST_GeomFromGeoJSON($${"type": "Point", "coordinates": [96.25518281124721, 4.335420234501272]}$$);

If you're working with database columns of type json;

If you're getting this error 

ERROR:  function st_geomfromgeojson(json) does not exist

Then you're doing something like this

SELECT ST_GeomFromGeoJSON(jsonCol);

But, that ST_GeomFromGeoJSON doesn't accept type json (though it really should -- I'll patch it later). Instead you need to do this

SELECT ST_GeomFromGeoJSON(jsonCol::text);
Improve this answer
2
  • It will return this error : "ERROR: unknown GeoJSON type", maybe because of backslash and double quote in my json value, that why i tried to remove them – Faizalprabowo Nov 17 '16 at 6:26
  • @Faizalprabowo What you're saying doesn't add up: which one is the problem. Is the problem escaping, or is this already JSON in the database that you want to call ST_GeomFromGeoJSON() -- if so just cast it ::text – Evan Carroll Nov 17 '16 at 7:21
-1

Are you sure you have PostGIS installed correctly (the tip being function ST_GeomFromGeoJSON does not exist)?

This fails for me:

SELECT ST_GeomFromGeoJSON("{\"type\": \"Point\", \"coordinates\": [96.25518281124721, 4.335420234501272]}");

(with ERROR: syntax error at or near "type" )

but this works:

SELECT ST_GeomFromGeoJSON('{"type": "Point", "coordinates": [96.25518281124721, 4.335420234501272]}');
Improve this answer
1
  • Yes i 'm, It seem that the problem is on back slash and double quotes in my json field.. – Faizalprabowo Nov 16 '16 at 11:55
-3

This is my solution refer to @John Barça suggestion

SELECT ST_GeomFromGeoJSON(replace(replace(replace(coordinate_json, '\"', '"'),'"{','{'), '}"', '}')) FROM Table_name

Thanks @John Barça and @matej..

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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