MySQL INSERT/UPDATE on POINT column

mysql sql geospatial spatial mysql-error-1416
Luis D Urraca picture Luis D Urraca · Mar 13, 2011 · Viewed 32.2k times · Source

I'm trying to populate my DB with geographical places of my country. One of my tables have 4 fields: ID[PK], latitude. longitude ande geoPoint 

EDIT `SCDBs`.`Punto_Geografico`;

SET @lat = 18.469692;

SET @lon = -63.93212;

SET @g = 'POINT(@lat @lon)';

UPDATE Punto_Geografico SET latitude = @lat, longitude =@lon, geoPoint =@g WHERE idpunto_geografico = 0;

im getting the following error: Error Code: 1416 Cannot get geometry object from data you send to the GEOMETRY field

I'm pretty sure that 'geoPoint' field is a POINT field with a spatial index. Am i missing anything.14

Answer

FattyPotatoes picture FattyPotatoes · Apr 11, 2012

You need to use this syntax:

UPDATE ... SET latitude=18, longitute=-63, geoPoint=GeomFromText('POINT(18 -63)') WHERE ...

Related questions

Find nearest latitude/longitude with an SQL query

I have latitude and longitude and I want to pull the record from the database, which has nearest latitude and longitude by the distance, if that distance gets longer than specified one, then don't retrieve it. Table structure: id latitude …

Read More
Use MySQL spatial extensions to select points inside circle

I have a table called flags that contains a column called coordinates that is full of MySQL 'points'. I need to perform a query where I get all the flags within a circle based on a latitude and longitude position …

Read More
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