1

The following query is used in an app to make shapes valid, however it does not work when using GEOMETRYCOLLECTIONs. I get the following error:

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"

While looking for a solution online, I came across ST_MakeValid(geometry input), but that gives 'ST_MakeValid' is not a recognized built-in function.

SQL Query:

DECLARE @g GEOMETRY;

SET @g = geometry::STGeomFromText('GEOMETRYCOLLECTION (LINESTRING (377965 203365.9, 377977 203363.9), POLYGON ((378282.8 203184.8, 378291.3 203193.3, 378290.3 203194.3, 378289.7 203194.7, 378281 203201.5, 378272.3 203210.3, 378272.1 203210.4, 378254.1 203227.4, 378254.2 203227.3, 378219.5 203261, 378215.7 203265.8, 378215.6 203265.8, 378210.6 203271.8, 378210.3 203272.3, 378203.5 203279, 378195.8 203289.5, 378194.9 203290.5, 378186.9 203297.5, 378186.6 203297.8, 378182.6 203300.8, 378181.7 203301.4, 378175.7 203304.4, 378174.9 203304.7, 378168.9 203306.7, 378167.7 203307, 378159 203307.9, 378149.2 203309.9, 378148.8 203309.9, 378141.8 203310.9, 378141 203311, 378135 203311, 378129.9 203312.7, 378129.2 203312.9, 378124.8 203313.8, 378118.7 203316.4, 378114.1 203319.1, 378114.3 203319, 378108.3 203323, 378108.1 203323.1, 378103.2 203326.1, 378085.5 203338.9, 378074.5 203346.9, 378073.7 203347.4, 378063.7 203352.4, 378062.2 203352.9, 378052.2 203354.9, 378052 203354.9, 378040 203356.9, 378039.6 203357, 378000.6 203361, 378000.5 203361, 377989.5 203362, 377989.9 203361.9, 377976.9 203363.9, 377965 203365.9, 377963 203354.1, 377975 203352.1, 377975.1 203352.1, 377988.1 203350.1, 377988.5 203350, 377999.5 203349, 377999.4 203349, 378038.1 203345.1, 378050 203343.1, 378049.8 203343.1, 378059.1 203341.3, 378064.3 203338.6, 378067.8 203336.9, 378078.5 203329.1, 378096.5 203316.1, 378096.9 203315.9, 378101.9 203312.9, 378101.7 203313, 378107.7 203309, 378107.9 203308.9, 378112.9 203305.9, 378113.6 203305.5, 378120.6 203302.5, 378121.8 203302.1, 378126.5 203301.2, 378132.1 203299.3, 378134 203299, 378140.6 203299, 378147.2 203298.1, 378146.8 203298.1, 378157.3 203296, 378165.8 203295.1, 378170.7 203293.4, 378175.8 203290.9, 378179.3 203288.3, 378186.5 203281.9, 378194.2 203271.5, 378194.8 203270.8, 378201.7 203263.8, 378206.4 203258.2, 378206.3 203258.3, 378210.3 203253.3, 378210.8 203252.7, 378245.8 203218.7, 378245.9 203218.6, 378263.9 203201.6, 378263.8 203201.8, 378273.3 203192.3, 378282 203185.5, 378282.8 203184.8)))', 0);

SET @g = @g.MakeValid();

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

SET @gm = 'GEOMETRYCOLLECTION (LINESTRING (-2.3204338274809784 51.728575525011415, -2.3202599586645087 51.728558017061047), POLYGON ((-2.3158212116936996 51.726959725510888, -2.3156986818238776 51.727036479747078, -2.315713222492648 51.727045431797244, -2.3157219343594697 51.727049004831144, -2.3158483192937425 51.727109804725814, -2.3159748300397363 51.727188586436164, -2.3159777319199928 51.727189477741796, -2.3162394039227223 51.7273416227615, -2.3162379498356924 51.727340727563124, -2.3167424585930618 51.727642369320471, -2.3167977775877344 51.727685377579519, -2.3167992254067666 51.72768537367336, -2.3168719938513953 51.727739124191395, -2.316876368771883 51.7277436079575, -2.316975242263521 51.727803581761847, -2.31708738568206 51.727897685997661, -2.3171004790753726 51.7279066417837, -2.3172167460502395 51.727969265754325, -2.317221108430001 51.72797195131249, -2.3172792105458071 51.727998767765044, -2.3172922788063568 51.728004127139833, -2.3173793376052458 51.728030865293789, -2.3173909391509118 51.728033531278726, -2.3174779350276378 51.728051278385543, -2.3174953279071215 51.728053928701783, -2.3176213459359207 51.728061679926107, -2.3177633595526439 51.7280792779884, -2.3177691508785565 51.72807926231598, -2.3178705621957425 51.72808797897595, -2.3178821511613537 51.728088846717625, -2.3179690210668418 51.728088611513662, -2.3180429678385019 51.728103696194232, -2.3180531152941959 51.728105466937826, -2.3181168767611049 51.728113386244281, -2.31820535881489 51.728136523473033, -2.3182721297685998 51.72816061859929, -2.318269227781204 51.728159727350636, -2.3183563506637688 51.728195455751482, -2.3183592526551582 51.728196346997954, -2.3184303862540436 51.728223127549242, -2.3186874631628696 51.728337516762053, -2.3188472319881193 51.728409012189374, -2.3188588463786717 51.728413476223309, -2.31891688670424 51.728431300889639, -2.3190039472780177 51.728458037835139, -2.3190256966013347 51.728462474318448, -2.3191706077006708 51.728480062808643, -2.3191735033888392 51.728480054937755, -2.3193473714934245 51.728497564496919, -2.3193531692128606 51.728498447843513, -2.3199180826866117 51.728532874677356, -2.3199195305322777 51.728532870732714, -2.3200788571072057 51.728541427683552, -2.3200730593682812 51.728540544372819, -2.3202614065109293 51.728558013112185, -2.3204338274809784 51.728575525011415, -2.320462033589092 51.728469352537225, -2.3202881651694192 51.728451844627592, -2.3202867173263946 51.728451848576782, -2.3200983706155642 51.72843437987693, -2.3200925728899668 51.728433496567092, -2.319933246692063 51.728424939634046, -2.3199346945342789 51.728424935689219, -2.3193741322590187 51.728391396215784, -2.3192017123924469 51.728373882759414, -2.3192046080738229 51.728373874887765, -2.3190698448528253 51.728358057094226, -2.3189943861489937 51.728333986023017, -2.318943604110701 51.728318839023245, -2.3187881916220161 51.728249130127985, -2.3185267588138641 51.728132954721332, -2.3185209548327754 51.728131172236566, -2.3184483734929948 51.728104395666016, -2.3184512754803452 51.728105286910257, -2.318364152696907 51.728069558579087, -2.3183612507135511 51.728068667332693, -2.3182886695679588 51.728041890662908, -2.3182785094783176 51.72803832174499, -2.3181769718449403 51.728011623490559, -2.3181595726251083 51.728008074177886, -2.318091467812371 51.728000166660621, -2.3180102694231897 51.727983303421105, -2.3179827417429659 51.727980680626843, -2.3178871850745582 51.727980939358439, -2.3177915716159272 51.7279731061361, -2.3177973629282587 51.727973090462328, -2.3176524539763546 51.727955500273708, -2.3176452085316921 51.727954620759583, -2.3175220864512571 51.727946861730061, -2.3174510357612816 51.727931768892233, -2.3173770390527686 51.727909491058746, -2.3173262013227194 51.727886251480577, -2.3172215547887887 51.72782899091137, -2.3171094175591032 51.7277357859001, -2.3171006865627835 51.727729515677545, -2.3170003463668234 51.7276668485942, -2.3169319465077649 51.727616682821683, -2.3169334006173412 51.727617578011355, -2.3168751733631332 51.727572779434944, -2.316867896537318 51.7275674043852, -2.3163590255231821 51.727263077571976, -2.3163575714363231 51.727262182375085, -2.3160958994978733 51.727110037616349, -2.3160973598507426 51.727111831914151, -2.3159664931653485 51.72703126382703, -2.3159592228075803 51.727026787817884, -2.3158328379570317 51.726965988041464, -2.3158212116936996 51.726959725510888)))';

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

select @g, @gg

Is there a way to validate GEOMETRYCOLLECTION without breaking it down and validating individual shapes?

Improve this question
12
  • 1
    Well, there's not, but also, the actual issue is that you have GEOMETRYCOLLECTIONs (rather than MULTIPOLYGONs), not that they are invalid.
    – geozelot
    Nov 10, 2021 at 15:30
  • I'm not sure I understand. What's wrong with GEOMETRYCOLLECTION? The app must be able to handle geometrycollections and must be able to make the shapes contained within valid. The above contains a linestring which cannot be contained within a multipolygon.
    – NickyLarson
    Nov 10, 2021 at 15:47
  • As far as I know ST_MakeValid is a PostGIS function, not SQL Server.
    – Berend
    Nov 10, 2021 at 16:05
  • Could you add a specific example with actual coordinates that fail? Because geometry::STGeomFromText('GEOMETRYCOLLECTION (LINESTRING (1 2, 3 4), POLYGON ((1 2, 3 4, 5 6, 1 2)))',0).MakeValid(); seems to work fine
    – Berend
    Nov 10, 2021 at 16:08
  • @Berend, it's on the second set of coordinates that the MakeValid() doesn't work. When applying it to a geocollection as a string. I've updated the above with the coordinates I'm using.
    – NickyLarson
    Nov 10, 2021 at 17:37

1 Answer 1

Reset to default
0

I have found the solution to this problem.

I was incorrect in thinking that the issue was that the Geometrycollection was not being validated. The problem was that STMPolyFromText() is not compatible with Geometrycollections. Instead, STGeomCollFromText() should be used.

Solution:

DECLARE @g GEOMETRY;
SET @g = geometry :: STGeomFromText('GEOMETRYCOLLECTION (LINESTRING (377965 203365.9, 377977 203363.9), POLYGON ((378282.8 203184.8, 378291.3 203193.3, 378290.3 203194.3, 378289.7 203194.7, 378281 203201.5, 378272.3 203210.3, 378272.1 203210.4, 378254.1 203227.4, 378254.2 203227.3, 378219.5 203261, 378215.7 203265.8, 378215.6 203265.8, 378210.6 203271.8, 378210.3 203272.3, 378203.5 203279, 378195.8 203289.5, 378194.9 203290.5, 378186.9 203297.5, 378186.6 203297.8, 378182.6 203300.8, 378181.7 203301.4, 378175.7 203304.4, 378174.9 203304.7, 378168.9 203306.7, 378167.7 203307, 378159 203307.9, 378149.2 203309.9, 378148.8 203309.9, 378141.8 203310.9, 378141 203311, 378135 203311, 378129.9 203312.7, 378129.2 203312.9, 378124.8 203313.8, 378118.7 203316.4, 378114.1 203319.1, 378114.3 203319, 378108.3 203323, 378108.1 203323.1, 378103.2 203326.1, 378085.5 203338.9, 378074.5 203346.9, 378073.7 203347.4, 378063.7 203352.4, 378062.2 203352.9, 378052.2 203354.9, 378052 203354.9, 378040 203356.9, 378039.6 203357, 378000.6 203361, 378000.5 203361, 377989.5 203362, 377989.9 203361.9, 377976.9 203363.9, 377965 203365.9, 377963 203354.1, 377975 203352.1, 377975.1 203352.1, 377988.1 203350.1, 377988.5 203350, 377999.5 203349, 377999.4 203349, 378038.1 203345.1, 378050 203343.1, 378049.8 203343.1, 378059.1 203341.3, 378064.3 203338.6, 378067.8 203336.9, 378078.5 203329.1, 378096.5 203316.1, 378096.9 203315.9, 378101.9 203312.9, 378101.7 203313, 378107.7 203309, 378107.9 203308.9, 378112.9 203305.9, 378113.6 203305.5, 378120.6 203302.5, 378121.8 203302.1, 378126.5 203301.2, 378132.1 203299.3, 378134 203299, 378140.6 203299, 378147.2 203298.1, 378146.8 203298.1, 378157.3 203296, 378165.8 203295.1, 378170.7 203293.4, 378175.8 203290.9, 378179.3 203288.3, 378186.5 203281.9, 378194.2 203271.5, 378194.8 203270.8, 378201.7 203263.8, 378206.4 203258.2, 378206.3 203258.3, 378210.3 203253.3, 378210.8 203252.7, 378245.8 203218.7, 378245.9 203218.6, 378263.9 203201.6, 378263.8 203201.8, 378273.3 203192.3, 378282 203185.5, 378282.8 203184.8)))', 0);
SET @g = @g.MakeValid();
DECLARE @gt NVARCHAR(MAX);
DECLARE @gm GEOMETRY;
DECLARE @gmvalid GEOMETRY;
DECLARE @gg GEOGRAPHY;
DECLARE @gmType AS NVARCHAR(MAX)
SET @gm = 'POLYGON ((-2.60317795114543 51.686954587857912, -2.6034085478583955 51.686890464094411, -2.603412875277507 51.686889542728146, -2.603556923126253 51.686843845969122, -2.6035612385765634 51.686842025529842, -2.6037627854087408 51.686769059088441, -2.603964331590976 51.686696092302455, -2.603970081494245 51.686693365340986, -2.6041570427713858 51.686611481946713, -2.6041584892214629 51.686611474504666, -2.6043166406329803 51.686538730370209, -2.6043209440474508 51.6865360108356, -2.6044933197953539 51.686445210672581, -2.6044918973098086 51.686447016253723, -2.6046787367951589 51.686356141357166, -2.6046816057121784 51.686354328325294, -2.6048683245656727 51.686254462453448, -2.6048683125787266 51.686253563386011, -2.6050837194877654 51.686135566804971, -2.6050837314789277 51.686136465872409, -2.6053709789425055 51.685982133376591, -2.6053724133753886 51.685981226852242, -2.6058462533409492 51.685718035557663, -2.6058448069197935 51.685718043020415, -2.6061607372219298 51.685545577993913, -2.606165052444001 51.685543757459307, -2.6061937404963591 51.685525626775636, -2.6060697743388097 51.6854498406802, -2.6060410862843275 51.68546797133385, -2.6057309057261331 51.685637708993191, -2.6057294593075064 51.685637716454508, -2.6052556194953818 51.685900907281081, -2.6052570539280273 51.685900000758124, -2.60496980659221 51.686054332977108, -2.6049698185810808 51.686055232044588, -2.6047544117537069 51.686173228418106, -2.6047543997690523 51.686172329350619, -2.6045691274389151 51.686272187595428, -2.6043837225415567 51.686362155806229, -2.6043822880768484 51.686363062318549, -2.6042127813580707 51.68645204929917, -2.6040560645561794 51.686523886786112, -2.604057511003492 51.686523879345309, -2.6038719843876192 51.686604856075725, -2.6036747538895191 51.68667600226015, -2.6034760882762495 51.686748054615883, -2.6033378025592104 51.6867919234128, -2.6031072062446383 51.686856047037224, -2.60317795114543 51.686954587857912))';

SET @gmvalid = @gm.MakeValid();
SET @gt = @gmvalid.STAsText();
SET @gmType = @gmvalid.STGeometryType();

IF @gmType = 'Polygon' BEGIN
SET @gg = GEOGRAPHY :: STPolyFromText(@gt, 4326);
END 
ELSE IF @gmType = 'GeometryCollection' BEGIN
SET @gg = GEOGRAPHY :: STGeomCollFromText (@gt, 4326);
END
ELSE BEGIN 
SET @gg = GEOGRAPHY :: STMPolyFromText(@gt, 4326);
END

select @g, @gg
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.