Making the MySQL FDO provider ... geospatially useful

For the longest time, the MySQL FDO provider was of limited utility, though not to the fault of the provider itself. The last time this provider saw serious development, the latest version of MySQL was around 5.0/5.1 and the spatial capabilities of MySQL at that point in time left a lot to be desired.

While you could store spatial data, querying this data out spatially was another matter. MySQL at this point in time only offered bounding-box-based spatial predicates which manifests in user-facing viewer behavior like this:

Why would such a box selection query select that line? Because their minimal bounding boxes spatially intersect according to MySQL's limited spatial predicates. While MySQL 5.6 finally introduced proper spatial capabilities, the provider itself was still working against the pre-5.6 feature set until recently.

After receiving signs that people still use MySQL for spatial data, I've finally decided to tackle this long standing annoyance. If you have MapGuide Open Source 3.1.2 64-bit installed, you can download the patched MySQL Provider to unlock the full set of spatial capabilities if you are connected to MySQL 5.6 or higher, the end result is that map selections against MySQL data sources now actually make sense!

That line is no longer selected out of nowhere! You have to actually box select on the line, like an actual ST_Intersects spatial predicate should!

Since MySQL has long been forked into another popular and highly-compatible fork called MariaDB, I did some testing to make sure this provider works against MariaDB as well. It turns out that the [MySQL version is >= 5.6] check the provider does to determine whether to unlock the full spatial capabilities in the provider is not quite correct when working with MariaDB. The problem was that the version checking APIs provided by the MySQL/MariaDB client return "5.5.5" when connected to MariaDB, which breaks the version check as 5.5 < 5.6.

This left me scratching my head for a bit as why would MariaDB 10.4 (the version of MariaDB I was testing) return a version of "5.5.5"? It turns out this version number has special meaning as a versioning hack to support replication compatibility with MySQL. The real version can be obtained by getting the version string and if it contains "mariadb", check for the "5.5.5-" prefix and if it's present, parse the version number following that prefix for the real version number. Because there was never a release of MySQL 5.5.5, the presence of this "5.5.5-" prefix in the version string gives us 99.9% certainty that we're actually dealing with MariaDB. With this change, the provider will now have full spatial capabilities when connected to MariaDB as well.

These changes will be rolled into FDO trunk and will be part of the MySQL FDO Provider that ships with the next preview release of MapGuide Open Source 4.0