1

I have table polygon_versions with geo_json field of type json.

DB fiddle: https://dbfiddle.uk/mzsp-4tg

How can I select all table polygon_versions records within radius of 20 kilometers from geometry point with exact combination of latitude / longtitude?

For example latitude is: 30.502390312011734 and longtitude is: 50.4947950787348

What SQL query I can execute in PostGIS database?

I have tried:

SELECT * FROM polygon_versions WHERE geo_json LIKE '%30.502390312011734%'

but it gives me the following error message:

ERROR:  operator does not exist: json ~~ unknown
LINE 19: SELECT * FROM polygon_versions WHERE geo_json LIKE '%30.5023...
Improve this question
4
  • 1
    If the data is in a JSON field, it's not yet in PostGIS. Please Edit the Question to place the actual SQL you have attempted in the body, not just a link.
    – Vince
    Commented Apr 3, 2023 at 17:55
  • 1
    Have a look at postgis.net/docs/ST_GeomFromGeoJSON.html
    – TomazicM
    Commented Apr 3, 2023 at 18:46
  • Thank you, @TomazicM it looks useful, but it did not help to solve my task completely. I'm steel investigating on it
    – Taras
    Commented Apr 4, 2023 at 11:15
  • Please edit your question and explain what you mean by "select all table polygon_versions records within radius of 20 kilometers from geometry point". How much of the polygon has to be within radius?
    – TomazicM
    Commented Apr 4, 2023 at 11:43

1 Answer 1

Reset to default
0

I have found the following solution. SQL query is:

SELECT * FROM polygon_versions WHERE  geo_json::jsonb @> '{"geometry":{"coordinates":[[[30.502390312011734,50.4947950787348]]]}}'
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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