通过psycopg2创建具有新几何图形类型的红移表

Question

在Redshift宣布支持几何类型和空间函数之后，我想为所有国家创建一个包含多边形的表格。我没能完成插入，非常感谢您的帮助

以下是我尝试过的：

我已经下载了geojson并解压缩了（https://datahub.io/core/geo-countries）

然后使用以下python代码片段成功创建了表（我使用了类型GEOMETRY，不确定是否可以优化和使用子类型POLYGON）：

import psycopg2

conn = psycopg2.connect(...connection params)
cur = conn.cursor()
cur.execute("CREATE TABLE engagement.geospatial_countries (id INTEGER PRIMARY KEY, name VARCHAR(25), code VARCHAR(10), polygon GEOMETRY);")

以下脚本成功读取geojson，“国家”中的每个条目都具有多边形geojson功能：

f = open("geospatial-data/countries.geojson", "r")
countries_file_contents = f.read()
countries_geojson = json.loads(countries_file_contents)
countries = countries_geojson["features"]

对于那些不熟悉GeoJson的人来说，它只是一组描述地理空间形状的JSON数据。以下是数据摘录：

{ "type": "FeatureCollection", "features": [{ "type": "Feature", "properties": { "ADMIN": "Aruba", "ISO_A3": "ABW" }, "geometry": { "type": "Polygon", "coordinates": [ [ [ -69.996937628999916, 12.577582098000036 ], [ -69.936390753999945, 12.531724351000051 ], [ -69.924672003999945, 12.519232489000046 ], [ -69.915760870999918, 12.497015692000076 ], [ -69.880197719999842, 12.453558661000045 ], [ -69.876820441999939, 12.427394924000097 ], [ -69.888091600999928, 12.417669989000046 ], [ -69.908802863999938, 12.417792059000107 ], [ -69.930531378999888, 12.425970770000035 ], [ -69.945139126999919, 12.44037506700009 ], [ -69.924672003999945, 12.44037506700009 ], [ -69.924672003999945, 12.447211005000014 ], [ -69.958566860999923, 12.463202216000099 ], [ -70.027658657999922, 12.522935289000088 ], [ -70.048085089999887, 12.531154690000079 ], [ -70.058094855999883, 12.537176825000088 ], [ -70.062408006999874, 12.546820380000057 ], [ -70.060373501999948, 12.556952216000113 ], [ -70.051096157999893, 12.574042059000064 ], [ -70.048736131999931, 12.583726304000024 ], [ -70.052642381999931, 12.600002346000053 ], [ -70.059641079999921, 12.614243882000054 ], [ -70.061105923999975, 12.625392971000068 ], [ -70.048736131999931, 12.632147528000104 ], [ -70.00715084499987, 12.5855166690001 ], [ -69.996937628999916, 12.577582098000036 ] ] ] } }, ... more countries }]}

在插入所有国家/地区之前，我首先只想尝试为单个国家/地区创建它：

country = countries[0]
geometry_to_insert = (
    country["properties"]["ADMIN"],
    country["properties"]["ISO_A3"],
    Json.dumps(country["geometry"]) # Have also tried psycopg2.extras.Json(country["geometry"]), as well as just using the dict
)

以下操作失败：

cur.execute(
  "INSERT INTO engagement.geospatial_countries (name, code, polygon) VALUES %s",
  geometry_to_insert
)

出现以下错误： TypeError:在字符串格式化过程中并非所有参数都已转换

我也试过了

cur.execute(
  "INSERT INTO engagement.geospatial_countries (name, code, polygon) VALUES (%s, %s, %s)",
  geometry_to_insert
)

但这会产生以下错误：psycopg2.errors.InternalError\ux:Compass I/O异常：找到无效的十六进制字符

如何使用新的几何体类型将多边形插入到红移中

Answer 1

postgres/redshift几何体不是GeoJSON，您需要使用JSON列类型：

...
cur.execute("CREATE TABLE engagement.geospatial_countries (id INTEGER PRIMARY KEY, name VARCHAR(25), code VARCHAR(10), polygon JSON)")
country = countries[0]
geometry_to_insert = (
    country["properties"]["ADMIN"],
    country["properties"]["ISO_A3"],
    Json.dumps(country["geometry"])
)
cur.execute(
      "INSERT INTO engagement.geospatial_countries (name, code, polygon) VALUES (%s, %s, %s)", geometry_to_insert
)

Answer 2

这应该起作用：

cur.execute(
  "INSERT INTO engagement.geospatial_countries (name, code, polygon) VALUES (%s, %s, %s)",
  geometry_to_insert
)

请阅读文档how to pass parameters to the query，如果您想同时插入多个对象，请阅读the ^{} function

Answer 3

这里我给出了将其插入数据库的步骤

首先，在为几何图形创建表格时，使用IDENTITY创建一个自动递增ID，这是一个小小的更正：

conn = psycopg2.connect(...connection params)
cur = conn.cursor()
cur.execute("CREATE TABLE engagement.geospatial_countries (id INTEGER IDENTITY(0,1) PRIMARY KEY, name VARCHAR(25), code VARCHAR(10), polygon GEOMETRY);")

在几何图形上。要插入该值，请使用WKT值：

import geojson
from shapely.geometry import shape
...
# exact same steps as in question to read file, then
country = countries[0]
geom = shape(country["geometry"])
geometry_to_insert = (
    country["properties"]["ADMIN"],
    country["properties"]["ISO_A3"],
    geom.wkt
)

然后使用以下命令插入值：

cur.execute(
  "INSERT INTO engagement.geospatial_countries (name, code, polygon) VALUES (%s, %s, ST_GeomFromText(%s))",
  geometry_to_insert
)

来自@Maurice Meyer和@piro的回答引导我找到了这个答案