将纬度/经度字段转换为地理空间点

Question

我有一张表，包含纬度、经度和坐标三列。该表中有超过50万行数据，其中每一行的坐标字段目前为空。我需要将纬度和经度转换为地理空间 POINT() 并将其放入坐标列中。是否可以实现这个需求？

我知道可以使用以下方式插入 POINT：

INSERT INTO `myTable` (`coordinates`) VALUES (GeomFromText('POINT(50.2 51.6)'));

我实际上需要做的就是从每一行中获取latitude和longitude的值，与此完全相同。

- jskidd3

---

纬度是50.2，经度是51.6吗？ - ganders

@ganders 不，那只是一个例子。有500,000个不同的纬度和经度点，类型为FLOAT(10,6)。 - jskidd3

我想问的是，50.2是否对应纬度列，51.6是否对应经度列？（并且像你说的那样，有500,000个条目） - ganders

@ganders 是的，没错（抱歉），纬度 - 经度。 - jskidd3

你使用的是哪个版本的Mysql？ - Ray

@Ray MySQL 版本 5.6.14 - jskidd3

Answer 1

这是一个较旧的问题，但我注意到所有答案都是错误的（至少在2019年），纬度和经度必须相反

正确应该是：POINT(longitude,latitude)

Mysql 5.6及以上版本可使用：

Update myTable
Set coordinates = POINT(longitude, latitude);

如果我们尝试像这样：

SELECT ST_Distance_Sphere(
    POINT(13.1500000,99.9666700), 
    POINT(48.861105, 120.335337)
);

出现错误：

错误 1210 (HY000)：st_distance_sphere的参数不正确

因为我在答案中使用了像POINT(latitude, longitude)这样的代码。

但是，如果我们尝试使用POINT(longitude,latitude)：

SELECT ST_Distance_Sphere(
    POINT(99.9666700, 13.1500000), 
    POINT(120.335337, 48.861105)
);

结果是：

+--------------------------------------------+
| ST_Distance_Sphere(
        POINT(99.9666700, 13.1500000),
        POINT(120.335337, 48.861105)
    ) |
+--------------------------------------------+
|                                                                                      4389299.754585881 |
+--------------------------------------------+
1 row in set (0.00 sec)

- MTK

Answer 2

一个GeomFromText需要一个字符串，你有很多连接。

INSERT INTO myTable (coordinates)  
   SELECT GeomFromText(CONCAT('POINT(',ot.latitude, ' ', ot.longitude,')'))
    FROM otherTable ot;

如果是将已有的表格中带有latitude和longitude列更新到一个新列coordinates，请按照以下步骤操作：

UPDATE  myTable 
 SET coordinates =  GeomFromText(CONCAT('POINT(',latitude, ' ', longitude,')'));

- Ray

---

如果纬度和经度在同一张表中，是的，进行更新。我不确定它们是否在同一张表中。 - Ray

您的SQL语法存在错误，请检查与您的MySQL服务器版本相对应的手册，以获取在第2行使用的正确语法（用于更新查询）。 - jskidd3

@jskidd3 可能是空格的问题？ - Ray

@jskidd3，我使用我所包含的表在我的5.5系统上运行了插入和更新操作。你可以把你的创建语句发到问题里吗？ - Ray

一切都很好，谢谢 Ray，工作完美。感谢您的帮助。 - jskidd3

Answer 3

这样的东西可以用吗？

Update myTable
Set coordinates = GeomFromText('POINT(' + latitude + ' ' + longitude + ')')

假设你的坐标列与经纬度存在于同一张表中。

（请记住这是 SqlServer/Tsql 语法）...

- ganders