locked
Boundary of geography instance RRS feed

  • Question

  • Hi

    I am trying to accurately remove the internal polygons from a multipolygon instance. With a geometry instance I can use STBoundary but this method does not exist for geography instances.

    I have a multipolygon instance such as: -

    declare @g1 geography = 'MULTIPOLYGON (((30 0, 50 0, 50 20, 30 20, 30 0), (35 5, 35 15, 45 15, 45 5, 35 5)), ((0 0, 20 0, 20 20, 0 20, 0 0), (15 5, 5 5, 5 15, 15 15, 15 5)))'
    select @g1
    

    and wish to return an instance such as: -

    declare @g2 geography = 'MULTIPOLYGON (((30 0, 50 0, 50 20, 30 20, 30 0)), ((0 0, 20 0, 20 20, 0 20, 0 0)))'
    select @g2
    

    Does any one have any suggestions?

    Thanks in advance,

    Blair

    Tuesday, September 28, 2010 8:38 AM

Answers

  • You could loop through each geometry in the collection and then use RingN(1) to select the "outer" ring, like this:

    DECLARE @g1 geography = 'MULTIPOLYGON (((30 0, 50 0, 50 20, 30 20, 30 0), (35 5, 35 15, 45 15, 45 5, 35 5)), ((0 0, 20 0, 20 20, 0 20, 0 0), (15 5, 5 5, 5 15, 15 15, 15 5)))'
    
    DECLARE @i int = 1;
    DECLARE @x geography = 'GEOMETRYCOLLECTION EMPTY';
    WHILE @i <= @g1.STNumGeometries() BEGIN
    SELECT @x = @x.STUnion(@g1.STGeometryN(@i).RingN(1));
    SET @i = @i + 1;
    END
    SELECT @x;
    


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by BSDZ Tuesday, September 28, 2010 9:14 AM
    • Unmarked as answer by BSDZ Tuesday, September 28, 2010 9:23 AM
    • Proposed as answer by vIndEx Tuesday, September 28, 2010 10:00 AM
    • Marked as answer by BSDZ Tuesday, September 28, 2010 10:21 AM
    Tuesday, September 28, 2010 8:52 AM
    Answerer
  • Hi,

    Use this function

     

    CREATE FUNCTION dbo.RemoveHoles
    (
    	@MULTIGEOMETRY geography
    )
    RETURNS geography
    AS
    BEGIN
    	declare @TOTAL int = @MULTIGEOMETRY.STNumGeometries()
    	declare @i int = 1
    
    	declare @res varchar(Max)='MULTIPOLYGON('
    
    	while (@i<=@TOTAL)
    	BEGIN
    		IF (@i>1)
    			SET @res = @res+','
    	
    		set @res = @res+ REPLACE(
    							@MULTIGEOMETRY.STGeometryN(@i).RingN(1).ToString()
    							,'LINESTRING'
    							,'(') 
    				  +')'
    	set @i = @i+1
    	END
    	
    	set @res = @res+')'
    	
    	
    	return geography::STGeomFromText(@res,@MULTIGEOMETRY.STSrid)
    
    END
    GO
    

    • Marked as answer by BSDZ Tuesday, September 28, 2010 9:13 AM
    Tuesday, September 28, 2010 9:01 AM

All replies

  • You could loop through each geometry in the collection and then use RingN(1) to select the "outer" ring, like this:

    DECLARE @g1 geography = 'MULTIPOLYGON (((30 0, 50 0, 50 20, 30 20, 30 0), (35 5, 35 15, 45 15, 45 5, 35 5)), ((0 0, 20 0, 20 20, 0 20, 0 0), (15 5, 5 5, 5 15, 15 15, 15 5)))'
    
    DECLARE @i int = 1;
    DECLARE @x geography = 'GEOMETRYCOLLECTION EMPTY';
    WHILE @i <= @g1.STNumGeometries() BEGIN
    SELECT @x = @x.STUnion(@g1.STGeometryN(@i).RingN(1));
    SET @i = @i + 1;
    END
    SELECT @x;
    


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by BSDZ Tuesday, September 28, 2010 9:14 AM
    • Unmarked as answer by BSDZ Tuesday, September 28, 2010 9:23 AM
    • Proposed as answer by vIndEx Tuesday, September 28, 2010 10:00 AM
    • Marked as answer by BSDZ Tuesday, September 28, 2010 10:21 AM
    Tuesday, September 28, 2010 8:52 AM
    Answerer
  • Thanks for your help Tanoshimi. 

    Is there anyway I can return a multipolygon instead of a multilinestring? Admittedly the questions title did ask for boundaries but I am really interested in filling in my geography instances.

    Tuesday, September 28, 2010 9:00 AM
  • Hi,

    Use this function

     

    CREATE FUNCTION dbo.RemoveHoles
    (
    	@MULTIGEOMETRY geography
    )
    RETURNS geography
    AS
    BEGIN
    	declare @TOTAL int = @MULTIGEOMETRY.STNumGeometries()
    	declare @i int = 1
    
    	declare @res varchar(Max)='MULTIPOLYGON('
    
    	while (@i<=@TOTAL)
    	BEGIN
    		IF (@i>1)
    			SET @res = @res+','
    	
    		set @res = @res+ REPLACE(
    							@MULTIGEOMETRY.STGeometryN(@i).RingN(1).ToString()
    							,'LINESTRING'
    							,'(') 
    				  +')'
    	set @i = @i+1
    	END
    	
    	set @res = @res+')'
    	
    	
    	return geography::STGeomFromText(@res,@MULTIGEOMETRY.STSrid)
    
    END
    GO
    

    • Marked as answer by BSDZ Tuesday, September 28, 2010 9:13 AM
    Tuesday, September 28, 2010 9:01 AM
  • Actually, you've answered a similar question before here: -

    http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/d94dd89a-ab86-46ed-98c8-0c73dedbff97/

    So thanks again.

    Tuesday, September 28, 2010 9:03 AM
  • Hi Tanoshimi,

    This method actually returns a multilinestring. Anyway,  it's true that STBoundary returns also a multilinestring, and with the interior rings also.

    Tuesday, September 28, 2010 9:05 AM
  • Thank you very much. That's a complete solution.
    Tuesday, September 28, 2010 9:13 AM
  • I know it returns a MultiLineString... that's deliberate!

    The boundary of a polygon is made up of the linestrings that form the rings of the polygon - they're not "filled in".

    So, once you've you've found the "boundary of a (multipolygon) geography instance" and "removed the internal polygons", what you're left with is the first ring of each element in the collection. I'm only answering the question that was asked!


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, September 28, 2010 9:18 AM
    Answerer
  • Just a misunderstanding... 

    Let's say that the title of the post "ask" for a multilinestring, and the content for a multipolygon. Two solutions, double knowledge!

    Tuesday, September 28, 2010 9:59 AM
  • Everyone's a winner!
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, September 28, 2010 10:20 AM
    Answerer