SQL Server STIntersects Query Returns false positives

Question

The problem

STIntersects is returning false positives

The scenario

1. I draw a polygon representing a “London Congestion Charge” zone using GoogleMaps and save the coordinates to the database in a geography field
2. I get a polyline from Google Directions API for a route
3. I check if the polyline intersects with any of the zone I have stored.

The issue

1. A route goes nowhere near London (avoids it by 50 miles) yet STIntersects is showing an intersection for the London Congestion Charge polygon in central London.
2. I know the polyline is fine as I can render this on a Google map and see it. I also know this is not the problem (see later)
3. The polygon coordinates appear to be absolutely fine (although I suspect something is amiss here) as when I render the polygon visually on a Google map it displays correctly.

I believe the polygon coordinates are a contributor is because I have tried the following:

1. I have created two completely separate instances of the application and database
2. I have drawn the London Congestion Charge zone more specifically (more points) in one than the other
3. Although visually they look very similar (they do both cover the London Congestion Charge area) the version with more points works fine. A polyline can pass right next to it without a reported intersection whilst the version with less points returns a false positive for polylines literally miles (50 odd) away from it.

How can this be?

Here's how to recreate the problem.

Add the problem polygon to a geography database field:

INSERT INTO Polygons (ZoneName,Coordinates) VALUES ('IDoNotWork',geography::STGeomFromText(‘POLYGON ((51.48822 -0.13046, 51.49763 -0.09613, 51.51045 -0.07828, 51.52242 -0.09064, 51.52583 -0.1181, 51.52071 -0.15106, 51.50361 -0.14626, 51.48822 -0.13046)) ', 4326))

Add the working polygon to a geography database field:

INSERT INTO Polygons (ZoneName,Coordinates) VALUES ('IWork',geography::STGeomFromText('POLYGON ((51.51964 -0.16788, 51.5222 -0.15518, 51.52412 -0.14076, 51.52925 -0.12463, 51.52968 -0.12222, 51.5284 -0.11776, 51.53139 -0.11158, 51.52968 -0.09853, 51.52626 -0.08892, 51.52541 -0.08377, 51.51665 -0.07244, 51.50596 -0.07622, 51.49592 -0.08411, 51.49506 -0.09544, 51.49613 -0.09956, 51.49271 -0.103, 51.48844 -0.11227, 51.48673 -0.12119, 51.48758 -0.126, 51.49592 -0.14145, 51.49806 -0.14351, 51.49934 -0.14729, 51.50169 -0.14969, 51.50404 -0.14969, 51.51323 -0.15793, 51.51473 -0.16136, 51.51964 -0.16788))', 4326))

Run this polyline check using stintersects SQL Query:

 [Select ZonePolygonId, ZoneName  FROM dbo.Polygons  
  WHERE Coordinates.STIntersects(geography::STGeomFromText('LINESTRING (51.30835 -0.7802, 51.30856 -0.78056, 51.30865 -0.78066, 51.3093 -0.77954, 51.31049 -0.77735, 51.31125 -0.77586, 51.31149 -0.77572, 51.31149 -0.77573, 51.31149 -0.77576, 51.31151 -0.77578, 51.31153 -0.7758, 51.31156 -0.77581, 51.31161 -0.77574, 51.31161 -0.77564, 51.31159 -0.77561, 51.31172 -0.77532, 51.3122 -0.77494, 51.31275 -0.77437, 51.31331 -0.77363, 51.31351 -0.77179, 51.31395 -0.7678, 51.31424 -0.76718, 51.31496 -0.76648, 51.31609 -0.76546, 51.31621 -0.76533, 51.3154 -0.76344, 51.31424 -0.76016, 51.31237 -0.75621, 51.31193 -0.75574, 51.31045 -0.75527, 51.31023 -0.75506, 51.31001 -0.75484, 51.30957 -0.75449, 51.30903 -0.75363, 51.30864 -0.75257, 51.30846 -0.75148, 51.30862 -0.75088, 51.30865 -0.75022, 51.30861 -0.74951, 51.30936 -0.74848, 51.30971 -0.7482, 51.30979 -0.74818, 51.31035 -0.74823, 51.31157 -0.74911, 51.31311 -0.75048, 51.31361 -0.75105, 51.31429 -0.75224, 51.31464 -0.75328, 51.31449 -0.75371, 51.31447 -0.75439, 51.3147 -0.75463, 51.31561 -0.75462, 51.31654 -0.75584, 51.31702 -0.75665, 51.31764 -0.75776, 51.31778 -0.75856, 51.31764 -0.75913, 51.31705 -0.75958, 51.31669 -0.75948, 51.31641 -0.75906, 51.31629 -0.75852, 51.31638 -0.75767, 51.31687 -0.75598, 51.319 -0.74976, 51.3211 -0.74501, 51.32512 -0.73723, 51.32815 -0.73219, 51.33599 -0.71895, 51.33954 -0.71249, 51.34141 -0.709, 51.34306 -0.70635, 51.34485 -0.70398, 51.34605 -0.70264, 51.34792 -0.70054, 51.34854 -0.69965, 51.34963 -0.69756, 51.35113 -0.69318, 51.35289 -0.6881, 51.35592 -0.68078, 51.3575 -0.67668, 51.35856 -0.67201, 51.35893 -0.66851, 51.35895 -0.66475, 51.35863 -0.66067, 51.3586 -0.6577, 51.3592 -0.65349, 51.35984 -0.65129, 51.36167 -0.64633, 51.36357 -0.64179, 51.36563 -0.63772, 51.36673 -0.63515, 51.36762 -0.63231, 51.36933 -0.62452, 51.37112 -0.61722, 51.37285 -0.61133, 51.37588 -0.60193, 51.37759 -0.59701, 51.37887 -0.59423, 51.38018 -0.59206, 51.38109 -0.59086, 51.38306 -0.58805, 51.3844 -0.58548, 51.38549 -0.58273, 51.3879 -0.57429, 51.39021 -0.56749, 51.39183 -0.56344, 51.39364 -0.55938, 51.39781 -0.55101, 51.3987 -0.54861, 51.40083 -0.5416, 51.40187 -0.53944, 51.40233 -0.53786, 51.40221 -0.53679, 51.40197 -0.53619, 51.40156 -0.53571, 51.40091 -0.53546, 51.39954 -0.53593, 51.39874 -0.53593, 51.39483 -0.53373, 51.39255 -0.53166, 51.39073 -0.52946, 51.38941 -0.52744, 51.38612 -0.52151, 51.38435 -0.5183, 51.38173 -0.51445, 51.38053 -0.51309, 51.3791 -0.51178, 51.37656 -0.50984, 51.37211 -0.50772, 51.36923 -0.50599, 51.36738 -0.50448, 51.3657 -0.50273, 51.36104 -0.49779, 51.35756 -0.49448, 51.35308 -0.49025, 51.35079 -0.48773, 51.34962 -0.48671, 51.34841 -0.48595, 51.34731 -0.4855, 51.34621 -0.48524, 51.34347 -0.48524, 51.33955 -0.48504, 51.33643 -0.48421, 51.33345 -0.48275, 51.3326 -0.48214, 51.33031 -0.47973, 51.32904 -0.47789, 51.32809 -0.47627, 51.32686 -0.47351, 51.32576 -0.47025, 51.3253 -0.46794, 51.32478 -0.46416, 51.3246 -0.46203, 51.32419 -0.45855, 51.32363 -0.45568, 51.32189 -0.44907, 51.32012 -0.44432, 51.31799 -0.44002, 51.31476 -0.43285, 51.31141 -0.42433, 51.30819 -0.41523, 51.30656 -0.40764, 51.30599 -0.40284, 51.30596 -0.40107, 51.30618 -0.39735, 51.30672 -0.39385, 51.30805 -0.38844, 51.31035 -0.38077, 51.31106 -0.37774, 51.31221 -0.37184, 51.31268 -0.36825, 51.31309 -0.36135, 51.31342 -0.35522, 51.31388 -0.35235, 51.31495 -0.34771, 51.31538 -0.34592, 51.31579 -0.34315, 51.31591 -0.34011, 51.31584 -0.33885, 51.31525 -0.33478, 51.31431 -0.33159, 51.31325 -0.32916, 51.31139 -0.32613, 51.31016 -0.32482, 51.30751 -0.3231, 51.30535 -0.32177, 51.30293 -0.31986, 51.3009 -0.31783, 51.29883 -0.31529, 51.29745 -0.31319, 51.2963 -0.31104, 51.29482 -0.30718, 51.29404 -0.30411, 51.29363 -0.30174, 51.29295 -0.29387, 51.29221 -0.28796, 51.29157 -0.28449, 51.29051 -0.28037, 51.28878 -0.27562, 51.28658 -0.27121, 51.28423 -0.26751, 51.28255 -0.26529, 51.28081 -0.26334, 51.27846 -0.26136, 51.27549 -0.25953, 51.27328 -0.25752, 51.27205 -0.25605, 51.27061 -0.25378, 51.2676 -0.2475, 51.26259 -0.23699, 51.26043 -0.23171, 51.25895 -0.22669, 51.25802 -0.22171, 51.25763 -0.21622, 51.25767 -0.21098, 51.25803 -0.20457, 51.2589 -0.19782, 51.26034 -0.19159, 51.26217 -0.18624, 51.2633 -0.18369, 51.2648 -0.18087, 51.26605 -0.17803, 51.2669 -0.17554, 51.26772 -0.17221, 51.26832 -0.16892, 51.26872 -0.16537, 51.26884 -0.16242, 51.26878 -0.15998, 51.26851 -0.15774, 51.26813 -0.15591, 51.26703 -0.15235, 51.26596 -0.14801, 51.26533 -0.14369, 51.26509 -0.14025, 51.26504 -0.13716, 51.2649 -0.13356, 51.26499 -0.13076, 51.26479 -0.12965, 51.26384 -0.12792, 51.26278 -0.12626, 51.26227 -0.1258, 51.26159 -0.12549, 51.25924 -0.12558, 51.25727 -0.12505, 51.256 -0.12446, 51.25252 -0.12246, 51.24852 -0.11968, 51.24723 -0.11871, 51.24592 -0.11796, 51.24433 -0.11742, 51.24215 -0.11729, 51.23967 -0.11781, 51.23765 -0.11795, 51.23565 -0.11761, 51.23296 -0.11644, 51.23095 -0.11565, 51.22913 -0.11552, 51.22705 -0.11604, 51.2251 -0.11722, 51.2222 -0.11987, 51.21947 -0.12175, 51.21824 -0.12238, 51.21634 -0.12311, 51.21022 -0.12521, 51.2047 -0.12771, 51.20236 -0.12826, 51.19991 -0.12811, 51.1963 -0.12694, 51.1942 -0.12651, 51.19164 -0.12632, 51.18754 -0.12662, 51.17548 -0.12758, 51.16975 -0.12821, 51.16807 -0.12865, 51.16575 -0.12967, 51.16343 -0.13138, 51.16195 -0.13251, 51.16026 -0.13344, 51.15921 -0.13359, 51.15887 -0.13344, 51.15859 -0.13365, 51.15846 -0.13406, 51.15858 -0.13506, 51.15882 -0.13589, 51.15932 -0.13663, 51.15972 -0.1396, 51.15992 -0.14264, 51.15995 -0.14657, 51.15966 -0.15095, 51.15945 -0.15322, 51.15919 -0.15372, 51.15901 -0.15393, 51.15898 -0.15471, 51.15922 -0.15531, 51.15932 -0.15659, 51.15946 -0.16097, 51.16001 -0.1644, 51.16084 -0.16722, 51.16207 -0.17094, 51.16209 -0.17196, 51.16198 -0.17233, 51.16201 -0.17286, 51.16231 -0.17305, 51.16317 -0.17418, 51.16347 -0.17477, 51.16346 -0.17498, 51.16343 -0.17535, 51.16365 -0.1754, 51.16441 -0.17635, 51.16466 -0.17715, 51.1643 -0.18029, 51.16371 -0.18437, 51.16351 -0.18481, 51.16263 -0.18552, 51.16175 -0.18677, 51.16077 -0.18832, 51.16065 -0.18834, 51.16056 -0.18846, 51.15994 -0.18793, 51.15882 -0.18748, 51.15811 -0.18723, 51.15789 -0.18702, 51.15826 -0.18398)', 4326).MakeValid())>0

You’ll see the 'IDoNotWork' record is selected when it really should not be.

Any help would be greatly appreciated.

Here's the exact same coordinates plotted as a polyline. You can see they go no where near the zone.

Answer 1

You have a ring orientation problem with your "IDoNotWork" geography instance. Specifically, the order in which you specify the points (clockwise vs counterclockwise) matters. For the IDoNotWork instance, you defined an area that is essentially Earth-sized (minus a tiny hole in London). How did I determine this? I selected the data and looked at the spatial results tab in SQL Server Management Studio. For this particular geography instance, the bounding box was (-90, 90, -180, 180). Whenever I see that, it's a good indication to me that the order of the points in the polygon was reversed from what was intended. There's a "right-hand" rule to defining the points. That is, if you were to drive around the area you want in a car always keeping the polygon itself on the right side of your car, you'll be traversing the points in the correct order.

I'm guessing that you're using SQL 2012 since this would have been an error in SQL 2008 (you couldn't have a geography instance that spanned hemispheres there). Since that's the case, there's a ReorientObject() method you can call that will re-order the points for you. Perhaps when you load you can use a heuristic (i.e. "the area of this polygon is way bigger than it should be") and if it fails the heuristic call ReorientObject on it.

TL;DR:: If you reverse the order in which you define the points (see below), you should get more of what you're looking for.

INSERT INTO Polygons (ZoneName,Coordinates) VALUES ('IDoNotWork2',geography::STGeomFromText('POLYGON ((
    51.48822 -0.13046 
    , 51.50361 -0.14626
    , 51.52071 -0.15106
    , 51.52583 -0.1181
    , 51.52242 -0.09064
    , 51.51045 -0.07828
    , 51.49763 -0.09613
    , 51.48822 -0.13046)) '
, 4326))