David Lexa David Lexa - 2 years ago 194
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 Source
   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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download