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?

About these ads

9 comments so far

  1. Ron Bruder on

    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

  2. Kirk Kuykendall on

    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

  3. Justin Bronn on

    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.

  4. Mapperz on

    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/

  5. Kirk Kuykendall on

    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

  6. Dave Smith on

    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?

  7. Kirk Kuykendall 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.

  8. Stephan on

    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.

  9. Kirk Kuykendall on

    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.
    http://www.publish.csiro.au/?act=view_file&file_id=AS06026.pdf

    Regards, Kirk


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: