Search code examples
sql-servert-sqlgeography

TSQL Geography STGeomFromText Displaying Wrong Result

I'm trying to write a code which will tell me whether point is inside polygon or not

DECLARE @Latitude varchar(20) = '41.694110'
DECLARE @Longitude varchar(20) = '44.833680'
DECLARE @g geography;
DECLARE @p geography;

DECLARE @PolygonString varchar(500) = 'POLYGON((41.711921 44.658505, 41.851703 44.773175, 41.763158 44.972302, 41.654421 44.843083, 41.711921 44.658505))'

SET @g = geography::STGeomFromText(@PolygonString, 4326);
SET @p = geography::Point(@Latitude, @Longitude, 4326)

SELECT @g.STIntersects(@p)

It always return 0. Here is visual representation of scenario.

http://sbtutor.com/

Any ideas what is wrong with my code?

Thanks.

Solution

  • I am not an expert in the geospatial types, but can give you some thoughts. For the WKT string (the string you use to make the polygon), the pairs need to be long/lat rather than lat/long.

    That said, I still could not get your polygon to be recognized as valid as a geography because I did not apply the counter-clockwise rule that Jaaz pointed out later. So I used the geometry type which is not as restrictive, and it worked correctly.

    Using geometry for the polygon, the POINT has to be geometry as well. In which case, geometry::POINT() expects X,Y coordinates (lng/lat) instead of of the lat/lng pair used with the geography type.

    So, this will yield true on the intersect:

    DECLARE @Latitude float = 41.694110
DECLARE @Longitude float = 44.833680
DECLARE @g geometry = geometry::Point(@Longitude, @Latitude, 4326)
DECLARE @p geometry = geometry::STPolyFromText('POLYGON((44.658505 41.711921, 44.773175 41.851703, 44.972302 41.763158, 44.843083 41.654421, 44.658505 41.711921))', 4326);
SELECT @g.STIntersects(@p)