MySQL

MySQL and Geospatial Data

MySQL has had basic support for Geospatial Data since 4.1, but has lacked some of the features of the OpenGIS specifications since then. The good news is, this is rapidly changing. Our own Holyfoot has been hammering away at WorkLog #1327, to provide precise functions for our GIS support.

Even better, it’s fast. How fast? Well, the good people at Oki Labs, apart from having implemented several new GIS functions for MySQL, have done some benchmarking, and it’s looking good. If you’ll excuse the cliched comparison to Postgres, here are the response times (seconds) of MySQL GIS vs. PostGIS in Oki’s test:

Connections PostGIS MySQL
1 1.817 0.220
100 10.517 0.557

Source: http://www.osgeo.jp/wordpress/wp-content/uploads/2008/11/foss4g2008_okumura.pdf

If you’re interested in checking it out, the source tree (regularly merged with MySQL 5.1) is available here. Have a look at Giuseppe’s guide to running a Bazaar export in MySQL Sandbox.

Standard

3 thoughts on “MySQL and Geospatial Data

  1. I actually would love to have generalized R-Tree indices – they are right now limited to 2-dimensional Point-Type data.

    But in many cases there are queries that are conjunctions of range queries on different columns – stuff such as

    SELECT … FROM t
    WHERE a BETWEEN 1 AND 10
    AND b BETWEEN 1 AND 10
    AND c BETWEEN 1 AND 10

    Queries of this type are very common – they exist in geocoding and place finders with 2 dimensions, but they also exist in social networking where you want to find somebody who is compatible with you

    SELECT name FROM persons
    WHERE xpos BETWEEN @xlow AND @xhigh
    AND ypos BETWEEN @ylow AD @yhigh
    AND age BETWEEN @agelow AND @agehigh
    AND interests IN ( … )
    AND sex IS @wantedsex

    and in many other areas. Queries like this are hard to speed up with BTREE (an intersection is needed), but an RTREE(xpos, ypos, age, interests) can be even more useful.

Leave a Reply