将纬度/经度文本列移动到“点"类型列中 [英] Moving lat/lon text columns into a 'point' type column

问题描述

我的MySQL数据库中有一个名为house的表.

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

在house表中，有几个名为latitude和longitude的文本列.

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

我添加了一个名为coords的新列，其类型为point-

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

如何将latitude和longitude值移动到新的coords列中?

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

推荐答案

假设您要在此列上使用SPATIAL索引:

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.

更新:

在MySQL的早期版本中，您需要使用WKT填充Point列:

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

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

这篇关于将纬度/经度文本列移动到“点"类型列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！