PostgreSQL 15+PostGIS如何在单列存储点坐标集合？

你的数据是闭合的点序列，对应地理上的多边形区域，应该存储为PostGIS的Polygon类型（而非单个点或点列表），以下是具体实现步骤：

1. 修正表结构 #

首先替换原有的Point列，创建适配多边形的空间列：

-- 先移除错误的Point列（如果已存在）
ALTER TABLE cities DROP COLUMN IF EXISTS city_bounds;
-- 新建Polygon类型的空间列，指定SRID为4326（对应WGS84经纬度坐标系）
ALTER TABLE cities ADD COLUMN city_bounds geometry(Polygon, 4326);

2. 将VARCHAR字符串转换为Polygon #

你的源数据是标准JSON格式的点数组，推荐用PostgreSQL的JSON函数配合PostGIS空间函数完成转换，这种方式比纯字符串替换更可靠：

假设你的源字符串存储在varchar_bounds列中，执行以下更新语句：

UPDATE cities c
SET city_bounds = ST_MakePolygon(
    ST_SetSRID(
        ST_MakeLine(
            ARRAY(
                -- 注意：原数据是[纬度, 经度]，PostGIS要求坐标顺序为[经度, 纬度]，所以调换位置
                SELECT ST_MakePoint(
                    (point->>1)::double precision,  -- 提取经度（数组第二个元素）
                    (point->>0)::double precision   -- 提取纬度（数组第一个元素）
                )
                FROM jsonb_array_elements(c.varchar_bounds::jsonb) AS point
            )
        ),
        4326
    )
)
WHERE c.varchar_bounds IS NOT NULL AND c.varchar_bounds != '';

关键逻辑说明： #

• varchar_bounds::jsonb：将字符串转换为JSONB类型，方便解析数组元素
• jsonb_array_elements：拆分JSON数组，逐个取出每个点的坐标数组
• ST_MakePoint(lng, lat)：按PostGIS要求的坐标顺序创建单个点
• ST_MakeLine：将所有点连接成闭合线串（你的源数据首尾点相同，天然满足闭合要求）
• ST_MakePolygon：将闭合线串转换为多边形
• ST_SetSRID：为多边形指定空间参考系（4326对应全球通用的WGS84经纬度）

3. 验证转换结果 #

执行以下语句检查转换后的多边形是否有效：

-- 查看转换后的WKT格式，确认坐标顺序和多边形结构
SELECT city_id, ST_AsText(city_bounds) FROM cities;

-- 验证多边形的有效性（无效的话可使用ST_MakeValid修复）
SELECT city_id, ST_IsValid(city_bounds) FROM cities;

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