Determining the 'closest' centre point for a shape in Sql Server 2012

Sql Server 2008 and Sql Server 2012 both have the ability to find the 'centre' point for a shape. This is all great and awesome if your shape is .. well .. normal. That is .. it doesn't have any missing 'area' around the middle of the shape.

Check this out.

This is a shape for Melbourne, Australia.

The 'centre' point is actually outside of the -real- shape.

So .. this quick Sql function now finds the closest centre point for a shape. If the point is really inside (aka. Intersects) the shape, then use that. Otherwise, find the Shortest Line To the shape .. then use that point.

Code or GTFO.

CREATE FUNCTION dbo.ToClosestCentrePoint
(
    @Shape GEOGRAPHY
)
RETURNS GEOGRAPHY
AS
BEGIN

    -- First get the centrepoint.   
    DECLARE @CentrePoint GEOGRAPHY
    SET @CentrePoint = @Shape.EnvelopeCenter()

    -- Now check to see if this centre point actually
    -- exists in the this shape?
    IF @CentrePoint.STIntersects(@Shape) = 0 BEGIN
        -- It doesn't so find the closest point.

        -- Grab the last end point of this linestring 
        -- (which contains a start and and end, only)
        -- and use that as the new centrepoint
        SET @CentrePoint = @CentrePoint
                            .ShortestLineTo(@Shape)
                            .STEndPoint()
    END

    RETURN @CentrePoint
END
GO

Now - it's not perfect. Meaning, the centre point for Melbourne should really be somewhere in the visual-middle of that funny shape. But .. for an automated process, this works well.

For a minimum viable solution, this code now makes sure that the closest centre point really does intersect the original shape.