I'm using PHP to physically locate items in a MYSQL table based on their relationship to other items. Specifically, I'm creating the spatial WKT by running a series of ST_UNIONs on the result. In most cases, it works perfectly. However, with seemingly random items, the resulting geometry is all sorts of messed up. The result should be an ordinary polygon, like this
POLYGON((####.#### ####.####, ####.####, ####.#### ... ))
Instead, it's returning polygons with multipolygon styling, even though none of the original shapes are multi-polygon
POLYGON((####.#### ####.####, ####.#### ####.#### ... ) , (####.#### ####.####, ####.#### ####.#### ... ))
I'm using the following PHP code to perform the operations:
foreach( $geomlist as $geom ) {
$joinquery = "SELECT ST_AsText(ST_Union(ST_GeomFromText('$shapeJoined[0]'),ST_GeomFromText('$geom')))";
try {
$shapeQuery = $mysqli->query( $joinquery );
$shapeJoined = $shapeQuery->fetch_row();
} catch( \Exception $e ) {
\Log::exception( $e );
print_r($geom);
echo "Union failed $subNameFirst # $i Bypassing: <br />";
continue;
}
}
As an example, here's some original data, and the query where it fails
POLYGON((-12951395.3140387 5399179.48135967,-12951395.4053932 5399107.85297065,-12951395.414582 5399096.74529657,-12951395.4420126 5399075.66745855,-12951395.4957161 5399034.56892463,-12951395.5424425 5398998.74019955,-12951395.5903323 5398962.08545828,-...
POLYGON((-12951748.2267166 5399362.90401563,-12951748.3000896 5399301.78457083,-12951712.5979939 5399301.74157136,-12951676.8956147 5399301.69830706,-12951641.1933875 5399301.65477479,-12951605.4910067 5399301.61123401,-12951569.7887533 5399301.56755406,...
POLYGON((-12951939.7462742 5399368.07535402,-12951944.270232 5399359.20794296,-12951948.8797615 5399361.15560363,-12951953.7383569 5399362.34660684,-12951958.7235481 5399362.75093078,-12951982.4092019 5399362.78039813,-12951991.4551735 5399288.60375317,-...
POLYGON((-12951939.7462742 5399368.07535402,-12951944.270232 5399359.20794296,-12951948.8797615 5399361.15560363,-12951953.7383569 5399362.34660684,-12951958.7235481 5399362.75093078,-12951982.4092019 5399362.78039813,-12951991.4551735 5399288.60375317,-12951987.3644991 5399226.53208933,-12951918.183864 5399145.26093471,-12951913.4684493 5399135.83641208,-12951913.8814454 5399135.61163775,-12951904.3332945 5399116.55867888,-12951903.927882 5399116.74357765,-12951899.2571852 5399107.40628981,-12951861.8288395 5399037.82696136,-12951864.4613895 5398902.25022501,-12951953.791619 5398902.40734603,-12951954.4847977 5398623.25755837,-12952000.7970468 5398623.4495414,-12952230.8586126 5398624.40029873,-12952512.1274292 5398625.78936691,-12952511.1921024 5398904.0792081,-12952492.2878344 5398904.03597947,-12952492.2882982 5398929.32555675,-12952488.9274721 5398929.31729989,-12952297.0652336 5398928.84644612,-12952228.4114637 5398928.67353007,-12952228.3891507 5399025.07900224,-12952210.7575255 5399108.21695781,-12952176.6696972 5399107.91749672,-12952126.6082496 5399155.36771784,-12952126.8464588 5399258.89928137,-12952126.9072956 5399326.80981195,-12952127.010758 5399389.1582418,-12952127.1215953 5399460.45076972,-12952109.3111236 5399460.03183488,-12951934.5839858 5399459.81089523,-12951934.6990876 5399377.96826722,-12951939.7462742 5399368.07535402))
The MYSQL Query fails with the error "MySQL Error Geometry overlay calculation error: geometry data is invalid in function st_union. on query"
Here's the resulting query causing the error:
SELECT ST_AsText(ST_Union(ST_GeomFromText('POLYGON((-12951711.3355789 5398901.82011831,-12951677.2662006 5398901.65918194,-12951538.9488948 5398901.28128935,-12951396.2030745 5398900.92353019,-12951395.6370876 5398926.25726792,-12951395.5903323 5398962.08545828,-12951395.5424425 5398998.74019955,-12951395.4957161 5399034.56892463,-12951395.4420126 5399075.66745855,-12951395.414582 5399096.74529657,-12951395.4053932 5399107.85297065,-12951395.3140387 5399179.48135967,-12951395.0929387 5399345.56286133,-12951394.9406646 5399459.6775143,-12951471.802297 5399459.82795907,-12951492.952804 5399459.86915312,-12951620.8165043 5399460.07442554,-12951773.3346841 5399460.31696633,-12951907.7619755 5399459.77658898,-12951934.5839858 5399459.81089523,-12952109.3111236 5399460.03183488,-12952127.1215953 5399460.45076972,-12952127.010758 5399389.1582418,-12952126.9072956 5399326.80981195,-12952126.8464588 5399258.89928137,-12952126.6082496 5399155.36771784,-12952176.6696972 5399107.91749672,-12952210.7575255 5399108.21695781,-12952228.3891507 5399025.07900224,-12952228.4114637 5398928.67353007,-12952297.0652336 5398928.84644612,-12952488.9274721 5398929.31729989,-12952492.2882982 5398929.32555675,-12952492.2878344 5398904.03597947,-12952511.1921024 5398904.0792081,-12952512.1274292 5398625.78936691,-12952230.8586126 5398624.40029873,-12952000.7970468 5398623.4495414,-12951954.4847977 5398623.25755837,-12951953.791619 5398902.40734603,-12951864.4613895 5398902.25022501,-12951815.5836553 5398902.04220092,-12951711.3355789 5398901.82011831),(-12951944.270232 5399359.20794296,-12951948.8797615 5399361.15560363,-12951953.7383576 5399362.34660321,-12951948.8797626 5399361.1556001,-12951944.270232 5399359.20794296))'),ST_GeomFromText('POLYGON((-12951939.7462742 5399368.07535402,-12951944.270232 5399359.20794296,-12951948.8797615 5399361.15560363,-12951953.7383569 5399362.34660684,-12951958.7235481 5399362.75093078,-12951982.4092019 5399362.78039813,-12951991.4551735 5399288.60375317,-12951987.3644991 5399226.53208933,-12951918.183864 5399145.26093471,-12951913.4684493 5399135.83641208,-12951913.8814454 5399135.61163775,-12951904.3332945 5399116.55867888,-12951903.927882 5399116.74357765,-12951899.2571852 5399107.40628981,-12951861.8288395 5399037.82696136,-12951864.4613895 5398902.25022501,-12951953.791619 5398902.40734603,-12951954.4847977 5398623.25755837,-12952000.7970468 5398623.4495414,-12952230.8586126 5398624.40029873,-12952512.1274292 5398625.78936691,-12952511.1921024 5398904.0792081,-12952492.2878344 5398904.03597947,-12952492.2882982 5398929.32555675,-12952488.9274721 5398929.31729989,-12952297.0652336 5398928.84644612,-12952228.4114637 5398928.67353007,-12952228.3891507 5399025.07900224,-12952210.7575255 5399108.21695781,-12952176.6696972 5399107.91749672,-12952126.6082496 5399155.36771784,-12952126.8464588 5399258.89928137,-12952126.9072956 5399326.80981195,-12952127.010758 5399389.1582418,-12952127.1215953 5399460.45076972,-12952109.3111236 5399460.03183488,-12951934.5839858 5399459.81089523,-12951934.6990876 5399377.96826722,-12951939.7462742 5399368.07535402))')))
Since the original geometries all seem to be fine, and other similar polygons seem to work using this process, I'm curious on why this wouldn't be correctly applying the ST_UNION. Could it be due to crisscrossing geometry or something? Any help or insight would be greatly appreciated.
The query will also very occasionally fail, even though the geometry seems fine. Here's one of those, just in case it helps somehow:
SELECT ST_AsText(ST_Union(ST_GeomFromText('POLYGON((-12971740.3571383
5393662.64528624,-12971737.4859167 5393665.00447575,-12971732.2755485 5393668.82321108,-12971722.5225529 5393667.68635881,-12971713.7034295 5393658.17547535,-12971704.2879096 5393649.25895586,-12971694.3156833 5393640.97438376,-12971681.4666079 5393657.62803543,-12971646.4314763 5393693.43836315,-12971654.7738314 5393699.86234216,-12971662.5666727 5393706.94733339,-12971669.7581403 5393714.64619007,-12971682.1323959 5393731.66187434,-12971687.4155324 5393741.20671365,-12971691.8775947 5393751.16475156,-12971695.4867776 5393761.46501499,-12971698.137629 5393771.66423823,-12971699.9520571 5393782.04623065,-12971700.8203384 5393790.85082552,-12971701.0867317 5393799.69440126,-12971701.0733284 5393803.0813849,-12971700.9898141 5393824.14475605,-12971700.9062833 5393845.20639524,-12971700.8227686 5393866.26980729,-12971700.7392541 5393887.33326675,-12971700.7165349 5393893.06251008,-12971698.1797833 5393912.60033493,-12971696.5448753 5393925.1872069,-12971689.2408459 5393937.84469319,-12971674.2508454 5393963.06896215,-12971641.3929639 5393988.22255328,-12971641.3336323 5393988.26797533,-12971652.9218868 5393988.32105273,-12971631.5128171 5393988.22297639,-12971606.32565 5393988.10742759,-12971584.9164723 5393988.0091902,-12971551.7533669 5393987.85705247,-12971530.7655011 5393987.76138052,-12971496.2511583 5393987.60307772,-12971466.3182382 5393987.46556637,-12971431.6930854 5393987.30627819,-12971410.7069609 5393987.2069195,-12971377.1261421 5393987.0513689,-12971377.0160062 5394036.76004172,-12971376.966312 5394057.80647239,-12971388.4053982 5394057.87961012,-12971388.1585225 5394111.79119806,-12971416.3850079 5394111.92209408,-12971417.5439888 5394111.92744551,-12971442.7315447 5394112.04312982,-12971464.5607597 5394112.14332673,-12971485.5503094 5394112.2396738,-12971510.7378656 5394112.35522634,-12971531.7275226 5394112.45146734,-12971552.4107621 5394112.54626727,-12971553.5567399 5394112.55172328,-12971571.0933406 5394112.63482943,-12971583.7817759 5394112.69258225,-12971605.8669961 5394112.79306859,-12971625.9864585 5394112.88456902,-12971627.2764531 5394112.89039293,-12971658.341114 5394113.03047915,-12971673.4275223 5394113.09854032,-12971689.4030997 5394113.17172614,-12971715.4108174 5394113.29084404,-12971717.6298541 5394113.31917014,-12971744.3453053 5394113.66032896,-12971823.3162046 5394114.66827307,-12971823.4506064 5394013.62416029,-12971823.4603907 5394006.26132903,-12971823.5117011 5393983.86507062,-12971823.5432511 5393970.10796384,-12971823.5801085 5393954.08923607,-12971823.6263595 5393933.98126742,-12971823.648624 5393924.31349525,-12971823.7094667 5393897.85449478,-12971823.7171215 5393894.53585412,-12971823.7920548 5393861.77008994,-12971823.7923012 5393861.67544891,-12971823.7865556 5393838.53083719,-12971823.7859637 5393838.47885899,-12971823.7549888 5393835.16081763,-12971823.878095 5393804.04129698,-12971823.8801094 5393803.53670314,-12971823.9768885 5393771.88707767,-12971823.9838301 5393769.62206843,-12971824.0799107 5393738.22045509,-12971824.0876055 5393735.70761006,-12971824.1897512 5393702.31603185,-12971824.1913277 5393701.79327512,-12971824.259352 5393679.55903747,-12971824.2951042 5393667.87906258,-12971824.3916491 5393652.22266121,-12971824.5040588 5393633.99030071,-12971824.5345516 5393624.01782202,-12971824.6077817 5393600.07628049,-12971824.6343755 5393595.75129814,-12971824.8083705 5393567.48114384,-12971824.816491 5393566.16145181,-12971824.9755796 5393539.21518288,-12971825.0199942 5393531.69386543,-12971825.088459 5393510.72959495,-12971825.1289243 5393498.33314849,-12971825.1769169 5393482.64480912,-12971825.2326486 5393464.41961992,-12971825.2931007 5393454.56006307,-12971825.4413577 5393430.5052829,-12971825.4458211 5393429.00074356,-12971825.5428748 5393396.35508533,-12971825.5473475 5393395.31282905,-12971825.6373458 5393374.23779759,-12971825.641822 5393373.1843818,-12971825.9974357 5393309.99096726,-12971826.004231 5393299.45923848,-12971618.6313056 5393299.20133689,-12971577.0493164 5393299.14909151,-12971587.5293751 5393309.69257214,-12971587.4783749 5393350.69684286,-12971580.0576034 5393358.12533395,-12971574.7979891 5393358.11874386,-12971574.5496908 5393358.11842517,-12971574.5265479 5393379.18092993,-12971574.7717302 5393379.18122385,-12971592.8719691 5393379.20390044,-12971592.810159 5393428.91158943,-12971592.8100965 5393428.95111535,-12971622.8791466 5393428.98426655,-12971653.2870354 5393429.01765105,-12971683.6948159 5393429.05093635,-12971714.0734189 5393429.08412529,-12971714.0196198 5393478.79195038,-12971716.1740225 5393499.85777302,-12971716.1200503 5393549.77670833,-12971751.5907371 5393549.81537321,-12971751.5527454 5393566.80468937,-12971751.4618005 5393637.92243957,-12971751.3084866 5393641.00859485,-12971750.8741629 5393644.06772804,-12971750.1624038 5393647.0740299,-12971749.1793152 5393650.00231451,-12971747.9331034 5393652.8279154,-12971746.434186 5393655.52706205,-12971744.6952487 5393658.07714897,-12971742.7308674 5393660.4566314,-12971740.3571383 5393662.64528624))'),ST_GeomFromText('POLYGON((-12971580.7087918 5393882.68793489,-12971580.8287494 5393852.86507988,-12971580.8289072 5393852.83002706,-12971580.8389533 5393850.32025705,-12971580.9474966 5393823.03925753,-12971581.0487376 5393797.58613386,-12971581.0488833 5393797.55555712,-12971581.0491351 5393796.73843188,-12971576.0644211 5393791.75600025,-12971599.1253548 5393747.21063298,-12971600.9702944 5393724.96283659,-12971640.6132994 5393689.5577338,-12971646.4314763 5393693.43836315,-12971654.7738314 5393699.86234216,-12971662.5666727 5393706.94733339,-12971669.7581403 5393714.64619007,-12971682.1323959 5393731.66187434,-12971687.4155324 5393741.20671365,-12971691.8775947 5393751.16475156,-12971695.4867776 5393761.46501499,-12971696.0195508 5393763.26644323,-12971698.3333714 5393772.58303457,-12971699.9520571 5393782.04623065,-12971700.8203384 5393790.85082552,-12971701.0867317 5393799.69440126,-12971701.0865506 5393799.74422951,-12971701.0733284 5393803.0813849,-12971700.9898141 5393824.14475605,-12971700.9322964 5393838.64163537,-12971700.9320273 5393838.70510769,-12971700.9062833 5393845.20639524,-12971700.8227686 5393866.26980729,-12971700.7392541 5393887.33326675,-12971700.7165349 5393893.06251008,-12971698.1797833 5393912.60033493,-12971696.5448753 5393925.1872069,-12971689.2408459 5393937.84469319,-12971674.7308196 5393962.26129462,-12971674.2508454 5393963.06896215,-12971641.3929639 5393988.22255328,-12971641.3336323 5393988.26797533,-12971631.5128171 5393988.22297639,-12971606.32565 5393988.10742759,-12971606.1025839 5393988.10638819,-12971584.9164723 5393988.0091902,-12971583.016408 5393988.00049605,-12971551.7533669 5393987.85705247,-12971551.1040518 5393987.85406783,-12971530.7655011 5393987.76138052,-12971530.1125319 5393987.75837466,-12971496.2511583 5393987.60307772,-12971480.6392795 5393987.53138906,-12971480.6549908 5393975.01196988,-12971480.6829788 5393952.7002373,-12971480.7110735 5393930.330533,-12971480.7393123 5393907.82585146,-12971530.2298489 5393907.88825463,-12971551.2635868 5393907.9146891,-12971551.2613628 5393909.68821075,-12971558.6611877 5393917.15048104,-12971580.5698168 5393917.22976026,-12971580.7087918 5393882.68793489))')))
This is not multipolygon, but a polygon with hole. First loop describes polygon shell, subsequent loop(s) describe hole(s).
It is quite normal to get such polygon even when joining polygons without holes. Imagine e.g. three polygons that buffer each edge of a triangle, their union is polygon that buffers triangle, leaving the hole in the center.