| Bug #78206 | ST_UNION() RETURNS AN INVALID GEOMETRYCOLLECTION | ||
|---|---|---|---|
| Submitted: | 25 Aug 2015 11:05 | Modified: | 30 Sep 2015 17:40 |
| Reporter: | Menelaos Karavelas | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S3 (Non-critical) |
| Version: | 5.7.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | st_union | ||
[25 Aug 2015 12:28]
Menelaos Karavelas
Posted by developer: The test case reported in this bug report was initially reported as part of BUG#21658405 (as an additional failing test case). Since the root case of the test case in this bug is different from the other/main test case in BUG#21658405, we have this new bug report.
[31 Aug 2015 7:26]
Norvald Ryeng
Posted by developer:
A simpler version of the original test case:
SELECT ST_ASTEXT(
ST_UNION(
ST_GEOMFROMTEXT('
LINESTRING(-9 -17,17 -11)
'),
ST_GEOMFROMTEXT('
GEOMETRYCOLLECTION(
LINESTRING(8 16,-8 -3),
POLYGON((2 3,-9 -7,12 -13,2 3)),
MULTILINESTRING((-2 2,11 -10),(6 0,-15 0,16 0))
)
')
)
) AS res;
SELECT ST_ISVALID(
ST_UNION(
ST_GEOMFROMTEXT('
LINESTRING(-9 -17,17 -11)
'),
ST_GEOMFROMTEXT('
GEOMETRYCOLLECTION(
LINESTRING(8 16,-8 -3),
POLYGON((2 3,-9 -7,12 -13,2 3)),
MULTILINESTRING((-2 2,11 -10),(6 0,-15 0,16 0))
)
')
)
) AS valid;
Note that the two queries are the same, the difference is just ST_AsText vs. ST_IsValid.
The invalid linestring in the output, (-1.2999999999999998 0,-1.2999999999999998 0), is the leftmost point where the polygon intersects the line along the x axis.
[30 Sep 2015 17:40]
Paul DuBois
Noted in 5.7.9, 5.8.0 changelogs. For some inputs, ST_Union() could return an invalid geometry collection.
Description: mysql> SELECT ST_astext(ST_UNION(ST_GEOMFROMTEXT('LINESTRING(12 6,9 4,-9 1,-4 -6,12 -9,-9 -17,17 -11,-16 17,19 -19,0 -16,6 -5,15 3,14 -5,18 13,-9 10,-11 8)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(MULTILINESTRING((-18 2,1 7),(-19 -3,-16 -12),(10 0,3 8,12 19,8 -15)),MULTILINESTRING((8 16,-8 -3),(18 3,8 12),(-19 4,20 14)),POLYGON((2 3,-9 -7,12 -13,2 3)),MULTILINESTRING((16 -7,-2 2,11 -10,-1 8),(6 0,-15 0,16 0,-6 -14)))'))); output: ST_astext(ST_UNION(ST_GEOMFROMTEXT('LINESTRING(12 6,9 4,-9 1,-4 -6,12 -9,-9 -17,17 -11,-16 17,19 -19,0 -16,6 -5,15 3,14 -5,18 13,-9 10,-11 8)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(MULTILINESTRING((-18 2,1 7),(-19 -3,-16 -12),(10 0,3 8,12 19,8 -15)),MULTI GEOMETRYCOLLECTION(MULTILINESTRING((8 16,-8 -3),(18 3,8 12),(-19 4,20 14),(16 -7,4.727272727272727 -1.3636363636363633),(-0.12903225806451693 1.0645161290322585,-2 2,-0.5610687022900758 0.6717557251908404),(8.931818181818183 -8.090909090909093,11 -10,-1 8),(6 0,3.875 0),(-1.2999999999999998 0,-1.2999999999999998 0),(3.875 0,16 0,7.32520325203252 -5.520325203252032),(0.6619718309859159 -9.76056338028169,-6 -14)),POLYGON((2 3,-9 -7,12 -13,2 3)),MULTILINESTRING((12 6,9 4,2.0943396226415096 2.849056603773585),(1.7755102040816322 2.795918367346939,-9 1,-5.535433070866141 -3.850393700787401),(9.168141592920355 -8.469026548672566,12 -9,9.98076923076923 -9.76923076923077),(6 -11.285714285714286,-9 -17,10.361702127659576 -12.53191489361702),(11.53781512605042 -12.260504201680673,17 -11,3.693548387096774 0.2903225806451615),(1.2758620689655178 2.3416927899686524,-16 17,-0.3297587131367301 0.8820375335120643),(9.9 -9.64,19 -19,0 -16,3.033707865168539 -10.438202247191011),(6.642857142857143 -4.428571428571429,15 3,14 -5,18 13,-9 10,-11 8),(-18 2,1 7),(-19 -3,-16 -12),(10 0,3 8,12 19,8.831683168316832 -7.9306930693069315),(8.357723577235772 -11.959349593495935,8 -15))) mysql> SELECT ST_ISVALID(ST_UNION(ST_GEOMFROMTEXT('LINESTRING(12 6,9 4,-9 1,-4 -6,12 -9,-9 -17,17 -11,-16 17,19 -19,0 -16,6 -5,15 3,14 -5,18 13,-9 10,-11 8)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(MULTILINESTRING((-18 2,1 7),(-19 -3,-16 -12),(10 0,3 8,12 19,8 -15)),MULTILINESTRING((8 16,-8 -3),(18 3,8 12),(-19 4,20 14)),POLYGON((2 3,-9 -7,12 -13,2 3)),MULTILINESTRING((16 -7,-2 2,11 -10,-1 8),(6 0,-15 0,16 0,-6 -14)))'))); output: ST_ISVALID(ST_UNION(ST_GEOMFROMTEXT('LINESTRING(12 6,9 4,-9 1,-4 -6,12 -9,-9 -17,17 -11,-16 17,19 -19,0 -16,6 -5,15 3,14 -5,18 13,-9 10,-11 8)'), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(MULTILINESTRING((-18 2,1 7),(-19 -3,-16 -12),(10 0,3 8,12 19,8 -15)),MULT 0 The st_union operation applied to a (essentially) a polygon a collection of linear geometries produces an output geometry collection that is invalid. The reason that the output geometry collection is invalid is because one of the multilinestrings in it contains an 1-point linestring (a linestring with two points that coincide). Such a linestring has topological dimension 0 and is considered as invalid by the st_isvalid function (st_is_valid does not accept linestrings that degenerate to a point as valid). How to repeat: As described above. Suggested fix: Go over the st_union output and report 1-point linestrings as points rather than as 1-point linestrings. If more than one such points are found they may be reported in a single multipoint.