David Lexa David Lexa - 7 months ago 40
SQL Question

POSTGRESQL - Polygon function for geolocations

I have 2 tables User_places where I can see for each user their home location defined by 2 separate attributes: longitude and latitude.
And I have second table Neighborhoods with attribute 'Area', that defines each neighborhood as polygon - jsonb format - "[{"latitude":XXXXX,"longitude":YYYYY},{"latitude":ZZZZZ,"longitude":AAAAA},{"latitude":BBBBBB,"longitude":CCCCC},{"latitude":DDDDD,"longitude":EEEEE}]".

Does anybody know how to check whether particular user lives in given neighborhood in Postgresql?

Answer
SELECT
user_id,
ST_Contains(
ST_GeomFromText(
   (select 
   replace(replace(replace(replace(replace(area::text,']','))'),'[','POLYGON(('),'}',  ''),',"longitude":',' '),'{"latitude":','')
   from neighbourhoods
   limit 1), 4326)
   (ST_SetSRID(ST_MakePoint(latitude, longitude),4326))
) as polygon_check
from user_places