point in polygon (without spatial functions)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

point in polygon (without spatial functions)

Author

Topic

skin
Starting Member

1 Post

Posted - 2010-09-29 : 09:11:15

Hi all,

I'm a newbie to SQL Server - but I have to write a POINT_IN_POLYGON function for SQL server 2005, 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 online http://www.sql-statements.com/point-in-polygon.html and was using it as the basis for my code - however when I test it I don't think it works!

Can anyone tell me if I am doing something wrong?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-29 : 16:55:51

The smartest way is to make the observation point move straight right from original position to the rightmost coordinate.
If the number of crossed lines are odd, you started inside the polygon. If the number of crossed lines are even, you started outside the polygon.

Here is an excellent page I use for checking intersecting lines.
http://local.wasp.uwa.edu.au/~pbourke/geometry/lineline2d/

I am capable of checking about 10,000 polygons per second with my function in SQL Server.

N 56°04'39.26"
E 12°55'05.63"

sansancute
Starting Member

1 Post

Posted - 2011-07-05 : 03:31:48

can you give me demo follow address :maytrang_bmt90@yahoo.com or sanpt@infoworldstudent.com Thanks a lot

sansan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources