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柵格功能LibXML2LIBJSONPostGIS的特點如下:
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
執行效果如下圖所示:
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.pdfhttps://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E4%BA%8C%E7%AB%A0https://wiki.postgresql.org/wiki/Main_Page易百教程:https://www.yiibai.com/postgresql/postgresql-datatypes.html中文手冊:http://www.postgres.cn/docs/9.6/index.htmlPostgres中文社區:http://www.postgres.cn/v2/home關於PostGIS的官方學習資料如下,請參考:
--END--
碼字不易,如果您覺得文章寫得不錯,請關注作者~ 您的關注是我寫作的最大動力
友情提示:原文排版精美,可點擊分享鏈接查看。