Last Updated: February 25, 2016
·
736
· purekrome

Percentage of a shape that exist within another shape in Sql Server 2008/2012

A simple scalar function to see how much a shape exists within another shape, as a percentage.

eg. How much does SoHo fit within Manhattan?

CREATE FUNCTION [dbo].[PercentageOfIntersection]
(
    @SmallShape GEOGRAPHY,
    @LargeShape GEOGRAPHY
)
RETURNS FLOAT
AS
BEGIN
    RETURN (@SmallShape 
             .STIntersection(@LargeShape )
             .STArea() /  
                 (CASE
                     WHEN @SmallShape .STArea() <= 0 
                         THEN 0.0000001 
                         ELSE @SmallShape .STArea() 
                         END)) * 100.00
                  END