locked
find neighborPolygon which has a greater Boundary(length) RRS feed

  • Question

  • Hi,

    how to calculate the touching size of a polygon to another polygon?

    These are the declarations for my test:

    DECLARE @newP geography;
    DECLARE @oldP geography;
    DECLARE @freeP geography;
    DECLARE @nachbar1 geography;
    DECLARE @nachbar2 geography;
    Declare @nachbar3 geography;
    
    SET @nachbar1 = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.358 47.649, -122.348 47.649, -122.358 47.653))', 4326);
    SET @nachbar2 = geography::STGeomFromText('POLYGON((-122.348 47.658, -122.348 47.649, -122.342 47.649, -122.342 47.658, -122.348 47.658))', 4326);
    SET @nachbar3 = geography::STGeomFromText('POLYGON((-122.358 47.658, -122.348 47.658, -122.348 47.662, -122.358 47.662, -122.358 47.658))', 4326);
    SET @oldP = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);
    SET @newP = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.653, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);
    Set @freeP= @oldP.STDifference(@newP.STBuffer(0.1))
    

    Now I want to know which polygon has the longest boundary to my @freeP Polygon. When I know which polygon "belongs" to my @freeP I want to union them...

    Is STCrosses a opinion?But that would only be possible in geometry format, right?

    If you have an idea, please let me know.

    Thanks!!!

     

    Tuesday, October 12, 2010 9:14 AM

Answers

  • SELECT
     @freeP.STIntersection(@nachbar1).STLength() AS length1,
     @freeP.STIntersection(@nachbar2).STLength() AS length2,
     @freeP.STIntersection(@nachbar3).STLength() AS length3
    


    Gives the following results, showing that intersection with @nachbar2 has the greatest length (and that @nachbar3 doesn't intersect at all):

    length1          length2        length3
    872.843943651293 889.2682588172 0

     

    Then, to union this with @freeP:

    SELECT @freeP.STUnion(@nachbar2)
    


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Dru_MS Tuesday, October 12, 2010 10:49 AM
    Tuesday, October 12, 2010 10:41 AM
    Answerer

All replies

  • SELECT
     @freeP.STIntersection(@nachbar1).STLength() AS length1,
     @freeP.STIntersection(@nachbar2).STLength() AS length2,
     @freeP.STIntersection(@nachbar3).STLength() AS length3
    


    Gives the following results, showing that intersection with @nachbar2 has the greatest length (and that @nachbar3 doesn't intersect at all):

    length1          length2        length3
    872.843943651293 889.2682588172 0

     

    Then, to union this with @freeP:

    SELECT @freeP.STUnion(@nachbar2)
    


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Dru_MS Tuesday, October 12, 2010 10:49 AM
    Tuesday, October 12, 2010 10:41 AM
    Answerer
  • Just to understand better, are you looking for the longest "line defining where the polygons touch", or the greatest amount of "overlap" or longest perimeter of the overlapping area? 

    If you are looking for the longest "touching line", there might be some trouble. Try the following tsql, which shows the "length" from STLength to be greater for the poly with the "shortest" touching line.

    declare @foobar1 geography
    SET @foobar1 = geography::STGeomFromText('POLYGON((-122.348 47.658, -122.348 47.649, -122.342 47.649, -122.342 47.658, -122.348 47.658))', 4326);
    
    declare @foobar2 geography
    SET @foobar2 = geography::STGeomFromText('POLYGON((-122.3425 47.658,-122.3425 47.649,-122.33 47.649,-122.33 47.658,-122.3425 47.658))', 4326);
    
    declare @foobar3 geography
    SET @foobar3 = geography::STGeomFromText('POLYGON((-122.349 47.655,-122.343 47.649, -122.343 47.658, -122.349 47.655))', 4326);
    
    -- show the 3 polygons
    select @foobar1 as g, '@foobar1' as l
    union all
    select @foobar2 as g, '@foobar2' as l
    union all
    select @foobar3 as g, '@foobar3' as l
    
    SELECT
     @foobar1.STIntersection(@foobar2).STLength() AS length1,
     @foobar1.STIntersection(@foobar3).STLength() AS length2
     
    -- length1	length2
    --2076.42387419805	2305.61292871148
    
    
    Tuesday, October 12, 2010 2:04 PM
  • what i actually wanne do is to assign the polygon @freeP to another polygon. the criteria/rule is to assign the @freeP Polygon to the polygon which has the longest touching line.

    What I actually did:

    I created a CURSOR to get all neigbors of the freeP, than I used a variable (e.g. mylenghth ) and calculated the length of the Intersection like tanoshimi told ...

    I stored the length and the ID of the polygon which will be assigned and used STUnion...

    This works fine...

    Tuesday, October 12, 2010 3:30 PM