MySQL's (crappy) Spatial Extensions

Introduction

In the world of mapping it is often required to determine whether a certain point on a map falls within pre-defined zones or areas, or the opposite, to determine the specific points that falls in a particular zone or area. These zones are defined by shapes such as polygons, rectangles and circles/ovals and are often referred to as geospatial data or objects. Luckily for database designers many of the popular database engines now supports the indexing and storage of these geospatial objects for quick indexing and searching.

Since geospatial technology in itself is a rather vast and relatively complex subject I'm not going to discuss it in great depth here. I need to mention though that most database engines support the OpenGeo specification for spatial data. You can read more about it here.

MySQL's Spatial Implemention and its Catches

Let me first say that although MySQL is an immensely popular database engine it does have its drawbacks in some areas. Its Geospatial implementation is one of those areas unfortunately. Lets have a quick overview of these issues:

Problem 1: Even in the latest and greatest MySQL v5.6 InnoDB has no support for spatial indexing, meaning that although it supports the storage and some functionality revolving around spatial data, it is all going to be terribly slow to try and lookup anything. This means that if you are an InnoDB user you'll have to revert to MyISAM for fast spatial index based lookups.

Problem 2: Geospatial data can not be dumped or exported. That's right, MySQL stores the geospatial data in a binary format that is completely ignored by mysqldump's --hex-blob and --compatible=target parameters. What you get instead is a whole lot of binary garbage in your dumped text file that you will be unable to use for imports in the feature. What this comes down to is that you will have to ignore the table when you do a mysqldump (e.g. --ignore-table=yourdb.yourgeotable) and write your own tool to parse and dump the geospatial data. This issue is absolutely terrible in my books.

Problem 3: Although fixed now, MySQL 5.0.16 had a bug where if you tried to use geospatial functions using InnoDB it would literally crash the server instead of just generating SQL errors. If you are still dealing with an older unpatched MySQL 5.0.16 be aware.

Problem 4: Since you have to use MyISAM for your geospatial indexes you have no support for transactions.

Problem 5: The limited implementation of OpenGeo (OGC) function support can make it really hard or impossible to use MySQL for complex geospatial functionality.

Conclusion

Using MySQL's Spacial implementation is Ok if you are willing to use MyISAM for your table structure and if you intend to use fairly basic indexing of geospatial features. It is not absolutely worthless but I really hope that Oracle will allow MySQL to catch up to other database engines in this regard in the near future.