2

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 kindly 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.

Any tips on ogr2ogr tutorials (other than the online driver doc pages) would be much appreciated, too.

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

2

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 ...
0

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

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

enter image description here

enter image description here

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

enter image description here

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

enter image description here

  • I need to be able to do this pro programmatically, on demand, on a headless web server. – LeeGee Feb 3 '14 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.