MySQL INSERT/UPDATE on POINT column

Question

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 1

Try doing it without assigning your values to server values. Especially if they contain function calls. MySQL treats the contents of the variables as plain text and won't see that there's a function call in there.

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

Answer 2

You need to use this syntax:

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