SQL (Presto): How to pull locations within X mile radius of lat/lon pont

user_10869169 0
Viewed 647

I have a bunch of stores with their own Latitudes and Longitudes. I'm trying to pull data that is within a 2 mile radius of each point. Eg. How many stores are within 2 miles of each store. What is the best way to go about this?

I know rounding the lat/longs to the tenth (18.4, -66.2) can essentially give me 5 mile radius, but how do I get more granular. I'm not sure how granular rounding to the 100th (18.4, -66.21) gets me in terms of miles, but seems too small of a radius.

Date is stored as:

  • Store Name (string)
  • Latitude (double)
  • Longitude (double)
presto radius geospatial latitude-longitude sql
1 Answers

What you want is spatial join: https://prestodb.io/blog/2020/05/07/local-spatial-joins

Just join a table with itself, on condition that distance between two points is below 2 miles, and aggregate. Something like this:

SELECT 
  a.store_name, 
  (COUNT(*) - 1) AS neighbors    -- subtract 1 for self
FROM stores a JOIN stores b
ON ST_Distance(ST_Point(a.longitude, a.latitude), 
               ST_Point(b.longitude, b.latitude)) < 2 * 1609
GROUP BY a.store_name

Make sure you have a relatively fresh Presto installation, I think Presto got it optimized around end of 2018, and it would run as plain cross join before that - which would be too slow.

user_198800
Related