links: [[Postgres MOC]] --- # Guide to use PostGis ## How to add location to existing table ```sql alter table deals add column location geography(point); ``` The new thing in above syntax is `geography(point)` which comes when you enable postgis extension. ## Insert location to table ```sql insert into deals (title, dealer, location) values ('Brand Factory 12k Offer purchase', '123', 'SRID=4326;POINT(77.6852708 12.9090924)') ``` One important thing here is PostGis accepts longitude first and then latitude. We have to add `SRID=4326` to make Postgres understand it's a **Point** ## Query Rows based on distance ```sql select * from deals where ST_DWithin(deals.geolocation, 'SRID=4326;POINT(77.685367 12.9253292)', 1500); ``` Here we are querying the rows in deals with the (user location) Point provided and distance in meters ## Convert PostGis Point to readable format ```sql create or replace function public.nearby_deals(longitude float, latitude float, radius int) returns table(id int, title text, description text, dealer uuid, geolocation geography(point), wkt text, location geometry) as $body$ select deals.id, deals.title, deals.description, deals.dealer, deals.geolocation, ST_AsText(deals.geolocation), ST_AsGeoJSON(deals.geolocation) from deals where ST_DWithin(deals.geolocation, ST_SetSRID(ST_MakePoint(longitude, latitude),4326), radius) $body$ language sql; ``` Here we are using Postgres function to return record --- tags: #postgis