Check well-formed and/or valid geometries in mysql

Question

Migrating a MariaDB table with geometric data to MySql, some data cannot be inserted, because they are not well-formed, even if it's not a issue for MariaDB.

This request works on MariaDB (10.2).

CREATE TABLE IF NOT EXISTS geo (
    id INT AUTO_INCREMENT NOT NULL,
    value GEOMETRY NOT NULL,
    SPATIAL INDEX idx_value (value),
    PRIMARY KEY(id)
) ENGINE = InnoDB;
INSERT INTO geo (value) SELECT ST_GeomFromText('LINESTRING(1 2)');

Not on MySql (5.7.20), where the error is:

3037 - Invalid GIS data provided to function st_geometryfromtext.

There are three functions to identify such geometries in MySql: ST_IsSimple(), ST_IsValid(), and ST_Validate() but they don't work with badly formatted geometries:

SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 2)'));
SELECT ST_IsValid(ST_GeomFromText('LINESTRING(1 2)'));
SELECT ST_AsText(ST_Validate(ST_GeomFromText('LINESTRING(1 1)')));

3055 - Geometry byte string must be little endian.

This example comes from https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html, but it doesn't work. So it's strange (the doc was not updated for 5.7). More details about validity on mysql: https://dev.mysql.com/doc/refman/5.7/en/geometry-well-formedness-validity.html (mysql accepts any syntactically well-formed input, but not the geometrically invalid).

Similar issues here:

MySQL spatial geometry validate wkt, where the answer is : the functions provided by MySQL to test validity of geometries requires well formed geometry as input...
MySQL 5.7: Invalid GIS data, where there is the idea to use a stored function and to create an exception handler, so a bit complicated.
A bug report asking for the same issue, but without response: https://bugs.mysql.com/bug.php?id=76595
https://github.com/creof/doctrine2-spatial/issues/155 (doctrine php orm), that says that the result is changing between versions of mysql.

But none of them answer to the issue: how to identify badly formatted geometries on mysql 5.7?

@RickJames It's ubuntu 16.04, but it's related to mysql 5.7, not to the os. mysql 5.7 changed the backend to manage geometries, so there are issues when there is an upgrade from 5.6 or from mariadb. — Daniel-KM, Feb 02 '19 at 07:16

score 1 · Answer 1 · answered Feb 02 '19 at 13:27

LineString needs at least two Points. Perhaps 5.6 was negligent in pointing that out.

mysql> SELECT hex(ST_GeomFromText('LINESTRING(1 1)'));
ERROR 3037 (22023): Invalid GIS data provided to function st_geometryfromtext.

mysql> SELECT hex(ST_GeomFromText('LINESTRING(1 1, 2 3)'));
+--------------------------------------------------------------------------------------------+
| hex(ST_GeomFromText('LINESTRING(1 1, 2 3)'))                                               |
+--------------------------------------------------------------------------------------------+
| 00000000010200000002000000000000000000F03F000000000000F03F00000000000000400000000000000840 |
+--------------------------------------------------------------------------------------------+

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 5.7.15    |
+-----------+

Indeed mysql 5.6 and mariadb don't care of badly formed wkt. They store them as they are. So have you an idea of a request to identify all badly formed geometries? — Daniel-KM, Feb 03 '19 at 07:22

Check well-formed and/or valid geometries in mysql

1 Answers1