jess jess - 2 months ago 17
MySQL Question

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.

Answer

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);
    
Comments