PostGis

links: Postgres MOC

---

Guide to use PostGis

How to add location to existing table

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

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

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

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