Redshift中如何向geometry类型列插入点坐标数据

报错原因 #

1. 插入语句列值数量不匹配：你声明了5个要插入的字段，但是VALUES子句仅提供了4个值，同时错误将坐标字符串赋值给了city字段，给geometry类型的coordinates字段传入了时区字符串。Redshift的geometry类型默认接收十六进制编码的WKB格式输入，传入普通文本就会触发无效十六进制字符的报错。
2. 字段类型不匹配：你建表时timezone字段类型为timestamp with time zone，但传入的是时区名称字符串，无法直接识别。如果你的需求是存储时区名，建议先修改该字段类型为varchar；如果需要存储带时区的时间戳，需要传入符合格式的时间值。

正确插入方法 #

Redshift提供空间函数ST_GeomFromText()可以将WKT（Well-known text）格式的几何文本转为geometry类型，点坐标的WKT标准格式为POINT(经度 纬度)。

步骤1：（可选）调整timezone字段类型

如果要存储时区名称，先修改字段类型：

ALTER TABLE airports_data ALTER COLUMN timezone TYPE varchar;

步骤2：执行插入语句

INSERT INTO airports_data(airport_code, airport_name, city, coordinates, timezone) 
VALUES (
  'YKS',
  'Yakutsk Airport',
  'Yakutsk', -- 替换为实际城市名
  ST_GeomFromText('POINT(129.77099609375 62.093299865722656)', 4326),
  'Asia/Yakutsk'
);

注意事项 #

• ST_GeomFromText的第一个参数必须符合WKT规范，点坐标的经度和纬度之间用空格分隔，不能用逗号
• 第二个参数为SRID（空间参考标识符），示例中4326对应常用的WGS84坐标系，无特殊需求可以省略该参数
• 如果你需要保留timezone字段为timestamp with time zone类型，需要将最后一个值替换为合法的带时区时间戳，例如'2024-01-01 00:00:00 Asia/Yakutsk'

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