[GIS] Finding nearest point from POI in PostGIS

point-of-interestpostgis

I have Point of Interest data in MySQL. This are the fields

latitude,longitude,address,types

in which I have stored the data. Now i want to import it into PostGIS Database. But does it required geometry of this point??

which command is to be used to find nearest point with radius or distance ?

Best Answer

Bring your data into PostgreSQL with something like mysql_fdw, or other methods.

Once you have this table, add a geography column, and populate the new column:

ALTER TABLE places ADD COLUMN geog geography(Point,4326);
UPDATE places SET geog = ST_MakePoint(longitude, latitude);

Now select the nearest 10 places that are within 100 kms:

SELECT places.*, ST_Distance(geog, poi)/1000 AS distance_km
FROM places,
  (select ST_MakePoint(-90,47)::geography as poi) as poi
WHERE ST_DWithin(geog, poi, 100000)
ORDER BY ST_Distance(geog, poi)
LIMIT 10;
Related Question