Hobu, Inc.

OGR had support for reading MySQL tables in the past, but it didn’t support native geometry types, and MySQL doesn’t really have a formal mechanism for supporting the OGC Simple Features for SQL specification. Under the direction of Frank Warmerdam, I have been bringing the OGR driver up to par with the rest of the vector data drivers in OGR. Here’s a list of the features that I added:

1. Read geometry data types. All of the OGC data types that MySQL supports are handled.
2. Read SRS information from the spatial_ref_sys table and layer information from the geometry_columns table. The OGC SF for SQL spec states that there should be a geometry_columns and spatial_ref_sys table that stores auxiliary information about the layers in the database. OGR now supports these tables.
3. Better reading of data types. The MySQL driver now handles field precisions and widths much better, and it can auto-detect a surrogate feature ID and the geometry column for layers that are created from a SQL statement.
4. Support for spatial filters. The driver will use the spatial index to query against geometries that are specified as filters.
5. Write support. The MySQL OGR driver now has the ability to create features in the database, much like the PostGIS and Oracle Spatial drivers. This support includes options for preserving precision information and laundering field names.

Impressions of MySQL Spatial

MySQL is known for being fast, widely deployed, and frequently used. Unlike PostGIS, however, it does not support spatial predicates and operators (for testing whether one geometry exists in another, for example), its spatial indexing is only supported in MyISAM database backend, which is not transactional, and it doesn’t provide anything for the OGC Simple Features for SQL specification other than to provide the geometry types themselves.

I’ve found it interesting that people clammor so much for MySQL support. If you already have the development momentum moving that way, I can understand why it is desired, but if you are starting from scratch, start with PostgreSQL/PostGIS. Here’s some things that you’d be missing if you stuck with MySQL:

• No ability to query for an extent of a selection set and have that result come from the spatial index, ie “SELECT EXTENT(SHAPE_COLUMN) FROM TABLE WHERE SOME FILTER”. This functionality is critically important in my mind to the operation and utility of a spatial database.
• No spatial predicates. All the database can really do for you is store the geometry and give you the ability to quickly select things based on an extent. It can’t help you answer questions like “does this feature intersect this other feature” or “is this point, when buffered by a given distance, within the distance of this other set of features.”

Another thing I found really frustrating is the MySQL C API. How anyone gets anything done with that is astonishing. I found myself wishing I had Oracle’s Pro*C a few times, which is saying a lot. It isn’t self describing, you need to know a lot about the life cycle of a query, and you end up inferring the results of you queries in a very backwards way. I’m so happy that database abstractions like the Python DB API exist to squash this stuff out.

Give it a try

The OGR MySQL support will be available in the upcoming GDAL 1.3.2 release, but you can try it out via CVS if you are daring. I’m still working on a few bugs and issues, but most of the basics are there. If you are a MySQL shop looking to use it with MapServer, this is what you’ll want to use, rather than the unsupported and unmaintained native MyGIS driver.

Overall it was a worthwhile experience for me to develop this. I learned quite a bit about the internals of OGR and MySQL, gained some C++ development experience, and got a wonderful opportunity to work under a great mentor.

Many are calling yesterday’s meeting (and subsequent decisions that were made) a watershed moment for the Open Source GIS software community. I for the most part agree with that sentiment. I am excited that the initial board members that were chosen represent a variety of software projects and a variety of personalities. I think we were able to hone down the issues that people had mostly discussed via email on the maillist to things we could all agree on.

I must say that Autodesk was a gracious and proactive host. Their involvement throughout the last few months (including the ill-fated MapServer foundation/naming announcement) has been admirable. They have listened to the community, acted appropriate in both their interest and the interest of the community, and shown that they are willing to work with what already exists in the Open Source GIS ecosystem. Missteps that the initial group made (mostly the fact that foundation stuff was discussed and proposed under an NDA that was only to cover the fact that Autodesk was releasing a bunch of software under an Open Source license) have not only been recoverable — they have been instructive. The formation of the Open Source Geospatial Foundation is a result of that learning process.

I think that most of the uproar proceeding the initial announcement was related to naming and the idea that the MapServer project was exclusively involved in the formation of a foundation. Now that we have gone beyond that, many opportunities for more structured cooperation and collaboration between various Open Source projects that elect to join the foundation now exist. The common ground we articulated and the organization we have proposed will allow the projects under the OSGeo umbrella to deal with issues that a project by itself could only do with much difficulty — outreach, common and low-overhead infrastructure, a potential entity to flow through aggregate funding to a project, etc.

Another thing that I find exciting is the possibility that by being in the foundation, software projects are structurally encouraged to collaborate. I’ve reinvented my fair share of wheels, and many of the projects that are in OSGeo have done so also. That the projects in the foundation will be overtly encouraged to collaborate is in my opinion a great thing. “Building the stack” as was said yesterday.

The board members have an unenviable amount of work to do to bootstrap OSGeo into existence, but we came away from the meeting with the principles that the board can implement. All of these developments will mean a brighter and more organized future for Open Source GIS.