1

I am trying to get all the points inside the polygon and after a few tests I finally caught.

STIntersects always return 1 for all my entities even in cases when a point "visually" not in a polygon.

I have found posts about polygon creation direction and try it but it didnt't help me :)

So I've decided to create simple case:

  1. polygon with 4 vertexes somewhere in Russia (corners: 55 37, 56 38)
  2. one point inside this polygon
  3. and another one outside

here is a link to google map with points

and results of this test knocked me out of a rut

declare @cw geography, @ccw geography, @pointIn geography, @pointOut geography

-- counterclockwise direction from bottom left corner
set @ccw = geography::STPolyFromText (
'POLYGON((
55.0 37.0
,55.0 38.0
,56.0 38.0
,56.0 37.0
,55.0 37.0
))', 4326
)

-- clockwise direction from bottom left corner
set @cw = geography::STPolyFromText (
'POLYGON((
55.0 37.0
,56.0 37.0
,56.0 38.0
,55.0 38.0
,55.0 37.0
))', 4326
)

set @pointIn = geography::Point(55.5, 37.5, 4326)
set @pointOut = geography::Point(54, 36, 4326)


select @pointIn.STIntersects(@ccw) ccw, @pointIn.STIntersects(@cw) cw
-- result: 1   0

--here i should get inversed values, but it didnt happens
select @pointOut.STIntersects(@ccw) ccw, @pointOut.STIntersects(@cw) cw
-- result: 1   0

Why does this happen? I just can't understand what I am missing

I expect that the pointIn should return 1 when my polygon is small and return 0 when my polygon is whole world minus selected area, and pointOut should return 0 in first case and return 1 in the second one

But both points return 1 in counterclockwise polygon.

UPDATE

finaly My error was in the order of the input parameters of geography::STPolyFromText first should be lng and the second lat. and in extension the order is diferent msdn says: Point ( Lat, Long, SRID )

taburetkin
  • 313
  • 3
  • 17

1 Answers1

1

In SqlGeography instances, @ccw defines a hole (due to it's counter-clockwise order). As this hole is not within a polygon, it becomes a hole in the Globe. Put another way, you get a Polygon covering the entire globe, minus your @cw polygon.

Geography::Point also uses reverse coordinates to WKT and therefore I believe your latitude and longitude coordinates are the wrong way round (impossible to be absolutely sure) as only you know your data. Regardless, the attached image should enough to explain this. The "Orange" colour being @ccw, the blue being @cw, the rest is labelled.

If you have SSMS you can visualise it using the following query, note the points are buffered so you can see them.

select
'Clockwise' AS Label,
@cw AS Item

union all

select
'Counter-Clockwise' AS Label,
@ccw AS Item

union all

select
'Point In' AS Label,
@pointIn.STBuffer(100000) AS Item

union all

select
'Point Out' AS Label,
@pointOut.STBuffer(100000) AS Item

Hope it helps.

enter image description here

Jon Bellamy
  • 3,333
  • 20
  • 23
  • Thank you Jon, i just checked msdn about geography::Point, it says that input parameters are Point ( Lat, Long, SRID ). But i get correct behaviour on my example only in clockwise polygon and other order of Point parameters - Point ( Long, Lat, SRID ). Now i am checking this on my real data and give you feed back later. Thank you – taburetkin Aug 29 '14 at 08:33
  • ok. finaly. for ms sql 2012 the correct answer is - clockwise direction of creating polygon(square of polygon calculated by right hand rule). and for geography::Point - longitude is first parameter and latitude is the second one while in geography::STPointFromText is used WKT in wich Latitude is first and longitude is the second one. – taburetkin Aug 29 '14 at 08:58