Point in polygon algorithm for SQL Server

Question

I am trying to write an SQL query that determines if a given point is in a polygon. (I am using SQL Server 2008 R2).

I followed this tutorial (just copy / paste it and change the name of the table), and it works just about the same, but it is not entirely accurate. For example, consider a given point whose coordinates: . If you draw a small polygon (approximate square) around this point with four vertex coordinates:
P = 45.7664, 4.87383




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 indicates that the point is not in the polygon, while it ... This is the result (with my own formatting text):

(Polygon 20 is the polygon above)

But if you increase the square (10 times more in my test), the procedure will answer that my point is on the square.

So, I'm looking for another algorithm, more precisely.

Here is my VERTICE table containing all the coordinates of the vertices of each polygon of my database

I need to check that for TOTAL polygons (there are several thousand), if the given point is passed in the parameter, it is in the polygon (and, if so, which one). I can do the loop alone, but I am missing the correct point in the polygon algorithm.

Can someone help me? Thank you very much.

ESSENCE

Corresponding SQL script: http://sqlfiddle.com/#!3/0caa4/1

Answer 1

, . SQL

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;

, @s . , , "", . @t, , . , SQL 2012, , 2012 , , . 2008 , , , @s. , @s, .

(/) " ". , , . , , - , , .