Point In Polygon

The Point In Polygon problem asks that how to find if a given point lies within a polygon. Now we have an SQL solution for this problem.

Method

The idea of the algorithm we use in this article comes from W. Randolph Franklin:
http://www.ecse.rpi.edu/Homepages/wrf/Research/Short_Notes/pnpoly.html

The SQL Code

Table: polygon

The following is the definition of an SQL function ufn_PointInPolygon, which will return if a point in a polygon. The code passed the test in SQL Server 2005.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- http://www.sql-statements.com/point-in-polygon.html
-- Test: select dbo.ufn_PointInPolygon(-79.37553, 44.06699,1)
-- =============================================
CREATE FUNCTION [dbo].[ufn_PointInPolygon]
(
    -- Add the parameters for the function here
    @pointLat REAL, @pointLon REAL, @polygonID INT
)
RETURNS INT
AS
BEGIN
   
    DECLARE @insidePolygon INT

    DECLARE @nvert INT   
    DECLARE @lineLat1 REAL
    DECLARE @lineLon1 REAL
    DECLARE @lineLat2 REAL
    DECLARE @lineLon2 REAL

    DECLARE @i INT
    DECLARE @j INT

    SELECT @nvert=count(*) FROM polygon WHERE polygonID=@polygonID

    SET @insidePolygon = -1
    SET @i=0
    SET @j=@nvert-1

    WHILE (@i<@nvert)
    BEGIN
        SELECT @lineLat1 = latitude, @lineLon1 = longitude 
        FROM polygon 
        WHERE polygonID=@polygonID AND vertexID = @i

        SELECT @lineLat2 = latitude, @lineLon2 = longitude 
        FROM polygon 
        WHERE polygonID=@polygonID AND vertexID = @j

        IF( ((@lineLon1>@pointLon and @lineLon2<=@pointLon) OR (@lineLon1<=@pointLon and @lineLon2>@pointLon))
            AND (@pointLat < (
                        (@lineLat2 - @lineLat1) * (@pointLon - @lineLon1) / (@lineLon2 - @lineLon1)    + @lineLat1
                    )               
                )
            )
            SET @insidePolygon = -1 * @insidePolygon
       
        SET @j = @i
        SET @i = @i + 1
       
    END

    IF (@@ERROR <> 0) RETURN 0

    RETURN @insidePolygon

END
GO