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.
Hi!
great news, thanks!
Now if only someone would tackle R-Tree indexes in innodb.
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.