SQL Server 2005 Point In Polygon

Question

Hello,

I have a polygon structure in an sql2005 db as described below.

CREATE TABLE [dbo].[Polygons](
    [PolygonID] [int] IDENTITY(1,1) NOT NULL,
    [PolygonName] [varchar](255) NOT NULL,
    [PolygonColor] [varchar](7) NOT NULL,
    [PolygonRuleID] [int] NOT NULL)

CREATE TABLE [dbo].[Polylines](
    [LineID] [int] IDENTITY(1,1) NOT NULL,
    [LineX1] [float] NOT NULL,
    [LineY1] [float] NOT NULL,
    [LineX2] [float] NOT NULL,
    [LineY2] [float] NOT NULL,
    [PolygonID] [int] NOT NULL
)

Now I retrieve whole lines to application and put all to hit testing function.

public static bool PointInPolygon(float pointX, float pointY, PolylineCollection polygon)
        {
            int nvert = polygon.Count();
            int i, j = 0;
            bool c = false;
            for (i = 0, j = nvert - 1; i < nvert; j = i++)
            {
                if (((polygon[i].LineY1 > pointY) != (polygon[j].LineY1 > pointY)) &&
                 (pointX < (polygon[j].LineX1 - polygon[i].LineX1) * (pointY - polygon[i].LineY1) / (polygon[j].LineY1 - polygon[i].LineY1) + polygon[i].LineX1))
                    c = !c;
            }
            return c;
        }

But I need to move this function to sql server. But Sql 2005 doesn't have native spatial functions and I dont want to use any extra spatial functionality libraries. How can I port this function to T-SQL? :) Or anyone have different solution to PointInPolygon check?

Thanks

Answer 1

You could re-write PointInPolygon as a stored proc with a cursor.

Answer 2

SQL Server 2005 allows you to write native functions for the CLR that can execute server side. You can read the MSDN intro Using CLR Integration in SQL Server 2005. This should allow you to have your function implemented as an addition to sql server and run at native speeds.

Answer 3

You didn't rule out going with sql 2008 which has built-in geospatial types. I haven't used it so I can't offer anything beyond that.

Answer 4

I have to admit that I don't fully get your algorithm to test for point hitting. Anyways, the data structure is odd for a polygon, since X1/Y1 of a line have to be equal to X2/Y2 of the previous line in order to form a polygon. Therefore, I'd store single points only in order to make the data structure guaranteed to be consistent, and the last and the first point are interconnected again.

As for an algorithm for finding whether a point is inside the (2D) polygon or not, I'd first filter the lines which are candidates and create a "cut" (horizontal or vertical) so that I get a list of line intersection points and order them. Then, using a rank function, it is inside the polygon if the rank is odd, if it even we're outside the polygon (or in a "hole").

Answer 5

You can look at this page, it provides SQL code:

SQL code Point In Polygon