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.

Posted by jzawodn at January 06, 2003 11:46 PM

Reader Comments
# Marco said:

Now, if only we could have subselects...

(sorry, I know they're scheduled for development, but I couldn't resist... )

on January 7, 2003 05:23 AM
# Jeremy Zawodny said:

Subselects are there too. I'll deomonstrate them next. :-)

on January 7, 2003 07:04 AM
# Kumar Mettu said:

I couldn't find MySQL 4.1 installation (MySQL MAx for 4.1?). Where can I download it from for R-Tree support?

Thanks In Advance.

on January 15, 2003 01:03 AM
# Clive Page said:

I don't quite follow the last example, which was:

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)

Let me try to draw it (only works if you view this in a fixed-size font):

(0,1) +-----+
| \
| \
| \
| \
(0,0) (2,0)

As far as I can see, none of the three points in the table, and certainly (2,1), are within this polygon. So what's going on?

on March 25, 2003 01:41 AM
# said:

I get an error on the sql that cretes the table
create table map_test (name varchar(100) not null , loc tinyblob not null, spatial index(loc));

[dspersvr03] ERROR 1170: BLOB column 'loc' used in key specification without a key length

any ideas. when i remove the spatial index and teh inserts work, the selects return null in the loc column

on June 16, 2003 10:36 PM
# Matt said:

It seems that this implementation of spatial indices is only going to support 2-dimensional spatial data -- since all the Geometry classes are limited to (x,y) values. (Maybe 2^n dimensional at best?) Maybe I've misread the docs, but is there any hope for a generalized n-dimensional implementation of indices -- if it's not already here?

It seems rather odd that they wouldn't allow this generalization...

on August 1, 2003 10:47 AM
# MurthyCVRS said:

This worked for my insert statement

INSERT into map_test values ('One Two', GeomFromText('POINT(1 1)'));

similarly the other 2 INSERTS.

on August 6, 2003 03:23 AM
# David Robertus said:

Actually the demonstration you have doesn't reflect the use (necessarily) of the r-tree indexes. Without the indexes same results would have been returned but the return time may have been higher. This is particularly noticable when doing locational analysis against large numbers of complex polygons, which may or may not be overlapping. The demonstration presented here show good geometric analysis, but not so much shows of the capabilities of r-tree indexing.

on July 16, 2004 11:10 AM
# bfcl said:

Some information for mysql5.0 using Spatial Extensions :

on November 5, 2005 05:28 PM
# Imi said:

Clive Page: The algorihm Contains does not exactly clip on the polygon provided.

The algorithm is guaranteed to return *all* objects within the polygon, but may return more than that. In this case, it return all points within the rectangular bounding box of the polygon.

on October 19, 2006 06:18 AM
# Chandler said:

Is the same possible with WKB columns? I am not a SQL or math whiz.

on February 7, 2007 09:23 AM
# Roberto Jimenez said:

hi, i am working on a Google Earth + Google Maps + Mysql GIS proyect. Spatial functions are simple but i have a little problem when i try to query my mysql 5.1 database.

I have a GEOMETRY field called GEOM and i do insert POINT data then i use X() and Y() functions to get x, y coordinates and then i do generate a KML file to show placemarks on google maps views and google earth view and everything is ok. But when i try to show POLYGON data, there is no X() Y() functions for POLYGON object and i do not know how to query all my POLYGONS coordinates to show as KML or query POLYGON coordinates to get x,y to edit and update and area.

Thanks for your atention.

Kind regards,

Att. Roberto Jimenez

on February 9, 2009 09:29 AM
Disclaimer: The opinions expressed here are mine and mine alone. My current, past, or previous employers are not responsible for what I write here, the comments left by others, or the photos I may share. If you have questions, please contact me. Also, I am not a journalist or reporter. Don't "pitch" me.


Privacy: I do not share or publish the email addresses or IP addresses of anyone posting a comment here without consent. However, I do reserve the right to remove comments that are spammy, off-topic, or otherwise unsuitable based on my comment policy. In a few cases, I may leave spammy comments but remove any URLs they contain.