4

I'm trying to write a SQL query which determine if a given point is into a polygon. (I'm using SQL Server 2008 R2).

I was following this tutorial (just copy / paste it and change some table name) and it approximatively works BUT it is not precise at all. For example, let's considerate a given point which coordinates are :
P = 45.7664, 4.87383.

If you draw a little polygon (an approximate square) around this point with 4 vertices coordinates :
S = 45.97215 4.693909, 45.687 4.674683, 45.73302 5.460205, 46.05227 5.366821, 45.97215 4.693909

the procedure given in the link below answers the point is NOT in polygon, whereas it is... This is the output (with my own formatting text) :
false output
(Polygon 20 is the polygon above)

But if you enlarged the square (10 times bigger in my test), the procedure answers my point is in the square.

So, I'm seeking for another algorithm, more precise.
Here is my VERTICE table, containing all vertices coordinates of each polygon of my DB Vertice table

I need to check, for ALL polygons (there are a few thousands) if a given point passed in parameter is in a polygon (and if yes, which one(s)). I can do the loop by myself, but I miss a correct Point in polygon algorithm.

Could someone help me ? Thank you very much.

SUMMARY

The corresponding SQL fiddle : http://sqlfiddle.com/#!3/0caa4/1

AlexB
  • 7,302
  • 12
  • 56
  • 74
  • It's performance is poor if you have huge records in the database, i tried this query over 1600000 records and it took avg 2 minutes to complete. – Jitendra Pancholi Jul 23 '14 at 11:54
  • Hi Jitendra, this is an old question but the project is still in development (it's a personal project). I implemented the alogrithm given in my link, with the explainations of @Ben Thul and it perfectly works, but I never tried with so much records. You could suggest a better algorithm as an answer, it will be welcome ! – AlexB Jul 23 '14 at 12:31

1 Answers1

7

Your problem is that you've defined the polygon backwards. Let's explore this with the native SQL geography types

declare @s geography, @t geography, @p geography;
select @s = geography::STPolyFromText('POLYGON((45.97215 4.693909, 45.687 4.674683, 45.73302 5.460205, 46.05227 5.366821, 45.97215 4.693909))', 4326);
select @t = geography::STPolyFromText('POLYGON((46.05227 5.366821, 45.73302 5.460205, 45.687 4.674683, 45.97215 4.693909, 46.05227 5.366821))', 4326);
select @p = geography::STPointFromText('POINT(45.7664 4.87383)', 4326);

select @p.STIntersects(@s), @p.STIntersects(@t);
select @p.STBuffer(10), @s, @t;

As you can see, @s holds pretty much the whole world. In the result set viewer, if you zoom into where your "square" should be, you'll find a hole. Contrast that with @t which is the area that you expect. Also note that I ran this in SQL 2012 which improved on a limitation that existed pre-2012 that says that a geospatial instance can't cross a hemisphere boundary. If you run the above on a 2008 instance, you'll likely get an error to that effect for @s. Comment out the line defining @s and it'll run.

There's a "right-hand" rule when defining geo(graphy/metry) polygons. Imagine that you were in a car visiting the points in the order you've specified. The area that you're defining is what you'd see if you were looking out the right side of the car.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Thank you for your answer. But I don't understand why in `@s` as you defined it, you're crossing an hemisphere boundary... It's just a little polygon in North hemispere !?!? Else, `STIntersects` seems better than the algorithm I had, I suppose the function has been tested and re-tested... – AlexB Oct 27 '13 at 22:02
  • Because as you've defined the polygon, `@s` is the whole world minus the square you intended. – Ben Thul Oct 28 '13 at 00:20
  • You will think I'm an idiot, but I still don't understand one thing : you tell me `@s` is the whole world minus the square I inteded. But `@s` is a polygon located in France, which is a country 100% in North hemisphere, so why am I crossing an hemisphere boundary ?? Then, if I understand you, when I draw a polygon inside another polygon, the biggest polygon is itself minus the little one ? If so, how can I superimpose two polygons ? – AlexB Oct 28 '13 at 09:01
  • 1
    You're not an idiot, I'm just not explaining it well. You have a quadrilateral. If you traverse the points clockwise, you get the area that you'd expect. However, if you traverse the points counter-clockwise, you get the entire world minus the quadrilateral. In short, the order that you define the corners in the polygon matters; A-B-C-D-A != A-D-C-B-A – Ben Thul Oct 28 '13 at 12:57
  • Ben... I REALLY love you ! Thanks to yours answers, first, I understood something new with polygons, then, you just removed a LOT of problems in my project (it's a personnal project but I'd love it works one day). So, thank you very very much :-) – AlexB Oct 28 '13 at 22:01
  • @BenThul: It's performance is poor if you have huge records in the database, i tried this query over 1600000 records and it took avg 2 minutes to complete. – Jitendra Pancholi Jul 23 '14 at 11:53