sql - MakeValid() error: Expected "MULTIPOLYGON" at position 1. The input has "GEOMETRYCOLL"

Question

I'm getting an error while running the following sql query to make some geometry valid. This query is generated by an app.

If I remove SET @gt = @gmvalid.STAsText(); the query executes without error. I don't think this is even applicable to GEOMETRYCOLLECTION. IF so, How do I change a GEOMETRYCOLLECTION to a valid shape type?

Any help appreciated.

Query:

DECLARE @g GEOMETRY; 

SET @g = geometry::STGeomFromText( 
'GEOMETRYCOLLECTION (LINESTRING (340481.4 253721.8, 340469.4 253712.8), POLYGON ((340476.6 253703.2, 340488.6 253712.2, 340489 253712.6, 340499.8 253722.4, 340523.6 253740.2, 340547.6 253758.2, 340547.7 253758.3, 340570.7 253776.3, 340570.6 253776.2, 340594.6 253794.2, 340594.5 253794.1, 340660.5 253842.1, 340653.5 253851.9, 340587.5 253803.9, 340587.4 253803.8, 340563.4 253785.8, 340563.3 253785.7, 340540.3 253767.7, 340540.4 253767.8, 340492.4 253731.8, 340492 253731.4, 340481.2 253721.6, 340469.4 253712.8, 340476.6 253703.2)))'
, 0); 
SET @g = @g.MakeValid(); 

DECLARE @gt NVARCHAR(max); 
DECLARE @gm GEOMETRY; 
DECLARE @gmvalid GEOMETRY; 
DECLARE @gg GEOGRAPHY; 

SET @gm = 
'GEOMETRYCOLLECTION (LINESTRING (-2.8718409711193504 52.178527833652716, -2.8720148601250206 52.178445632021393), POLYGON ((-2.87190788915951 52.178360112372673, -2.8717340002864251 52.178442313846894, -2.8717282215602151 52.178445952829406, -2.871572019820706 52.1785352158733, -2.8712271282240533 52.1786977981049, -2.8708793446823497 52.178862198805589, -2.8708778999698188 52.178863108540845, -2.8705447366642627 52.179027400287367, -2.8705461813889226 52.17902649055614, -2.8701983927630952 52.179190889262976, -2.87019983750013 52.17918997953597, -2.8692431425939651 52.179628586667683, -2.8693472220059415 52.179715928156782, -2.8703039177422829 52.179277320183751, -2.8703053624799284 52.179276410455451, -2.8706531513709947 52.179112011434448, -2.8706545960961489 52.179111101701928, -2.8709877596150388 52.178946809646504, -2.8709863149020154 52.178947719383082, -2.8713340987089291 52.178783318368211, -2.8716818799461596 52.178618916335019, -2.8716876586984283 52.178615277354758, -2.8718438604832062 52.178526014158756, -2.8720148601250206 52.178445632021393, -2.87190788915951 52.178360112372673)))'
; 
SET @gmvalid = @gm.MakeValid(); 
SET @gt = @gmvalid.STAsText(); 

IF LEFT(@gt, 7) = 'POLYGON' 
  BEGIN 
      SET @gg = geography::STPolyFromText(@gt, 4326); 
  END 
ELSE 
  BEGIN 
      SET @gg = geography::STMPolyFromText(@gt, 4326); 
  END 

Error:

Msg 6522, Level 16, State 1, Line 25 A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": System.FormatException: 24142: Expected "MULTIPOLYGON" at position 1. The input has "GEOMETRYCOLL". System.FormatException: at Microsoft.SqlServer.Types.WellKnownTextReader.RecognizeToken(String token) at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type) at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid) at Microsoft.SqlServer.Types.SqlGeography.ParseText(OpenGisType type, SqlChars taggedText, Int32 srid) at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)

Answer 1

等待大神答复