Jeremy Zawodny's blog

Spatial (R-Tree) Indexes in MySQL 4.1

I've been playing around a bit with MySQL 4.1's spatial indexes as I write chapter three (Indexes) for the book. Here's a quick demonstration in which I add three points to a table and then ask MySQL which points fall within the bounds of a couple polygons.

Before you play with this, you might want to read the OpenGIS spec. Since the MySQL docs don't yet cover this stuff, that's one of the better references.

First, create the table with a small blob field to contain the spatial data:

mysql> create table map_test
    -> (
    ->   name varchar(100) not null primary key,
    ->   loc  tinyblob,
    ->   spatial index(loc)
    -> );
Query OK, 0 rows affected (0.00 sec)

And then add some points:

mysql> insert into map_test values ('One Two', point(1,2));
Query OK, 1 row affected (0.00 sec)

mysql> insert into map_test values ('Two Two', point(2,2));
Query OK, 1 row affected (0.00 sec)

mysql> insert into map_test values ('Two One', point(2,1));
Query OK, 1 row affected (0.00 sec)

Then ensure that it looks right in the table:

mysql> select name, AsText(loc) from map_test;
+---------+-------------+
| name    | AsText(loc) |
+---------+-------------+
| One Two | POINT(1 2)  |
| Two Two | POINT(2 2)  |
| Two One | POINT(2 1)  |
+---------+-------------+
3 rows in set (0.00 sec)

Finally, ask MySQL which points fall within a couple of polygons. (I'll omit the "mysql>" prompt to make sure things aren't too wide.)

SELECT name FROM map_test WHERE
Contains(GeomFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0))'), loc) = 1;

+---------+
| name    |
+---------+
| One Two |
| Two Two |
| Two One |
+---------+
3 rows in set (0.00 sec)

SELECT name, AsText(loc) FROM map_test WHERE
Contains(GeomFromText('POLYGON((0 0, 0 1, 1 1, 2 0, 0 0))'), loc) = 1;

+---------+-------------+
| name    | AsText(loc) |
+---------+-------------+
| Two One | POINT(2 1)  |
+---------+-------------+
1 row in set (0.00 sec)

Pretty cool, huh? That's really just the beginning. You can do much more impressive stuff with spatial indexes.

Open Source Blog

Wow. A Google search for "open source blog" ranks me pretty high. I'm not sure what to think of that. Makes you wonder how much they rank the incoming links compared to the page titles and h1/h2/h3 tags.

If nothing else, yeay for referer logs! :-)

In unrelated news, Sprnt's ever-flakey network is making it difficult to check my mail this morning. Grr.