locked
how to flatten a table of nested overlapping polygons into a table of nested non-overlapping polygons RRS feed

  • Question

  • Hi

    I was wondering if any one else may have solved a similar problem in the past.

    I have a table of nested overlapping polygons and I would like to flatten this set of polygons into a table of nested non-overlapping polygons. The new table will look almost the same except when a larger polygon overlaps a smaller one then the difference is taken. The effect being that no two polygons will overlap and if a covered point is chosen you will be guaranteed to be in the smallest polygon covering the point in the original table.

    To add some clarity here are two table definitions showing an example source table and an example destination table.

    declare @g1 geography = 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))';
    declare @g2 geography = 'POLYGON((1 1, 9 1, 9 9, 1 9, 1 1))';
    declare @g3 geography = 'POLYGON((2 2, 8 2, 8 8, 2 8, 2 2))';
    
    declare @h1 geography = 'POLYGON((20 0, 30 0, 30 10, 20 10, 20 0))';
    declare @h2 geography = 'POLYGON((21 1, 29 1, 29 9, 21 9, 21 1))';
    declare @h3 geography = 'POLYGON((22 2, 28 2, 28 8, 22 8, 22 2))';
    
    declare @source table (id int identity, name varchar(max),bounds geography)
    insert into @source values ('g1', @g1);
    insert into @source values ('g2', @g2);
    insert into @source values ('g3', @g3);
    insert into @source values ('h1', @h1);
    insert into @source values ('h2', @h2);
    insert into @source values ('h3', @h3);
    select * from @source
    
    declare @target table (id int identity, name varchar(max),bounds geography)
    insert into @target values ('G1', @g1.STDifference(@g2));
    insert into @target values ('G2', @g2.STDifference(@g3));
    insert into @target values ('G3', @g3);
    insert into @target values ('H1', @h1.STDifference(@h2));
    insert into @target values ('H2', @h2.STDifference(@h3));
    insert into @target values ('H3', @h3);
    select * from @target
    I hoped I could use a recursive CTE but could not discover a good enough recursive member.

    Thanks in advance
    Blair



    Friday, November 13, 2009 9:11 AM

Answers

  • Thanks for your reply Tanoshimi. I managed to find a way using nested ctes.

    For future reference here it is: -

    with cte1 (id, name, bounds, area) as (
    	select 
    		id, name, bounds, bounds.STArea()
    	from @source
    ),
     cte2 (pid, pname, pbounds, cbounds, rnk) as (
    	select 
    		p.id, p.name, p.bounds, c.bounds 
    		,row_number() over (partition by p.id order by c.area desc)
    	from cte1 p
    	left join cte1 c on p.bounds.STIntersects(c.bounds) = 1 and p.area > c.area
    )
    select pid [id], pname [name], 
    	case when cbounds is null then pbounds else  pbounds.STDifference(cbounds) end [bounds]
    from cte2
    where rnk = 1

    • Marked as answer by BSDZ Friday, November 13, 2009 2:21 PM
    Friday, November 13, 2009 2:21 PM

All replies

  • When you say that your overlapping polygons are 'nested', do you mean that every polygon is always contained entirely within another one (with the exception of the outermost polygon) as in your example?
    In other words, the boundaries of one polygon never crosses over another, so that only some of it is contained within it, and some not?

    If every polygon contains one and only one other polygon, is there another field in the table that allows you to identify the immediate 'child' polygons of each polygon? In your example, this is the shape that shares the same letter id, but simply one number higher (i.e. g1 contains g2, h2 contains h3), in which case it would be quite simple to set up a loop to go through each polygon nested in a set starting from the outermost...

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, November 13, 2009 12:23 PM
    Answerer
  • Thanks for your reply Tanoshimi. I managed to find a way using nested ctes.

    For future reference here it is: -

    with cte1 (id, name, bounds, area) as (
    	select 
    		id, name, bounds, bounds.STArea()
    	from @source
    ),
     cte2 (pid, pname, pbounds, cbounds, rnk) as (
    	select 
    		p.id, p.name, p.bounds, c.bounds 
    		,row_number() over (partition by p.id order by c.area desc)
    	from cte1 p
    	left join cte1 c on p.bounds.STIntersects(c.bounds) = 1 and p.area > c.area
    )
    select pid [id], pname [name], 
    	case when cbounds is null then pbounds else  pbounds.STDifference(cbounds) end [bounds]
    from cte2
    where rnk = 1

    • Marked as answer by BSDZ Friday, November 13, 2009 2:21 PM
    Friday, November 13, 2009 2:21 PM