在我上一个问题中，搜索范围纬度/经度坐标。 我的解决方案是创建下表。
mysql> select * from spatial_table where MBRContains(GeomFromText('LINESTRING(9 9, 11 11)'), my_spots); +------+---------------------------------+ | id | my_spots | my_polygons | +------+-------------+-------------------+ | 1 | $@ $@ $@ $@ | +------+-------------+-------------------+
现在，我需要转换下表中的现有经纬度对并将其移动到spatial_table。我将如何构造查询以实现此目的？我目前正在使用以下查询进行插入。
mysql> insert into spatial_table values (1, GeomFromText('POINT(1 1)'), GeomFromText('POLYGON((1 1, 2 2, 0 2, 1 1))')); Query OK, 1 row affected (0.00 sec) mysql> insert into spatial_table values (1, GeomFromText('POINT(10 10)'), GeomFromText('POLYGON((10 10, 20 20, 0 20, 10 10))') ); Query OK, 1 row affected (0.00 sec)
现有表：
+-------------+---------+--------+-----------+----- ------+-------------+--------------+ | location_id | country | region | city | latitude | longitude | name | +=============|=========|========|===========|============|=============|==============| | 316625 | US | CA | Santa Cruz| 37.044799 | -122.102096 | Rio Theatre | +-------------+---------+--------+-----------+------------+-------------+--------------+
这是成功的秘诀：）我的原始表格：
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) | +--------------------------------------+
