使用GeomFromText('POINT(1 1)')转换经纬度对，然后插入另一列 [英] Convert lat/lng pairs using GeomFromText('POINT(1 1)') and insert in another column

问题描述

在上一个问题中搜索范围纬度/经度坐标我的解决方案是要创建下表.

In my previous question Search for range Latitude/Longitude coordinates My solution was to create the table below.

mysql> select * from spatial_table where MBRContains(GeomFromText('LINESTRING(9 9, 11 11)'), my_spots);
+------+---------------------------------+
| id   | my_spots    | my_polygons       |
+------+-------------+-------------------+
|    1 |  $@      $@     $@      $@      |
+------+-------------+-------------------+

现在，我需要将下表中的现有经纬度对转换并移动到spatial_table.我将如何构造查询以实现此目的?我目前正在使用下面的查询进行插入.

Now I need to convert and move my existing lat/lng pairs in the table below to spatial_table. How would I structure my query to acheive this? I am currently using the queries below to insert.

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 |
+-------------+---------+--------+-----------+------------+-------------+--------------+    

推荐答案

这是成功的秘诀:) 我的原始表格:

Here is the secret recipe to success :) My original table:

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列

step 1: Add new POINT column

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.

Step 2: Update my_point with values from lat/lng fields.

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)   |
+--------------------------------------+

这篇关于使用GeomFromText('POINT(1 1)')转换经纬度对，然后插入另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！