MySQL Forums
Forum List  »  GIS

MBRContains and spatial index use
Posted by: Clive Cox
Date: September 07, 2005 08:17AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
MBRContains and spatial index use
10195
September 07, 2005 08:17AM
3984
September 07, 2005 08:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.