Search code examples
sql-servert-sqlpolygonpointgeography

SQL Server 2014 find a point in polygon

I do not know why SQL does not detect this point in my polygon

select geography::STGeomFromText('POLYGON ((51.561283 -0.199251
,51.562136 -0.045443
,51.468985 -0.045443
,51.472407 -0.197878
,51.561283 -0.199251))', 4326).STIntersects(geography::Point(51.519425,-0.127029, 4326))

My polygon is valid in result of STIsValid() and also is in right direction which SSMS display the polygon:

SSMS result of polygon

My point is also located in center of the polygon via Google Maps:

google map result

Here is the tools I used to detect if my points are correct: https://www.doogal.co.uk/polylines.php

With these data in text input:

51.561283,-0.199251
51.562136,-0.045443
51.468985,-0.045443
51.472407,-0.197878
51.561283,-0.199251
51.519425,-0.127029

The strange thing is that when I change lat and long to geography::Point function the result is true.

select geography::STGeomFromText('POLYGON ((51.561283 -0.199251
,51.562136 -0.045443
,51.468985 -0.045443
,51.472407 -0.197878
,51.561283 -0.199251))', 4326).STIntersects(geography::Point(-0.127029,51.519425, 4326))
Solution

  • The Text string representations of Geometry/Geography always uses the X Y convention for specifying coordinates. This means, when interpreted as geography, it defies the usual convention of putting Latitude first.

    So you need to use Long Lat in specifying your POLYGON in a string.

    You can see this in your SSMS output, where you can see that the polygon is located just south of the equator, somewhere ~51° East, and not somewhere in London.