如何从JSON数组坐标设置MySQL中的GEOMETRY类型字段

下面针对主流数据库给出具体实现方式，直接通过JSON坐标数组转换为GEOMETRY类型，无需文本格式中转：

MySQL 实现 #

插入新数据 #

如果是直接使用JSON坐标数组，通过ST_GeomFromJSON()函数转换：

INSERT INTO your_table (geo)
VALUES (ST_GeomFromJSON('{"type": "Point", "coordinates": [116.397, 39.908]}'));

若坐标数组存储在现有JSON字段（如coords_json）中，直接引用转换：

INSERT INTO your_table (geo)
SELECT ST_GeomFromJSON(JSON_OBJECT('type', 'Point', 'coordinates', coords_json))
FROM your_source_table;

更新已有数据 #

从原经纬度字段（lon为经度，lat为纬度）转换更新：

UPDATE your_table
SET geo = ST_GeomFromJSON(JSON_OBJECT('type', 'Point', 'coordinates', JSON_ARRAY(lon, lat)));

从JSON数组字段更新：

UPDATE your_table
SET geo = ST_GeomFromJSON(JSON_OBJECT('type', 'Point', 'coordinates', coords_json));

PostgreSQL（依赖PostGIS扩展） #

首先确保启用PostGIS扩展：

CREATE EXTENSION IF NOT EXISTS postgis;

插入新数据 #

通过ST_GeomFromGeoJSON()直接转换GeoJSON格式的坐标：

INSERT INTO your_table (geo)
VALUES (ST_GeomFromGeoJSON('{"type": "Point", "coordinates": [116.397, 39.908]}'));

从JSON数组字段（coords_json）转换：

INSERT INTO your_table (geo)
SELECT ST_GeomFromGeoJSON(json_build_object('type', 'Point', 'coordinates', coords_json))
FROM your_source_table;

更新已有数据 #

从原经纬度字段转换（指定WGS84坐标系，SRID=4326）：

UPDATE your_table
SET geo = ST_SetSRID(ST_MakePoint(lon, lat), 4326);

从JSON数组字段提取坐标转换：

UPDATE your_table
SET geo = ST_SetSRID(ST_MakePoint((coords_json->>0)::float8, (coords_json->>1)::float8), 4326);

核心注意事项 #

• 坐标顺序：必须遵循先经度、后纬度的GeoJSON标准，顺序错误会导致坐标位置完全偏离。
• 坐标系设置：务必指定正确的空间参考ID（SRID），全球GPS坐标通用4326（WGS84），平面坐标需匹配对应SRID。
• 权限与依赖：PostgreSQL需确保PostGIS扩展安装且用户有权限执行空间函数；MySQL需确保版本支持ST_GeomFromJSON()（5.7+）。

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