MySQL Spatial Simplify Geometries

Question

I'm trying to create a web map which contains large polygon data-sets. In order to increase performance, I'm hoping to decrease polygon detail when zooming out.

Is MySQL able to simplify polygons and other geometries as part of a query?

Answer 1

EDIT: As James has pointed out, since 5.7, MySQL does support ST_Simplify.

I am afraid there is no simplify function in MySQL spatial. I filed that as a feature request five years ago, and you can see it has received no attention since then.

You have a number of options depending on whether you want to do this as a one off, or if you have dynamic data.

1). Write a quick and dirty function using the PointN function to access your points, only taking every 5th point say, creating a WKT string representing a simplified geometry, and then recreating a geometry using the GeomFromText function.

2). Dump your polygon as WKT, using AsText(geom) out to csv. Import into Postgres/Postgis using COPY command (equivalent of LOAD DATA INFILE), and use the ST_Simplify function there, then reverse the process to bring in back into MySQL.

3). Use ogr2ogr to dump to shp format, and then a tool like mapshaper to simplify it, output to shp and then import again using ogr2ogr. Mapshaper is nice, because you can get a feel for how the algorithm works, and could potentially use it to implement your own, instead of option 1.

There are other options, such as using Java Topology Suite if you are using Java server side, but I hope this gives you some idea of how to proceed.

I am sorry that the initial answer is, No. I went through this a number of years ago and eventually made a permanent switch to Postgres/Postgis, as it is much more fully featured for spatial work.

Answer 2

MySQL 5.7 contains ST_Simplify function for simplifying geometries.

From https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html:

ST_Simplify(g, max_distance)

Simplifies a geometry using the Douglas-Peucker algorithm and returns a simplified value of the same type, or NULL if any argument is NULL.

MySQL 5.7 includes a huge overhaul of spatial functionality compared to 5.6 and is now in General Availability status as of 5.7.9.