Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2010
    Posts
    5

    Question 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. #2
    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. #3
    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. #4
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    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. #6
    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)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    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. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by skin View Post
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    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
  •