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

Excrescency

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

Matey

For some reason: UPDATE Punto_Geografico SET latitude = 18.469692, longitude = -63.93212, geoPoint =PoinT(18.469692, -63.93212) WHERE idpunto_geografico = 0; It says that it was succesful updating but i dont see any values in the geoPoint Column. Also i did a "," inside the POINT statement only way that worked. – Excrescency

Apparently in MySQL I cannot see what's inside the geometry column however if i use SELECT AsText(*) ... i can see point information. – Excrescency

This doesn't work, the answer by FattyPotatoes is correct. The POINT() needs to be represented as a string and wrapped with the GeomFromText() function. – Turenne

This is correct since MySQL 5.1.35, see dba.stackexchange.com/a/33454/2725 – Shea

I test following and it works for me: UPDATE ... SET latitude=18, longitute=-63, geoPoint=POINT(18, -63) WHERE .... I separate two argument of POINT with comma. – Catheryncatheter

Please update POINT params needs to be comma separated according to reference – Polash

This answer must be edited. POINT(-63 18), instead of POINT(18 -63). Latitude should be stored as Y, and X for longitude. – Askwith

Answer 2

You need to use this syntax:

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

Cistercian

This was only needed in MySQL < 5.1.35, see dba.stackexchange.com/a/33454/2725 - the accepted answer is correct. – Shea

It also works: UPDATE ... SET latitude=18, longitute=-63, geoPoint=POINT(18, -63) WHERE .... Separate arguments in POINT by comma – Catheryncatheter

Answer 3

INSERT INTO users (name, homeLocation)
    VALUES
    ("John",  POINT(25.7786222, -80.1956483) ),
    ("Anna",  POINT(23.7786222, -81.1956483) )

Fuze