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
Now, if only we could have subselects...
(sorry, I know they're scheduled for development, but I couldn't resist... )
Subselects are there too. I'll deomonstrate them next. :-)
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.
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):
(1,1)
(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?
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
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...
This worked for my insert statement
INSERT into map_test values ('One Two', GeomFromText('POINT(1 1)'));
similarly the other 2 INSERTS.
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.
Some information for mysql5.0 using Spatial Extensions :
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions-in-mysql.html
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.
Is the same possible with WKB columns? I am not a SQL or math whiz.
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
desarrollo.web.suscripciones@live.com.mx
