将含坐标的CSV导入PostgreSQL时geography类型转换失败求助

PostGIS的geography类型仅支持解析标准WKT格式的地理数据（例如POINT(-122.763091 49.04676)），而你CSV中使用的(-122.763091,49.04676)是自定义的带括号、逗号分隔格式，PostGIS无法自动识别这种非标准格式，因此触发转换失败错误。

提供三种可行方案，可根据实际场景选择：

方案1：修改CSV为标准WKT格式 #

直接调整CSV文件中location列的内容，将每个坐标字符串替换为POINT(lon lat)格式：

POINT(-122.763091 49.04676)
POINT(-123.141289 49.272057)
POINT(-122.850334 49.189992)

修改完成后，直接使用COPY命令导入即可，PostGIS会自动识别WKT格式并转换为geography类型。

方案2：先导入为数值列，再转换为geography #

若不想修改CSV文件，可先创建包含经度、纬度数值列的临时表，导入数据后再生成location列：

1. 创建临时表：

CREATE TABLE temp_import (
    lon FLOAT,
    lat FLOAT
);

2. 导入CSV数据：

COPY temp_import (lon, lat) FROM '/path/to/your/file.csv' WITH (FORMAT CSV);

3. 转换为geography类型：

-- 为临时表添加location列
ALTER TABLE temp_import ADD COLUMN location geography(POINT, 4326);
-- 计算并填充location值
UPDATE temp_import 
SET location = ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography;

方案3：在COPY时用函数解析自定义格式 #

如果CSV中的location列是单独的带括号字符串（例如每行内容为"(-122.763091,49.04676)"），可在导入时通过字符串函数拆分经纬度，再构造geography类型：

1. 假设目标表结构：

CREATE TABLE your_table (
    id SERIAL PRIMARY KEY,
    location geography(POINT, 4326)
);

2. 使用COPY结合函数导入：

COPY your_table (location) 
FROM '/path/to/your/file.csv' 
WITH (FORMAT CSV, QUOTE '"')
AS (
    SELECT ST_GeogFromText(
        'POINT(' || 
        regexp_replace(location, '^\((.*),(.*)\)$', '\1 \2') || 
        ')'
    )
);

这里的regexp_replace会将(-lon,lat)格式的字符串替换为lon lat，再拼接成标准WKT的POINT格式，最后通过ST_GeogFromText转换为geography类型。

转换完成后，可执行以下命令验证数据正确性：

SELECT ST_AsText(location) FROM your_table LIMIT 3;

正常输出应为标准POINT格式：

POINT(-122.763091 49.04676)
POINT(-123.141289 49.272057)
POINT(-122.850334 49.189992)

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