如何为含GEOMETRY POINT列的表添加并更新lat、lng十进制字段

没问题，我帮你整理了一套完整的SQL方案，分两种主流数据库（MySQL和PostgreSQL）来实现，完全匹配你的需求：

通用操作逻辑 #

整个流程分两步：先给目标表新增lat（纬度）和lng（经度）两个DECIMAL类型列，再从loc字段里提取对应值填充到新列中。

MySQL 版本实现 #

1. 新增lat和lng列 #

先执行ALTER TABLE语句添加列，这里我用了DECIMAL(10,6)的精度（足够覆盖绝大多数经纬度场景），你可以根据实际需求调整：

-- 替换your_table_name为你的实际表名
ALTER TABLE your_table_name
ADD COLUMN lat DECIMAL(10,6) NOT NULL,
ADD COLUMN lng DECIMAL(10,6) NOT NULL;

2. 提取经纬度并更新数据 #

MySQL提供了ST_X()和ST_Y()函数来提取POINT类型的坐标值。根据你的示例数据，loc字段的POINT存储顺序是(纬度, 经度)，所以直接用以下语句更新：

-- 替换your_table_name为你的实际表名
UPDATE your_table_name
SET lat = ST_X(loc),
    lng = ST_Y(loc);

注意：如果你的POINT是标准GIS存储顺序(经度, 纬度)，则需要调换函数：lat = ST_Y(loc), lng = ST_X(loc)

PostgreSQL 版本实现 #

PostgreSQL需要依赖PostGIS扩展处理几何类型，如果你还没启用，先执行以下语句开启：

CREATE EXTENSION postgis;

1. 新增lat和lng列 #

和MySQL的语法基本一致：

-- 替换your_table_name为你的实际表名
ALTER TABLE your_table_name
ADD COLUMN lat DECIMAL(10,6) NOT NULL,
ADD COLUMN lng DECIMAL(10,6) NOT NULL;

2. 提取经纬度并更新数据 #

PostGIS同样用ST_X()和ST_Y()提取坐标，根据你的示例数据顺序，执行：

-- 替换your_table_name为你的实际表名
UPDATE your_table_name
SET lat = ST_X(loc),
    lng = ST_Y(loc);

注意：如果是标准GIS存储顺序(经度, 纬度)，则改为lat = ST_Y(loc), lng = ST_X(loc)

一些重要提醒 #

• 操作前一定要备份数据，避免误操作导致数据丢失
• 如果表数据量很大，UPDATE语句可能会耗时较长，建议在业务低峰期执行，或者分批更新
• DECIMAL的精度可以按需调整，比如需要更高精度可以用DECIMAL(12,8)

内容的提问来源于stack exchange，提问作者hrushilok