PostgreSQL 安装与使用
-
安装
win 安装 https://www.cnblogs.com/haolb123/p/16553099.html
docker 安装
docker pull postgis/postgis
docker run --name some-postgis -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgis/postgisdocker run -t --name postgresql --restart always -e POSTGRES_USER='postgres' -e POSTGRES_PASSWORD='salis' -e ALLOW_IP_RANGE=0.0.0.0/0 -p 5432:5432
-v /backup/postgresql:/var/lib/postgresql/data -d kartoza/postgis:11.0-2.5run，创建并运行一个容器； –name，指定创建的容器的名字postgresql ； -e POSTGRES_PASSWORD=123456，设置环境变量，指定数据库的登录口令为123456 -e POSTGRES_USER='postgres 设置环境变量，指定数据库用户名为postgres -p 54321:5432，端口映射将容器的5432端口映射到外部机器的54321端口； -d kartoza/postgis:9.6-2.4，允许该容器以守护态（Daemonized）形式运行于后台 -e ALLOW_IP_RANGE=0.0.0.0/0，这个表示允许所有ip访问，如果不加，则非本机 ip 访问不了 -t 让docker分配一个伪终端（pseudo-tty）并绑定到该容器的标准输入上（这是为了在 交互模式下用户可以通过所创建的终端来输入命令) -v /backup/postgresql:/var/lib/postgresql/ 数据持久化 把数据挂载到宿主机的/backup/postgresql下
参考：https://blog.csdn.net/weixin_42478562/article/details/125264104
-
操作干货记录
-
on conflict 存在更新，不存在插入（数据库版本大于9.5）
# 唯一字段（不需要更新的字段） 需要更新的字段 "insert init 表名 (字段1,字段2,字段3) values (值1,值2,值3) on conflict (字段1,字段2) do update set 字段3 = excluded.字段3;"
# 如果只保证插入不重复，不做更新，添加这个就行(do nothing) "insert init 表名 () values () on conflict () do nothing"
-
插入shp文件，报错 ERROR: type "geometry" does not exist
# 打开你的PG数据库 控制台，执行支持 geometry的建表语句，会创建一张投影信息（EPSG）表。 # 是因为没有创建 shp格式，栅格格式，的扩展 create extension postgis; create extension postgis_raster;
-
插入shp数据
engine = create_engine( f"postgresql+psycopg2://{lll['user']}:{lll['password']}@{lll['host']}:{lll['port']}/{lll['database']}") print(engine) map_data = gpd.read_file(r'output.shp') print(map_data) print(map_data.geom_type) spatial_ref = int(map_data.crs.srs.split(':')[-1]) # 读取shp的空间参考 print(spatial_ref, type(spatial_ref)) map_data['geometry'] = map_data['geometry'].apply(lambda x: WKTElement(x.wkt, spatial_ref)) # geopandas 的to_sql()方法继承自pandas, 将GeoDataFrame中的数据写入数据库 map_data.to_sql( name='tbl_name1', con=engine, index=False, if_exists='replace', # 如果表存在，则替换原有表 chunksize=1000, # 设置一次入库大小，防止数据量太大卡顿 # 指定geometry的类型,这里直接指定geometry_type='GEOMETRY'，防止MultiPolygon无法写入 dtype={'geometry': Geometry(geometry_type='GEOMETRY', srid=spatial_ref)}, method='multi' )
geometry_type 类型的值，请参考： https://blog.csdn.net/jin80506/article/details/91386307
参考网址：https://zhuanlan.zhihu.com/p/160166790
https://blog.csdn.net/asd358924810/article/details/106142867?spm=1001.2101.3001.6650.14&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-14-106142867-blog-112927127.t5_eslanding_download_v1&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-14-106142867-blog-112927127.t5_eslanding_download_v1&utm_relevant_index=16 -
查询shp数据
cursor.execute('select ST_AsGeoJSON(geometry) from tbl_name;') print(cursor.fetchall())
就是下面的操作函数
-
查询几何类型geometry的简单操作
# 线段基本操作 ST_AsTest (返回一个WKT test) ST_Length (计算线段的长度) ST_StartPoint（返回线段开始的第一个坐标） ST_EndPoint (返回线段最后一个坐标) ST_NPoints (返回线坐标个数，只对线有用)
# 集合
ST_NumGeometries (返回集合个数)
ST_GeometryN(geom, 2) (返回第二个集合对象)
ST_Area (集合面积，是分别计算并相加)
ST_Length （返回集合中所有线段组成部分的总长度）
# 多边形 ST_Area （求多边形面积） ST_NRings (返回多边形中环的数量) ST_Perimeter (返回闭环的长度包括内环) ST_ExteriorRing (以线串的形式返回多边形最外面的环) ST_InteriorRingN （以线串的形式返回指定的内部环）
# 其他 ST_GeometryType (返回一个几何类型) ST_SRID (返回一个几何类型的SRID值) UpdateGeometrySRID (更新表的几何类型的SRID值) ST_NDims （返回一个几何类型的维数） ST_AsGeoJSON (以 json 格式返回)例子：
select ST_AsGeoJSON(geometry) from tbl_name;参考用法：https://wenku.baidu.com/view/cd5b621732126edb6f1aff00bed5b9f3f90f7229.htm
-
批量导入数据库 tif 文件，shp 文件
import subprocessfor wroot, wdirs, wfiles in os.walk(os.getcwd()): for file in wfiles : if file.endswith('.tif') or file.endswith('.shp'): fname,lname= file.split('.') if lname =='tif' : s += 1 postgresql='raster2pgsql -s 4326 -C {} tiffdb.{} | psql -h localhost -p' \ ' 5432 -U postgres -d alldata -W '.format(os.path.join(wroot, file), fname) subprocess.run(postgresql, shell=True) if lname == 'shp': t += 1 postgresql = 'shp2pgsql -s 4326 -I {} shpdb.{} | ' \ 'psql -h localhost -p 5432 -d alldata -U postgres -W '. \ format(os.path.join(wroot, file), fname) subprocess.run(postgresql, shell=True)
-
查询 tif 数据
# cur.execute("SELECT ST_AsTIFF(rast, 'LZW') AS rasttiff FROM staging.wsiearth WHERE rid=1;") cursor.execute("SELECT ST_AsTIFF(ST_Union(rast), 'LZW') AS rasttiff FROM public.tmean_19 WHERE filename='tmean10_19.tif';") # Fetch data as Python objects rasttiff = cursor.fetchone() # Write data to file if rasttiff is not None: open('D:/mapnik/mapnik-v2.2.0/shp/mapnik/tmean1.tif', 'wb').write(str(rasttiff[0]))
-
创建分区表
# 创建分区 sql (基于总表创建) 总表名 分区字段的值 create table {fq_table_name} partition of {table_name} for values in ('{fq}');
-
没有 raster2pgsql 命令
apt update apt install postgis32_14-client # 根据postgres版本适当调整 14
-
判断表是否存在
query = f"SELECT EXISTS (SELECT relname FROM pg_class WHERE relname = '{fq_table_name}');" cur.execute(query) rows = cur.fetchone()[0]
