特别说明:该专栏文章均来源自微信公众号《大数据实战演练》,欢迎关注!
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请点击:如何查找并下载rpm依赖包并使用yum离线安装rpm包 。
PostgreSQL与PostGIS版本的依赖关系可点击:http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS 。
三、基本使用
3.1 PostgreSQL
3.1.0 启停PostgreSQL
如果是初次安装Postgresql9.6的话,需要初始化数据库,执行以下命令:
# 初次启动需要初始化数据库/usr/pgsql-9.6/bin/postgresql96-setup initdb
数据库初始化完毕之后,在/var/lib/pgsql/9.6/data/目录下会生成很多文件。
接下来罗列一下postgresql的启动、停止、查看状态的命令:
# 启动postgresqlsystemctl start postgresql-9.6.service# 查看postgresql状态systemctl status postgresql-9.6.service# 停止postgresqlsystemctl stop postgresql-9.6.service
3.1.1 psql登陆
# 切换用户su postgres# 执行psql命令psql
默认连接postgres数据库,会出现“postgres=#”的字符串,执行效果如下图所示:
![225de22b63cf93c0604a031d7e12e2bc.png](https://i-blog.csdnimg.cn/blog_migrate/4550947b516bdcc939ba7167a187a273.jpeg)
3.1.2 创建数据库
创建testdb数据库:
CREATE DATABASE testdb;
3.1.3 复制数据库
创建demo数据库,内容与testdb数据库一致:
CREATE DATABASE demo TEMPLATE=testdb;
3.1.4 删除数据库
删除demo数据库:
drop database demo;
3.1.5 查看数据库列表
执行l来查看数据库列表:
l
执行效果如下图所示:
![041d854bde95fb66b112037387860a13.png](https://i-blog.csdnimg.cn/blog_migrate/f599f2db1d1aef8c12e55d4ce866b661.jpeg)
3.1.6 连接数据库
连接数据库有两种方式:
- psql模式内连接
假如连接testdb数据库,执行以下代码:
postgres=# c testdb
执行效果如下图所示:
![a69db6cc339a49225d6c57a06847f95d.png](https://i-blog.csdnimg.cn/blog_migrate/df6766e37e4b0311dffb75a74de76bb8.jpeg)
- psql模式外连接
postgres=# q # 退出psql模式bash-4.2$ psql -d testdb
执行效果如下图所示:
![2afbe34fd316660643e0393fa14b2cad.png](https://i-blog.csdnimg.cn/blog_migrate/bb28b3d1c356b7685024eb84e46861b3.jpeg)
3.1.7 创建表
创建一个表,用来存储城市的最高及最低温度。代码如下所示:
CREATE TABLE location_city ( name varchar(80), location point);
3.1.8 列出数据表
在testdb数据库下,执行d命令列出数据表:
d
执行效果如下:
![281d2ff477f1feeda9b455f227852ed3.png](https://i-blog.csdnimg.cn/blog_migrate/80dad7c28816274e9ff6ba02abc69508.jpeg)
3.1.9 查看表结构
使用d [tableName]命令来查看表结构,比如查看location_city表:
d location_city;
执行效果如下图所示:
![2bdd2313d98063fcadb37bf033225632.png](https://i-blog.csdnimg.cn/blog_migrate/8ed38832b803186cebd5b0e8501af7da.jpeg)
3.1.10 批量插入数据
批量插入数据有两种常用的方式:
- 多values方式
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