MySQL Forums
Forum List  »  GIS

Re: Spatial Query doesn't use spatial index..
Posted by: Alexander Barkov
Date: December 01, 2006 07:18AM

I could not reproduce the problems you describe:


mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`mem_userid` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
`mem_gp` point NOT NULL,
`mem_country` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`mem_userid`),
SPATIAL KEY `sp_index` (`mem_gp`(32))
) ENGINE=MyISAM AUTO_INCREMENT=295582 DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


mysql> select AsText(mem_gp) from t1;
+----------------------+
| AsText(mem_gp) |
+----------------------+
| POINT(1 1) |
| POINT(2 1) |
| POINT(2 3) |
| POINT(2 4) |
| POINT(2 5) |
| POINT(2 6) |
| POINT(46.262 -1.177) |
+----------------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE Contains( GeomFromText( 'POLYGON( ( 46.262 -1.177,46.212 -1.09,46.112 -1.09,46.062 -1.177,46.112 -1.263,46.212 -1.263,46.262 -1.177 ) )' ) ,mem_gp ) LIMIT 30;
+------------+---------------------------+-------------+
| mem_userid | mem_gp | mem_country |
+------------+---------------------------+-------------+
| 295581 | | 0 |
+------------+---------------------------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM t1 WHERE Contains( GeomFromText( 'POLYGON( ( 46.262 -1.177,46.212 -1.09,46.112 -1.09,46.062 -1.177,46.112 -1.263,46.212 -1.263,46.262 -1.177 ) )' ) ,mem_gp ) LIMIT 30;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | range | sp_index | sp_index | 32 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> optimize table t1;
+---------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.t1 | optimize | status | OK |
+---------+----------+----------+----------+
1 row in set (0.00 sec)


It seems your table got corrupted for some reasons. Try to repair it.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Spatial Query doesn't use spatial index..
3909
December 01, 2006 07:18AM


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.