[GIS] How to Aggregate and dissolve MySQL geometry

dissolveMySQLogr2ogr

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.

Best Answer

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 ...
Related Question