select and count devices at a location based on coordinates of both

By using STContains function of SQL geography it was possible to calculate if a set of coordinates is within an area , however this revealed that if last received coordinates were just outside the area than these coordinates do not match any location.

What would be an efficient way to display how many and which devices (out of 4000) are present in which location (out of 600) based on smallest distance between a static set of location coordinates and dynamic coordinates of devices?

Location table:

loc_name | loc_lat | loc_long | loc_geo_polygon | loc_geo_point

Device table:

dev_name | dev_lat | dev_long | dev_geo_point

Obvious solution is to query both tables for all data and loop through 4000 devices and measure distance to 600 locations returning smallest distance, but that creates 2.4 mil calculations every time I want to view data. Even if I remove from the list a device with already matched location, it is still quite a few calculations with limited potential for future growth.

Another way is to add a location_name column to Devices table and calculate the nearest location every time I receive coordinates from a device, that would be 100 devices x 600 locations calcs every minute, better than before, as it is a steady load rather than a burst.

Maybe there is a better way to approach this logically or with use of functions that I am not aware of?

Answer

I can think of a couple of ways. Both are going to leverage the previous solution’s ability to find the set of points that are within the specified tolerance of one of your fixed locations. Which, by extension, should give you a way to find the set of exceptional points.

Once that set is found, you can apply the “nearest neighbor” treatment outlined here. It’s going to look something like this:

select *
from ExceptionalPoints as e
cross apply (
   select top(1) l.*
   from Location as l
   order by l.loc_geo_point.STDistance(e.dev_geo_point)
) as nn

From the document, that should be able to leverage the existing spatial indexes on the tables.

The other approach I can think of is to create a Voronoi Tessellation using your Locations. You’d then add another column to your Locations table to store the Voronoi Cell associated with that location. At that point, it’d be a matter of joining the two tables much as in the original approach. Heck… this could even replace the original approach (using the Voronoi Cell instead of a fixed radius buffer around the location). But it comes with the caveat – calculating the tessellation is non-trivial. Granted, you only need to do it once (or whenever the set of locations changes).