In SQL Server how can I return values in one table that are less than 20 miles from a coordinate?

Question

This is a really interesting problem I have been struggling with all day. I have a table with an ID, Latitude, and Longitude of a location. This is a subset of locations from a larger set of locations.

What I am trying to do is use this subset of locations and, for each location, return locations within 20 miles from the larger set using air distance. My issue is not with calculating the air distances, that is working, my issue is with how to operate on each row like a For loop but with a set-based method.

So let's say this is my subset table, LocationSubset

+----+---------+----------+
| ID |   Lat   |    Lon   |
+----+---------+----------+
| 1  | 41.0575 | -92.1364 |
+----+---------+----------+
| 2  | 47.0254 | -92.5723 |
+----+---------+----------+
| 3  | 38.9897 | -88.7623 |
+----+---------+----------+

And I am looking towards the larger table, Locations

+----+---------+-----------+
| ID |   Lat   |    Lon    |
+----+---------+-----------+
| 1  | 41.0575 | -92.1364  |
+----+---------+-----------+
| 2  | 47.0254 | -92.5723  |
+----+---------+-----------+
| 2  | 38.9897 | -88.7623  |
+----+---------+-----------+
| 4  | 36.2137 | -91.6528  |
+----+---------+-----------+
| 5  | 39.2643 | -123.0073 |
+----+---------+-----------+
| 6  | 39.941  | -123.0073 |
+----+---------+-----------+
| 7  | 35.7683 | -91.6528  |
+----+---------+-----------+
| 8  | 45.8406 | -91.6528  |
+----+---------+-----------+

Let's assume, using the Haversine formula that locations 5 and 6 are within 20 miles of location 1 and locations 4 and 8 are within 20 miles of location 2.

I am looking to return something like this:

+----+------------+----------+
| ID | LocationID | Distance |
+----+------------+----------+
| 1  | 5          | 15.4     |
+----+------------+----------+
| 1  | 6          | 16       |
+----+------------+----------+
| 2  | 4          | 17.4     |
+----+------------+----------+
| 2  | 8          | 2.5      |
+----+------------+----------+

Each location could have zero to many locations within 20 miles and I am trying to capture this in another table.

I can add clarification if necessary. Thank you for your time.

you could try cross joining the 2 tables and using the function in one of these answers to filter the result http://stackoverflow.com/questions/13026675/calculating-distance-between-two-points-latitude-longitude — JamieD77, Oct 28 '15 at 20:12
Sorry that's a UDF, but it's a distance calculation that takes in two sets of latitudes and longitudes and returns the distance. Didn't realize it was a UDF until I looked closer. — Soulfire, Oct 28 '15 at 20:35
You might want to check out this post http://stackoverflow.com/questions/32157502/another-why-is-this-nearest-neighbor-spatial-query-so-slow/32160164#32160164 — Brad D, Oct 28 '15 at 21:00

Juan Carlos Oropeza · Accepted Answer · 2015-10-28T20:57:47.473

6

SELECT 
     LS.ID, 
     L.ID as LocationID, 
     MS_DISTANCE (LS.Lat , LS.Lon, L.Lat , L.Lon) as Distance
FROM LocationSubset LS
JOIN Locations L 
  ON MS_DISTANCE (LS.Lat , LS.Lon, L.Lat , L.Lon) < 20
 AND LS.ID <> L.ID    -- if you want remove comparasion with same object

But you are problably better using sql server spatial functions because those allow to use spatial index. Spatial Data

edited Oct 28 '15 at 20:57

answered Oct 28 '15 at 20:21

Juan Carlos Oropeza

47,252
12
78
118

My data set was a bit more complicated than I posted so it took a little effort to get it working but it appears to be running now. Thanks for your help! – Soulfire Oct 29 '15 at 16:16

score 2 · Answer 2 · answered Oct 28 '15 at 20:19

SELECT subtable.ID as ID1, subtable.Lat as Lat1, subtable.Lon AS Lon1,
    Locations.ID as ID2, Locations.Lat as Lat2, Locations.Lon AS Lon2,
    (CalculatedDistance using Lat1,Lon1,Lat2,Lon2) AS Distance
FROM subtable CROSS JOIN maintable
WHERE (CalculatedDistance using Lat1,Lon1,Lat2,Lon2)<20

Where subtable a query/view that generates your subtable

JamieD77 · Answer 3 · 2015-10-28T20:54:44.183

1

You probably want to exclude the records that match so use that in your Join then cross apply your distance function

SELECT ls.Id,
       l.Id LocationID,
       d.Distance
FROM   LocationSubset ls 
       JOIN Location l ON ls.Id <> l.Id
       CROSS APPLY (SELECT dbo.MS_DISTANCE(ls.Lat, ls.Lon, l.Lat, l.Lon) Distance) d
WHERE  d.Distance < 20

edited Oct 28 '15 at 20:54

answered Oct 28 '15 at 20:48

JamieD77

13,796
1
17
27

Does that require `MS_DISTANCE` to be a table-valued function? – Matt Gibson Oct 28 '15 at 20:49
no.. if it was, you'd have to use something like `Cross Apply (Select Distance From dbo.MS_DISTANCE(ls.Lat, ls.Lon, l.Lat, l.Lon))` – JamieD77 Oct 28 '15 at 20:50
I would use `LS.id <> L.id` instead. Because you can have different location on same place. Usually happen in my data when create multiple point in a Mall or a Building – Juan Carlos Oropeza Oct 28 '15 at 20:53
@JuanCarlosOropeza yeah you're right.. just noticed that.. much cleaner – JamieD77 Oct 28 '15 at 20:54
Thank you, I miss that step. Hope I dont get acuse of cheating by copying someone else answer :P – Juan Carlos Oropeza Oct 28 '15 at 20:55
Still dont understand why `cross apply` ?? Doesnt that calculate a table with all the combinations? – Juan Carlos Oropeza Oct 28 '15 at 20:59
it's really just the same as using the function in your select statement. but you can use the name in other places like Where.. or other table joins without typing out the function code every time – JamieD77 Oct 28 '15 at 21:02
@JuanCarlosOropeza it did add a Filter to the execution plan since it moves the filter to the `Where`, so I up voted your answer since it was shorter :) http://i.stack.imgur.com/tC4XG.png – JamieD77 Oct 28 '15 at 21:09

In SQL Server how can I return values in one table that are less than 20 miles from a coordinate?

3 Answers3