点在多边形中

“点在多边形中”问题是指如何判断一个给定点是否在一个多边形中。现在我们给出这个问题的SQL解决方案。

方法

算法思路是从W. Randolph Franklin的这篇文章而来:
http://www.ecse.rpi.edu/Homepages/wrf/Research/Short_Notes/pnpoly.html

SQL 代码

数据表: polygon

polygonID vertexID latitude longitude
1 1 -79.64452 44.06773
1 2 -79.62194 43.97181
1 3 -79.45356 43.92827
1 4 -79.31599 44.02789
1 5 -79.36526 44.13045
1 6 -79.45459 44.04116

下面是SQL函数 ufn_PointInPolygon 的定义,这个函数会判断一个点是否在多边形中。代码在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