postgresql 使用Python从GeoJSON文本在PostGres DB中插入几何

baubqpgj  于 2023-08-04  发布在  PostgreSQL
关注(0)|答案(1)|浏览(125)

我有一个GeoJSON文件,我需要用Python将几何图形导入到Postgres DB中。我试过这个:

...
cursor = conn.cursor()
data = json.load(json_file)
for row in data['features']
    geometry = row['geometry']
    params = (geometry)
    cursor.callproc('ST_GeomFromGeoJSON', params)
    wkb_geometry = cursor.fetchone()[0]
    sql = "INSERT INTO tbl(wkb_geometry) VALUES (%s)"
    val = (wkb_geometry)
    cursor.execute(sql, val)
    conn.commit()
...

字符串
我得到这个错误:

function st_geomfromgeojson(typt => unknown, coordinates => numeric[]) does not exist
LINE 1: SELECT * FROM ST_GeomFromGeoJSON("type":='Polygon',"coordina...

HINT: No function matches the given name and argument types. You might need to add explicit type casts.


我不知道如何添加建议的显式类型转换。

irlmq6kh

irlmq6kh1#

我找到了解决方案(非常简单):我只需要使用JSON.dumps()将JSON数据转换为字符串,并运行一个简单的插入SQL。

cursor = conn.cursor()
data = json.load(json_file)
for row in data['features']
    ...
    geometry = json.dumps(row['geometry'])
    
    sql = "INSERT INTO tbl(wkb_geometry) VALUES (ST_GeomFromGeoJSON(%s))"
    val = (wkb_geometry)
    cursor.execute(sql, val)
    conn.commit()
...

字符串

相关问题