16.4. Lesson: 空间查询

空间查询与其他数据库查询没有什么不同。可以像使用任何其他数据库列一样使用几何图形列。在我们的数据库中安装了PostGIS后，我们就有了查询数据库的附加功能。

The goal for this lesson: 来看看空间函数是如何实现的，类似于“正常”的非空间函数。

16.4.1. 空间运算符

当您想知道哪些点与点(X，Y)的距离在2度范围内时，可以使用以下命令：

select *
from people
where st_distance(the_geom,'SRID=4326;POINT(33 -34)') < 2;

结果：

 id |     name     | house_no | street_id |   phone_no    |   the_geom
----+--------------+----------+-----------+---------------+---------------
  6 | Fault Towers |       34 |         3 | 072 812 31 28 | 01010008040C0
(1 row)

备注

上面的_geom值在此页面上的空间被截断。如果您想要在人类可读的坐标中查看该点，请尝试类似于上面“将点视为wkt”一节中所做的操作。

我们如何知道上面的查询返回的是2内的所有点 degrees ？为什么不是2 meters ？或任何其他单位，就这一点而言？

回答

示例查询使用的单位是度，因为层使用的CRS是WGS 84。这是一个地理CRS，这意味着它的单位是度。像UTM预测一样，预测的CRS以米为单位。

请记住，当您编写查询时，您需要知道层的CRS在哪些单位中。这将允许您编写一个返回预期结果的查询。

16.4.2. 空间索引

我们还可以定义空间索引。空间索引使您的空间查询速度更快。要在几何图形列上创建空间索引，请使用：

CREATE INDEX people_geo_idx
  ON people
  USING gist
  (the_geom);

\d people

结果：

Table "public.people"
   Column   |         Type          |                Modifiers
 -----------+-----------------------+----------------------------------------
  id        | integer               | not null default
            |                       | nextval('people_id_seq'::regclass)
  name      | character varying(50) |
  house_no  | integer               | not null
  street_id | integer               | not null
  phone_no  | character varying     |
  the_geom  | geometry              |
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_geo_idx" gist (the_geom)  <-- new spatial key added
  "people_name_idx" btree (name)
Check constraints:
  "people_geom_point_chk" CHECK (st_geometrytype(the_geom) = 'ST_Point'::text
  OR the_geom IS NULL)
Foreign-key constraints:
  "people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

16.4.3. Try Yourself

修改Cities表，使其几何图形列在空间上建立索引。

回答

CREATE INDEX cities_geo_idx
  ON cities
  USING gist (the_geom);

16.4.4. PostGIS空间功能演示

为了演示PostGIS空间功能，我们将创建一个包含一些(虚拟)数据的新数据库。

首先，创建一个新数据库(首先退出psqlShell)：

createdb postgis_demo

请记住安装Postgis扩展：

psql -d postgis_demo -c "CREATE EXTENSION postgis;"

接下来，导入 exercise_data/postgis/ 目录。有关说明，请参考上一课，但请记住，您将需要创建一个新的到新数据库的PostGIS连接。您可以从终端或通过数据库管理器导入。将文件导入到以下数据库表中：

• points.shp into building

• lines.shp into road

• polygons.shp into region

将这三个数据库层加载到QGIS中 Add PostGIS Layers 对话框，像往常一样。打开它们的属性表时，您会注意到它们都具有 id 字段和一个 gid 由PostGIS导入创建的字段。

现在已经导入了表，我们可以使用PostGIS来查询数据。返回到您的终端(命令行)，并通过运行以下命令进入psql提示符：

psql postgis_demo

我们将通过创建视图来演示其中的一些SELECT语句，这样您就可以在QGIS中打开它们并查看结果。

按位置选择

获取夸祖鲁地区的所有建筑：

SELECT a.id, a.name, st_astext(a.the_geom) as point
  FROM building a, region b
    WHERE st_within(a.the_geom, b.the_geom)
    AND b.name = 'KwaZulu';

结果：

 id | name |                  point
----+------+------------------------------------------
 30 | York | POINT(1622345.23785063 6940490.65844485)
 33 | York | POINT(1622495.65620524 6940403.87862489)
 35 | York | POINT(1622403.09106394 6940212.96302097)
 36 | York | POINT(1622287.38463732 6940357.59605424)
 40 | York | POINT(1621888.19746548 6940508.01440885)
(5 rows)

或者，如果我们从它创建一个视图：

CREATE VIEW vw_select_location AS
  SELECT a.gid, a.name, a.the_geom
    FROM building a, region b
      WHERE st_within(a.the_geom, b.the_geom)
      AND b.name = 'KwaZulu';

将该视图添加为一个层，并在QGIS中进行查看：

选择邻居

显示与北海道地区相邻的所有地区名称的列表：

SELECT b.name
  FROM region a, region b
    WHERE st_touches(a.the_geom, b.the_geom)
    AND a.name = 'Hokkaido';

结果：

    name
--------------
 Missouri
 Saskatchewan
 Wales
(3 rows)

作为一种观点：

CREATE VIEW vw_regions_adjoining_hokkaido AS
  SELECT b.gid, b.name, b.the_geom
    FROM region a, region b
      WHERE st_touches(a.the_geom, b.the_geom)
      AND a.name = 'Hokkaido';

在QGIS中：

请注意缺失的区域(昆士兰)。这可能是由于拓扑错误造成的。像这样的人工制品可以提醒我们数据中的潜在问题。为了在不陷入数据可能出现的异常的情况下解开这个谜团，我们可以使用一个缓冲区交集：

CREATE VIEW vw_hokkaido_buffer AS
  SELECT gid, ST_BUFFER(the_geom, 100) as the_geom
    FROM region
      WHERE name = 'Hokkaido';

这在北海道地区周围形成了100米的缓冲区。

较暗的区域是缓冲区：

选择使用缓冲区：

CREATE VIEW vw_hokkaido_buffer_select AS
  SELECT b.gid, b.name, b.the_geom
    FROM
    (
      SELECT * FROM
        vw_hokkaido_buffer
    ) a,
    region b
    WHERE ST_INTERSECTS(a.the_geom, b.the_geom)
    AND b.name != 'Hokkaido';

在此查询中，原始缓冲区视图与任何其他表一样使用。它被赋予别名 a ，及其几何场， a.the_geom ，用于选择 region 表(别名 b )它与之相交。然而，北海道本身被排除在这个SELECT语句之外，因为我们不想要它；我们只想要毗邻它的地区。

在QGIS中：

也可以选择给定距离内的所有对象，而无需额外创建缓冲区：

CREATE VIEW vw_hokkaido_distance_select AS
  SELECT b.gid, b.name, b.the_geom
    FROM region a, region b
      WHERE ST_DISTANCE (a.the_geom, b.the_geom) < 100
      AND a.name = 'Hokkaido'
      AND b.name != 'Hokkaido';

这实现了相同的结果，而不需要临时缓冲步骤：

选择唯一值

显示昆士兰地区所有建筑的唯一城镇名称列表：

SELECT DISTINCT a.name
  FROM building a, region b
    WHERE st_within(a.the_geom, b.the_geom)
    AND b.name = 'Queensland';

结果：

  name
---------
 Beijing
 Berlin
 Atlanta
(3 rows)

再举几个例子。

CREATE VIEW vw_shortestline AS
  SELECT b.gid AS gid,
        ST_ASTEXT(ST_SHORTESTLINE(a.the_geom, b.the_geom)) as text,
        ST_SHORTESTLINE(a.the_geom, b.the_geom) AS the_geom
    FROM road a, building b
      WHERE a.id=5 AND b.id=22;

CREATE VIEW vw_longestline AS
  SELECT b.gid AS gid,
         ST_ASTEXT(ST_LONGESTLINE(a.the_geom, b.the_geom)) as text,
         ST_LONGESTLINE(a.the_geom, b.the_geom) AS the_geom
    FROM road a, building b
      WHERE a.id=5 AND b.id=22;

CREATE VIEW vw_road_centroid AS
  SELECT a.gid as gid, ST_CENTROID(a.the_geom) as the_geom
    FROM road a
      WHERE a.id = 1;

CREATE VIEW vw_region_centroid AS
  SELECT a.gid as gid, ST_CENTROID(a.the_geom) as the_geom
    FROM region a
      WHERE a.name = 'Saskatchewan';

SELECT ST_PERIMETER(a.the_geom)
  FROM region a
    WHERE a.name='Queensland';

SELECT ST_AREA(a.the_geom)
  FROM region a
    WHERE a.name='Queensland';

CREATE VIEW vw_simplify AS
  SELECT gid, ST_Simplify(the_geom, 20) AS the_geom
    FROM road;

CREATE VIEW vw_simplify_more AS
  SELECT gid, ST_Simplify(the_geom, 50) AS the_geom
    FROM road;

CREATE VIEW vw_convex_hull AS
  SELECT
    ROW_NUMBER() over (order by a.name) as id,
    a.name as town,
    ST_CONVEXHULL(ST_COLLECT(a.the_geom)) AS the_geom
    FROM building a
    GROUP BY a.name;

16.4.5. In Conclusion

您已经了解了如何使用PostGIS中的新数据库函数来查询空间对象。

16.4.6. What's Next?

接下来，我们将研究更复杂的几何图形的结构，以及如何使用PostGIS创建它们。