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?

Answer 1

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, ')')) 

Answer 2

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.