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

2.简介

2.1 什么是空间数据库

PostGIS是一种空间数据库，Oracle Spatial 和Sql Server(2008及以后版本)也是空间数据库。但是，是什么让普通数据库变成空间数据库？
简短回答就是：
空间数据库能像存储和操作普通对象一样操作空间对象。
下面简短说明空间数据库的演进，然后复习一下用空间数据库组织空间数据的3个重要的概念，数据类型（Spatial data types）、索引(spatial indexing)、函数(Spatial functions)。
1.空间数据类型指图形，比如点、线、面；
2.多维空间索引用来高效的处理空间操作；
3.空间函数支持用sql的方式来查询空间属性和空间关系。
对于优化性能和分析，三者组合起来形成一个复杂的结构。

4.创建空间数据库

新建数据库
添加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.加载空间数据

使用shp2pgsql程序，添加数据库连接，添加待导入文件，配置选项后即可导入；
查看空间参考信息：
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元数据表

空间数据库种定义的空间参考系统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)
常用函数：
-ST_Length(geometry)返回线的长度
-ST_StartPoint(geometry) 返回起点
-ST_EndPoint(geometry) 返回终点
-ST_NPoints(geometry) 返回点的个数

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空间关系

空间数据库的强大不仅在于可以存储几何，更体现在能比对几何之间的空间位置关系。

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个几何是相离的，则他们不相交，事实上，因为相交操作可基于空间索引进行，所以监测不相交比检测相离更高效，但可以得到相同的结果。

Cross

image.png

当2个几何的相交部分的几何维度比这2个几何中的最高维度低一个维度时，则称这2个几何为Cross关系。

Overlap

用于比较2个同维度的几何，当2个同维度的几何的交集与该2个几何都不相同，但是维度相同时，称为Overlap。
ex:找到与地铁站'Broad St'相交的社区名字

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函数提供了一个用索引加速了的方法，，比如这条路两边500米缓冲区内有多少条路，不用做缓冲区，使用该方法进行检测即可。

13 空间连接

空间连接是空间数据库的最基本的功能。
上一节内容中，先选择一个地铁站的点坐标，再使用空间关系操作查询该点附近的街区，使用空间连接则只需一部即可完成该操作：
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的空间关系函数都可以用来创建空间连接，最常用的包括：ST_Intersects, ST_Contains, and ST_DWithin。

13.1 Join and Summarize

Join和Group by的组合形成了GIS系统中最常见的分析。
例如：曼哈顿街区的人口和人种构成情况如何？

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;

如何做到的呢？理论（实际上数据库系统对执行顺序做了优化）上做了以下处理：

1. JOIN语句创建了一个同时包含了neighborhoods表和census表的所有字段的虚拟表；
2. where语句对虚拟表进行筛选，只保留Manhattan的记录；
3. 剩下的记录按照街区名字进行分组汇总，使用聚合函数sum()对人口进行求和，

4. 再经过一点小小的计算和格式化，得出结果。

   
   

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;

计算每条线路服务的人口数量及结构分布情况

15.空间索引

再次强调空间索引是空间数据库的三个关键要是之一。空间索引使得数据库操作大规模数据集合变成可能。
当我们加载nyc_census_blocks表时，pgShapeLoader自动创建了名为nyc_census_blocks_geom_idx的索引。为了展示索引的重要性，我们做个测试(thinkpad t480 i5 16G )，首先删除索引DROP INDEX nyc_census_blocks_geom_idx;执行如下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 空间索引是如何工作的

标准数据库的索引是给索引字段的值创建一个层次树。空间索引有一点点不同，它不索引集合要素本身，取而代之的是索引集合要素的bounding boxes（最小外接矩形）。如图：

15.2 Index-Only Queries

PostGIS中大多数函数（ST_Contains, ST_Intersects, ST_DWithin, etc）都自动包含了索引过滤，但是有些则没有（e.g., ST_Relate）。
使用&&操作符来执行外接矩形框的查询。例如：

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的查询分析器智能选择执行查询时是否使用索引。与直觉相反的时，使用索引并不总是最快的：如果查询结果时返回表里的所有记录，通过索引来检索每一条记录就比直接顺序读取记录要慢很多。
为了分辨出当前处理的是哪种情况（只读取一点记录还是读取大部分记录），PostgreSQL在持续统计每一个索引表列的数据量情况。通常情况下，PostgreSQL定期收集统计信息，然后，当你短时间内明显的改变了表的数据内容时，统计信息的更新可能就会有延迟（not be up-to-date）。
为了使统计信息与表内容相匹配，在大量的插入或者删除数据后，可以运行ANALYZE命令来触发更新。ANALYZE nyc_census_blocks;

15.4.Vacuuming

值得强调的是，仅仅创建索引不足以让PostgreSql高效的使用它。
新建索引或者大数据量的UPDATEs、INSERTs、DELETEs操作后都需要进行Vacuuming。VACUUM命令让PostgreSQL回收更新或者删除后留在表页上的未利用的空间。
Vacuuming是如此重要，所以PostgresSQL提供了autovacuuming选项。
默认的autovacuuming会定期执行，对于高度事务性数据库，等待autovacuuming是不明智的，在添加索引、大量加载数据或者大量的数据更新后，应该手动执行VACUUMM。
Vacuuming and analyzing可以根据需要分别执行。VACUUM不会更新数据库的统计信息，同样的ANALYZE也不会回收未利用的空间。这2个命令的执行对象可以是数据库、表和列。可同时执行。
VACUUM ANALYZE nyc_census_blocks;