18

I am new to the whole 'spatial index' thing, but it seems to be the best solution for filtering based on latitude/longitude. So I added a column to my 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:

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

What am I doing wrong here?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
jisaacstone
  • 4,234
  • 2
  • 25
  • 39

2 Answers2

30

OK I found the solution: Can't create a spatial index if some of the column fields contain no data. After running 

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

Everything worked fine.

JabbaHotep
  • 31
  • 7
jisaacstone
  • 4,234
  • 2
  • 25
  • 39
  • I now see that your answer is the same as mine, I tried to copy verbatim and it complained about lat and long being undefined :P ... – Mike Graf Jun 09 '12 at 20:57
  • 3
    The order of POINT is lng, lat as POINT( lng, lat ), Please change your answer to avoid future mistakes. – Ramsés Fernández Feb 12 '18 at 08:29
  • @RamsésFernández **That is not true** according to [this answer](https://dba.stackexchange.com/a/182626/140128) . MySQL handles it differently than PostGIS – Milan Velebit Jun 03 '20 at 23:40
5

I had this same error (Cannot get geometry object from data you send to the GEOMETRY field) but when trying to import spatial data from a mysql dump. I found that some rows had "null" (X is null or Y is null) spatial data even though the column was "NOT NULL"..

Check if you have the same problem as I'm describing with this SQL:

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

If you have some rows then this is what worked for me:

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

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

Mike Graf
  • 5,077
  • 4
  • 45
  • 58