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

2.簡介

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

PostGIS是一種空間數(shù)據(jù)庫床嫌，Oracle Spatial 和Sql Server(2008及以后版本)也是空間數(shù)據(jù)庫竖瘾。但是欢揖，是什么讓普通數(shù)據(jù)庫變成空間數(shù)據(jù)庫匆骗？
簡短回答就是：
空間數(shù)據(jù)庫能像存儲和操作普通對象一樣操作空間對象骡苞。
下面簡短說明空間數(shù)據(jù)庫的演進辽社，然后復習一下用空間數(shù)據(jù)庫組織空間數(shù)據(jù)的3個重要的概念费什，數(shù)據(jù)類型（Spatial data types）钾恢、索引(spatial indexing)、函數(shù)(Spatial functions)鸳址。
1.空間數(shù)據(jù)類型指圖形瘩蚪，比如點、線稿黍、面疹瘦；
2.多維空間索引用來高效的處理空間操作；
3.空間函數(shù)支持用sql的方式來查詢空間屬性和空間關系巡球。
對于優(yōu)化性能和分析拱礁，三者組合起來形成一個復雜的結構琢锋。

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

新建數(shù)據(jù)庫
添加postgis擴展create EXTENSION postgis;
查看postgis版本信息select postgis_full_version();，查詢結果：
"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ù)庫連接吴超，添加待導入文件，配置選項后即可導入鸯乃；
查看空間參考信息：
SELECT srtext FROM spatial_ref_sys WHERE srid = 26918;

7.簡單的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.簡單的sql練習

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簡介

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表達真實世界的對象

-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';返回文本形式表達的點POINT(0 0)
SELECT ST_X(geom), ST_Y(geom) FROM geometries WHERE name = 'Point';分別返回x\y坐標值

9.3.2linestring

closed:首尾點相同的線
simple:線沒有自身相交或者相鄰接的情況（closed時，末尾點除外）缨睡；
一個線可以是closed和simple;
返回線的文字表達：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) 返回起點
-ST_EndPoint(geometry) 返回終點
-ST_NPoints(geometry) 返回點的個數(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>'));

從文本轉換

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

11空間關系

空間數(shù)據(jù)庫的強大不僅在于可以存儲幾何细诸，更體現(xiàn)在能比對幾何之間的空間位置關系。

11.1

ST_Equals(geometry A, geometry B) :測試兩個幾何是否空間相等陋守。如果兩個幾何擁有完全一樣的坐標值震贵，則返回TRUE。
支持如下幾何類型之間進行比較水评，

11.2 ST_Intersects, ST_Disjoint, ST_Crosses and ST_Overlaps

ST_Intersects, ST_Crosses, and ST_Overlaps 檢測幾何是否存在相交關系猩系。其中Intersects為廣義的相交，包含了Cross和Overlap2中情況中燥，具體入下：
ST_Intersects(geometry A, geometry B) ：如果2個幾何有任何公共部分寇甸，則返回true;
ST_Disjoint，如果2個幾何是相離的疗涉，則他們不相交拿霉，事實上，因為相交操作可基于空間索引進行咱扣，所以監(jiān)測不相交比檢測相離更高效友浸，但可以得到相同的結果。

Cross

image.png

當2個幾何的相交部分的幾何維度比這2個幾何中的最高維度低一個維度時偏窝，則稱這2個幾何為Cross關系收恢。

Overlap

用于比較2個同維度的幾何，當2個同維度的幾何的交集與該2個幾何都不相同祭往，但是維度相同時伦意，稱為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檢測兩個幾何是否擁有共同邊界硼补，但不存在內部相交關系驮肉。

ST_Within and ST_Contains

ST_Within and ST_Contains 檢測一個幾何是否完全在另一個幾何內。

11.5 ST_Distance and ST_DWithin

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

13 空間連接

空間連接是空間數(shù)據(jù)庫的最基本的功能浪读。
上一節(jié)內容中昔榴，先選擇一個地鐵站的點坐標，再使用空間關系操作查詢該點附近的街區(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的空間關系函數(shù)都可以用來創(chuàng)建空間連接互订，最常用的包括：ST_Intersects, ST_Contains, and ST_DWithin。

13.1 Join and Summarize

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

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ù)據(jù)庫系統(tǒng)對執(zhí)行順序做了優(yōu)化）上做了以下處理：

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

4. 再經(jīng)過一點小小的計算和格式化粒褒，得出結果识颊。

   
   

13.2 高級連接

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;

計算每條線路服務的人口數(shù)量及結構分布情況

15.空間索引

再次強調空間索引是空間數(shù)據(jù)庫的三個關鍵要是之一∞确兀空間索引使得數(shù)據(jù)庫操作大規(guī)模數(shù)據(jù)集合變成可能祥款。
當我們加載nyc_census_blocks表時，pgShapeLoader自動創(chuàng)建了名為nyc_census_blocks_geom_idx的索引月杉。為了展示索引的重要性刃跛，我們做個測試(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';查詢結果為一條記錄苛萎，時間為64msec;

15.1 空間索引是如何工作的

標準數(shù)據(jù)庫的索引是給索引字段的值創(chuàng)建一個層次樹∏谈牵空間索引有一點點不同桂塞，它不索引集合要素本身，取而代之的是索引集合要素的bounding boxes（最小外接矩形）馍驯。如圖：

15.2 Index-Only Queries

PostGIS中大多數(shù)函數(shù)（ST_Contains, ST_Intersects, ST_DWithin, etc）都自動包含了索引過濾干像，但是有些則沒有（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條記錄。使用外接矩形查詢結果當然比精確查詢多戚篙，上一條查詢結果理解為外接矩形與外接矩形相交的所有記錄五鲫。

15.3Analyzing

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

15.4.Vacuuming

值得強調的是削锰，僅僅創(chuàng)建索引不足以讓PostgreSql高效的使用它通铲。
新建索引或者大數(shù)據(jù)量的UPDATEs、INSERTs器贩、DELETEs操作后都需要進行Vacuuming颅夺。VACUUM命令讓PostgreSQL回收更新或者刪除后留在表頁上的未利用的空間。
Vacuuming是如此重要蛹稍，所以PostgresSQL提供了autovacuuming選項吧黄。
默認的autovacuuming會定期執(zhí)行，對于高度事務性數(shù)據(jù)庫唆姐，等待autovacuuming是不明智的拗慨，在添加索引、大量加載數(shù)據(jù)或者大量的數(shù)據(jù)更新后奉芦，應該手動執(zhí)行VACUUMM赵抢。
Vacuuming and analyzing可以根據(jù)需要分別執(zhí)行。VACUUM不會更新數(shù)據(jù)庫的統(tǒng)計信息声功，同樣的ANALYZE也不會回收未利用的空間烦却。這2個命令的執(zhí)行對象可以是數(shù)據(jù)庫、表和列先巴∑渚簦可同時執(zhí)行。
VACUUM ANALYZE nyc_census_blocks;