PostgreSQL 安装与使用

1. 安装

   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/postgis

   docker 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.5 

     

   run，创建并运行一个容器；
   –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

2.  操作干货记录
   https://zhuanlan.zhihu.com/p/438001983

3. 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"

4. 插入shp文件，报错 ERROR: type "geometry" does not exist

   # 打开你的PG数据库 控制台，执行支持 geometry的建表语句，会创建一张投影信息（EPSG）表。
   # 是因为没有创建 shp格式，栅格格式，的扩展
   
   create extension postgis; 
   
   create extension postgis_raster;

5. 插入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

6. 查询shp数据

   cursor.execute('select ST_AsGeoJSON(geometry) from tbl_name;')
   print(cursor.fetchall())

   就是下面的操作函数

7. 查询几何类型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

8. 批量导入数据库  tif  文件，shp 文件

   import subprocess

   for 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)

   参考 ：https://blog.csdn.net/qq_45658025/article/details/101535934?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_baidulandingword~default-0-101535934-blog-84146364.pc_relevant_default&spm=1001.2101.3001.4242.1&utm_relevant_index=3

9. 查询 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])) 

     参考：https://cloud.tencent.com/developer/article/1386148

10. 创建分区表
    https://cloud.tencent.com/document/product/409/72286
    
    

11. 没有 raster2pgsql 命令
    

    apt update
    apt install postgis32_14-client     # 根据postgres版本适当调整 14