主讲人：胡飞玲
会议时间：2020年8月21日 15:00 - 17:30
参会人员：胡飞玲，陶小明，于福豪，李小龙，刘小标，田银伟
会议记录：田银伟
会议内容概要
前言
此分享内容，建议在听取分享内容之后，进行实践一下，在项目上要知道业务库和空间库是如何设置的。
1. PostgreSQL，PostGIS介绍
1. PostgreSQL 简介
PostgreSQL 是开源数据库系统。
PostgreSQL 的单个数据库大小、单条记录最大值（1.6 TB）、单字段最大允许 （1 GB）等极限值。
PostgreSQL 特点：支持 SQL 标准、可插、拔的类型扩展和功能扩展等，具有很好的扩展性。
2. PostGIS
PostGIS 与 PostgreSQL 的关系：PostGIS 是 PostgreSQL 的一个插件。通过向 PostgreSQL 添加对空间数据类型、空间索引、空间函数的支持，将 PostgreSQL 数据库管理系统转换为 空间数据库。
PostGIS 能做的事情：空间数据的存取、编辑、空间关系判断、拓扑分析等。（需要 PostgreSQL 9.1 以上版本才支持 PostGIS）。
PostGIS 其实就是在 PostgreSQL 基础上添加了相关的函数。
3. 空间数据存储的体系架构的发展
介绍了三代 GIS 空间数据库的的发展过程。
4. PostGIS 与 PostgreSQL 版本的对应关系
一般 PostgreSQL 9.5 及以上版本基本对于 PostGIS 的功能都是支持的。
5. ArcGIS 与 PostgreSQL 版本的对应关系
ArcGIS 10.1.x 支持 9.1.3(PostGIS 2.0*)\9.0.5(PostGIS 1.5.1) 等对应关系。
6. SuperMap 与 PostgreSQL 版本的对应关系
SuperMap SDX+ for PostgreSQL 对 PostgreSQL 8.2 版本不支持，建议安装 8.3及以上版本。
7. PostGIS 与 PostgreSQL 的安装
介绍了 PostGIS 与 PostgreSQL 的安装过程及注意事项等。
PostGIS 安装的前提是要先安装 PostgreSQL ，即需要 PostgreSQL 的安装环境。
8. PostgreSQL 客户端工具对比
pgAdmin ，用户普遍会使用 pgAdmin 来启动 PostgreSQL 。免费且开源，具有强大的社区支持和丰富的资源。
DBeaver，跨平台 PostgreSQL GUI 工具，免费版比 pgAdmin 好用。
OmniDB，缺乏社区支持，功能不及 Navicat 和 DataGrip 的付费版丰富。
Navicat，收费。
DataGrip，收费。
9. PostgreSQL 基础概念详解
9.1.逻辑结构
PostgreSQL 实例 (http://….: port)→数据库 (db)→schema→对象 (table等)
最上层是实例，实例中允许创建多个数据库，每个数据库中可以创建多个 schema，每个 schema 下面可以创建多个对象。对象包括表、物化视图、操作符、索引、视图、序列、函数等等。
SCHEMA详解：
PostgreSQL 模式（SCHEMA）可以看做是一个表的集合。
一个模式可以包含视图、索引、据类型、函数和操作符等。
相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。
使用模式的优势：
- 允许多个用户使用一个数据库并且不会互相干扰。
- 将数据库对象组织成逻辑组以便更容易管理。
- 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。
模式类似于操作系统层的目录，但是模式不能嵌套。
9.1.1 表空间
在 PostgreSQL 中，表空间是一个目录，里面存储的是它所包含的数据库的各种物理文件。
官方建议：一个表空间中不要存储多个数据库。
补充：
- 表空间概念：
PostgreSQL中的表空间允许在文件系统中定义用来存放表示数据库对象的文件的位置。在PostgreSQL中表空间实际上就是给表指定一个存储目录。
表空间作用：
能合理利用磁盘性能和空间,制定最优的物理存储方式来管理数据库表和索引。
表空间跟数据库的关系：
- 在Oracle数据库中；一个表空间只属于一个数据库使用；而一个数据库可以拥有多个表空间。属于”一对多”的关系
- 在PostgreSQL集群中；一个表空间可以让多个数据库使用；而一个数据库可以使用多个表空间。属于”多对多”的关系。
系统自带表空间：
- 表空间 pg_default 是用来存储系统目录对象、用户表、用户表 index、和临时表、临时表 index、内部临时表的默认空间。对应存储目录 $PADATA/base/
- 表空间 pg_global 用来存放系统字典表；对应存储目录 $PADATA/global/
9.1.2 用户/角色
PostgreSQL 里没有区分用户和角色，在 PostgreSQL 两者区别仅在于用户比角色多了登陆权限。如果给角色赋予了登陆的权限，两者就一样了。
9.1.3 数据库
在 PostgreSQL 中，数据库的创建是通过克隆数据库模板实现的。
新建的数据库对所有的用户都有连接权限。要实现隔离，可以回收数据库权限：如只有超级用户、属主用户可以连。
因此新建的数据库建议执行数据库的权限设置。
9.1.4 数据库对象
模式 (schema) 是对数据库 (database) 逻辑分割。
模式作用和模式特点的介绍。
官方建议：在管理员创建一个具体数据库后，应该为所有可以连接到该数据库的用户分别创建一个与用户名相同的模式，然后将 search_path 设置为「$user」，这样，任何当某个用户连接上来后，会默认将查找或定义的对象都定位到与之同名的模式中。这是一个号的设计架构。
包括表、物化视图、操作符、索引、视图、序列、函数等等。
一个用户对应一个数据库，就不用考虑模式的问题。
但多对多时，用户、数据库、模式需要对应。
9.2. 权限体系
实例权限（ip 访问权限）、数据库权限 （连接、创建模式的权限）、schema权限（创建、删除、更新表等操作权限）、object权限（操作权限）、表空间权限 （创建等权限）。
9.3. 数值类型
PostgreSQL 的字符、数值、日期、几何类型等等的规范及要求等。
9.5 其他配置
允许远程数据库访问等的配置。
10. PostgreSQL 和 PostGIS 实践
业务库、空间库的创建：如 SuperMap 基于 PostgreSQL 的操作、SuperMap 基于 PostGIS 的操作以及纯粹基于 PostGIS 的操作。
10.1 业务库的创建流程：
10.1.1.配置 PostgreSQL 远程访问连接。
10.1.2 创建
①创建表空间②创建用户（可多个）③创建数据库（如果一个数据库要对应多个用户的话，要创建模式并授权）④创建模式（与用户同名）
如果需要多个模式数据库隔离：一个数据库所有者，一个模式一个用户名（建议同名）。
如果有对象授权了该用户，则该用户无法删除。
创建完数据库之后，需要设置数据库对应的用户的角色的权限，去除非当前数据库对应的其他角色的权限。（因此建议数据库名、表名、和 用户的角色 同名，如果是多个角色，尽量名称相似，方便数据库的权限授予给安全的、可知的用户）。
revoke CONNECT ON DATABASE test from public; //权限设置
如果只有一个用户 可以选择默认的 public 模式，如果多个用户 要创建模式 进行权限授权。
10.2 空间库
10.2.1 空间库创建流程：
建一个普通的数据库 postgistest
加载 PostGIS 扩展：
在数据库 postgistest 上加载 PostGIS 空间扩展，在当前数据库的查询工具中执行：
CREATE EXTENSION postgis; //此函数执行的实际意义是：为当前数据库添加一系列 PostGIS 的函数和存储过程
通过运行 PostGIS 函数来确认是否安装了 PostGIS:
SELECT postgis_full_version();
在 PostGIS 中创建一个空间表：
包含两步：
①创建一个一般表
②给这个表添加几何字段：如再给 public 模式下 cities 表添加一个名为 shape 的集合字段 (二维点)：
select AddGeometryColumn(‘public’,’cities’,’shape’,4326,’POINT’,2); //该操作是向 geometry_columns 系统表中添加当前语句的一条记录。
11. ArcGIS 空间库 （PG）
11.1 部署环境
11.1.1 拷贝 dll 到 PostgreSQL
ArcMap安装目录下\DatabaseSupport\PostgreSQL\9.5文件夹下的文件拷贝到 PostgreSQL 安装目录下的 \lib 文件夹内
11.1.2 拷贝 PostgreSQL 客户端文件到 ArcGIS (有的版本不用拷贝，因为 Desktop 下自带)
11.1.3 修改配置文件，允许远程访问连接
11.1.4 创建企业级空间数据库
创建企业级数据库以后，在原先数据库下，会创建 sde 模式，并且创建 sde 用户，授权该数据库下模式连接，用户要以 sde 连接，否则创建要素类提示。
11.1.5 新增一个空间数据库
如何在一个已有的企业数据库服务器上新增一个空间库：
1. 创建同名表空间，创建普通数据库，数据库的所有者设为SDE，表空间设置。
2. Arcgis Catalog 中以 sde 用户连接到新建的数据库，右键 Enable Geodatabae，授权文件选择 ecp 模式。
11.2 ArcGIS 创建的空间库的存储解析
ArcGIS 库表内部存储：图层命名格式：数据库.sde.图层名称
ArcGIS 创建的在 PostgreSQL 中的数据库存储：如果业务库和空间库一起，那其他普通表存储在 public 模式下；如果是单纯的空间库：系统表和图层表都存储在 sde 模式下，public 模式下存了坐标系表；sde 模式下，以 gdb 和 sde 开头存储的系统表。
12. SuperMap 空间库 （PG/PostGIS）
12.1 PG:
① pgAdmin 创建数据库②超图 Desktop，新建数据库数据源，选择 PostgreSQL 类型数据库③在连接好的数据源下，可以新建图层。
创建完空间库以后，会新建一个 sdx 模式。
12.2 PostGIS:
① pgAdmin 创建数据库②加载 PostGIS 扩展③超图 Desktop，新建数据库数据源，选择 PostgreSQL 类型数据库③在连接好的数据源下，可以新建图层。
创建完空间库以后，会新建一个 sdx 模式。
12.3 SuperMap 创建的空间库的存储解析
12.3.1 PostgreSQL 建库的存储解析：
①新建空间库以后，空间库增加 sdx 模式，下面存了一系列系统表。
②图层没有单个表存储，而是分开存的，图层列表存在 sdx.smregister 里。
③图层属性数据存储在 smtablename 这列对应的表里。
12.3.2 PostGIS 建库的存储解析：
①新建空间库以后，空间库没有增加模式，所有内从都存在 public 模式下。
②一个图层一个表，属性存在图层表里。
13. 数据库备份还原
13.1 业务库备份还原
推荐：用 PostgreSQL 内置工具， pg_dump.exe，psql.exe。
备份：pg_dump.exe。
还原：psql.exe
13.2 空间库备份还原
ArcGIS：拷贝到 gdb，mdb 等文件型数据源里。
SuperMap：超图客户端有备份数据库操作，数据集另存 udbx 文件。
14. PostGIS 操作 Geometry 方法
SELECT ST_PointFromText() 等 PostGIS 函数操作。
15. 资源网站
问题与讨论
1. PostgreSQL 与 PostGIS 的性能对比
超图技术支持回复：
数据量小的情况下，两者的读写上差别不大，数据量越大，PostgreSQL 要略优于 PostGIS 。
查询 PostGIS 会优于 PostgreSQL 。
查询和空间分析，PostgreSQL 优于 PostGIS。