# 数据库计算应用-PostgreSQL,PostGIS

作者:胡飞玲 时间: 2020-10-16

# 1. PostegreSQL ,PostGIS 介绍

  1. PostgreSQL 介绍

    1)开源数据库系统,从加州大学伯克利分校写的 POSTGRES 软件包发展而来的。有二十多年的历史。

    2)PostgreSQL 是由一个由众多开发人员和公司组成的全球社区来开发它。

    3)支持所有主流操作系统,包括 Linux、Unix(AIX、BSD、HP-UX、SGI IRIX、Mac OS X、Solaris 和 Tru64)和 Windows

    4)支持 2008 标准的数据类型,包括整型、数值型、布尔型、字节型、字符型、日期型、时间间隔型和时间型,它也支持存储二进制的大对像,包括图片、声音和视频

    5)高级开发语言有原生的编程接口,如 C/C++、Java、.Net、Perl、Python、Ruby、Tcl 和 ODBC 以及其他语言等

    image-20200812112753071

    PostgreSQL 的特点:

    • 被证明的默认情况下的强大的可靠性事务完整性(ACID)
    • 严谨地支持SQL 标准(完整 SQL92)
    • 可插、拔的类型扩展功能扩展
    • 面向社区的发展模式(开源)
    • 不限制列大小(可用元组)以支持大型 GIS 对象
    • 通用索引结构(Generic Index Structure - GIST)允许R-Tree 索引
    • 易于添加自定义功能
  2. PostGIS 介绍

    PostGIS 是对象关系型数据库 PostgreSQL 的一个插件,通过向 PostgreSQL 添加对空间数据类型空间索引空间函数的支持,将 PostgreSQL 数据库管理系统转换为空间数据库

    PostGIS 遵循 OpenGIS 的规范,是最著名的开源 GIS 数据库。

    需要 PostgreSQL9.1 以上版本才支持 PostGIS.

    PostGIS 能做的事情:

    1.空间数据存储 2.空间数据输出 3.空间数据访问 4.空间数据编辑 5.空间数据处理 6.空间数据关系判断和测量 7.空间拓扑实现

    image-20200812132421130

# 2. PostgreSQL,PostGIS 版本

PostgreSQL 版本:

image-20200812113641096

PostgreGIS 版本:

image-20200812144417776

PostgreSQL 与 PostgreGIS 版本对应关系:

image-20200812145748515

Arcgis 与 PostgreSQL 支持版本

ArcGIS PostgreSQL(PostGIS) 64 位
ArcGIS 10.8.x 11.5(postgis 2.5.1) \ 10.10(postgis2.4) \ 9.6.15( postgis2.3)
ArcGIS 10.7.x 11.2*( postgis2.5.1*) \ 10.7(postgis2.4) \ 9.6.12(postgis 2.3) \ 9.5.12( postgis2.2*)
ArcGIS 10.6.x 10.3( postgis2.4) \ 9.6.8(postgis2.3) \ 9.5.12( postgis2.2) \ 9.4.8( postgis2.2)
ArcGIS 10.5.x 9.5.3( postgis2.2) \ 9.4.8(postgis2.2) \ 9.3.13( postgis2.2)
ArcGIS 10.4.x 9.4.5(postgis 2.2) \ 9.3.10(postgis2.2) \ 9.2.14( postgis2.1)
ArcGIS 10.3.x 9.3.5(postgis 2.1) \ 9.2.9(postgis2.0) \ 9.1.14( postgis2.0)
ArcGIS 10.2.x 9.2.2( postgis2.0) \ 9.1.3(postgis2.0) \ 9.0.5(postgis1.5.1)
ArcGIS 10.1.x 9.1.3( postgis2.0*) \ 9.0.5(postgis1.5.1)

SuperMap 与 PostgreSQL 支持版本

SuperMap SDX+ for PostgreSQL 对 PostgreSQL 8.2 版本不支持,建议安装 8.3 及以上版本。

# 3. PostegreSQL ,PostGIS 安装

PostgreSQL 下载:https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

PostGIS 下载: http://download.osgeo.org/postgis/windows/

PostgreSQL 安装:

  1. 安装很简单,直接下一步下一步就可以。不同版本中不太一样,如 10 版本可以选择安装

  2. 其中有一步要设置系统管理员(postgres)登录密码。数据库端口

    image-20200812164242605 image-20200813093431136
  3. 语言

    image-20200813093524386
  4. 如果是更高版本会带有 pgadmin 的安装选项,9.6 没有。

  5. 安装完成后选中复选框再按完成,则会立即运行 Application Stack Builder,可用于下载和安装附加的工具、驱动程序和应用程序如 PostGIS。

    image-20200812164437364

PostGIS 安装:

  1. 如上步中 Stack Builder 运行后,选择安装目标软件为 PostgreSQL 9.6 on port 5432。然后在安装程序选择对话框中选择 PostGIS 2.3。

    image-20200812165207270 image-20200812165228334
  2. 勾选以后 Stack Builder 会下载 PostGIS 2.3 的安装程序。下载后就会安装,在设置安装组件时,最好选择"Create spatial database",以便在创建数据库时可以以此作为模板。对于其他步骤的设置都选择默认值即可

如果上步安装不成功,则需要单独下载 postgis 安装包

  1. 如果要创建空间库,要以管理员运行安装包。会默认选中安装 postgresql 安装目录下

    image-20200813103952139
  2. 输入 postgresql 系统管理员密码

    image-20200813104045852
  3. 如果勾选了自动创建空间库,后续填写空间名称

# 4. PostgreSQL 客户端工具

用户普遍会使用 pgAdmin 来启动 PostgreSQL。它具有强大的社区支持和丰富的资源。

追求更好的 UI、以及可视化的编辑器,那么 DBeaver 和 OmniDB 作为开源的解决方案,会是您的理想选择。

  1. pgAdmin(https://www.pgadmin.org/) 免费且开源

    pgAdmin 是首款 PostgreSQL GUI 工具。,目前最新版本 4.24。有 Windows、Linux 和 Mac OS 版本

    优点:

    • 可以对所有常见的 PostgreSQL 对象进行创建、查看和编辑。
    • 提供具有语法高亮显示功能的图形化查询与计划任务。
    • 其仪表板可以让您监视到包括:数据库锁、已连接的会话、就绪的事务在内的各项服务器活动。
    • 由于pgAdmin 是一个 Web 应用,因此您可以将其部署到任何服务器上,并远程访问它
    • pgAdmin UI 提供各种可拆分的面板,您可以根据自己的喜好进行重排。
    • 其程序语言调试器可以帮助您调试代码。
    • pgAdmin 的可移植版本,可以让用户轻松地在机器之间移动数据。

    缺点:

    • 与其他付费版 GUI 工具相比,其 UI 较为缓慢、且不直观。

    • pgAdmin 比较消耗系统资源。

  1. DBeaver(https://dbeaver.io/)

    跨平台 PostgreSQL GUI 工具,有免费和企业两个版本。免费版比 pgadmin 好用

    支持所有流行的数据库,包括:MySQL、MariaDB、Sybase、SQLite、Oracle、SQL Server、DB2、MS Access、Firebird、Teradata、以及 Derby。

    优点:

    • 可视化的查询构建器可以帮助您,在无需掌握过多 SQL 相关知识的情况下,构建出复杂的 SQL 查询。
    • 可以使用多种数据视图,来支持不同的用户需求。
    • 提供方便的数据导航。
    • 在 DBeaver 中,您可以生成近似于真实的伪数据,以用于测试系统。
    • 提供针对所有选定表和视图的全文数据搜索,能将搜索结果显示为已过滤的表和视图。
    • 能够在各个数据库系统表中,进行行间元数据的搜索。
    • 可导入、导出为 CSV、HTML、XML、JSON、XLS、XLSX 等多种文件格式的数据。
    • 通过将密码存储在受主密码保护的存储区域中,以提高数据库的安全性。
    • 能够自动为数据库及其机构模式生成 ER 图。
    • 其企业版提供了特殊的在线支持系统。

    缺点:

    • 与一些昂贵的 GUI 工具(例如 Navicat 和 DataGrip)相比,它在处理大型数据集时,可能会比较慢。
  2. OmniDB(https://omnidb.org/en/downloads-en)

    与 pgAdmin 和 DBeaver 相比,OmniDB 缺乏社区的支持

    OmniDB 的功能不及 Navicat 和 DataGrip 的付费版那样丰富。

  3. Navicat(https://www.navicat.com/en/products/navicat-for-postgresql) 收费

  4. DataGrip(http://jetbrains.com/datagrip/) 收费

    DataGrip 支持包括:SQL Server、MySQL、Oracle、SQLite、Azure 数据库、DB2、H2、MariaDB、Cassandra、HyperSQL、以及 Apache Derby 在内的各种大量数据库管理系统。DataGrip 也支持 Windows、Linux 和 Mac OS,三大主要操作系统。

# 5. PostgreSQL 基础概念

# 5.1 逻辑结构

image-20200813155501247

最上层是实例,实例中允许创建多个数据库,每个数据库中可以创建多个 schema,每个 schema 下面可以创建多个对象。对象包括表、物化视图、操作符、索引、视图、序列、函数、... 等等。

表空间,用户,角色不在这个结构里面;是独立之外的 object

# 5.1.1 表空间

在 PostgreSQL 中,表空间是一个目录,里面存储的是它所包含的数据库的各种物理文件。

表空间的作用:

  • 控制控制磁盘的布局

    比如将表空间创建在服务器的不同分区,如果初始化集群所在分区已经用光,可以方便的其他分区上创建表空间已达到扩容的目的。

  • 优化性能

    比如不太常用的表可以建立在便宜的硬盘上,如用来存储用于进行归档文件的表

    对于频繁访问的数据可以存储在性能较高、较快的磁盘分区

官方建议:一个表空间中不要存储多个数据库

# 5.1.2 用户/角色

PostgreSQL 里没有区分用户和角色,在 PostgreSQL 两者区别仅在于用户比角色多了登陆权限。如果给角色赋予了登陆的权限,两者就一样了。

用以下 sql 语句创建的,是没有权限

CREATE ROLE roletest PASSWORD 'pass';

CREATE User usertest PASSWORD 'pass';

等同于用户的角色创建:CREATE ROLE kanon PASSWORD 'kanon' LOGIN;

# 5.1.3 数据库

在 PostgreSQL 中,数据库的创建是通过克隆数据库模板来实现的,这与 SQL SERVER 是同样的机制。

新建的数据库对所有的用户都有连接权限,还可以在对应的 public-schema 下可以创建 object;不管是不是超级用户、属主用户;这样很不安全,要实现隔离,我们可以回收数据库权限;只有超级用户、属主用户可以连。

revoke CONNECT ON DATABASE db1 from public;
1

# 5.1.4 模式

模式(schema)是对数据库(database)逻辑分割,类似数据集概念

**模式作用:**将不同的模式指派该不同的角色,可以实现权限分离,又可以通过授权,实现模式间对象的共享,而 public 模式可以存储大家都需要访问的对象。

模式特点:

  • 每创建一个数据库,会默认创建一个模式--public
  • 数据库中一个对象的完整名称为 SCHEMA.object

  • 通过 DATABASE 的 search_path 来确定 SCHEMA 的搜索顺序

    查看当前数据库搜索路径(sql 语句):

    SHOW search_path;
    
    1

    设置新的搜索路径(sql 语句):

    SET search_path TO "$user", public;
    
    1
  • 默认每个人都在 public 模式上有 CREATE 权限,所以连接到数据库上的用户都可以在 public 创建对象。 这个权限可以撤销:REVOKE CREATE ON public FROM PUBLIC;

    第一个 public 是 SCHEMA,第二个 PUBLIC 是指所有用户。

官方建议在管理员创建一个具体数据库后,应该为所有可以连接到该数据库的用户分别创建一个与用户名相同的模式,然后,将 search_path 设置为"$user", 这样,任何当某个用户连接上来后,会默认将查找或者定义的对象都定位到与之同名的模式中。这是一个好的设计架构。

# 5.1.5 数据库对象

包括表、物化视图、操作符、索引、视图、序列、函数

# 5.3 权限体系

在数据库中所有的权限都和角色(用户)挂钩,public 是一个特殊角色,代表所有人。 超级用户是有允许任意操作对象的,普通用户只能操作自己创建的对象。 另外有一些对象是有赋予给 public 角色默认权限的,所以建好之后,所以人都有这些默认权限。

image-20200813171501018
  1. 实例级别的权限由 pg_hba.conf 来控制

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            trust
    host all postgres 0.0.0.0/0 reject
    host all all 0.0.0.0/0 md5
    
    1
    2
    3
    4
    5
    6
    7

    允许任何本地用户无密码连接任何数据库 不允许 postgres 用户从任何外部地址连接任何数据库 允许其他任何用户从外部地址通过密码连接任何数据库

  2. 数据库权限

    数据库级别的权限,包括允许连接数据库,允许在数据库中创建 schema。 默认情况下,数据库在创建后,允许 public 角色连接,即允许任何人连接。 默认情况下,数据库在创建后,不允许除了超级用户和 owner 之外的任何人在数据库中创建 schema。 默认情况下,数据库在创建后,会自动创建名为 public 的 schema,这个 schema 的 all 权限已经赋予给 public 角色,即允许任何人在里面创建对象。

  3. 模式权限

    schema 级别的权限,包括允许查看 schema 中的对象,允许在 schema 中创建对象。 默认情况下新建的 schema 的权限不会赋予给 public 角色,因此除了超级用户和 owner,任何人都没有权限查看 schema 中的对象或者在 schema 中新建对象。

    注意:不要把自己的对象创建到别人的 schema 下面

  4. 数据库对象权限

    对象级别的权限,每种类型的对象权限属性都不一样

    以表为例,有 SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER 这些权限。

    其他具体参考:https://www.postgresql.org/docs/9.5/sql-grant.html

  5. 表空间权限

  6. 如何查看对象的权限情况

    • 查询用户 user 的权限 select * from INFORMATION_SCHEMA.role_table_grants where grantee='user';

# 5.2 数值类型

PostgreSQL 提 供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。

  • 字符类型

    image-20200814170050098
  • 数值类型

    image-20200814170124612
  • 日期类型

    image-20200814170222040
  • 几何类型

    image-20200814170639568
  • 其他,参考:https://www.runoob.com/postgresql/postgresql-data-type.html

# 5.4 其他配置

  1. 最大连接数

    PostgreSQL\9.2\data\postgresql.conf,改完重启,这个最大连接数是针对 PG 整个服务

    image-20200814112348918
  2. 允许数据库远程访问

    1)允许数据库服务器监听来自任何主机的连接请求:postgresql.conf

    允许数据库服务器监听来自任何主机的连接请求

    修改``#listen_addresses = 'localhost' 为 listen_addresses='*'
    
    1

    2)信任远程连接:pg_hba.conf

    数值取值范围为:0,8,16,24,32  即表示掩码 bit 的个数,  例如:         32 -> 192.168.1.1/32 表示必须是来自这个 IP 地址的访问才合法;       24 -> 192.168.1.0/24 表示只要来自 192.168.1.0 ~ 192.168.1.255 的都合法;       16 -> 192.168.0.0/16 表示只要来自 192.168.0.0 ~ 192.168.255.255 的都合法;       8 -> 192.0.0.0/16 表示只要来自 192.0.0.0 ~ 192.255.255.255 的都合法;       0 -> 0.0.0.0/0 表示全部 IP 地址都合法,/左边的 IP 地址随便了只要是合法的 IP 地址即可;

    修改如下内容,信任指定服务器连接
    # IPv4 local connections:
    host  all      all   127.0.0.1/32   ident
    host  all      all   192.168.137.1/32(需要连接的服务器IP) trust
    
    1
    2
    3
    4

# 6. PostgreSQL,PostGIS 实践

# 6.1 业务库

创建表空间,创建用户(可多个),创建数据库, 创建模式(于用户同名)

如果需要多个模式数据库隔离:一个数据库所有者,一个模式一个用户(建同名)

  1. 配置允许远程连接

    配置 pg_hba.conf,修改 PostgreSQ 的 pg_hba.conf 文件,添加“host all all 0.0.0.0/0 md5”

    配置 postgresql.conf,修改 listen_addresses='*'

    PostgreSQL 默认是不允许远程连接的,需要修改配置文件

  2. 创建表空间

    定义表空间名称,路径(绝对路径),目录必须先存在,在创建表空间前先建一个同名目录

    image-20200814101202272 image-20200814101847426

    测试了下,每次初始创建,都在下面生成了这样一个目录。

  3. 创建用户

    一个数据库一个模式分配一个用户,实现数据隔离。如果没有多个,就可以默认用 public 模式

    image-20200814105707480

    创建好后:

    image-20200813135645639

    **删除:**如果有对象授权了该用户,该用户无法删除

  4. 创建数据库

    PostgreSQL 创建数据库可以用以下三种方式:

    • 1、使用 CREATE DATABASE dbname; SQL 语句来创建。

      ​ 用 CREATE DATABASE test1;创建好的数据库,owner 为 posgres ,其他属性如下图

    image-20200814134406481
  • 2、使用 createdb 命令来创建。

    createdb -h localhost -p 5432 -U postgres test1
    
    1

    以上命令我们使用了超级用户 postgres 登录到主机地址为 localhost,端口号为 5432 的 PostgreSQL 数据库中并创建 test1 数据库

  • 3、使用 pgAdmin 工具

    image-20200814140201539

控制权限:

​ 通过上述方式创建的数据库对所有用户都连接权限,所以要去掉,只让超级管理员和数据库所有者能操作

revoke CONNECT ON DATABASE test from public;

​ 给一个数据库添加多个用户操作权限,如后续建多个模式情况:

image-20200814143951742

但是上述授权,只让授权用户对 public 模式有操作权限,其他模式则默认没有权限的

**删除:**如果因为占用无法删除,要断开其他所有连接和打开页面

  1. 创建模式

    • sql 语句创建:

    • pgadmin 创建:

    image-20200814145233033

    创建的模式,如果没有给其他用户授权,则无法创建和使用数据库对象

  1. 模式下创建数据库对象
  • 表:如果有必要,表可以给其他用户单独授权
  • 序列:自增序列

    pg 自增序列和 oralcle 类似,先创建序列,可以 pgadmin 图形创建,也可以 sql 语句创建

    image-20200814165249942

    读取序列:

    SELECT nextval('gys.mytable_myid_seq');

    为字段设置自增序列:

    alter table gys.mytable alter column myid set default nextval('gys.mytable_myid_seq');

  • 索引

# 6.2 空间库

# 6.2.1 PostGIS 空间库

  1. 建一个普通的数据库 postgistest

  2. 加载 PostGIS 扩展

    在数据库 postgistest 上加载PostGIS 空间扩展,在当前数据库的查询工具中执行

    CREATE EXTENSION postgis;
    
    1

    通过运行 PostGIS 函数来确认是否安装了 PostGIS:

    SELECT postgis_full_version();
    
    1
    image-20200814173634325

    安装了 postgis 后,在 public 模式下默认增加了一张系统表

    系统表:spatial_ref_sys 表:OGC 规范的空间参考

    字段 说明
    srid 空间参考的 Well-Known ID
    srtext 存放的是以字符串描述的空间参考
    proj4text 存放的则是以字符串描述的 PROJ.4 投影定义(PostGIS 使用 PROJ.4 实现投影)
  1. 在 PostGIS 中创建一个空间表

    包含两步,第一步创建一个一般表,第二步给这个表添加几何字段

    如再给 public 模式下 cities 表添加一个名为 shape 的几何字段(二维点):

   select AddGeometryColumn('public', 'cities', 'shape', 4326, 'POINT', 2)
1

系统表:geometry_columns 表:当前数据库中所有几何字段的信息

字段 说明
f_table_schema 空间表所在的模式
f_table_name 空间表的表名
f_geometry_column 空间表中几何字段的名称
srid 空间表的空间参考

image-20200817094927378

# 6.2.2 ArcGIS 空间库 (PG)

  1. 部署环境

    1) 拷贝 dll 到 PostgreQL

在 ArcMap 的安装目录下找到 \DatabaseSupport\PostgreSQL\9.5,windows 及 linux 用户分别将对应文件夹内的文件拷贝到 Postgresql 安装目录下的\lib 文件夹内。

2) 拷贝 PostgreSQL 客户端文件到 arcgis(有的版本不用拷贝,因为 desktop 下自带)

为了让 ArcMap 连接数据库的需要把连接 postgres 的 32 位的客户端的 dl 五个 dll(libeay32.dll、libiconv-2.dll、libintl-8.dll、libpq.dll 和、ssleay32.dll)放到 Arcmap 的 bin 目录下。

image-20200819110151735
  1. 修改配置文件,允许远程连接(arcgis 和数据库不在同一台机器上)

    配置 pg_hba.conf,修改 PostgreSQ 的 pg_hba.conf 文件,添加“host all all 0.0.0.0/0 md5”

    配置 postgresql.conf,修改 listen_addresses='*'

    PostgreSQL 默认是不允许远程连接的,需要修改配置文件

  2. 创建企业级空间数据库

    toolboxes->System Toolboxes->Data Management tool=>Geodatabase Administration=>create Enterprise Geodatabase

    image-20200818100105456

    创建企业级数据库以后,在原先数据库下,会创建 sde 模式,并且创建 sde 用户,授权该数据库下模式

    连接,用户要以 sde 连接,否则创建要素类提示 Database user name and current user schema do not match

    image-20200818171534352
  1. 新增一个空间数据库

    如何在一个已有的企业数据库服务器上新增一个空间库

    • 创建同名表空间,创建普通数据库,数据库的所有者设成 SDE,表空间设置

    • Arcgis Catalog 中以 sde 用户连接到新建的数据库,右键 Enable Geodatabase,授权文件选择 ecp 格式

      image-20200820113042941 image-20200820113145929

# 6.2.3 超图空间库 (PG)

  • pgadmin 创建数据库

  • 超图 Desktop,新建数据库数据源,选择 PostgreSQL 类型数据库

  • 在连接好的数据源下,可以新建图层

    创建完空间库以后,会新建一个 sdx 模式

    image-20200817180741702

# 6.2.4 超图空间库(PostGIS)

  • pgadmin 创建数据库
  • 加载 PostGIS 扩展
  • 超图 Desktop,新建数据库数据源,选择 PostGIS 类型数据库
  • 在连接好的数据源下,可以新建图层

# 6.2.5 超图空间库 PostgreSQL 和 PostGIS 的差异

1.据量小的情况下,两者的读写上差别不大,数据量越大,postgresql 要略优于 postgis。

2.查询 postgis 会优于 postgresql。

3.查询和空间分析,postgresql 优于 postgis

# 7. PostgreSQL 数据库表存储解析

# 7.1 PostgreSQL 系统表

系统表:数据库——目录——PostgreSQL(pg_catalog)下

image-20200814093305552

参考:https://www.yiibai.com/manual/postgresql/catalogs.html

数据库中的所有表:pg_tables

查询所有角色列表:视图 pg_roles 可以查看所有的角色列表

Information_schema:(隐藏的)

自动的存在于每个 database 中,里面包含了数据库中所有对象的定义信息。

查找表信息:SELECT * FROM information_schema.tables WHERE table_name='geometry_columns';

某个表的所有字段:select * from information_schema.columns where table_schema='public' and table_name ='your_table_name';

# 7.2 PostGIS 系统表

image-20200820140151727

增加了很多空间数据管理函数

# 7.3 ArcGIS 库表内部存储

  • ArcGIS 下查看内容:

    图层命名格式:数据库.sde.图层名称

    image-20200820104559504
  • PgAdmin 里看到数据库存储

public 模式 SDE 模式
image-20200820114234037 image-20200820114401363

1)如果是单纯的空间库:系统表和图层表都存储在 sde 模式下,public 模式下存了坐标系表

2)sde 模式下,以 gdb 和 sde 开头存储的系统表

3)如果是业务库和空间库一起,那其他普通表存储在 public 模式下

# 7.4 超图 PostgreSQL 内部存储

  • 超图里看到内容:

    image-20200820135421765
  • pgadmin 里看到内容:

image-20200820135017049

​ 1)新建空间库以后,空间库增加 sde 模式,下面存了一系列系统表

​ 2) 图层没有用单个表存储,而是分开存的,图层列表存在 sdx.smregister 里

​ 3) 图层属性数据存储在 smtablename 这列对应的表里,比如图中 smdtv_1

# 7.5 超图 PostGIS 内部存储

  • 超图桌面端看到内容:

    image-20200820140039740
  • pgadmin 看到内容:

image-20200820135639457

​ 1)新建空间库以后,空间库没有增加模式,所有内容都存在 public 模式下

​ 2) 一个图层一个表,属性存在图层表里

# 8. 数据库备份还原

# 8.1 业务库备份还原

  1. 方式一:用 pgadmin 里的备份,恢复

    优点:操作方便

    缺点:数据库慢慢庞大的时候,经常出错,备份的文件过程中出错的几率相当大,手动调节空间有限

    image-20200819112855818

    注意:在文件名,选择的时候,导出文件名需要在文本框手动加上文件名,创建按钮才不会置灰

  2. 方式二(推荐):用 postgresql 内置工具, pg_dump.exe,psql.exe

  • 备份: pg_dump.exe

    开始-运行-cmd 弹出 dos 控制台;然后 在控制台里,进入 PostgreSQL 安装目录 bin 下,然后执行以下命令

    pg_dump -h 164.82.233.54 -U postgres databasename > C:\databasename.bak
    
    1
image-20200819135108137
  • 还原:

    psql -h localhost -U postgres -d databasename <  C:\databasename.bak
    如: psql -h localhost -U postgres -d dcp < c:\dcp.bak
    
    1
    2
    image-20200821110014642

# 8.2 空间库备份还原

arcgis:和 oracle 数据库一样操作,拷贝到 gdb,mdb 等文件型数据源里

supermap:超图客户端有备份数据库操作,数据集另存 udbx 等文件

# 9. PostGIS 操作 geometry 方法

WKT 定义几何对象格式:

几何类型 WKT 格式
POINT(0 0)
线 LINESTRING(0 0,1 1,1 2)
POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))
多点 MULTIPOINT(0 0,1 2)
多线 MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))
多面 MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))
几何集合 GEOMETRYCOLLECTION(POINT(2 3),LINESTRING((2 3,3 4)))

样例:

SELECT ST_PointFromText('POINT(116.39088 39.90763)',4326);

SELECT ST_GeomFromText('LINESTRING(0 0,1 1,1 2)', 26910)

常用函数:

功能 函数
wkt 转 geometry st_geomfromtext(wkt,wkid)
geometry 转 wkt st_astext(geom)
获取点对象 x、y 坐标值 st_x(geom)、st_y(geom)
获取线/面对象四至 st_xmin(geom)、st_ymin(geom)、st_xmax(geom)、st_ymax(geom)
计算两点之间距离 st_distance(geom,geom) / st_distance(wkt,wkt)
计算线的长度 st_length(geom) / st_length(wkt)
计算面积 st_area(geom) / st_area(wkt)
缓冲区计算 st_buffer(geom,distance) / st_buffer(wkt,distance)

管理函数:

功能 函数
添加几何字段 AddGeometryColumn(, , , , , )
删除几何字段 DropGeometryColumn(, , )
检查数据库几何字段并在 geometry_columns 中归档 Probe_Geometry_Columns()
给几何对象设置空间参考(在通过一个范围做空间查询时常用) ST_SetSRID(geometry, integer)

几何对象关系函数 :

功能 函数
获取两个几何对象间的距离 ST_Distance(geometry, geometry)
如果两个几何对象间距离在给定值范围内
则返回 TRUE
ST_DWithin(geometry, geometry, float)
判断两个几何对象是否相等 ST_Equals(geometry, geometry)
判断两个几何对象是否分离 ST_Disjoint(geometry, geometry)
判断两个几何对象是否相交 ST_Intersects(geometry, geometry)
判断两个几何对象的边缘是否接触 ST_Touches(geometry, geometry)
判断两个几何对象是否互相穿过 ST_Crosses(geometry, geometry)
判断 A 是否被 B 包含 ST_Within(geometry A, geometry B)
判断两个几何对象是否是重叠 ST_Overlaps(geometry, geometry)
判断 A 是否包含 B ST_Contains(geometry A, geometry B)
判断 A 是否覆盖 B ST_Covers(geometry A, geometry B)
判断 A 是否被 B 所覆盖 ST_CoveredBy(geometry A, geometry B)
通过 DE-9IM 矩阵判断两个几何对象的关系是否成立 ST_Relate(geometry, geometry, intersectionPatternMatrix)
获得两个几何对象的关系(DE-9IM 矩阵) ST_Relate(geometry, geometry)

几何对象处理函数:

功能 函数
获取几何对象的中心 ST_Centroid(geometry)
面积量测 ST_Area(geometry)
长度量测 ST_Length(geometry)
返回曲面上的一个点 ST_PointOnSurface(geometry)
获取边界 ST_Boundary(geometry)
获取缓冲后的几何对象 ST_Buffer(geometry, double, [integer])
获取多几何对象的外接对象 ST_ConvexHull(geometry)
获取两个几何对象相交的部分 ST_Intersection(geometry, geometry)
将经度小于 0 的值加 360 使所有经度值在 0-360 间 ST_Shift_Longitude(geometry)
获取两个几何对象不相交的部分(A、B 可互换) ST_SymDifference(geometry A, geometry B)
从 A 去除和 B 相交的部分后返回 ST_Difference(geometry A, geometry B)
返回两个几何对象的合并结果 ST_Union(geometry, geometry)
返回一系列几何对象的合并结果 ST_Union(geometry set)
用较少的内存和较长的时间完成合并操作 ST_MemUnion(geometry set)

几何对象存取函数:

功能 函数
获取几何对象的空间参考 ID ST_SRID(geometry)
获取几何对象的 WKB 描述 ST_AsBinary(geometry)
获取几何对象的 WKT 描述 ST_AsText(geometry)
获取几何对象的维数 ST_Dimension(geometry)
获取几何对象的边界范围 ST_Envelope(geometry)
判断几何对象是否为空 ST_IsEmpty(geometry)
判断几何对象是否不包含特殊点(比如自相交) ST_IsSimple(geometry)
判断几何对象是否闭合 ST_IsClosed(geometry)
判断曲线是否闭合并且不包含特殊点 ST_IsRing(geometry)
获取多几何对象中的对象个数 ST_NumGeometries(geometry)
获取多几何对象中第 N 个对象 ST_GeometryN(geometry,int)
获取几何对象中的点个数 ST_NumPoints(geometry)
获取几何对象的第 N 个点 ST_PointN(geometry,integer)
获取多边形的外边缘 ST_ExteriorRing(geometry)
获取多边形内边界个数 ST_NumInteriorRings(geometry)
获取多边形的第 N 个内边界 ST_InteriorRingN(geometry,integer)
获取线的终点 ST_EndPoint(geometry)
获取线的起始点 ST_StartPoint(geometry)
获取几何对象的类型 GeometryType(geometry)
即 POINTM 对象会被判断为 point ST_GeometryType(geometry)
获取点的 X 坐标 ST_X(geometry)
获取点的 Y 坐标 ST_Y(geometry)
获取点的 Z 坐标 ST_Z(geometry)

几何对象构造函数 : 参考语义: Text:WKT WKB:WKB Geom:Geometry M:Multi Bd:BuildArea Coll:Collection ST_GeomFromText(text,[])

ST_LineFromText(text,[]) ST_LinestringFromText(text,[]) ST_PolyFromText(text,[]) ST_PolygonFromText(text,[]) ST_MPointFromText(text,[]) ST_MLineFromText(text,[]) ST_MPolyFromText(text,[]) ST_GeomCollFromText(text,[]) ST_GeomFromWKB(bytea,[]) ST_GeometryFromWKB(bytea,[]) ST_PointFromWKB(bytea,[]) ST_LineFromWKB(bytea,[]) ST_LinestringFromWKB(bytea,[]) ST_PolyFromWKB(bytea,[]) ST_PolygonFromWKB(bytea,[]) ST_MPointFromWKB(bytea,[]) ST_MLineFromWKB(bytea,[]) ST_MPolyFromWKB(bytea,[]) ST_GeomCollFromWKB(bytea,[]) ST_BdPolyFromText(text WKT, integer SRID) ST_BdMPolyFromText(text WKT, integer SRID)

# 10. 资源网站

  1. PostgreSQL 中文社区:http://www.postgres.cn/index.php/v2/home
  2. PostgreSQL 手册: http://www.postgres.cn/docs/12/index.html
  3. PostgreSQL 官网: https://www.postgresql.org/
  4. PostGIS 官网:http://www.postgis.net/
  5. PostGIS 下载: http://download.osgeo.org/postgis/windows/
  6. PostgreSQL 下载:https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
  7. PgAdmin 官网:https://www.pgadmin.org/
  8. PgAdmin 下载:https://www.pgadmin.org/download/pgadmin-4-windows/
  9. PostGIS 技术文章:https://blog.csdn.net/qq_35732147/article/details/85256640