ST_EQUALS is not returning correct result?-CodePudding

I've come to these weird results when running ST_EQUALS on two Polygons:

SELECT ST_EQUALS(
ST_GEOMFROMTEXT(
'POLYGON((81.65 34.8667,81.65 5.9167,75.9833 34.8667,75.9833 5.9167,81.65 34.8667))',4326), 
ST_GEOMFROMTEXT(
'POLYGON((81.65 34.8667,81.65 5.9167,75.9833 34.8667,75.9833 5.9167,81.65 34.8667))',4326));

The coordinates of the polygon are exactly alike and yet MYSQL returns 0 indicating they are not equal.

When I ran the query without SRID (default = 0),

SELECT ST_EQUALS(
ST_GEOMFROMTEXT(
'POLYGON((81.65 34.8667,81.65 5.9167,75.9833 34.8667,75.9833 5.9167,81.65 34.8667))'), 
ST_GEOMFROMTEXT(
'POLYGON((81.65 34.8667,81.65 5.9167,75.9833 34.8667,75.9833 5.9167,81.65 34.8667))'));

MySQL returns 1 indicating they are indeed equal.

Is there something I'm missing here? Thank you.

CodePudding user response：

It is an invalid polygon:

SELECT ST_IsValid(ST_GEOMFROMTEXT('POLYGON((81.65 34.8667,81.65 5.9167,75.9833 34.8667,75.9833 5.9167,81.65 34.8667))',4326)) AS v

0

And with invalid geometries, the result of any geospatial function is undefined - it could give correct result, or could give random value. It seems to work as you expected with geometric SRID (0), and give different result with geodesic SRID (4326).

See this for more details on valid geometries: https://dev.mysql.com/doc/refman/5.7/en/geometry-well-formedness-validity.html

The rule Polygons are not self-intersecting is broken here.

With invalid geometry, this doc says "Due to the computational expense, MySQL does not check explicitly for geometric validity. 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."