Moving lat/lon text columns into a 'point' type column

Question

I've got a table in my MySQL database called house.

Within the house table, there are a couple of text columns called latitude and longitude.

I've added a new column called coords, of type point - http://dev.mysql.com/doc/refman/5.0/en/gis-class-point.html

How would I move the latitude and longitude values into the new coords column?

This Question and some of the Answers are getting stale -- InnoDB now has SPATIAL and ST_DISTANCE_SPHERE(). — Rick James, Apr 14 '22 at 06:33

score 41 · Answer 1 · edited Mar 14 '19 at 10:24

41

Assuming you want a SPATIAL index on this column:

ALTER TABLE mytable ADD coords Point;

UPDATE  mytable
SET     coords = Point(lon, lat);

ALTER TABLE mytable MODIFY coords POINT NOT NULL;

CREATE SPATIAL INDEX sx_mytable_coords ON mytable(coords);

If you don't, you can omit the last two steps.

Update:

In earlier versions of MySQL, you would need to populate Point columns using WKT:

UPDATE  mytable
SET     coords = GeomFromText(CONCAT('POINT (', lon, ' ', lat, ')'))

edited Mar 14 '19 at 10:24

Pete Houston

14,931
6
47
60

answered Apr 22 '11 at 14:05

Quassnoi

413,100
91
616
614

1

The second command doesn't seem to move the values into coords. I get this error: Cannot get geometry object from data you send to the GEOMETRY field – cannyboy Apr 22 '11 at 14:25
@cannyboy: which version of `MySQL` are you using? Can `lat` and `lon` be `NULL`? – Quassnoi Apr 22 '11 at 14:27
MySQL 5.0.45. the latitude and longitude text columns are not null. – cannyboy Apr 22 '11 at 14:31
can't get the update to work either. maybe sqlbuddy isn't showing points properly? i'll have a look in phpmyadmin – cannyboy Apr 22 '11 at 14:58
@cannyboy: what do you mean by "can't get the update to work"? Please post the exact error text. – Quassnoi Apr 22 '11 at 15:01
9

@Quassnoi You have the order wrong. It is `POINT(long, lat)`. See http://dev.mysql.com/doc/refman/5.7/en/gis-class-point.html. GeomFromText is redundant as well in the recent MySQL versions. – Gajus Apr 30 '13 at 10:09
@GajusKuizinas: It does not really matter, as `MySQL` does not support geography functions anyway and you'll have to use conversion formulas. – Quassnoi Apr 30 '13 at 12:06
@GajusKuizinas - The order actually doesn't matter. Yes, technically longitude is your 'x' coordinate and latitude is your 'y', but as long as you're consistent in terms of which order you use when providing your latitude/longitude values to MySQL you can do so in either order. Given that the convention when working with lat/long pairs is to specify them as `(latitude, longitude)`, I'd argue that `Point(lon, lat)` is counterintuitive and should therefore be avoided. – aroth Oct 30 '15 at 04:22
4

@aroth It does matter, at least from my anecdotal experience. Working with functions such as `ST_Contains` and `ST_Distance` produced odd results when I have stored point data as `lat, long`. – Gajus Oct 30 '15 at 11:08
@GajusKuizinas - Fair enough. I've primarily only used the older `MBRContains` function, which in my experience works the same regardless of coordinate ordering (so long as it's always consistently one way or the other). Perhaps the newer ones are different? – aroth Oct 30 '15 at 14:52
@Quassnoi in the 4th line, where you are altering the table to make the field NOT NULL, you should also write the dataType, which is POINT in this case. So it should be like: ALTER TABLE mytable MODIFY coords POINT NOT NULL; – Seyed Hamed Shams Jan 03 '19 at 07:23

score 15 · Answer 2 · answered Oct 06 '13 at 04:44

MySQL Version 5.5.8

My latitude and longitude are of type float. To update existing rows...

UPDATE table_name SET coord = POINT(longitude_field, latitude_field);

Something to consider, if you are collecting data and need to save the latitude and longitude separately, in their respective columns, I suggest adding a trigger to your table

CREATE DEFINER=`username`@`localhost` TRIGGER `table_name`.`create_point_geom` 
BEFORE INSERT ON database_name.table_name FOR EACH ROW
BEGIN
    SET NEW.coord = POINT(NEW.longitude, NEW.latitude);
END;

I collect geo-tagged social media data and I use this method to add geometry to my tables.

score 10 · Answer 3 · answered Apr 22 '11 at 15:36

10

Concisely:

UPDATE myTable SET coords = GeometryFromText( CONCAT( 'POINT(', lon, ' ', lat, ')' ) );

Note that answer from Quassnoi is in error since the proper input format is POINT(X Y), or in terms of earth POINT(lon lat).

Note you can show points via the X() and Y() functions like the following example:

SELECT X( GeometryFromText( CONCAT( 'POINT(', 35, ' ', 60, ')' ) ) ) AS x, Y( GeometryFromText( CONCAT( 'POINT(', 35, ' ', 60, ')' ) ) ) AS y;

answered Apr 22 '11 at 15:36

Joseph Lust

19,340
7
85
83

Are you positive that it is lon-lat.. everything else on the net says the opposite – Jonathan Tonge Dec 03 '12 at 09:33
@Jonathan It is POINT(x,y). See this proof `SELECT X( POINT( 3, 2 ) ) AS x` You'll get back 3. – Joseph Lust Dec 03 '12 at 17:01

score 2 · Answer 4 · edited Jul 08 '19 at 23:13

FINALLY! I was able to fix these errors:

#3037 - Invalid GIS data provided to function st_geometryfromtext.
#1416 - Cannot get geometry object from data you send to the GEOMETRY field

By doing a custom SQL query, where I pointed the lat and long points. In my case, the SQL string that did it was:

UPDATE wp_wpgmza SET latlng = GeometryFromText( CONCAT( 'POINT(', 38.5775167, ' ', -121.4868583, ')' ) ) WHERE id = 63;

Moving lat/lon text columns into a 'point' type column

4 Answers4

Linked