CODEWITHSUNDEEP
mysqlindexinggeospatial
Uday Sawant
Uday Sawant

Reputation: 5798

Error while creating index on geometry column

I have a table with spatial column(data type geometry) and with around 450k rows. When i tried to add a spatial index on this column, it returns an error as "All parts of a SPATIAL index must be NOT NULL".

The query to create index is 

create spatial index spatIdx on table_name(ogc_geom)

1. Am I doing something wrong?
2. Where these NULL parts came from?
3. If its in my spat data how can i remove it (i tried with is null).

Upvotes: 5

Views: 4703

Answers (2)

cegfault
cegfault

Reputation: 6632

In the MySQL documentation, it states, "Currently, columns in spatial indexes must be declared NOT NULL". My guess is the column ogc_geom is allowed to have NULL. Try:

ALTER TABLE table_name MODIFY COLUMN ogc_geom .... NOT NULL

Any column you create a spatial index on must be defined with "NOT NULL", or else you will get an error.

Upvotes: 8

Christopher Pelayo
Christopher Pelayo

Reputation: 802

how about if you use the "ALTER TABLE" statement instead to update your table structure and add the index into it.

try to check the sysntax from this link: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

Upvotes: -2

Related Questions