Hi,
I had been expereincing problems with getting spatial indexes used with Contains clauses.
From examples taken from
http://jeremy.zawodny.com/blog/archives/000418.html
I was using SQL like:
select cityidx,GLength(LineStringFromWKB(LineString(AsBinary(spatialcity.location),asBinary(GeomFromText('Point(52.176 0.19'))))) as distance from spatialcity where Contains(GeomFromText('Polygon((52.07 0.09,52.07 0.29,52.27 0.29,52.27 0.09,52.07 0.09))'),spatialcity.location) = 1 order by distance asc limit 1;
Notes the Contains(...) = 1
This does not use my spatial index on spatialcity.location column. Explain shows
+----+-------------+-------------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | spatialcity | ALL | NULL | NULL | NULL | NULL | 92872 | Using where; Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-----------------------------+
However, I spotted in the manual (http://dev.mysql.com/doc/mysql/en/using-a-spatial-index.html)
that they just have contains(...)
The follwoing SQL correctly uses the index.
select cityidx,GLength(LineStringFromWKB(LineString(AsBinary(spatialcity.location),asBinary(GeomFromText('Point(52.176 0.19'))))) as distance from spatialcity where Contains(GeomFromText('Polygon((52.07 0.09,52.07 0.29,52.27 0.29,52.27 0.09,52.07 0.09))'),spatialcity.location) order by distance asc limit 1;
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-----------------------------+
| 1 | SIMPLE | spatialcity | range | location | location | 32 | NULL | 1 | Using where; Using filesort |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-----------------------------+
Is the original SQL incorrect or is this a bug in MySQL? (I use MySQL 4.1.12)
Also, does anyone have any suggestions on better ways to get the closest point to a given point (the above requires a filesort) that doesn't require the GLength jiggery-pokery above, or is this just the state of limitations in the current GIS implementation in the current MySQL?
Clive
Edited 1 time(s). Last edit at 09/07/2005 08:19AM by Clive Cox.