In my table I have three columns ID , long and lat how to find points within radius (ex., 20 km with center long and lat) without using PostGIS.
-
2Well, it's not impossible, but it's so oppressively difficult that you really ought to just use PostGIS. And be sure to name your "long" column "lon".– VinceCommented Apr 30, 2020 at 5:03
-
Furthermore, with PostGIS, you can answer the more useful question what the N nearest points are (independent of any arbitrary radius), and it has geographic indexes so that you can get these answers in a reasonable amount of time even for large tables.– CL.Commented Apr 30, 2020 at 9:46
-
This is also supported with a supplied module in vanilla Postgres: postgresql.org/docs/current/earthdistance.html– Nick Q.Commented Sep 1, 2023 at 17:28
Add a comment
|
1 Answer
You can use the haversine formula, using your current coordinates (target_latitude_deg/longitude) and the column name (latitude_deg, longitude_deg), both expressed in degrees
SELECT *
FROM myTable
WHERE acos(
sin(radians(target_latitude_deg))
* sin(radians(latitude_deg))
+ cos(radians(target_latitude_deg))
* cos(radians(latitude_deg))
* cos( radians(target_longitude_deg)
- radians(longitude_deg))
) * 6371 <= 20;
