roundingbounding-boxazure-sql-serverazure-mapssqlgeography

MSSQL geography::STIntersects returns incorrect results when on or very close to a bounding box edge. What is the correct way to "Fix" this

Consider the following (derived from SQL Geography point inside polygon not returning true on STIntersect (but returns true using Geometry))

-- TEST CASE1
DECLARE @point1 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (0 -0.0000001)', 4326)
DECLARE @polygon1 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)
SELECT @polygon1.STIntersects(@point1), @point1.STIntersects(@polygon1), @point1.STDistance(@polygon1)

-- TEST CASE2
DECLARE @point2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)
SELECT @polygon2.STIntersects(@point2), @point2.STIntersects(@polygon2), @point2.STDistance(@polygon2)

In the above:

How should I handle the rounding errors of geography points.

Backgound

Test 2 - is a real-world issue. The bounding box is derived from a point cluster atlas.data.BoundingBox.fromPositions(coords) (azure maps). When the items are selected from SQL using the bounding box there are items missing from the result set even though these are the same points used to create the bounding box!

If I need, I could expand the bounding box slightly or select data where distance < 2 metres. I can't help but think that I'm missing a trick somewhere and my google-foo is lacking.

I've tried Range(xx) no luck (or not understood)

Perhaps the expectation is that you "just" need to +/-EPSILON?

Ideas welcome x x x

Solution

  • I came across this before with SQL when using bounding boxes. The issue comes down to geography object vs geometry object. When using a geography, the lines between points follow a geodesic path (a path that follows the curvature of the earth). So, when using geography, a "bounding box" actually represents an area that looks more like the following image:

    enter image description here

    This is why Geometry has a STEnvelope method and Geography doesn't. When working with Geography, a bounding circle is more accurate.EnvelopeCenter, EnvelopeAngle methods). You would be surprised to know that most major map platforms actually store their spatial data as geometries for the purpose of rendering and queries based on square bounding boxes.

    There are a couple of solutions to your situation;

    Solution 1

    Since your query shape is coming from a Mercator map, using Geometry instead of Geography will work much more accurately (it is also faster). One caveat is that it won't work well if you need to support shapes that cross the anti-Merdian (-180/180 longitude). A second caveat is that all distances will be in degrees, not meters. Here is what this would look like:

    -- TEST CASE1
DECLARE @point1 GEOMETRY = GEOMETRY::STGeomFromText('POINT (0 -0.0000001)', 4326)
DECLARE @polygon1 GEOMETRY = GEOMETRY::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)
SELECT @polygon1.STIntersects(@point1), @point1.STIntersects(@polygon1), @point1.STDistance(@polygon1)

-- TEST CASE2
DECLARE @point2 GEOMETRY = GEOMETRY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOMETRY = GEOMETRY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)
SELECT @polygon2.STIntersects(@point2), @point2.STIntersects(@polygon2), @point2.STDistance(@polygon2)

    Here is what the output looks like:

    enter image description here

    To get distances in meters rather than degrees, you can convert the geometry to geography. Note that this will not align with what your see visually on a Mercator map but will be spatially accurate. Here is an example:

    DECLARE @point2 GEOMETRY = GEOMETRY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOMETRY = GEOMETRY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)

DECLARE @polygonGeo2 GEOGRAPHY = GEOGRAPHY::STGeomFromWKB(@polygon2.STAsBinary(), 4326)

SELECT GEOGRAPHY::STGeomFromWKB(@point2.STAsBinary(), 4326).STDistance(@polygonGeo2)

    Converting between Geometry and Geography using well known binary is extremely fast.

    Note that if you do this with point1/polygon1 the result is 0 because the distance in meters is extremely small (~1cm).

    Solution 2

    Convert your data to Geometry may not be desired if you have other spatial queries you plan to make, such as finding all points within a certain distance in meters. Three ways to handle this,

    1. Add a second column for a geometry version of your points and use solution 1 with that column. The downside to this is that the database ends up being bigger and you end up having to do more work keeping things in sync if a rows point changes location.
    2. Convert the points to geometry on the fly as part of the query with a geometry polygon.
    -- TEST CASE2
DECLARE @point2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOMETRY = GEOMETRY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)

SELECT @polygon2.STIntersects(GEOMETRY::STGeomFromWKB(@point2.STAsBinary(), 4326)), 
    GEOMETRY::STGeomFromWKB(@point2.STAsBinary(), 4326).STIntersects(@polygon2), 
    GEOMETRY::STGeomFromWKB(@point2.STAsBinary(), 4326).STDistance(@polygon2)
    1. Calculate the geography bounding circle for the points of your bounding box by leveraging EnvelopeAggregate.
    DECLARE @point2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)

Declare @env Geography = Geography::EnvelopeAggregate(@polygon2);

SELECT @point2.STIntersects(@env), @point2.STDistance(@env)

    This would likely be the easiest and fastest method, but you will want to test this some more as I just came across this solution.