MySQL INSERT/UPDATE on POINT column

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 ...