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

score 13 · Answer 1 · answered Apr 11 '12 at 08:54

13

You need to use this syntax:

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

answered Apr 11 '12 at 08:54

FattyPotatoes

823
1
9
14

This was only needed in MySQL < 5.1.35, see http://dba.stackexchange.com/a/33454/2725 - the accepted answer is correct. – cweiske Feb 23 '17 at 07:40
It also works: `UPDATE ... SET latitude=18, longitute=-63, geoPoint=POINT(18, -63) WHERE ...`. Separate arguments in POINT by comma – hadi.mansouri Aug 25 '17 at 23:45

score 13 · Accepted Answer · edited Aug 14 '19 at 16:40

13

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

edited Aug 14 '19 at 16:40

Michail Terezakis

3
1
3

answered Mar 13 '11 at 23:00

Marc B

356,200
43
426
500

2

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. – Luis D Urraca Mar 13 '11 at 23:07
Apparently in MySQL I cannot see what's inside the geometry column however if i use SELECT AsText(*) ... i can see point information. – Luis D Urraca Mar 14 '11 at 01:55
3

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. – Adam Oct 04 '14 at 13:42
This is correct since MySQL 5.1.35, see http://dba.stackexchange.com/a/33454/2725 – cweiske Feb 23 '17 at 07:41
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. – hadi.mansouri Aug 25 '17 at 23:44
Please update `POINT` params needs to be comma separated according to [reference](https://dev.mysql.com/doc/refman/5.7/en/gis-point-property-functions.html) – Lau Real Aug 20 '20 at 03:44
This answer must be edited. POINT(-63 18), instead of POINT(18 -63). Latitude should be stored as Y, and X for longitude. – Muhammad Irvan Hermawan Mar 08 '22 at 13:53

score 2 · Answer 3 · answered Jun 07 '22 at 14:46

2

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

answered Jun 07 '22 at 14:46

cancerbero

6,799
1
32
24

MySQL INSERT/UPDATE on POINT column

3 Answers3

Linked