GeoPandas.GeoDataFrame:不存在“几何图形”类型
GeoPandas.GeoDataFrame:不存在“几何图形”类型
提问于 2022-03-23 20:41:14
Ubuntu20.04.3 LTS (WSL2)
菲奥娜1.8.21
地质公园0.10.2
地质公园-基础0.10.2
地质2.2.0
numpy 1.22.3
熊猫1.4.1
psycopg2 2.9.3
pyproj 3.3.0
rtree 0.9.7
外形1.8.0
PostgreSQL14.2-1.pgdg20.04+1
PostgreSQL14-postgis-33.2.1+dfsg-1.pgdg20.04+1
试图将两个多边形发送到postgresql数据库(postgis)。没有运气。-总是收到同样的错误信息。有人能帮我吗？
Exception in thread Thread-2 (_create_searchwindows):
Traceback (most recent call last):
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1808, in _execute_context
self.dialect.do_execute(
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedObject: type "geometry" does not exist
LINE 4: geometry geometry(POLYGON,25832)
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/user/miniconda3/envs/geo/lib/python3.10/threading.py", line 1009, in _bootstrap_inner
self.run()
File "/home/user/miniconda3/envs/geo/lib/python3.10/threading.py", line 946, in run
self._target(*self._args, **self._kwargs)
File "/home/user/github/create_searchwindows/searchwindow_creator.py", line 143, in _create_searchwindows
grid.to_postgis(self.args.searchwindows_tablename,self.engine,if_exists="append",index=True,index_label="Index")
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/geopandas/geodataframe.py", line 1808, in to_postgis
geopandas.io.sql._write_postgis(
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/geopandas/io/sql.py", line 431, in _write_postgis
gdf.to_sql(
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/pandas/core/generic.py", line 2963, in to_sql
return sql.to_sql(
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/pandas/io/sql.py", line 697, in to_sql
return pandas_sql.to_sql(
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/pandas/io/sql.py", line 1729, in to_sql
table = self.prep_table(
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/pandas/io/sql.py", line 1628, in prep_table
table.create()
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/pandas/io/sql.py", line 842, in create
self._execute_create()
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/pandas/io/sql.py", line 828, in _execute_create
self.table.create(bind=self.pd_sql.connectable)
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 950, in create
bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2119, in _run_ddl_visitor
visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
return meth(obj, **kw)
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 893, in visit_table
self.connection.execute(
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1295, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
return connection._execute_ddl(
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1387, in _execute_ddl
ret = self._execute_context(
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1851, in _execute_context
self._handle_dbapi_exception(
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2032, in _handle_dbapi_exception
util.raise_(
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1808, in _execute_context
self.dialect.do_execute(
File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) type "geometry" does not exist
LINE 4: geometry geometry(POLYGON,25832)
^
[SQL:
CREATE TABLE public.sw32 (
"Index" BIGINT,
geometry geometry(POLYGON,25832)
)
]
(Background on this error at: https://sqlalche.me/e/14/f405)我确保在数据库中激活了postgis扩展。如果我使用psql在数据库中创建带有几何图形列的表，那么它的工作非常好。在安装python包时，似乎有些地方不正确。是不是有人想出了同样的麻烦？
代码如下：
import geopandas as gpd
from sqlalchemy import create_engine
engine = create_engine(f'postgresql://{db_user}:{db_password}@{db_host}:{db_port}/')
grid = gpd.GeoDataFrame(polygons,geometry="geometry",crs="EPSG:25832")
grid.to_postgis("testtable",engine,if_exists="append",index=True,index_label="Index") 编辑1:
我目前的解决方法是将多边形存储在*.shp文件中，并执行shp2psql将多边形发送到数据库。代码如下：
grid.to_file("tmp_name.shp")
cmd = f"shp2pgsql -s 25832 tmp_name.shp public.testtable |psql postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{database}"
os.system(cmd)回答 2
Stack Overflow用户
回答已采纳
发布于 2022-03-24 10:00:51
实际上，我的连接字符串是不正确的。忘了提到数据库了。对连接字符串进行如下调整：
engine = create_engine(f'postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{database}')一切都很好！
Stack Overflow用户
发布于 2022-05-03 06:34:16
可能与您的"geoalchemy2“版本有关。您的geoalchemy2版本应该是"0.9.2“。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71593785
复制相关文章
相似问题
腾讯云开发者
