Eirikur Eiriksson (9/15/2014)
Quick thought, don't ever use geometry for geographical data, use geometry with the appropriate SRID, normally 4326.mickyT's solution is straight forward, just make certain that the direction is correct and that the start point is the same as the endpoint.
😎
+100 on the Geography for Lat/Longs.
And I just noticed that you said MULTILINESTRING not LINESTRING. This makes me think that potentially the lines aren't closing correctly. Here's a variation that will be a bit more robust. It is Geometry again, but should work for Geography as well
DECLARE @g Geometry = Geometry::STGeomFromText('MULTILINESTRING ((0 0, 10 0),(0 10, 10 10, 10 0),(0 10, 0 0),(20 20, 30 20, 30 30),(20 20, 20 30, 30 30))',0)
SELECT @g.STUnion(@g.STPointN(1)).ToString() cleanedResult,
CASE
WHEN
@g.STUnion(@g.STPointN(1)).STIsClosed() = 1 AND -- better check than STIsRing for this
@g.STUnion(@g.STPointN(1)).STGeometryType() like '%LINESTRING' THEN
Geometry::STGeomFromText(
CONCAT(
REPLACE(@g.STUnion(@g.STPointN(1)).ToString(),'LINESTRING (','POLYGON ((')
,')')
,@g.STSrid)
END