How to efficiently access Microsoft.Maui.Devices.Sensor.Locations in SQL Server-CodePudding

This is more a design question so please bear with me.

I have a system that stores locations consisting of the ID, Longitude and Latitude.

I need to compare the distance between my current location and the locations in the database a only choose ones that are within a certain distance.

I have the formula that calculates the distance between 2 locations based on the long/lat and that works great.

My issue is I may have 10 of thousands of locations in the database and don't want to loop through them all every time I need a list of locations close by.

Not sure what other datapoint I can store with the location to make it so I only have to compare a smaller subset.

Thanks.

CodePudding user response：

As was mentioned in the comments, SQL Server has had support for geospatial since (iirc) SQL 2008. And I know that there is support within .NET for that as well so you should be able to define the data and query it from within your application.

Since the datatype is index-able, k nearest neighbor queries are pretty efficient. There's even a topic in the documentation for that use case. Doing a lift and shift from that page:

DECLARE @g geography = 'POINT(-121.626 47.8315)';

SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address  
WHERE SpatialLocation.STDistance(@g) IS NOT NULL  
ORDER BY SpatialLocation.STDistance(@g);

If you need all the points within that radius, just omit the top clause.

CodePudding user response：

https://gis.stackexchange.com/ is a good place for in-depth advice on this topic.

A classic approach to quickly locating "nearby" values, is to "grid" the area of interest:
Associate each location with a "grid cell", where each cell is a convenient size. Pick a cell-edge-length such that most cells will hold a small number of values and/or that is similar to the distance range you typically query.

If cell edge is 1 km, and you need locations within 2 km, then get data from 5x5 cells centered at the "target" location.

This is guaranteed to include all data - 2 km from any location within the central cell.

Apply distance formula to each returned location; some will be beyond 2 km.

I've only done this in memory, not from a DB. I think you add two columns, one for X cell number, other for Y cell number.
With indexes on both of those. So can efficiently get a range of Xs by a range of Ys.
Not sure if a combined "X,Y" index helps or not.