4

The following query seems to be working just fine under MySQL 5.6 (returns 0 for the Area), but on MySQL 5.7 it returns an error saying ERROR 3037 (22023): Invalid GIS data provided to function st_area..

SELECT ST_Area(GeomFromText('POLYGON((0 0, 0 0, 0 0, 0 0, 0 0))'))

Is it possible to either ignore the error and return zero, or create a query to filter any such geometries from the query before running it?

A very simple schema and query example would be:

CREATE TABLE `polygons` (`id` int, `geom` GEOMETRY);

INSERT INTO `polygons` VALUES (1, GeomFromText('POLYGON((0 0, 0 0, 0 0, 0 0, 0 0))'));

SELECT `id`, ST_Area(`geom`) FROM `polygons`;
eggyal
  • 113,121
  • 18
  • 188
  • 221
uri2x
  • 3,012
  • 1
  • 9
  • 22
  • 1
    Likely explanation (though not a solution): 5.7 uses [Boost.Geometry](http://www.boost.org/doc/libs/1_60_0/libs/geometry/) for its spatial functions, whereas 5.6 did not. – eggyal Dec 29 '15 at 13:24
  • Is a zero-area polygon actually a thing? Is 5.6 wrong to allow it or is 5.7 wrong to reject it? If the former, what should the representation be? A point? I can think of a somewhat shady (yet clean-looking) way to "eval" a WKT string for validity using a stored function and an exception handler, but what spatial object/value would be the correct replacement? – Michael - sqlbot Dec 29 '15 at 23:09
  • IMO, a POINT (or a LINESTRING if the Polygon has several points along the same 1 dimension line - which causes the same issue) would be a valid replacement. The thing is that if the GIS data is invalid, it shouldn't be allowed to be inserted into the database to begin with. For my own test case - I've now added validation in the code before inserting the data to the DB, but this is, indeed, shady. – uri2x Dec 30 '15 at 06:30
  • You should be able to do the `INSERT` query in a stored procedure, and declare a `HANDLER` for SQL Error 3037 (Invalid GIS data). In this handler, do your treatment, like inserting NULL or an empty geometry in the column. – Xenos Mar 06 '19 at 08:44

0 Answers0