# 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

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