MySQL vs. PostGIS
I’ve read some of Paul Ramsey’s response to the Timmy’s Telethon discussion. While this is very helpful, I would like to see discussion of MySQL vs. PostGIS.
As someone who’s just comparing these two toolsets, I feel like a shopper trying to choose between HD-DVD / Blu Ray (in Dec 2007).
Is there a study somewhere that compares these?
The Memory Leak 
Kirk,
I’m still a newbie with regard to spatially enabled RDBMSs and thus also recently tried to understand the strengths and weaknesses of the MySQL spatial extensions vs PostGIS. One important distinctions I did discover was that MySQL still largely only executes its overlay and analysis functionality using minimum bounding rectangles, or MBRs.
A description can be found here:
http://dev.mysql.com/doc/refman/6.0/en/relations-on-geometry-mbr.html
and:
http://forge.mysql.com/wiki/GIS_Functions
From this latter page:
“While MySQL already provides some functionality to store and operate on geospatial data, the functionality leaves quite a lot to be desired and is far from providing full OpenGIS compatibility.
Most notably is that all functions that query spatial data only operate on MBRs (minimum bounding rectangles), to simplify the operations.”
While there has been some recent (10/07) enhancements to make things more precise, it just seemed too fresh to me to stake a project on it.
Anyone who is more familiar with the MySQL extensions, please correct me if I am off track on this.
Ron
Ron
Hi Ron –
Yeah it certainly looks like MySQL is behind right now, but I wonder if Sun’s aquisition will change things any.
http://www.mysql.com/news-and-events/sun-to-acquire-mysql.html
Kirk
There is no study that I’m aware of; however, I’ll give my impression based on my experiences with both.
The MySQL implementation is not OGC-compliant. Besides only operating on the MBR’s (as Ron noted), it lacks knowledge of spatial reference systems (e.g., it doesn’t have the `spatial_ref_sys` and `geometry_columns` tables). In other words, MySQL is completely impotent when trying to convert from one spatial reference system to another — there is no `transform()` stored procedure.
As Ron also noted, there have been recent enhancements (provided in a ‘beta’ release) to make the spatial operations not limited to MBRs. However, I found the implementation to be very immature as my unit tests for GeoDjango caused segmentation faults — I reported the bug (which is reportedly fixed):
http://bugs.mysql.com/?id=32032
Finally, MySQL’s spatial indexes are limited to MyISAM tables only. MyISAM tables means that transactions are _not_ supported — another big drawback (especially for those concerned w/data integrity).
IMHO, MySQL’s spatial operations are not ready for production GIS use. For those seeking simple MBR geometry operations, it may be OK — but you’ll quickly hit a wall when attempting more complex tasks. I don’t see this changing as anytime soon, MySQL (Sun acquisition or not) has a long way to catch up in the spatial arena.
MySQL is great for simple retrieval like locations from a bounding box or do a radius search on flat non-spatial files
But when it comes using postgis there is far more you can do with it.
‘Find me the locations inside a multi-part polygon (with a hole in it) then compare it to an overlapping polygon that touches a boundary of (something) that is close to a polyline (route) I want to take’
The list goes on… postgres postgis for GIS complex spatial functions
http://postgis.refractions.net/docs/ch06.html#id3011852
HD DVD or Blu>r ray?
Mapperz
http://mapperz.blogspot.com/
Looks like HD-DVD has thrown in the towel. Good thing I waited it out. Maybe the HD format war analogy is not accurate, but I still wonder that given sheer numbers of MySQL deployments, that it represents a “disruptive technology” vis-a-vis PostGreSql.
http://en.wikipedia.org/wiki/Disruptive_technology
I wonder how many (neogeographers?) choose MySQL citing an 80-20 rule. http://en.wikipedia.org/wiki/Pareto_principle
That’s a good point, Kirk… I wonder if pressure from the geo community on Sun might yield some interesting results…
I’m curious to see what Sun has in mind for this – BI tools and integration, Data Mart / Data Warehouse, and so on?
Hey Dave –
Here’s an interesting conspiracy theory: Sun bought MySql at the behest of Oracle, with the intention to eliminate it as a viable competitor to Oracle, with whom Sun is a tight business partner.
http://www.marketwatch.com/news/story/john-dvoraks-second-opinion-sun-mysql/story.aspx?guid=%7B88606B4A-A4AF-46FC-9C80-6B186A622456%7D&dist=hplatest
I hope that is not the case.
Hey
Kirk, I agree with other commenters, MySQL is okay for simple things, you need PostGIS if it gets more complicated. But MySQL seems to handle large datasets nicely: I did a test with a point dataset of 67 million points. It took a while to compute the spatial index (6-8 hrs if I remember correctly on a consumer PC) and the DB amounts to a total of 11GB of diskspace, but the bbox query performs instantly.
One advantage of MySQL: you have a variety of choices for (cheap and managed) hosting.
Hi Stephan –
Thanks for the input, that got me interested … I tracked down this paper with some good comparisons between Oracle and MySQL for nearest neighbor astronomy queries.
Click to access
Regards, Kirk