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