官方教程地址：https://postgis.net/workshops/postgis-intro/index.html

2.簡(jiǎn)介

2.1 什么是空間數(shù)據(jù)庫

PostGIS是一種空間數(shù)據(jù)庫，Oracle Spatial 和Sql Server(2008及以后版本)也是空間數(shù)據(jù)庫。但是，是什么讓普通數(shù)據(jù)庫變成空間數(shù)據(jù)庫？
簡(jiǎn)短回答就是：
空間數(shù)據(jù)庫能像存儲(chǔ)和操作普通對(duì)象一樣操作空間對(duì)象。
下面簡(jiǎn)短說明空間數(shù)據(jù)庫的演進(jìn)，然后復(fù)習(xí)一下用空間數(shù)據(jù)庫組織空間數(shù)據(jù)的3個(gè)重要的概念，數(shù)據(jù)類型（Spatial data types）、索引(spatial indexing)、函數(shù)(Spatial functions)。
1.空間數(shù)據(jù)類型指圖形，比如點(diǎn)、線、面；
2.多維空間索引用來高效的處理空間操作；
3.空間函數(shù)支持用sql的方式來查詢空間屬性和空間關(guān)系。
對(duì)于優(yōu)化性能和分析，三者組合起來形成一個(gè)復(fù)雜的結(jié)構(gòu)。

4.創(chuàng)建空間數(shù)據(jù)庫

新建數(shù)據(jù)庫
添加postgis擴(kuò)展create EXTENSION postgis;
查看postgis版本信息select postgis_full_version();，查詢結(jié)果：
"POSTGIS="3.0.1 3.0.1" [EXTENSION] PGSQL="110" GEOS="3.8.0-CAPI-1.13.1 " PROJ="Rel. 5.2.0, September 15th, 2018" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)""

5.加載空間數(shù)據(jù)

使用shp2pgsql程序，添加數(shù)據(jù)庫連接，添加待導(dǎo)入文件，配置選項(xiàng)后即可導(dǎo)入；
查看空間參考信息：
SELECT srtext FROM spatial_ref_sys WHERE srid = 26918;

7.簡(jiǎn)單的sql

SELECT avg(char_length(name)), stddev(char_length(name))  FROM nyc_neighborhoods  WHERE boroname = 'Brooklyn';
SELECT boroname, avg(char_length(name)), stddev(char_length(name))  FROM nyc_neighborhoods  GROUP BY boroname;

8.簡(jiǎn)單的sql練習(xí)

SELECT Sum(popn_total) AS population  FROM nyc_census_blocks;
SELECT Sum(popn_total) AS population  FROM nyc_census_blocks  WHERE boroname = 'The Bronx';
SELECT  boroname,  100 * Sum(popn_white)/Sum(popn_total) AS white_pct FROM nyc_census_blocks GROUP BY boroname;

9. 幾何

9.1簡(jiǎn)介

CREATE TABLE geometries (name varchar, geom geometry);

INSERT INTO geometries VALUES  ('Point', 'POINT(0 0)'),  ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),  ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),  ('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),  ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 , 0 1, 0 0)))');

SELECT name, ST_AsText(geom) FROM geometries;

9.2元數(shù)據(jù)表

空間數(shù)據(jù)庫種定義的空間參考系統(tǒng)spatial_ref_sys
描述所有要素類的幾何字段信息geometry_columns
SELECT * FROM geometry_columns;

9.3表達(dá)真實(shí)世界的對(duì)象

-ST_GeometryType(geometry) 返回幾何類型；
-ST_NDims(geometry)返回幾何維度
-ST_SRID(geometry)返回幾何的空間參考id

SELECT name, ST_GeometryType(geom), ST_NDims(geom), ST_SRID(geom)  FROM geometries;

points

SELECT ST_AsText(geom) FROM geometries WHERE name = 'Point';返回文本形式表達(dá)的點(diǎn)POINT(0 0)
SELECT ST_X(geom), ST_Y(geom) FROM geometries WHERE name = 'Point';分別返回x\y坐標(biāo)值

9.3.2linestring

closed:首尾點(diǎn)相同的線
simple:線沒有自身相交或者相鄰接的情況（closed時(shí)，末尾點(diǎn)除外）；
一個(gè)線可以是closed和simple;
返回線的文字表達(dá)：SELECT ST_AsText(geom) FROM geometries WHERE name = 'Linestring';LINESTRING(0 0, 1 1, 2 1, 2 2)
常用函數(shù)：
-ST_Length(geometry)返回線的長度
-ST_StartPoint(geometry) 返回起點(diǎn)
-ST_EndPoint(geometry) 返回終點(diǎn)
-ST_NPoints(geometry) 返回點(diǎn)的個(gè)數(shù)

polygons

9.3.4集合

-MultiPoint, a collection of points
-MultiLineString, a collection of linestrings
-MultiPolygon, a collection of polygons
-GeometryCollection, a heterogeneous collection of any geometry (including other collections)由任何幾何組成的混雜的集合，包括集合
-ST_NumGeometries(geometry) returns the number of parts in the collection
-ST_GeometryN(geometry,n) returns the specified part
-ST_Area(geometry) returns the total area of all polygonal parts
-ST_Length(geometry) returns the total length of all linear parts

9.4 幾何輸入和輸出

postgis支持的幾類格式

• Well-known text (WKT)
  • ST_GeomFromText(text, srid) returns geometry
  • ST_AsText(geometry) returns text
  • ST_AsEWKT(geometry) returns text
• Well-known binary (WKB)
  • ST_GeomFromWKB(bytea) returns geometry
  • ST_AsBinary(geometry) returns bytea
  • ST_AsEWKB(geometry) returns bytea
• Geographic Mark-up Language (GML)
  • ST_GeomFromGML(text) returns geometry
  • ST_AsGML(geometry) returns text
• Keyhole Mark-up Language (KML)
  • ST_GeomFromKML(text) returns geometry
  • ST_AsKML(geometry) returns text
• GeoJSON
  • ST_AsGeoJSON(geometry) returns text
• Scalable Vector Graphics (SVG)
  • ST_AsSVG(geometry) returns text

SELECT encode( ST_AsBinary(ST_GeometryFromText('LINESTRING(0 0,1 0)')),  'hex');
SELECT ST_AsText(ST_GeometryFromText('LINESTRING(0 0 0,1 0 0,1 1 2)'));
SELECT ST_AsGeoJSON(ST_GeomFromGML('<gml:Point><gml:coordinates>1,1</gml:coordinates></gml:Point>'));

從文本轉(zhuǎn)換

采用 oldata::newtype的簡(jiǎn)寫形式進(jìn)行格式轉(zhuǎn)化，例如：SELECT 0.9::text;將double轉(zhuǎn)化為文本;SELECT 'POINT(0 0)'::geometry;wkt轉(zhuǎn)化為幾何；SELECT 'SRID=4326;POINT(0 0)'::geometry;帶空間參考

11空間關(guān)系

空間數(shù)據(jù)庫的強(qiáng)大不僅在于可以存儲(chǔ)幾何，更體現(xiàn)在能比對(duì)幾何之間的空間位置關(guān)系。

11.1

ST_Equals(geometry A, geometry B) :測(cè)試兩個(gè)幾何是否空間相等。如果兩個(gè)幾何擁有完全一樣的坐標(biāo)值，則返回TRUE。
支持如下幾何類型之間進(jìn)行比較，

11.2 ST_Intersects, ST_Disjoint, ST_Crosses and ST_Overlaps

ST_Intersects, ST_Crosses, and ST_Overlaps 檢測(cè)幾何是否存在相交關(guān)系。其中Intersects為廣義的相交，包含了Cross和Overlap2中情況，具體入下：
ST_Intersects(geometry A, geometry B) ：如果2個(gè)幾何有任何公共部分，則返回true;
ST_Disjoint，如果2個(gè)幾何是相離的，則他們不相交，事實(shí)上，因?yàn)橄嘟徊僮骺苫诳臻g索引進(jìn)行，所以監(jiān)測(cè)不相交比檢測(cè)相離更高效，但可以得到相同的結(jié)果。

Cross

image.png

當(dāng)2個(gè)幾何的相交部分的幾何維度比這2個(gè)幾何中的最高維度低一個(gè)維度時(shí)，則稱這2個(gè)幾何為Cross關(guān)系。

Overlap

用于比較2個(gè)同維度的幾何，當(dāng)2個(gè)同維度的幾何的交集與該2個(gè)幾何都不相同，但是維度相同時(shí)，稱為Overlap。
ex:找到與地鐵站'Broad St'相交的社區(qū)名字

SELECT name, ST_AsText(geom)
FROM nyc_subway_stations
WHERE name = 'Broad St';
return:POINT(583571 4506714)
SELECT name, boroname
FROM nyc_neighborhoods
WHERE ST_Intersects(geom, ST_GeomFromText('POINT(583571 4506714)',26918));

ST_Touches

ST_Touches檢測(cè)兩個(gè)幾何是否擁有共同邊界，但不存在內(nèi)部相交關(guān)系。

ST_Within and ST_Contains

ST_Within and ST_Contains 檢測(cè)一個(gè)幾何是否完全在另一個(gè)幾何內(nèi)。

11.5 ST_Distance and ST_DWithin

GIS中最常見的問題“就是找到距離一個(gè)東西X距離以內(nèi)的所有東西”
ST_Distance(geometry A, geometry B)以float形式返回兩個(gè)幾何之間的最短距離
SELECT ST_Distance( ST_GeometryFromText('POINT(0 5)'), ST_GeometryFromText('LINESTRING(-2 2, 2 2)'));
為了測(cè)試物體是否位于另一物體一定距離內(nèi)，ST_DWithin函數(shù)提供了一個(gè)用索引加速了的方法，，比如這條路兩邊500米緩沖區(qū)內(nèi)有多少條路，不用做緩沖區(qū)，使用該方法進(jìn)行檢測(cè)即可。

13 空間連接

空間連接是空間數(shù)據(jù)庫的最基本的功能。
上一節(jié)內(nèi)容中，先選擇一個(gè)地鐵站的點(diǎn)坐標(biāo)，再使用空間關(guān)系操作查詢?cè)擖c(diǎn)附近的街區(qū)，使用空間連接則只需一部即可完成該操作：
SELECT subways.name AS subway_name, neighborhoods.name AS neighborhood_name, neighborhoods.boroname AS borough FROM nyc_neighborhoods AS neighborhoods JOIN nyc_subway_stations AS subways ON ST_Contains(neighborhoods.geom, subways.geom) WHERE subways.name = 'Broad St';
任何返回true/false的空間關(guān)系函數(shù)都可以用來創(chuàng)建空間連接，最常用的包括：ST_Intersects, ST_Contains, and ST_DWithin。

13.1 Join and Summarize

Join和Group by的組合形成了GIS系統(tǒng)中最常見的分析。
例如：曼哈頓街區(qū)的人口和人種構(gòu)成情況如何？

SELECT
  neighborhoods.name AS neighborhood_name,
  Sum(census.popn_total) AS population,
  100.0 * Sum(census.popn_white) / Sum(census.popn_total) AS white_pct,
  100.0 * Sum(census.popn_black) / Sum(census.popn_total) AS black_pct
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_census_blocks AS census
ON ST_Intersects(neighborhoods.geom, census.geom)
WHERE neighborhoods.boroname = 'Manhattan'
GROUP BY neighborhoods.name
ORDER BY white_pct DESC;

如何做到的呢？理論（實(shí)際上數(shù)據(jù)庫系統(tǒng)對(duì)執(zhí)行順序做了優(yōu)化）上做了以下處理：

1. JOIN語句創(chuàng)建了一個(gè)同時(shí)包含了neighborhoods表和census表的所有字段的虛擬表；
2. where語句對(duì)虛擬表進(jìn)行篩選，只保留Manhattan的記錄；
3. 剩下的記錄按照街區(qū)名字進(jìn)行分組匯總，使用聚合函數(shù)sum()對(duì)人口進(jìn)行求和，

4. 再經(jīng)過一點(diǎn)小小的計(jì)算和格式化，得出結(jié)果。

   
   

13.2 高級(jí)連接

SELECT
  lines.route,
  100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
  100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
  Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.geom, subways.geom, 200)
JOIN subway_lines AS lines
ON strpos(subways.routes, lines.route) > 0
GROUP BY lines.route
ORDER BY black_pct DESC;

計(jì)算每條線路服務(wù)的人口數(shù)量及結(jié)構(gòu)分布情況

15.空間索引

再次強(qiáng)調(diào)空間索引是空間數(shù)據(jù)庫的三個(gè)關(guān)鍵要是之一?？臻g索引使得數(shù)據(jù)庫操作大規(guī)模數(shù)據(jù)集合變成可能。
當(dāng)我們加載nyc_census_blocks表時(shí)，pgShapeLoader自動(dòng)創(chuàng)建了名為nyc_census_blocks_geom_idx的索引。為了展示索引的重要性，我們做個(gè)測(cè)試(thinkpad t480 i5 16G )，首先刪除索引DROP INDEX nyc_census_blocks_geom_idx;執(zhí)行如下sqlSELECT blocks.blkid FROM nyc_census_blocks blocks JOIN nyc_subway_stations subways ON ST_Contains(blocks.geom, subways.geom) WHERE subways.name = 'Broad St';查詢結(jié)果為一條記錄，時(shí)間為64msec;

15.1 空間索引是如何工作的

標(biāo)準(zhǔn)數(shù)據(jù)庫的索引是給索引字段的值創(chuàng)建一個(gè)層次樹?？臻g索引有一點(diǎn)點(diǎn)不同，它不索引集合要素本身，取而代之的是索引集合要素的bounding boxes（最小外接矩形）。如圖：

15.2 Index-Only Queries

PostGIS中大多數(shù)函數(shù)（ST_Contains, ST_Intersects, ST_DWithin, etc）都自動(dòng)包含了索引過濾，但是有些則沒有（e.g., ST_Relate）。
使用&&操作符來執(zhí)行外接矩形框的查詢。例如：

SELECT Sum(popn_total)
FROM nyc_neighborhoods neighborhoods
JOIN nyc_census_blocks blocks
ON neighborhoods.geom && blocks.geom
WHERE neighborhoods.name = 'West Village';

return 49821條記錄。
使用精確空間查詢：

SELECT Sum(popn_total)
FROM nyc_neighborhoods neighborhoods
JOIN nyc_census_blocks blocks
ON ST_Intersects(neighborhoods.geom, blocks.geom)
WHERE neighborhoods.name = 'West Village';

return 26718條記錄。使用外接矩形查詢結(jié)果當(dāng)然比精確查詢多，上一條查詢結(jié)果理解為外接矩形與外接矩形相交的所有記錄。

15.3Analyzing

PostGIS的查詢分析器智能選擇執(zhí)行查詢時(shí)是否使用索引。與直覺相反的時(shí)，使用索引并不總是最快的：如果查詢結(jié)果時(shí)返回表里的所有記錄，通過索引來檢索每一條記錄就比直接順序讀取記錄要慢很多。
為了分辨出當(dāng)前處理的是哪種情況（只讀取一點(diǎn)記錄還是讀取大部分記錄），PostgreSQL在持續(xù)統(tǒng)計(jì)每一個(gè)索引表列的數(shù)據(jù)量情況。通常情況下，PostgreSQL定期收集統(tǒng)計(jì)信息，然后，當(dāng)你短時(shí)間內(nèi)明顯的改變了表的數(shù)據(jù)內(nèi)容時(shí)，統(tǒng)計(jì)信息的更新可能就會(huì)有延遲（not be up-to-date）。
為了使統(tǒng)計(jì)信息與表內(nèi)容相匹配，在大量的插入或者刪除數(shù)據(jù)后，可以運(yùn)行ANALYZE命令來觸發(fā)更新。ANALYZE nyc_census_blocks;

15.4.Vacuuming

值得強(qiáng)調(diào)的是，僅僅創(chuàng)建索引不足以讓PostgreSql高效的使用它。
新建索引或者大數(shù)據(jù)量的UPDATEs、INSERTs、DELETEs操作后都需要進(jìn)行Vacuuming。VACUUM命令讓PostgreSQL回收更新或者刪除后留在表頁上的未利用的空間。
Vacuuming是如此重要，所以PostgresSQL提供了autovacuuming選項(xiàng)。
默認(rèn)的autovacuuming會(huì)定期執(zhí)行，對(duì)于高度事務(wù)性數(shù)據(jù)庫，等待autovacuuming是不明智的，在添加索引、大量加載數(shù)據(jù)或者大量的數(shù)據(jù)更新后，應(yīng)該手動(dòng)執(zhí)行VACUUMM。
Vacuuming and analyzing可以根據(jù)需要分別執(zhí)行。VACUUM不會(huì)更新數(shù)據(jù)庫的統(tǒng)計(jì)信息，同樣的ANALYZE也不會(huì)回收未利用的空間。這2個(gè)命令的執(zhí)行對(duì)象可以是數(shù)據(jù)庫、表和列。可同時(shí)執(zhí)行。
VACUUM ANALYZE nyc_census_blocks;