MySQL Spatial: Is it possible to ignore results with "Inconsistent intersection points"?-CodePudding

I have a MySQL database with geolocations and I'd like to get the ST_INTERSECTION result from the intersecting polygons and a given polygon.

I need the ST_INTERSECTION result to measure if the resulting polygon is at least 50 % inside the given polygon.

The problem is that some of the results will cause an "Inconsistent intersection points" error.

Is it possible to ignore those results or validate that the ST_INTERSECTION will work before it crashes?

EDIT: The 2 polygons, which produce the error:

SELECT
ST_INTERSECTION(
    ST_GeomFromGeoJSON('{\"type\": \"Polygon\", \"coordinates\": [[[18.04682, 59.33574000000001], [18.0473, 59.33566], [18.04773, 59.3355], [18.05032, 59.33467], [18.05202, 59.33382000000001], [18.05403, 59.33475], [18.05554, 59.3351], [18.05601, 59.33522], [18.05601, 59.33522], [18.05605, 59.33523], [18.05605, 59.33523], [18.05886, 59.33592999999999], [18.05882, 59.33598], [18.05882, 59.33598], [18.0588, 59.336000000000006], [18.0588, 59.336000000000006], [18.05796, 59.33699], [18.05689, 59.3367], [18.05605, 59.33648000000001], [18.05605, 59.33648000000001], [18.05601, 59.336470000000006], [18.05601, 59.336470000000006], [18.05502, 59.33621000000001], [18.05457, 59.33666], [18.05397, 59.33728], [18.05354, 59.3377], [18.05311, 59.33758999999999], [18.05456, 59.336000000000006], [18.05456, 59.336000000000006], [18.05458, 59.33598], [18.05458, 59.33598], [18.05476, 59.33578], [18.05337, 59.33547000000001], [18.05122, 59.33592999999999], [18.05056, 59.33598], [18.05048, 59.336000000000006], [18.05048, 59.336000000000006], [18.05011, 59.33610999999999], [18.05056, 59.33622999999999], [18.05056, 59.33622999999999], [18.05112, 59.33637], [18.05056, 59.33698], [18.04831, 59.33631], [18.04803, 59.33635], [18.04734, 59.336000000000006], [18.04734, 59.336000000000006], [18.0473, 59.33598], [18.0473, 59.33598], [18.04682, 59.33574000000001]]]}'),
    ST_GeomFromGeoJSON('{\"type\": \"Polygon\", \"coordinates\": [[[18.05202, 59.33382000000001], [18.05287, 59.3332], [18.05287, 59.3332], [18.0529, 59.33318], [18.05289, 59.33318], [18.05294, 59.33315], [18.05427, 59.333549999999995], [18.05463, 59.3332], [18.05463, 59.3332], [18.05465, 59.33318], [18.05465, 59.33318], [18.05479, 59.33305000000001], [18.05453, 59.33297999999999], [18.0564, 59.33088], [18.05775, 59.33123], [18.0585, 59.3304], [18.0585, 59.3304], [18.05942, 59.32939], [18.06056, 59.32976], [18.06, 59.3304], [18.06, 59.3304], [18.05811, 59.33253], [18.05752, 59.33318], [18.05752, 59.33318], [18.05751, 59.33319], [18.0583, 59.333400000000005], [18.05897, 59.33356], [18.06011, 59.33388000000001], [18.0615, 59.33424999999999], [18.06129, 59.33448], [18.06051, 59.33427], [18.05736, 59.33347], [18.05697, 59.33380999999999], [18.05667, 59.33373], [18.05657, 59.33382000000001], [18.05665, 59.33385], [18.0559, 59.33466], [18.05444, 59.33432], [18.05431, 59.33449], [18.05577, 59.33485], [18.05554, 59.3351], [18.05403, 59.33475], [18.05202, 59.33382000000001]]]}')
)

SELECT
ST_INTERSECTION(
    ST_GeomFromGeoJSON('{\"type\": \"Polygon\", \"coordinates\": [[[18.04682, 59.33574000000001], [18.0473, 59.33566], [18.04773, 59.3355], [18.05032, 59.33467], [18.05202, 59.33382000000001], [18.05403, 59.33475], [18.05554, 59.3351], [18.05601, 59.33522], [18.05601, 59.33522], [18.05605, 59.33523], [18.05605, 59.33523], [18.05886, 59.33592999999999], [18.05882, 59.33598], [18.05882, 59.33598], [18.0588, 59.336000000000006], [18.0588, 59.336000000000006], [18.05796, 59.33699], [18.05689, 59.3367], [18.05605, 59.33648000000001], [18.05605, 59.33648000000001], [18.05601, 59.336470000000006], [18.05601, 59.336470000000006], [18.05502, 59.33621000000001], [18.05457, 59.33666], [18.05397, 59.33728], [18.05354, 59.3377], [18.05311, 59.33758999999999], [18.05456, 59.336000000000006], [18.05456, 59.336000000000006], [18.05458, 59.33598], [18.05458, 59.33598], [18.05476, 59.33578], [18.05337, 59.33547000000001], [18.05122, 59.33592999999999], [18.05056, 59.33598], [18.05048, 59.336000000000006], [18.05048, 59.336000000000006], [18.05011, 59.33610999999999], [18.05056, 59.33622999999999], [18.05056, 59.33622999999999], [18.05112, 59.33637], [18.05056, 59.33698], [18.04831, 59.33631], [18.04803, 59.33635], [18.04734, 59.336000000000006], [18.04734, 59.336000000000006], [18.0473, 59.33598], [18.0473, 59.33598], [18.04682, 59.33574000000001]]]}'),
    ST_GeomFromGeoJSON('{\"type\": \"Polygon\", \"coordinates\": [[[18.05202, 59.33382000000001], [18.05287, 59.3332], [18.05287, 59.3332], [18.0529, 59.33318], [18.05289, 59.33318], [18.05294, 59.33315], [18.05427, 59.333549999999995], [18.05463, 59.3332], [18.05463, 59.3332], [18.05465, 59.33318], [18.05465, 59.33318], [18.05479, 59.33305000000001], [18.05453, 59.33297999999999], [18.0564, 59.33088], [18.05775, 59.33123], [18.0585, 59.3304], [18.0585, 59.3304], [18.05942, 59.32939], [18.06056, 59.32976], [18.06, 59.3304], [18.06, 59.3304], [18.05811, 59.33253], [18.05752, 59.33318], [18.05752, 59.33318], [18.05751, 59.33319], [18.0583, 59.333400000000005], [18.05897, 59.33356], [18.06011, 59.33388000000001], [18.0615, 59.33424999999999], [18.06129, 59.33448], [18.06051, 59.33427], [18.05736, 59.33347], [18.05697, 59.33380999999999], [18.05667, 59.33373], [18.05657, 59.33382000000001], [18.05665, 59.33385], [18.0559, 59.33466], [18.05444, 59.33432], [18.05431, 59.33449], [18.05577, 59.33485], [18.05554, 59.3351], [18.05403, 59.33475], [18.05202, 59.33382000000001]]]}')
)

CodePudding user response：

I tried some thing with this statement (the second one you provided):

SELECT
ST_INTERSECTION(
    ST_GeomFromGeoJSON('{\"type\": \"Polygon\", \"coordinates\": [[[18.04682, 59.33574000000001], [18.0473, 59.33566], [18.04773, 59.3355], [18.05032, 59.33467], [18.05202, 59.33382000000001], [18.05403, 59.33475], [18.05554, 59.3351], [18.05601, 59.33522], [18.05601, 59.33522], [18.05605, 59.33523], [18.05605, 59.33523], [18.05886, 59.33592999999999], [18.05882, 59.33598], [18.05882, 59.33598], [18.0588, 59.336000000000006], [18.0588, 59.336000000000006], [18.05796, 59.33699], [18.05689, 59.3367], [18.05605, 59.33648000000001], [18.05605, 59.33648000000001], [18.05601, 59.336470000000006], [18.05601, 59.336470000000006], [18.05502, 59.33621000000001], [18.05457, 59.33666], [18.05397, 59.33728], [18.05354, 59.3377], [18.05311, 59.33758999999999], [18.05456, 59.336000000000006], [18.05456, 59.336000000000006], [18.05458, 59.33598], [18.05458, 59.33598], [18.05476, 59.33578], [18.05337, 59.33547000000001], [18.05122, 59.33592999999999], [18.05056, 59.33598], [18.05048, 59.336000000000006], [18.05048, 59.336000000000006], [18.05011, 59.33610999999999], [18.05056, 59.33622999999999], [18.05056, 59.33622999999999], [18.05112, 59.33637], [18.05056, 59.33698], [18.04831, 59.33631], [18.04803, 59.33635], [18.04734, 59.336000000000006], [18.04734, 59.336000000000006], [18.0473, 59.33598], [18.0473, 59.33598], [18.04682, 59.33574000000001]]]}'),
    ST_GeomFromGeoJSON('{\"type\": \"Polygon\", \"coordinates\": [[[18.05202, 59.33382000000001], [18.05287, 59.3332], [18.05287, 59.3332], [18.0529, 59.33318], [18.05289, 59.33318], [18.05294, 59.33315], [18.05427, 59.333549999999995], [18.05463, 59.3332], [18.05463, 59.3332], [18.05465, 59.33318], [18.05465, 59.33318], [18.05479, 59.33305000000001], [18.05453, 59.33297999999999], [18.0564, 59.33088], [18.05775, 59.33123], [18.0585, 59.3304], [18.0585, 59.3304], [18.05942, 59.32939], [18.06056, 59.32976], [18.06, 59.3304], [18.06, 59.3304], [18.05811, 59.33253], [18.05752, 59.33318], [18.05752, 59.33318], [18.05751, 59.33319], [18.0583, 59.333400000000005], [18.05897, 59.33356], [18.06011, 59.33388000000001], [18.0615, 59.33424999999999], [18.06129, 59.33448], [18.06051, 59.33427], [18.05736, 59.33347], [18.05697, 59.33380999999999], [18.05667, 59.33373], [18.05657, 59.33382000000001], [18.05665, 59.33385], [18.0559, 59.33466], [18.05444, 59.33432], [18.05431, 59.33449], [18.05577, 59.33485], [18.05554, 59.3351], [18.05403, 59.33475], [18.05202, 59.33382000000001]]]}')
);

When adding ST_GeomFromText(ST_ASTEXT(......)) (two times):

SELECT
ST_INTERSECTION(
    ST_GeomFromText(ST_ASTEXT(ST_GeomFromGeoJSON('{\"type\": \"Polygon\", \"coordinates\": [[[18.04682, 59.33574000000001], [18.0473, 59.33566], [18.04773, 59.3355], [18.05032, 59.33467], [18.05202, 59.33382000000001], [18.05403, 59.33475], [18.05554, 59.3351], [18.05601, 59.33522], [18.05601, 59.33522], [18.05605, 59.33523], [18.05605, 59.33523], [18.05886, 59.33592999999999], [18.05882, 59.33598], [18.05882, 59.33598], [18.0588, 59.336000000000006], [18.0588, 59.336000000000006], [18.05796, 59.33699], [18.05689, 59.3367], [18.05605, 59.33648000000001], [18.05605, 59.33648000000001], [18.05601, 59.336470000000006], [18.05601, 59.336470000000006], [18.05502, 59.33621000000001], [18.05457, 59.33666], [18.05397, 59.33728], [18.05354, 59.3377], [18.05311, 59.33758999999999], [18.05456, 59.336000000000006], [18.05456, 59.336000000000006], [18.05458, 59.33598], [18.05458, 59.33598], [18.05476, 59.33578], [18.05337, 59.33547000000001], [18.05122, 59.33592999999999], [18.05056, 59.33598], [18.05048, 59.336000000000006], [18.05048, 59.336000000000006], [18.05011, 59.33610999999999], [18.05056, 59.33622999999999], [18.05056, 59.33622999999999], [18.05112, 59.33637], [18.05056, 59.33698], [18.04831, 59.33631], [18.04803, 59.33635], [18.04734, 59.336000000000006], [18.04734, 59.336000000000006], [18.0473, 59.33598], [18.0473, 59.33598], [18.04682, 59.33574000000001]]]}'))),
    ST_GeomFromText(ST_ASTEXT(ST_GeomFromGeoJSON('{\"type\": \"Polygon\", \"coordinates\": [[[18.05202, 59.33382000000001], [18.05287, 59.3332], [18.05287, 59.3332], [18.0529, 59.33318], [18.05289, 59.33318], [18.05294, 59.33315], [18.05427, 59.333549999999995], [18.05463, 59.3332], [18.05463, 59.3332], [18.05465, 59.33318], [18.05465, 59.33318], [18.05479, 59.33305000000001], [18.05453, 59.33297999999999], [18.0564, 59.33088], [18.05775, 59.33123], [18.0585, 59.3304], [18.0585, 59.3304], [18.05942, 59.32939], [18.06056, 59.32976], [18.06, 59.3304], [18.06, 59.3304], [18.05811, 59.33253], [18.05752, 59.33318], [18.05752, 59.33318], [18.05751, 59.33319], [18.0583, 59.333400000000005], [18.05897, 59.33356], [18.06011, 59.33388000000001], [18.0615, 59.33424999999999], [18.06129, 59.33448], [18.06051, 59.33427], [18.05736, 59.33347], [18.05697, 59.33380999999999], [18.05667, 59.33373], [18.05657, 59.33382000000001], [18.05665, 59.33385], [18.0559, 59.33466], [18.05444, 59.33432], [18.05431, 59.33449], [18.05577, 59.33485], [18.05554, 59.3351], [18.05403, 59.33475], [18.05202, 59.33382000000001]]]}')))
);

The error seems to be gone.

This might be the effect of rounding which I did see somewhere on the internet, but unfortunately did not store the link