CODEWITHSUNDEEP
sql-servert-sqlgeography
Michael Samteladze
Michael Samteladze

Reputation: 1330

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.

Upvotes: 1

Views: 4539

Answers (2)

mdisibio
mdisibio

Reputation: 3540

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)

Upvotes: 2

Jaaz Cole
Jaaz Cole

Reputation: 3180

mdisbio's answer got me thinking about the ordering of arguments, so I had to reorder the points once I inverted the long/lat ordering per argument so as to adhere to the left hand rule, and it finally came out as intersecting.

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

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

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

SELECT @g.STIntersects(@p)
--1

So for Points, the ordering is Lat/Long, but seemingly for polygons, the ordering is interpreted as Long/Lat. Keep that in mind, and this should be a breeze.

Upvotes: 3

Related Questions