Reducing Shape Function In SQL Server 2008

USE [Region]
GO
/****** Object:  UserDefinedFunction [dbo].[GetReducedShape]    Script Date: 11/10/2009 16:44:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  
-- Create date: 
-- Description: 
-- =============================================
ALTER FUNCTION [dbo].[GetReducedShape] 
(
 -- Add the parameters for the function here
 @shapeToReduce geometry
 --, @reduceFactor double 
 --0.001
 
)
RETURNS Geometry
AS
BEGIN
 ---- Declare the return variable here
 --DECLARE <@ResultVar, sysname, @Result> 

 ---- Add the T-SQL statements to compute the return value here
 --SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

 DECLARE @reducedShape geometry, @border geometry, @BUFFER geometry,@diff geometry

 SELECT @border = @shapeToReduce.STBoundary()
 --SELECT @BUFFER=@border.BufferWithTolerance(0.1, @reduceFactor, 0)
 --SELECT @BUFFER=@border.BufferWithTolerance(0.1, 0.001, 0)
 --SELECT @BUFFER=@border.BufferWithTolerance(0.003, 0.001, 0)
 SELECT @BUFFER=@border.BufferWithTolerance(0.048, 0.001, 0)
 
 SELECT @diff=@BUFFER.STIntersection(@shapeToReduce)
 SELECT @reducedShape = @shapeToReduce.STSymDifference(@diff) 
 return @reducedShape--@reducedShape
 
 -- Return the result of the function
 --RETURN <@ResultVar, sysname, @Result>

END

Trimming Spatial Fat

I had some map data from a 3rd party that wasn't quite correct. Region boundaries where overlapping. This made my query of finding regions within regions come back with bad results. I thought if I could reduce the region polygon by some factor, that it would help avoid picking up the subtley overlapping regions. I was looking for a STShrinkShape(int factor) function but couldn't see anything. If someone knows of a better way, I would appreciate the enlightenment. Here is how I did it:


1. Identify polygon and it's boundary. In this example I create a point and buffer it to create a polygon.

DECLARE @g geometry
DECLARE @h geometry
DECLARE @border geometry
DECLARE @BUFFER geometry

SET @g = geometry::STGeomFromText('POINT(0 0)', 0);
SELECT @g.BufferWithTolerance(1, .5, 0).ToString();
SELECT @h=@g.BufferWithTolerance(1, .5, 0)

SELECT @h as h

SELECT @border = @h.STBoundary()
SELECT @border as border



2. Add a buffer, with some buffer factor, around the border and create a new shape. The the inner part of this buffer is going to get subtracted from the original polygon/shape.

SELECT @BUFFER=@border.BufferWithTolerance(0.1, 0.001, 0)

SELECT @border as border
union all
SELECT @BUFFER as test

SELECT @BUFFER as test




3. Identify the polygon/shape of the inner part of the buffer by getting the polygon that intersect the buffer and the original shape.

SELECT @BUFFER as test
union all
select @h as h

declare @diff geometry
SELECT @diff=@BUFFER.STIntersection(@h)
select @diff as diff



4. Subtract this overlapping polygon from the original shape and we get our desired results.

SELECT @h.STSymDifference(@diff) as blah






OGC Methods on Geometry Instances