top of page
CerebroSQL

MySQL: 

ST_VALIDATE

ST_Validate(g)

Validates a geometry according to the OGC specification. A geometry can
be syntactically well-formed (WKB value plus SRID) but geometrically
invalid. For example, this polygon is geometrically invalid: POLYGON((0
0, 0 0, 0 0, 0 0, 0 0))

ST_Validate() returns the geometry if it is syntactically well-formed
and is geometrically valid, NULL if the argument is not syntactically
well-formed or is not geometrically valid or is NULL.

ST_Validate() can be used to filter out invalid geometry data, although
at a cost. For applications that require more precise results not
tainted by invalid data, this penalty may be worthwhile.

If the geometry argument is valid, it is returned as is, except that if
an input Polygon or MultiPolygon has clockwise rings, those rings are
reversed before checking for validity. If the geometry is valid, the
value with the reversed rings is returned.

The only valid empty geometry is represented in the form of an empty
geometry collection value. ST_Validate() returns it directly without
further checks in this case.

As of MySQL 8.0.13, ST_Validate() handles its arguments as described in
the introduction to this section, with these exceptions:

o If the geometry has a geographic SRS with a longitude or latitude
that is out of range, an error occurs:

o If a longitude argument is not in the range (?180, 180], an
ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
ce.html#error_er_geometry_param_longitude_out_of_range) error
occurs (ER_LONGITUDE_OUT_OF_RANGE
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
ce.html#error_er_longitude_out_of_range) prior to MySQL 8.0.12).

o If a latitude argument is not in the range [?90, 90], an
ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
ce.html#error_er_geometry_param_latitude_out_of_range) error occurs
(ER_LATITUDE_OUT_OF_RANGE
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
ce.html#error_er_latitude_out_of_range) prior to MySQL 8.0.12).

Ranges shown are in degrees. The exact range limits deviate slightly
due to floating-point arithmetic.

Prior to MySQL 8.0.13, ST_Validate() handles its arguments as described
in the introduction to this section, with these exceptions:

o If the geometry is not syntactically well-formed, the return value is
NULL. An ER_GIS_INVALID_DATA
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference
.html#error_er_gis_invalid_data) error does not occur.

o If the geometry has an SRID value for a geographic spatial reference
system (SRS), an ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference
.html#error_er_not_implemented_for_geographic_srs) error occurs.

URL: https://dev.mysql.com/doc/refman/8.0/en/spatial-convenience-functions.html

Example

mysql> SET @ls1 = ST_GeomFromText('LINESTRING(0 0)');
mysql> SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
mysql> SELECT ST_AsText(ST_Validate(@ls1));
+------------------------------+
| ST_AsText(ST_Validate(@ls1)) |
+------------------------------+
| NULL |
+------------------------------+
mysql> SELECT ST_AsText(ST_Validate(@ls2));
+------------------------------+
| ST_AsText(ST_Validate(@ls2)) |
+------------------------------+
| LINESTRING(0 0,1 1) |
+------------------------------+

bottom of page