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.