MySQL ERROR 3037: Invalid GIS data provided to function st_within

user_1762087 0
Viewed 797

Following query:

SELECT st_contains(ST_GeomFromText('POLYGON((
9.2949170148074 3.5550157117451,
12.230624391667 3.5550157117451,
12.24455565975 4.9035765788215,
9.300807190941 4.8942904468525,
9.3019824958588 3.5550157117451,
9.2949170148074 3.5550157117451
))'),ST_GeomFromText("POINT( 6.31  8.92)"))

issues error Invalid GIS data provided to function st_within.

Although I don't see anything wrong with the query (last point is the same as first, points create ring, syntax is correct). When I remove the second to last point (9.3019824958588 3.5550157117451) then the query succeeds:

SELECT st_contains(ST_GeomFromText('POLYGON((
9.2949170148074 3.5550157117451,
12.230624391667 3.5550157117451,
12.24455565975 4.9035765788215,
9.300807190941 4.8942904468525,
9.2949170148074 3.5550157117451))'),ST_GeomFromText("POINT( 6.31  8.92)"))

Did I miss something?

Can I somehow further debug the message about Invalid GIS data to be more useful?

I'm using MySQL 5.7.31-0ubuntu0.18.04.1

mysql
1 Answers

I believe I found the answer: I tried ST_IsValid on both polygons, first one returned false (probably because it intersects itself), second one true. However, I tried several other self-intersecting polygons to feed the st_contains function without raising an error.

Then according to https://dev.mysql.com/doc/refman/5.7/en/geometry-well-formedness-validity.html

Spatial computations may detect some cases of invalid geometries and raise an error, but they may also return an undefined result without detecting the invalidity.

So whenever I got some result using self-intersecting polygons in the past, it was undefined and only this particular polygon in this question raised an error.

Conclusion is that if one uses spatial functions, one should check validity of geometry beforehand using ST_IsValid, because MySQL does not check it on inserts, nor updates - according to documentation:

It is permitted to insert, select, and update geometrically invalid geometries, but they must be syntactically well-formed. Due to the computational expense, MySQL does not check explicitly for geometric validity.

One point to add, this functionality has apparently changed between MySQL 5.6 and 5.7, in 5.6 the "faulty" polygon does not raise an error, whereas in 5.7 does.

user_17620870
Related