PostgreSQL与PostGIS的基础入门 | ━Start。平常心

PostgreSQL版本：9.6.12

PostGIS版本：2.4.6

一、概述

1.1 PostgreSQL概述

PostgreSQL是一个功能强大的对象关系型数据库管理系统（ORDBMS）。用于安全地存储数据，支持最佳做法，并允许在处理请求时检索它们。

PostgreSQL的特点如下：

PostgreSQL支持SQL的许多功能，例如复杂SQL查询、SQL子选择、外键、触发器、视图、事务、多进程并发控制、流式复制、热备等。
支持更多的数据类型，除了支持SQL基本的数据类型以外，还支持uuid，monetary， enumerated，geometric，binary，network address，bit string，text search，xml，json，array，composite和range数据类型，以及一些内部对象标识和日志位置类型。
可以安装多个扩展以向PostgreSQL添加附加功能，比如支持PostGIS扩展。
继承表，分区表就是依赖于继承实现的。
搜索索引：全文搜索索引足以应对简单场景；丰富的索引类型，支持函数索引，条件索引。
支持OLAP：citus分布式插件，ANSI SQL兼容，窗口函数，CTE，CUBE等高级分析功能，任意语言写UDF。

1.2 PostGIS概述

PostGIS作为新一代空间数据存储标准模型，将空间地理信息数据结构规范为关系型数据库可以承载的sp模式（simple features），这样，使得之前门槛颇高的gis空间数据存储模式变得通俗易懂、简单明了。

最重要的只要接触过SQL语言，就可以利用PostGIS的SQL语法便捷的操纵装载着空间信息的数据框（数据表），这些二维表除了被设定了一个特殊的空间地理信息字段（带有空间投影信息、经纬度信息等）之外，与主流数据管理系统所定义的各种字段并无两样。

PostGIS安装不仅依赖于PostgreSQL，还依赖于很多插件：

GEOS几何对象库
GDAL栅格功能
LibXML2
LIBJSON

PostGIS的特点如下：

PostGIS支持所有的空间数据类型，这些类型包括：点（POINT）、线（LINESTRING）、面（POLYGON）、多点（MULTIPOINT）、多线（MULTILINESTRING）、多面（MULTIPOLYGON）和几何集合（GEOMETRYCOLLECTION）等。PostGIS支持所有的对象表达方法，比如WKT和WKB。
PostGIS支持所有的数据存取和构造方法，如GeomFromText()、AsBinary()，以及GeometryN()等。
PostGIS提供简单的空间分析函数（如Area和Length）同时也提供其他一些具有复杂分析功能的函数，比如Distance。
PostGIS提供了对于元数据的支持，如GEOMETRY_COLUMNS和SPATIAL_REF_SYS。同时，PostGIS也提供了相应的支持函数，如AddGeometryColumn和DropGeometryColumn。
PostGIS提供了一系列的二元谓词（如Contains、Within、Overlaps和Touches）用于检测空间对象之间的空间关系，同时返回布尔值来表征对象之间符合这个关系。
PostGIS提供了空间操作符（如Union和Difference）用于空间数据操作。
数据库坐标变换
球体长度运算
三维的几何类型
空间聚集函数
栅格数据类型

二、安装

如何安装PostgreSQL + PostGIS请点击：这里。

PostgreSQL与PostGIS版本的依赖关系可点击：这里。

三、基本使用

3.1 PostgreSQL

3.1.0 启停PostgreSQL

如果是初次安装Postgresql9.6的话，需要初始化数据库，执行以下命令：

1 2	# 初次启动需要初始化数据库 /usr/pgsql-9.6/bin/postgresql96-setup initdb

数据库初始化完毕之后，在/var/lib/pgsql/9.6/data/目录下会生成很多文件。

接下来罗列一下postgresql的启动、停止、查看状态的命令：

# 启动postgresql
systemctl start postgresql-9.6.service
# 查看postgresql状态
systemctl status postgresql-9.6.service
# 停止postgresql
systemctl stop postgresql-9.6.service

3.1.1 psql登陆

# 切换用户
su postgres
# 执行psql命令
psql

默认连接postgres数据库，会出现“postgres=#”的字符串，执行效果如下图所示：

3.1.2 创建数据库

创建testdb数据库：

1	CREATE DATABASE testdb;

3.1.3 复制数据库

创建demo数据库，内容与testdb数据库一致：

1	CREATE DATABASE demo TEMPLATE=testdb;

3.1.4 删除数据库

删除demo数据库：

1	drop database demo;

3.1.5 查看数据库列表

执行\l来查看数据库列表：

\l

执行效果如下图所示：

3.1.6 连接数据库

连接数据库有两种方式：

psql模式内连接

假如连接testdb数据库，执行以下代码：

1	postgres=# \c testdb

执行效果如下图所示：

psql模式外连接

1 2	postgres=# \q # 退出psql模式 bash-4.2$ psql -d testdb

执行效果如下图所示：

3.1.7 创建表

创建一个表，用来存储城市的最高及最低温度。代码如下所示：

CREATE TABLE location_city (
    name            varchar(80),
    location        point
);

3.1.8 列出数据表

在testdb数据库下，执行\d命令列出数据表：

\d

执行效果如下：

3.1.9 查看表结构

使用\d [tableName]命令来查看表结构，比如查看location_city表：

1	\d location_city;

执行效果如下图所示：

3.1.10 批量插入数据

批量插入数据有两种常用的方式：

多values方式

1	INSERT INTO location_city VALUES ('San Francisco', '(-194.0, 53.0)'), ('New York', '(-184.0, 43.0)'), ('北京', '(-94.0, 133.0)'), ('Los Angeles', '(-297.0, 63.0)'), ('Chicago', '(-94.0, 283.0)');

copy方式

首先，在/tmp创建一个文件location_city.csv，插入如下数据，其中第一行的内容为空：


"Denver","(123,34)"
"Fort Worth","(-23,21)"
"上海","(45,66)"

执行如下代码：

1	COPY location_city FROM '/tmp/location_city.csv' delimiter ',' csv header;

执行结果如下图所示：

copy扩展

1	copy location_city to '/tmp/location_city_out.csv' delimiter ',' csv header encoding 'GBK';

打开该文件后效果：

通过window的excel查看，正常。
用linux的vim命令查看，中文乱码。解决方案：

解决linux下查看文件乱码问题：

1 2	vim ~/.vimrc set encoding=utf-8 fileencodings=ucs-bom,utf-8,cp936

查看文件编码：

1 2	# 在Vim中可以直接查看文件编码 :set fileencoding

3.1.11 查询数据

查询location_city表中name与position字段的所有数据：

1	SELECT * FROM location_city;

3.1.12 更新数据

使用UPDATE … SET …命令来更新location_city表的数据：

1	UPDATE location_city SET location = '(52,53)' WHERE name = 'Fort Worth';

3.1.13 删除数据

删除指定数据，比如删除name为“San Francisco”的数据：

1	DELETE FROM location_city WHERE name = 'San Francisco';

清空cities表数据：

1
2
3

DELETE FROM location_city;
# 或者
TRUNCATE location_city;

3.2 PostGIS

PostgreSQL数据库安装PostGIS扩展，数据库将可以进行空间数据管理、数量测量与几何拓扑分析。

3.2.1 在testdb数据库下安装PostGIS扩展

安装PostGIS扩展：

1	CREATE EXTENSION postgis;

验证PostGIS扩展是否安装成功：

1	SELECT postgis_full_version();

执行效果如下图所示：

还可以安装其它的一些扩展：

-- Enable Topology
CREATE EXTENSION postgis_topology;
-- Enable PostGIS Advanced 3D-- and other geoprocessing algorithms
-- sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- rule based standardizer
CREATE EXTENSION address_standardizer;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

可使用\dx命令查看已安装的扩展。

3.2.2 创建空间数据表

先建立一个常规的表存储

1	CREATE TABLE cities(id smallint,name varchar(50));

添加一个空间列，用于存储城市的位置。

习惯上这个列叫做 “the_geom”。它记录了数据的类型（点、线、面）、有几维（这里是二维）以及空间坐标系统。这里使用 EPSG:4326 坐标系统：
1
SELECT AddGeometryColumn ('cities', 'the_geom', 4326, 'POINT', 2);

3.2.3 插入数据到空间表

批量插入三条数据：

INSERT INTO cities(id, the_geom, name) VALUES (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326),'London, England'), (2,ST_GeomFromText('POINT(-81.233 42.983)',4326),'London, Ontario'), (3,ST_GeomFromText('POINT(27.91162491 -33.01529)',4326),'East London,SA');