Spatial Index in MySQL - ERROR - Cannot get geometry object from data you send to the GEOMETRY field

mysql geometry geolocation spatial-index mysql-error-1416
jisaacstone picture jisaacstone · May 3, 2011 · Viewed 42.5k times · Source

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?

Answer

jisaacstone picture jisaacstone · May 4, 2011

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.

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More