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
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 |
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