CODEWITHSUNDEEP
mysqlindexinggeospatialmysql-5.7
albator
albator

Reputation: 859

Spatial index on geometry column and not NULL column impossible

From the mysql doc

A geometry column should be NOT NULL, but a geometry column cannot have DEFAULT values

So problem comes when I want to add a spatial index, ALL part of spatial index must be not null and if I tick NULL I have to put a DEFAULT VALUE that gave me the first error.

I'm using mysql 5.7, innodb column, here is what I've done to reproduce the problem:

my origin table geotest3:
id MEDIUMINT
lat DECIMAL
lon DECIMAL

I've added a geom column:

alter table geotest3 add geom geometry;

Next I fill the column with lat lon data:

UPDATE geotest3 
  SET geom = PointFromText(CONCAT('POINT(',geotest3.lon,' ',geotest3.lat,')'));

Now I cannot add a spatial index on geom column for problem explained above.

I don't know if its a bug or if I missed something.

Upvotes: 5

Views: 3586

Answers (1)

singe3
singe3

Reputation: 2105

You forgot one step (3.)
The whole procedure should be :

  1. Create you geometry column allowing null values (ok)
    alter table geotest3 add geom geometry;

  2. Fill you column (ok)
    UPDATE geotest3 SET geom = PointFromText(CONCAT('POINT(',geotest3.lon,' ',geotest3.lat,')'));

  3. Now the column doesn't contain null value anymore, so you can make it not null and add the spatial index (missing step)

    ALTER TABLE `geotest3` 
CHANGE COLUMN `geom` `geom` GEOMETRY NOT NULL,
ADD SPATIAL INDEX `geom_SPATIAL` (`geom` ASC);

Upvotes: 3

Related Questions