How to Aggregate and dissolve MySQL geometry

Question

On CentOS, I have a MySQL 5 database with a MyISAM table that has a geometry column with a spatial index, containing US county boundaries.

Trying to combine/aggregate/dissolve some of these into as few polygons as possible, to create minimum KML/KMZ files for public use, using the ogr2ogr just built with the GDAL 1.10.1 sources.

INFO: Open of `MySQL:mydb,user=root,password=password'
      using driver `MySQL' successful.

Layer name: counties
Geometry: Unknown (any)
Feature Count: 3234
Extent: (-178.309480, -14.601813) - (145.909365, 71.441059)
Layer SRS WKT:
(unknown)
Geometry Column = geo
county_fips: Integer (0.0)
state_fips: Integer (0.0)
state_abrv: String (100.0)
county_name: String (255.0)

@afalciano provided the syntax for combining the data for ESRI Shapefiles, but I have virtually no GDAL/ogr experience, and am having problems translating this to work with MySQL.

Ideally, I'd output as KML, but have had issues with that, so would be happy to get the output in WKT or anything Perl could be made to parse, or into another MySQL table with geometry column.

Can you provide any tips on ogr2ogr tutorials (other than the online driver doc pages)?

I need to do this programmatically, on demand, on a headless CentOS web server.

Answer 1

If you have MySQL 5.7.5 or later, you can use MySQL's ST_Union function from ogr2ogr:

ogr2ogr -sql "SELECT ST_Union(geo) FROM counties" -f KML counties.kml MySQL:"..."

If you have GDAL/OGR built with SpatiaLite support, then you can let GDAL/OGR do the unions with the SQLite dialect. As a special case, "Union" is called GUnion:

ogr2ogr -dialect SQLITE -sql "SELECT GUnion(geo) FROM counties" -f KML counties.kml MySQL:"..."

There are a few options for the KML driver to check out. Also, to make a KMZ file (e.g. countries.kmz) it needs a small trick to the datasource name:

ogr2ogr ... -f KML /vsizip/counties.kmz/counties.kml ...

Answer 2

Since MySQL ST_Union() only accepts two geometries, the workaround is to construct two geometry collections. One collection contains all features to be aggregated together by using group_concat() on the feature WKT. The second geometry collection can be empty.

You will likely need to substantially increase the @@sort_buffer_size and @@group_concat_max_len variables for this to work.

SELECT ST_Union(
    ST_GeomFromText(
      concat("GEOMETRYCOLLECTION (",
        group_concat( ST_AsText(shape) ),
        ")" 
      )
    ), 
    ST_GeomFromText("GEOMETRYCOLLECTION EMPTY") 
  ) as geom
FROM ...
WHERE ...
GROUP BY ...;

Answer 3

Is there a problem if you'll easily do these tasks using QGIS?

You only need to connect QGIS to your MySQL as bellow:

Then, add your tables as vector layers using the desired CRS and do your geoprocessing tasks:

And, eventually, save your layer in the KML format: