MySQL Forums
Forum List  »  GIS

Re: Create spatial index on BLOB column?
Posted by: Lenz Grimmer
Date: August 19, 2009 05:37AM

Hi,

James Allen Wrote:
-------------------------------------------------------

> I am trying to add a spatial index to a table
> column named Location of type BLOB. If I try
> this:
>
> ALTER TABLE route ADD SPATIAL
> INDEX(Location);
>
> I get:
>
> #Error: BLOB/TEXT column 'Location' used in key
> specification without a key length
>
> But in the official docs for MySql 5.1 (the
> version I am using), it clearly says:
>
> "In MySQL 5.1, column prefix lengths are
> prohibited. The full width of each column is
> indexed."
>
> This surely says that I don't need to provide a
> prefix. I tried adding a prefix anyway like this:
>
> ALTER TABLE route ADD SPATIAL
> INDEX(Location(256));
>
> And I get:
>
> #Error: Incorrect prefix key; the used key part
> isn't a string, the used length is longer than the
> key part, or the storage engine doesn't support
> unique prefix keys
>
> So what the heck is going on?? For info, I am
> using MySQL 5.1.37 community, and my table is
> MyISAM, this is the create statement:
>
> CREATE TABLE `climb`.`route` (
> `Id` int(11) NOT NULL,
> `Name` varchar(255) NOT NULL,
> `Location` blob,
> PRIMARY KEY (`Id`),
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The error message is a bit misleading. What it is trying to tell you is that it's not possible to add a SPATIAL index to a BLOB column - it needs to be of the type GEOMETRY (as documented in the manual here: http://dev.mysql.com/doc/refman/5.1/en/optimizing-spatial-analysis.html):

lenz@localhost:test > create table geom (g blob);
Query OK, 0 rows affected (0.16 sec)

lenz@localhost:test > describe geom;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| g     | blob | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.12 sec)

lenz@localhost:test > alter table geom add spatial index(g);
ERROR 1170 (42000): BLOB/TEXT column 'g' used in key specification without a key length
lenz@localhost:test > drop table geom;
Query OK, 0 rows affected (0.00 sec)

lenz@localhost:test > create table geom (g geometry not null);
Query OK, 0 rows affected (0.01 sec)

lenz@localhost:test > describe geom;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| g     | geometry | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

lenz@localhost:test > alter table geom add spatial index(g);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

lenz@localhost:test > describe geom;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| g     | geometry | NO   | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

So it works, if you use the correct column type "geometry".

I've submitted a bug report about this - the error message should be less confusing: http://bugs.mysql.com/46808

Bye,
LenZ

Lenz Grimmer - MySQL Community Relations Manager - http://de.sun.com/
Sun Microsystems GmbH, Sonnenallee 1, 85551 Kirchheim-Heimstetten, DE
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels
Vorsitz d. Aufsichtsrates: Martin Haering AG Muenchen: HRB161028

Options: ReplyQuote


Subject
Views
Written By
Posted
6223
August 15, 2009 03:50PM
Re: Create spatial index on BLOB column?
4532
August 19, 2009 05:37AM


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.