# Thread: Point in Polygon function using parameter values not a table

1. Registered User
Join Date
Sep 2010
Posts
5

## Unanswered: Point in Polygon function using parameter values not a table

Hi Experts,

I'm using SQL Server 2005 to try to write a POINT_IN_POLYGON function, which takes co-ords for a point and a polygon as parameters and returns 1 if point is in polygon 0 if outside.

I need to be able to call the function like this:
SELECT * from LOCATIONS where point_in_polygon('(6|3)','(5|1)|(8|1)|(8|6)|(5|7)| (5|1)') =1 ;

I have done this for mySQL and Oracle already and am having some problems doing this in SQL Server 2005 as there are no spatial functions.

Has anyone done something similar?

I have found this article Point In Polygon | SQL Statements Tutorial – Learn SQL Online which is great - works a treat.

However, I'm new to SQL Server, so I'm wondering how to use this if I want to call my function as above, and not store the polygon in a table.

Problem is, I need to know how many vertices there are and then read each point into a variable.
That's my problem, I don't know how to go about doing that in SQL Server.

In English, I was thinking I need to replace the ')|(' with ',' and then replace the '(' and ')' with blanks.
So I get a string as follows:
'5|1,8|1,8|6,5|7,5|1'
Then split the resulting string into the 5 vertices by splitting by ','
Then split the 5 strings into 10 points by splitting by '|' and then once I have all the lats and longs as variables I should be able to do the statement:

# IF( ((@lineLon1>@pointLon and @lineLon2<=@pointLon) OR (@lineLon1<=@pointLon and @lineLon2>@pointLon))
# AND (@pointLat < (
# (@lineLat2 - @lineLat1) * (@pointLon - @lineLon1) / (@lineLon2 - @lineLon1) + @lineLat1
# )
# )
# )

Can any give me a pointer on how to do this - or if there is a better way before I go down this path!

All comments and pointers will be much appreciated.

2. Registered User
Join Date
Sep 2010
Posts
5

## Update on my Attempted solution

Hi all,

Here's my attempted solution, but I need help!!

Few problems :
1) Its not working - It still tells me points that are not inside are inside (like 3,3 should be outside but returns 1)
2) I have hard coded the substring function. This isn't ideal. I need to use the
"set @pos1 = charindex('|',@PtString)" line to determine the positioning of the X,Y co-ordinates - but I'll work on that later.

I'd just like to get this working first on a simple example like
SELECT dbo.point_in_polygon('(6|3)','(5|1)|(8|1)|(8|6)|(5 |7)|(5|1') returns 1
but so does SELECT dbo.point_in_polygon('(3|3)','(5|1)|(8|1)|(8|6)|(5 |7)|(5|1') - this should return 0

All Advise greatly appreciated - I've a deadline for the 30th !!

_______________

drop function [dbo].[PointInPolygon]
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================
-- CREATE FUNCTION
-- ===================

--POINT_IN_POLYGON function, takes co-ords for a point and a polgon
-- returns 1 if point is in polygon 0 if outside
Create FUNCTION [dbo].[PointInPolygon](@PtString varchar(max), @polyString varchar(max))
--RETURNS INT
Returns VARCHAR(max)
AS
BEGIN
DECLARE @returnVal INT
DECLARE @numPoints INT
DECLARE @loop INT
DECLARE @lenPoly INT
DECLARE @pSearchChar VARCHAR(3)
DECLARE @insidePolygon INT
DECLARE @polyX INT
DECLARE @polyY INT
DECLARE @polyA INT
DECLARE @polyB INT
DECLARE @ptX INT
DECLARE @ptY INT
DECLARE @polyString2 VARCHAR(MAX)
DECLARE @vertex VARCHAR(3)
DECLARE @i INT
DECLARE @pos1 int
DECLARE @pos2 int

SET @i=0
SET @insidePolygon = 0
--determine how many points in the polygon
SET @pSearchChar = ')'
SET @numPoints = (LEN(@polyString) - LEN(REPLACE(@polyString, @pSearchChar, '')))
--seperate the point into X and Y
set @PtString = REPLACE(@PtString, ')', '')
set @PtString = REPLACE(@PtString, '(', '')
-- split into X adn Y using | as delimiter
set @pos1 = charindex('|',@PtString)
set @ptX = substring(@PtString,1,1)
set @ptY = substring(@PtString,3,1)
-- seperate the vertices of the polygon
SET @pSearchChar = ')|('
set @polyString2 = REPLACE(@polyString, @pSearchChar, ',')
set @polyString2 = REPLACE(@polyString2, ')', '')
set @polyString2 = REPLACE(@polyString2, '(', '')
-- have string like 5|1,8|1,8|6,5|7,5|1
-- seperate into X,Y coords
set @lenPoly = len(@polyString2)
set @pos1 = 1
--9
set @loop = (@numPoints-1)
--while (@pos1 < @lenPoly)
while (@loop > 0 )
BEGIN
set @polyX = substring(@polyString2,@pos1,1)
set @pos1 = @pos1+2
set @polyY = substring(@PolyString2,@pos1, 1)
set @pos1 = @pos1+2
set @polyA = substring(@polyString2,@pos1,1)
set @pos1 = @pos1+2
set @polyB = substring(@polyString2,@pos1,1)

IF( ( (@polyY>@ptY and @polyB<=@ptY) OR (@polyY<=@ptY and @polyB>@ptY))
AND (@ptX < ((@polyA - @polyX) * (@ptY - @polyY) / (@polyB - @polyY) + @polyX ) )
)
SET @insidePolygon = 1
set @loop = @loop -1
set @pos1 = @pos1-2
END

IF (@@ERROR <> 0) RETURN 0
RETURN @insidePolygon
END
GO
Last edited by skin; 09-28-10 at 13:22. Reason: correcting code

3. Registered User
Join Date
Jun 2005
Posts
319
SQL 2008 has geometry and geography data types if that will simplify your life any.

geometry (Transact-SQL)

4. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Not much in 2005....

OO - I keep meaning to look at this but haven't had the time. I have something running now, I'll see if I can test.

5. Registered User
Join Date
Sep 2010
Posts
5

## Pollywolly doodle

Hi Gagnon,

No I gotta stick to SQl Server 2005 as our product supports this platform - I will have to also code this in SQL Server 2008 once I have this completed - so hold that thought!

Pootle Flump - I'd love to hear your thoughts.

I'm going demented.

I have implemented the original code Point In Polygon | SQL Statements Tutorial – Learn SQL Online as is and as far as I can see it doesn't work - This also tells me that point 3,3 is inside the polygon whereas it IS NOT

For example I have a polygon like 5|1,8|1,8|6,5|7,5|1

and so point 6,3 is inside and point 3,3 is outside the polygon - but when I call the function like so:

select dbo.ufn_PointInPolygon(3,3,1)

it tells me that 3,3 is also inside!

I'm going nuts! I am supposed to have this competed tomorrow...

Here is the code to recreate this:

CREATE TABLE [dbo].[Polygons](
[polygonID] [int] NOT NULL,
[vertexID] int NOT NULL,
[latitude] [decimal](12, 9) NOT NULL,
[longitude] [decimal](12, 9) NOT NULL)

-- have string like 5|1,8|1,8|6,5|7,5|1
insert into dbo.[polygons]
values (1,1,5,1);
insert into dbo.[polygons]
values (1,2,8,1);
insert into dbo.[polygons]
values (1,3,8,6);
insert into dbo.[polygons]
values (1,4,5,7);
insert into dbo.[polygons]
values (1,5,5,1);

select * from dbo.polygons

Function code is:
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Point In Polygon | SQL Statements Tutorial – Learn SQL Online
-- 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 dbo.polygons WHERE polygonID=@polygonID

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

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

SELECT @lineLat2 = latitude, @lineLon2 = longitude
FROM dbo.polygons
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

SET @j = @i
SET @i = @i + 1

END

IF (@@ERROR <> 0) RETURN 0

RETURN @insidePolygon

END
GO

6. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
All I can see is that there appears to be an error in the algorithm.

For the second pass of the loop it resolves to this:
Code:
```    IF  (((1 > 3 AND 6 <= 3) OR (1 <= 3 and 6 > 3))
AND (3 < ((8 - 8) * (3 - 1) / (6 - 1) + 8 ) )
)
BEGIN

PRINT   'IN'

END```
That is for Pt(3,3) against shape(8|1)|(8|6)

7. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Ok, so having looked at the original link I have to ask why did you change this crucial line?
Code:
`SET @insidePolygon = -1 * @insidePolygon`

8. Registered User
Join Date
Sep 2010
Posts
5

## polygone

Honest answer - I didn't think it was crucial!

I just wanted the function to return 1 if inside and 0 if outside.
Not -1
So I had changed a few things there - sorry.

However - I just set this back but I am still getting incorrect answers
I am seeing 2,5 as inside as well as 3,3

The original algorithm is here:
PNPOLY - Point Inclusion in Polygon Test - WR Franklin (WRF)

I really appreciate your taking the time to investigate this.
Cheers,
Skin

9. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by skin
Honest answer - I didn't think it was crucial!
The gist of how the algorithm works in the original link is that if that expression resolves to true an even number of times then the return is true. If it resolves an odd number of times the return is false. You have also changed the initial value of the returned parameter too - since it is initially set to 0 then you will always return 0.

I think you should start again with the original function, test it, make sure you are happy and then expand it to your requirements.

10. Registered User
Join Date
Sep 2010
Posts
5

## right under my nose

Thanks,

Pootle Flump.

I now understand it - before I had a cloudy idea.
I've the original working now and so should be able to tweak my code to rectify the problems I introduced.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•