CODEWITHSUNDEEP
mysqlblobmyisammysql-error-1170
James Allen
James Allen

Reputation: 7159

Error creating spatial index on MySql BLOB column

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 when referring to spatial indexes:

"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;

PS I have also tried making Location NOT NULL, this made no difference.

Upvotes: 2

Views: 3572

Answers (2)

Quassnoi
Quassnoi

Reputation: 425391

Spatial indexes should be created on GEOMETRY types.

CREATE TABLE `route` (
        `Id` int(11) NOT NULL, 
        `Name` varchar(255) NOT NULL, 
        `Location` GEOMETRY NOT NULL,
        PRIMARY KEY (`Id`),
        SPATIAL KEY (`Location`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Upvotes: 1

Zed
Zed

Reputation: 57658

I believe the column you try to add spatial index to should be declared not null.

Upvotes: 0

Related Questions