2021-11-11 空间查询练习

1.1、介绍

在前一节中，我们加载了各种数据。在开始处理数据之前，让我们先看一些更简单的示例。在pgAdmin中，再次选择nyc数据库并打开SQL查询工具。将此示例SQL代码粘贴到pgAdmin SQL Editor窗口中(删除默认情况下可能存在的任何文本)，然后执行。

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 1, 0 1, 0 0)))');

SELECT name, ST_AsText(geom) FROM geometries;

image

上面的示例创建一个表(几何图形)，然后插入五个几何图形:点、线、多边形、带孔的多边形和集合。最后，插入的行被选中并显示在Output窗格中。

1.2、元数据表

与SFSQL (Simple Features for SQL)规范一致，PostGIS提供了两个表来跟踪和报告给定数据库中可用的几何类型。

• 第一个表spatial_ref_sys定义了数据库已知的所有空间引用系统，稍后将进行更详细的描述。
• 第二个表(实际上是一个视图)，geometry_columns，提供了所有“要素”(定义为具有几何属性的对象)的列表，以及这些要素的基本细节。 image
  

  让我们看看数据库中的geometry_columns表。将此命令粘贴到查询工具中，就像之前一样

SELECT * FROM geometry_columns;

image

• F_table_catalog、f_table_schema和f_table_name提供包含给定几何结构的特性表的完全限定名。因为PostgreSQL不使用目录，f_table_catalog将趋向于为空。
• F_geometry_column是几何体包含列的列的名称——对于具有多个几何体列的要素表，每个几何体列将有一条记录。
• coord_dimension和srid分别定义几何(2维、3维或4维)的维数和空间参考系统标识符，该标识符指向spatial_ref_sys表。
• type列定义几何图形的类型，如下所述;到目前为止，我们已经看到了Point和Linestring类型。
  通过查询这个表，GIS客户端和库可以确定检索数据时需要什么，并可以执行任何必要的投影、处理或渲染，而不需要检查每个几何图形。

、1.3 表示真实世界中的物体

SQL的简单特性(SFSQL)规范是PostGIS开发的原始指导标准，它定义了如何表示真实世界中的对象。通过取一个连续的形状，并以固定的分辨率将其数字化，我们就获得了一个可通过的对象表示。SFSQL只处理二维表示。PostGIS将其扩展到包括3维和4维表示;最近，SQL- multimedia第3部分(SQL/MM)规范正式定义了它们自己的表示。
我们的示例表包含了不同几何类型的混合。我们可以使用读取几何元数据的函数来收集关于每个对象的一般信息。

• ST_GeometryType(geometry) 返回几何图形的类型
• ST_NDims(geometry) 返回几何图形的维数
• ST_SRID(geometry) 返回几何图形的空间引用标识符编号

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

     name       |    st_geometrytype    | st_ndims | st_srid
-----------------+-----------------------+----------+---------
 Point           | ST_Point              |        2 |       0
 Polygon         | ST_Polygon            |        2 |       0
 PolygonWithHole | ST_Polygon            |        2 |       0
 Collection      | ST_GeometryCollection |        2 |       0
 Linestring      | ST_LineString         |        2 |       0

1.3.1、Points

image

一个空间点代表地球上的一个位置。这个点由单个坐标表示(包括2维、3维或4维)。当精确的细节(如形状和大小)在目标尺度上不重要时，点被用来表示对象。例如，世界地图上的城市可以用点来描述，而一个州的地图可以用多边形来表示城市。

SELECT ST_AsText(geom)
  FROM geometries
  WHERE name = 'Point';


POINT(0 0)

一些处理点的特殊空间函数是:

• ST_X(geometry) 返回X坐标
• ST_Y(geometry) 返回Y坐标
  所以，我们可以像这样从一个点读取纵坐标:

SELECT ST_X(geom), ST_Y(geom)
  FROM geometries
  WHERE name = 'Point';

纽约市地铁站(nyc_subway_stations)表是一个以点表示的数据集。下面的SQL查询将返回与一个点(在ST_AsText列中)关联的几何图形。

SELECT name, ST_AsText(geom)
  FROM nyc_subway_stations
  LIMIT 1;

1.3.2、Linestrings

image

linestring是位置之间的路径。它的形式是两个或多个点的有序级数。道路和河流通常用线串表示。如果一个linestring在同一点开始和结束，那么它就是关闭（closed） 的。如果它不交叉或接触自己(除非在它的端点，如果它是关闭的)，它被称为简单（simple ）。linestring可以是封闭的，也可以是简单的。
纽约的街道网络(nyc_streets)在开始的早些时候已经安装完毕。该数据集包含名称和类型等详细信息。一个真实世界中的街道可能由许多线串组成，每一条线串代表具有不同属性的一段道路。
下面的SQL查询将返回与一个linestring关联的几何图形(在ST_AsText列中)。

SELECT ST_AsText(geom)
  FROM geometries
  WHERE name = 'Linestring';
//////////////////////////////////////////////////////////
LINESTRING(0 0, 1 1, 2 1, 2 2)

一些用于处理Linestrings的特定空间函数有:

• ST_Length(geometry) 返回行字符串的长度
• ST_StartPoint(geometry) 返回第一个坐标为一个点
• ST_EndPoint(geometry) 返回最后一个坐标为一个点
• ST_NPoints(geometry) 返回linestring中的坐标数
  所以，我们的linestring的长度是:

SELECT ST_Length(geom)
  FROM geometries
  WHERE name = 'Linestring';

3.41421356237309

1.3.3、Polygons image

多边形是面积的表示形式。这个多边形的外边界用一个环表示。这个环是一个linestring，就像上面定义的那样，它既是闭合的又是简单的。多边形内的洞也用环表示。
多边形用于表示其大小和形状很重要的对象。城市边界、公园、建筑足迹或水体通常都用多边形表示，当比例足够高时，可以看到它们的区域。道路和河流有时可以用多边形表示。
下面的SQL查询将返回与一个多边形关联的几何图形(在ST_AsText列中)。

SELECT ST_AsText(geom)
 FROM geometries
 WHERE name LIKE 'Polygon%';

我们不是在WHERE子句中使用=符号，而是使用LIKE操作符来执行字符串匹配操作。您可能习惯于使用' ' * ' '符号作为模式匹配的"通配符"，但在SQL中使用' ' % ' '符号，并使用LIKE操作符告诉系统进行通配符匹配。

POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))
POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))

第一个多边形只有一个环。第二个有一个内部的“洞”。大多数图形系统都包含“多边形”的概念，但GIS系统在允许多边形显式地有孔方面相对独特。 image

用于处理多边形的一些特定空间函数:

• ST_Area(geometry) 返回多边形的面积
• ST_NRings(geometry) 返回环的数量(通常为1个，如果有洞则更多)
• ST_ExteriorRing(geometry) 将外层环作为linestring返回
• ST_InteriorRingN(geometry,n)返回指定的内环作为linestring
• ST_Perimeter(geometry) 返回所有环的长度

我们可以使用面积函数计算多边形的面积:

SELECT name, ST_Area(geom)
  FROM geometries
  WHERE name LIKE 'Polygon%';
////////////////////////////////////////////////////////
Polygon            1
PolygonWithHole    99

! 注意，有孔的多边形的面积是外壳的面积(10x10的正方形)减去孔的面积(1x1的正方形)。

1.3.4 Collections

有四种集合类型，它们将多个简单几何组合成集合。
MultiPoint,点集合

MultiLineString, 线集合

MultiPolygon, 面集合

GeometryCollection,任何几何形状的异构集合(包括其他集合)
集合是GIS软件中比一般图形软件更常见的另一个概念。它们对于直接将真实世界对象建模为空间对象是有用的。例如，如何为许多被道路分割的东西建模?作为一个多多边形，其部分位于道路右侧的任意一侧。

image
我们的示例集合包含一个多边形和一个点:

SELECT name, ST_AsText(geom)
  FROM geometries
  WHERE name = 'Collection';

//////////////////////////////////////////
GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))

image

使用集合的一些特定空间函数:

• ST_NumGeometries(geometry) 返回集合中图形的数量
• ST_GeometryN(geometry,n) 返回指定的部分
• ST_Area(geometry) 返回所有多边形部分的总面积
• ST_Length(geometry) 返回所有线性部分的总长度

1.4 几何图形输入输出
在数据库中，几何图形以PostGIS程序使用的格式存储在磁盘上。为了让外部程序插入和检索有用的几何图形，需要将它们转换为其他应用程序可以理解的格式。幸运的是，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
    构造函数最常见的用法是将几何图形的文本表示转换为内部表示:
    请注意，除了具有几何表示的文本参数外，我们还使用一个数字参数提供几何的SRID。
    下面的SQL查询显示了WKB表示的示例(需要调用encode()将二进制输出转换为用于打印的ASCII格式):

SELECT encode(
  ST_AsBinary(ST_GeometryFromText('LINESTRING(0 0,1 0)')),
  'hex');

01020000000200000000000000000000000000000000000000000000000000f03f0000000000000000

出于本课程的目的，我们将继续使用WKT，以确保您能够阅读和理解我们正在查看的几何图形。然而，大多数实际的处理，如在GIS应用程序中查看数据、将数据传输到web服务或远程处理数据，都选择WKB格式。
因为WKT和WKB是在SFSQL规范中定义的，所以它们不处理3维或4维几何图形。针对这些情况，PostGIS定义了扩展已知文本(EWKT)和扩展已知二进制(EWKB)格式。它们提供了与WKT和WKB相同的格式化功能，只是增加了维度。
以下是WKT中的一个3D linestring示例:

SELECT ST_AsText(ST_GeometryFromText('LINESTRING(0 0 0,1 0 0,1 1 2)'));


LINESTRING Z (0 0 0,1 0 0,1 1 2)

注意，文本表示方式会发生变化!这是因为PostGIS的文本输入例程兼容性较好。它兼容下列格式：

• hex-encoded EWKB,

• extended well-known text, and

• ISO standard well-known text.
  在输出端，ST_AsText函数是保守的，只发出ISO标准的wkt。
  除了ST_GeometryFromText函数，还有许多其他方法可以wkt或类似的格式化输入创建几何图形:

-- Using ST_GeomFromText with the SRID parameter
SELECT ST_GeomFromText('POINT(2 2)',4326);

-- Using ST_GeomFromText without the SRID parameter
SELECT ST_SetSRID(ST_GeomFromText('POINT(2 2)'),4326);

-- Using a ST_Make* function
SELECT ST_SetSRID(ST_MakePoint(2, 2), 4326);

-- Using PostgreSQL casting syntax and ISO WKT
SELECT ST_SetSRID('POINT(2 2)'::geometry, 4326);

-- Using PostgreSQL casting syntax and extended WKT
SELECT 'SRID=4326;POINT(2 2)'::geometry;

除了针对各种形式的类型(WKT、WKB、GML、KML、JSON、SVG)， PostGIS也有针对四种形式的输出(WKT、WKB、GML、KML)。大多数应用程序使用WKT或WKB几何创建函数，但其他应用程序也可以工作。下面是一个使用GML和输出JSON的示例:

SELECT ST_AsGeoJSON(ST_GeomFromGML('<gml:Point><gml:coordinates>1,1</gml:coordinates></gml:Point>'));

image

1.5 从文本转换
到目前为止，我们看到的WKT字符串类型为' text '，我们已经使用PostGIS函数，如ST_GeomFromText()将它们转换为' geometry '类型。
PostgreSQL包含一个短格式语法，允许数据从一种类型转换为另一种类型，即强制转换语法oldata::newtype。例如，这个SQL将double转换为文本字符串。

SELECT 0.9::text;

更简单的是，这个SQL将一个WKT字符串转换为一个几何形状:

SELECT 'POINT(0 0)'::geometry;

使用强制类型创建几何图形需要注意的一点是:除非指定SRID，否则将得到一个SRID未知的几何图形。你可以使用EWKT的文本形式指定SRID，它在前面包含一个SRID块:

SELECT 'SRID=4326;POINT(0 0)'::geometry;

在使用WKT以及几何和地理列(请参阅geography)时，使用强制转换符号是非常常见的。

1.6、函数列表

ST_Area: 如果是多边形或多多边形，返回曲面的面积。对于“几何”类型，面积是SRID单位。“地理”面积以平方米为单位。

ST_AsText:：返回不含SRID元数据的几何/地理的知名文本(WKT)表示。

ST_AsBinary: 返回不含SRID元数据的几何/地理的知名二进制(WKB)表示。

ST_EndPoint: 返回LINESTRING几何图形的最后一个点作为一个点。

ST_AsEWKB: 返回带有SRID元数据的几何图形的(WKB)表示。

ST_AsEWKT: 返回带有SRID元数据的几何图形的wkt(WKT)表示。

ST_AsGeoJSON: 返回几何图形的GeoJSON 表示.

ST_AsGML:返回几何图形的GML表示.

ST_AsKML: 以KML元素的形式返回几何图形。几个变种。默认版本=2，默认精度=15。

ST_AsSVG: 返回给定几何或地理对象的SVG路径数据中的Geometry。

ST_ExteriorRing:返回表示多边形几何的外部环的线字符串。如果几何形状不是多边形则返回NULL。不能用于MULTIPOLYGON

ST_GeometryN: 如果几何图形是GEOMETRYCOLLECTION、MULTIPOINT、MULTILINESTRING、multiccurve或MULTIPOLYGON，则返回基于1的第n个几何图形。否则,返回NULL。

ST_GeomFromGML: 以几何图形的GML表示作为输入，输出一个PostGIS几何对象。
ST_GeomFromKML:以几何图形的KML表示作为输入，输出一个PostGIS几何对象

ST_GeomFromText: 从WKT返回指定的ST_Geometry值

ST_GeomFromWKB: 从WKB几何表示(WKB)和可选的SRID创建几何实例。

ST_GeometryType: 返回ST_Geometry值的几何类型

ST_InteriorRingN: 返回多边形几何图形的第n个内线字符串环。如果几何形状不是多边形或给定的N超出范围，则返回NULL。

ST_Length: 如果是linestring或multilinestring，则返回几何图形的2d长度。几何是空间参考的单位，地理是米(默认球体)

ST_NDims: 返回几何图形的坐标尺寸为小整数。取值范围为:2、3、4。

ST_NPoints:返回几何图形中的点(顶点)数量。

ST_NRings: 如果几何体是一个多边形或多多边形返回环的数量。
ST_NumGeometries: ：如果几何是一个GEOMETRYCOLLECTION(或MULTI*)返回几何的数量，否则返回NULL。

ST_Perimeter: 返回ST_Surface或ST_MultiSurface值边界的长度测量值。(多个多边形,多边形)

ST_SRID: 返回在spatial_ref_sys表中定义的ST_Geometry的空间引用标识符。

ST_StartPoint: 返回LINESTRING几何图形的第一个点作为一个点。

ST_X: 返回点的X坐标，如果不可用则返回NULL。输入必须是一个点。

ST_Y: 返回点的Y坐标，如果不可用则返回NULL。输入必须是一个点。

2.练习
这里是我们到目前为止看到的所有函数的提醒。它们应该对练习有用!

• sum(expression) aggregate to return a sum for a set of records
• count(expression) aggregate to return the size of a set of records
• ST_GeometryType(geometry) returns the type of the geometry
• ST_NDims(geometry) returns the number of dimensions of the geometry
• ST_SRID(geometry) returns the spatial reference identifier number of the geometry
• ST_X(point) returns the X ordinate
• ST_Y(point) returns the Y ordinate
• ST_Length(linestring) returns the length of the linestring
• ST_StartPoint(geometry) returns the first coordinate as a point
• ST_EndPoint(geometry) returns the last coordinate as a point
• ST_NPoints(geometry) returns the number of coordinates in the linestring
• ST_Area(geometry) returns the area of the polygons
• ST_NRings(geometry) returns the number of rings (usually 1, more if there are holes)
• ST_ExteriorRing(polygon) returns the outer ring as a linestring
• ST_InteriorRingN(polygon, integer) returns a specified interior ring as a linestring
• ST_Perimeter(geometry) returns the length of all the rings
• ST_NumGeometries(multi/geomcollection) returns the number of parts in the collection
• ST_GeometryN(geometry, integer) returns the specified part of the collection
• ST_GeomFromText(text) returns geometry
• ST_AsText(geometry) returns WKT text
• ST_AsEWKT(geometry) returns EWKT text
• ST_GeomFromWKB(bytea) returns geometry
• ST_AsBinary(geometry) returns WKB bytea
• ST_AsEWKB(geometry) returns EWKB bytea
• ST_GeomFromGML(text) returns geometry
• ST_AsGML(geometry) returns GML text
• ST_GeomFromKML(text) returns geometry
• ST_AsKML(geometry) returns KML text
• ST_AsGeoJSON(geometry) returns JSON text
• ST_AsSVG(geometry) returns SVG text

还记得我们有可用的表:

image.png

2.1 练习
1.“西村（West Village）”的面积是多少?

SELECT ST_Area(geom)
  FROM nyc_neighborhoods
  WHERE name = 'West Village';

1044614.5296486

面积以平方米为单位。要得到以公顷为单位的面积，除以10000。以英亩为单位，除以4047。
2.Pelham St的几何类型是什么?长度呢?

SELECT
   ST_GeometryType(geom),
   ST_Length(geom)
  FROM nyc_streets
  WHERE name = 'Pelham St';


ST_MultiLineString
50.323

3.“Broad St”地铁站的GeoJSON表示是什么?

SELECT
 ST_AsGeoJSON(geom)
FROM nyc_subway_stations
WHERE name = 'Broad St';

{"type":"Point",
 "crs":{"type":"name","properties":{"name":"EPSG:26918"}},
 "coordinates":[583571.905921312,4506714.341192182]}

4.纽约市街道的总长度(单位是公里)是多少?(提示:空间数据的计量单位为米，一公里有1000米。)

SELECT Sum(ST_Length(geom)) / 1000
  FROM nyc_streets;


10418.9047172

5.曼哈顿的面积有多少英亩?(提示:nyc_census_blocks和nyc_neighborhoods都有一个boronname。)

SELECT Sum(ST_Area(geom)) / 4047
  FROM nyc_neighborhoods
  WHERE boroname = 'Manhattan';
13965.3201224118

或者

SELECT Sum(ST_Area(geom)) / 4047
  FROM nyc_census_blocks
  WHERE boroname = 'Manhattan';

14601.3987215548

6.最西边的地铁站是哪一个?

SELECT ST_X(geom), name
  FROM nyc_subway_stations
  ORDER BY ST_X(geom)
  LIMIT 1;

Tottenville

7.哥伦布环岛(Columbus Circle)有多长?

SELECT ST_Length(geom)
  FROM nyc_streets
  WHERE name = 'Columbus Cir';

308.34199

8.纽约市街道的长度是多少，用类型来总结?

SELECT type, Sum(ST_Length(geom)) AS length
FROM nyc_streets
GROUP BY type
ORDER BY length DESC;

                       type                       |      length
--------------------------------------------------+------------------
 residential                                      | 8629870.33786606
 motorway                                         | 403622.478126363
 tertiary                                         | 360394.879051303
 motorway_link                                    | 294261.419479668
 secondary                                        | 276264.303897926
 unclassified                                     | 166936.371604458
 primary                                          | 135034.233017947
 footway                                          | 71798.4878378096
 service                                          |  28337.635038596
 trunk                                            | 20353.5819826076
 cycleway                                         | 8863.75144825929
 pedestrian                                       | 4867.05032825026
 construction                                     | 4803.08162103562
 residential; motorway_link                       | 3661.57506293745
 trunk_link                                       | 3202.18981240201
 primary_link                                     | 2492.57457083536
 living_street                                    | 1894.63905457332
 primary; residential; motorway_link; residential | 1367.76576941335
 undefined                                        |  380.53861910346
 steps                                            | 282.745221342127
 motorway_link; residential                       |  215.07778911517