Postgresql 转换失败：(—122.763091，49.04676)转换为地理(位置)""

发布于04月05日

我试图将一个CSV文件导入PostgreSQL，其中列名为location，其中包含格式为(-longitude,latitude)的坐标.但是，我得到了以下错误:

2:36846: conversion failed: "(-122.763091,49.04676)" to geography (location)
3:37257: conversion failed: "(-123.141289,49.272057)" to geography (location)
4:36495: conversion failed: "(-122.850334,49.189992)" to geography (location)

我判断了以下内容:

SRID设置为4326.
location列中的值是正确的格式(-longitude,latitude).
location列中没有无效或不完整的值.

我使用PostgreSQL 14和PostGIS 3.2.

Example:

以下是CSV文件中的数据示例:

-122.763091,49.04676
-123.141289,49.272057
-122.850334,49.189992

What am I doing wrong?

推荐答案

如果此示例类似于在CSV文件中保存这些地理位置的方式:

id,description,geog
1,"description1","(-122.850334,49.189992)"

然后，将第三个字段读取到geography类型列将不起作用，因为这不是一个有效的geography常量.这些都不起作用:

id,description,geog
1,"description1","(-122.850334 49.189992)"
1,"description1","-122.850334,49.189992"
1,"description1","-122.850334 49.189992"

这将:

id,description,geog
1,"description1","point(-122.850334 49.189992)"

您可以将列更改为text，按原样导入，在point前面加上逗号,，用空格替换逗号,，然后用alter将类型恢复为geography(point,4326).Demo at db<>fiddle:

create table my_table (id int, description text, geog geography(Point,4326));
--preparing a test file:
copy (select '1,"abc","(-122.850334,49.189992)"') to '/tmp/my_file.csv';

alter table my_table 
  alter column geog type text;

copy my_table from '/tmp/my_file.csv' csv delimiter ',' quote '"';

select *,pg_typeof(geog) from my_table;

id	description	geog	pg_typeof
1	abc	(-122.850334,49.189992)	text

update my_table 
  set geog='point'||replace(geog,',',' ')
  returning *,pg_typeof(geog);

id	description	geog	pg_typeof
1	abc	point(-122.850334 49.189992)	text

alter table my_table 
  alter column geog type geography(point,4326) 
  using geog::geography(point,4326);

select *,pg_typeof(geog),st_astext(geog) from my_table;

id	description	geog	pg_typeof	st_astext
1	abc	0101000020E6100000522B4CDF6BB65EC0354069A851984840	geography	POINT(-122.850334 49.189992)

您还可以利用这些值是有效的point类型常量这是PostgreSQL built-in point type，不要与PostGIS geometry(point)混淆.Postgr point可以直接接受这些值，然后从该值到PostGIS geometry(point)和从该值到geography(point)的预定义转换:

truncate my_table;
copy (select '1,"abc","(-122.850334,49.189992)"') to '/tmp/my_file.csv';
alter table my_table
  drop column if exists geog,
  add column geog point;

copy my_table from '/tmp/my_file.csv' csv delimiter ',' quote '"';

alter table my_table 
  alter column geog type geography(point,4326) 
  using geog::geometry(point)::geography(point,4326);