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版本的依賴關係可點擊：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的啟動、停止、查看狀態的命令：

# 啟動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數據庫：

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

執行效果如下圖所示：

3.1.6 連接數據庫

連接數據庫有兩種方式：

• psql模式內連接

假如連接testdb數據庫，執行以下代碼：

postgres=# \c testdb

執行效果如下圖所示：

• psql模式外連接

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表：

\d location_city;

執行效果如下圖所示：

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","(123,34)"
"Fort Worth","(-23,21)"
"上海","(45,66)"

執行如下代碼：

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

執行結果如下圖所示：

copy擴展

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

打開該文件後效果：

• 通過window的excel查看，正常。
• 用linux的vim命令查看，中文亂碼。解決方案：

解決linux下查看文件亂碼問題：

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

查看文件編碼：

# 在Vim中可以直接查看文件編碼
:set fileencoding

3.1.11 查詢數據

查詢location_city表中name與position字段的所有數據：

SELECT * FROM location_city;

3.1.12 更新數據

使用UPDATE … SET …命令來更新location_city表的數據：

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

3.1.13 刪除數據

• 刪除指定數據，比如刪除name為“San Francisco”的數據：

DELETE FROM location_city WHERE name = 'San Francisco';

• 清空cities表數據：

DELETE FROM location_city;
# 或者
TRUNCATE location_city;

3.2 PostGIS

PostgreSQL數據庫安裝PostGIS擴展，數據庫將可以進行空間數據管理、數量測量與幾何拓撲分析。

3.2.1 在testdb數據庫下安裝PostGIS擴展

安裝PostGIS擴展：

CREATE EXTENSION postgis;

驗證PostGIS擴展是否安裝成功：

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 創建空間數據表

• 先建立一個常規的表存儲

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

• 添加一個空間列，用於存儲城市的位置。
• 習慣上這個列叫做 “the_geom”。它記錄了數據的類型（點、線、面）、有幾維（這裡是二維）以及空間座標系統。這裡使用 EPSG:4326 座標系統：

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');

3.2.4 簡單查詢

標準的PostgreSQL語句都可以用於PostGIS，這裡我們查詢cities表數據：

SELECT * FROM cities;

執行效果如下圖所示：

這裡的座標是無法閱讀的 16 進制格式。要以WKT文本顯示，使用ST_AsText(the_geom)或ST_AsEwkt(the_geom)函數。也可以使用ST_X(the_geom)和ST_Y(the_geom)顯示一個維度的座標：

SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM cities;

執行效果如下圖所示：

3.2.5 空間查詢

以米為單位並假設地球是完美橢球，上面三個城市相互的距離是多少？

執行以下代碼計算距離：

SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;

執行效果如下圖所示：

四、總結

本文首先說明了PostgreSQL與PostGIS的基本概念，又羅列了兩者的yum安裝教程及版本兼容關係，最後講解了一下PostgreSQL的簡單使用及PostGIS的空間查詢的簡單示例。

關於PostgreSQL的一些官方學習資料如下，請參考：

• https://www.postgresql.org/files/documentation/pdf/9.6/postgresql-9.6-A4.pdf
• https://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E4%BA%8C%E7%AB%A0
• https://wiki.postgresql.org/wiki/Main_Page
• 易百教程：https://www.yiibai.com/postgresql/postgresql-datatypes.html
• 中文手冊：http://www.postgres.cn/docs/9.6/index.html
• Postgres中文社區：http://www.postgres.cn/v2/home

關於PostGIS的官方學習資料如下，請參考：

• 英文官方資料：http://www.postgis.net/stuff/postgis-2.4.pdf
• 中文社區資料：http://www.postgres.cn/docs/PostGis-2.2.0dev_Manual.pdf

--END--

碼字不易，如果您覺得文章寫得不錯，請關注作者~ 您的關注是我寫作的最大動力

友情提示：原文排版精美，可點擊分享鏈接查看。

閱讀更多 大數據實戰演練 的文章

關鍵字: 功能強大 數據結構 JSON