这是成功的秘诀:)我的原始表格:
MysqL> describe gls;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| location_id | int(255) | NO | PRI | 0 | |
| country | varchar(255) | NO | | | |
| region | varchar(255) | NO | | | |
| city | varchar(255) | NO | | | |
| latitude | float(13,10) | NO | | | |
| longitude | float(13,10) | NO | | | |
+-------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
步骤1:添加新的POINT列
MysqL> alter table gls add my_point point;
Query OK, 247748 rows affected (4.77 sec)
Records: 247748 Duplicates: 0 Warnings: 0
步骤2:使用lat / lng字段中的值更新my_point。
UPDATE gls SET my_point = PointFromText(CONCAT('POINT(',gls.longitude,' ',gls.latitude,')'));
步骤3:检查
MysqL> select aswkt(my_point) from gls where city='Santa Cruz';
+--------------------------------------+
| aswkt(my_point) |
+--------------------------------------+
| POINT(-122.1020965576 37.0447998047) |
| POINT(-66.25 -12.2833003998) |
| POINT(-2.3499999046 42.6666984558) |
+--------------------------------------+