Search code examples
entity-framework-coreazure-sql-databaseazure-mapsnettopologysuite

Map point (in DB) within bounding box

This is for Entity Framework Core and an Azure SQL database.

I have a column in my record that is a NetTopologySuite.Geometries.Point. To perform a search for all records within N miles of a point, I do:

public static IQueryable<Organization> WhereWithinRange(this IQueryable<Organization> source, Point location, int distance)
{
    var meters = MiscUtils.MilesToMeters(distance);
    return source.Where(org => org.Address.Location.Distance(location) < meters);
}

I also, for my map display, want to get all points within the bounding box of the map. Is there an equivalent of Distance() that I can pass it a bounding box?

I think Within() may be what I am looking for. But if so, does it reduce to a SQL query (i.e., it's fast) and how do I define the bounding box I pass in?

Also where can I find documentation of the methods I can apply to Point that reduce down to native SQL queries?

Solution

  • This scenario can get tricky at times. If the query is processed within SQL and your data is stored as Geography type, there is Distance and Intersects methods (convert bounding box to polygon, the test to see if points intersect polygon), and all of this will be spatially accurate. If you instead try to do the same calculation using NetTopologySuite within in the client and not as a query, you will get very different results as it uses geometry calculations, not geospatial calculations. so doing a query like you have in your question will be spatially accurate if your data in the database is a Geography type. However, if you tried doing a calculation like Point1.Distance(Point2) in your code using the NetTopologySuite Point class, it will calculates the distance based on the units of the coordinates, in this case degrees, which wouldn't be accurate. If you want to calculate distances in code, and not via a query to the database, you will have to convert the projection of the points to one that uses meters. This is documented here: https://learn.microsoft.com/en-us/ef/core/modeling/spatial Alternatively you can add a function to your code to do this fairly simply calculation (better than loading a complex library). There are three main algorithms used for calculating distances between lat/lon coordinates:

    1. Great circle distance - Simply and fast calculation. https://stackoverflow.com/questions/67062622/calculate-geographic-distance-between-points-using-nettopologysuite#:~:text=You%20need%20to%20calculate%20great%20circle%20distance.%20NetTopologySuite,GreatCircleDistance%28double%20lon1%2C%20double%20lat1%2C%20double%20lon2%2C%20double%20lat2%29
    2. Haversine formula - A bit more complex, fairly fast and accurate, used by most web map platforms. https://rosettacode.org/wiki/Haversine_formula#C# (Change the earth radius to 6371, as that is the more widely used value by web maps).
    3. Vincenty's Formulae - This does an ellipsoid based calculation since the earth isn't really a sphere. Its more complex, but more spatially accurate, but may not usually used in most distance based queries in web apps. Is the Haversine Formula or the Vincenty's Formula better for calculating distance?

    I would recommend using Haversine formula for in code (client side) distance calculations.

    Point in Bounding Box queries

    If you need to do this as an EF query to SQL, then the standard method is to convert the bounding box to a polygon, then use the Intersects method to test to see if the point intersects the bounding box polygon. Note that in the Azure Maps Web SDK, there is the `` function you can use for bounding box to polygon conversion. The EF query would look something like this:

    public static IQueryable<Organization> WhereIntersectsPolygon(this IQueryable<Organization> source, Polygon polygon)
{
    return source.Where(org => org.Address.Location.Intersects(polygon));
}

    A big benefit of making your backend code take in a polygon instead of a bounding box, and converting your bounding box to polygon in the web app, is that you can very quickly add support for complex polygon searches in the future (e.g. let users use drawing tools to draw a polygon, or buffer a line/path to search within a set distance of that path).

    Also note, there is an Intersection method. It is possible to use that instead, but it will be much slower as it calculates the actual intersection point, where as the Intersects method just does a quick Boolean test.

    For in code/client-side calculations, unless you have data near the anti-Merdian (180/-180 longitude) or using bounding boxes that cross it, you can get away with a simple range check (is point longitude between min/max longitude of bounding box, similar for latitude).

    If the bounding box crosses the anti-Merdian, one method is to split the bounding box into two (in Azure Maps Web SDK you can use atlas.data.BoundingBox.splitOnAntimeridian function) and do a test on both bounding boxes. An alternative method I've used in the past is to convert the NetTopologySuite point to a SqlGeogrpahy type (write/read WKB is a fast conversion method), and then do an intersection test using that class.

    In general

    Make sure to have a spatial index setup on your database. This makes a huge difference in performance, especially on large datasets. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-spatial-index-transact-sql?view=sql-server-ver16

    Here is a great resource that cross references SQL server spatial capabilities with NetTopologySuite in EF: https://learn.microsoft.com/en-us/ef/core/providers/sql-server/spatial