mysql

Interesting times ahead. The upcoming MySQL 5.7 promises to provide great enhancements in the spatial space. They've scrapped much of the existing GIS plumbing they have in place seen in prior 5.6 and are replacing muc of it with Boost Geometry. In addition R-Tree indexes will be available for InnoDb. Details: Why Boost.Geometry in MySQL? and Making use of Boost Geometry in MySQL. Along the roadmap (not for 5.7) they plan to have geography support as well and projections, 3D and the various output formats e.g GeoJSON currently available in PostGIS.

Just when I thought database spatial domain was getting boring. It will be interesting to see how the new MySQL Boost Geometry stacks against PostGIS GEOS /3D CGAL support. Might be time for a PostGIS/MySQL shoot-out soon.

If anyone has ever seen any of Paul's presentations or slides, you'll remember his famous Intro To PostGIS and slide Is MySQL a spatial database?, answer: Not Really. The functions only work against bounding boxes.. (page 5). And Paul's They keep on threatening us. Well Paul, I'm afraid you'll have to change that slide. They have finally made good on their threats and we PostGIS folks need to now keep a watchful eye.

We've lost interest for the most part in what MySQL does these days, except for the few customers we have that are still running MySQL that the migration to something else isn't worth the bother. We've lost interest mostly because MySQL movement in areas we care about have been so slow and PostgreSQL movement has been so fast. I did come across this tidbit reading Comparing Open Source GIS Implementations and to my shock Oh my god! Stop the presses, MySQL 5.6 has true Spatial relationship support.

The other thought I had is thank goodness PostGIS legacied all those non-ST functions like Contains(), Intersects etc, as that would cause people who use both databases even more confusion. Here are some important take-away points from MySQL 5.6 Spatial support:

• The old MBR alias functions Contains, Overlaps, etc, still only work against the bounding box. To get true checks, you need to use the ST_ variants
• While you can create geometries on InnoDb tables now, you can only create R-tree spatial indexes on MyISAM tables.
• I could be missing something, but don't see an ST_Distance function or anything like it. I don't have 5.6 handy to test, so if anyone knows, please correct me if I am wrong.
• General footnote, MariaDb also supports this new functionality in its 5.3+ versions

A few people have been asking us what are the pros and cons of using SQL Server 2008 Spatial and PostGIS and as a Windows user, why would you still consider using PostGIS. Rather than simply providing some hand-waving saying "well if you just care about displaying data, then use whatever you feel comfortable with, but if you want to do real intensive sophisticated spatial analysis and geometric processing without having to purchase a bunch of expensive software, then PostGIS is probably better for you. Hell why must you think in either or propositions - just use both using the strengths of each.", we have tried really hard to quantify the similarities and differences between the 2 and to boot - we have also added in MySQL.

Our analysis can be found at Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6

If you have any comments, suggestions of additions, things you felt we got wrong, then please don't hesitate to comment and we'll try to update our survey.