Monday, November 9, 2009

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

No comments: