Spatial Index in MySQL - ERROR - Unable to get geometry object from data sent to GEOMETRY field

Question

I am new to the whole “spatial index”, but it seems to be the best latitude / longitude filtering solution. So I added a column to the table:

So, I created a geometry field:

  ALTER TABLE `addresses` ADD `point` POINT NOT NULL 

And then I tried to add an index:

  ALTER TABLE `addresses` ADD SPATIAL INDEX ( `point` ) 

But I get an error message:

  #1416 - Cannot get geometry object from data you send to the GEOMETRY field 

What am I doing wrong here?

Answer 1

OK I found a solution: I can’t create a spatial index if there is no data in some fields of the column. After launch

  UPDATE `addresses` SET `point` = POINT( lat, lng ) 

Everything worked fine.

Answer 2

I had the same error (cannot get the geometry object from the data sent to the GEOMETRY field), but when trying to import spatial data from a mysql dump. I found that in some rows "null" (X is null or Y is null) is spatial data, although the column is "NOT NULL" ..

Check if you have the same problem as I describe using this SQL:

SELECT id FROM location WHERE X (coordinates) IS NULL OR Y (coordinates) NULL;

If you have multiple lines, this is what worked for me:

locations UPDATE SET coordinates = POINT (0,0) WHERE X (coordinates) IS NULL OR Y (coordinates) NULL;

Then try your mysqldump (or from phpmyadmin) and import again.