这是本节的多页打印视图。 点击此处打印.

返回本页常规视图.

使用 ST_Intersects 和 STV_Intersect 执行空间联接

利用空间联接可以确定两组空间数据之间的空间关系。例如，可使用空间联接：

  • 计算不同区域内的移动呼叫密度，以确定新蜂窝站的位置。

  • 确定在飓风影响区域内的家庭。

  • 计算在某个邮政编码区域内生活的用户数量。

  • 计算零售店在任意指定时间的顾客数量。

1 - 空间联接的最佳实践

使用以下最佳实践来提高总体性能并优化空间查询。

在 Vertica 中使用空间联接的最佳实践包括：

  • 通过表分段提高创建索引的速度

  • 适当调整 Geometry 列的大小以存储点数据

  • 在 COPY 语句中使用 STV_GeometryPoint，将熟知文本 (WKT) 直接加载到 Geometry 列中。

  • 将 OVER (PARTITION BEST) 与 STV_Intersect 变换查询配合使用

最佳实践示例

在执行以下示例中的步骤之前，请从 Vertica Place GitHub 存储库 (https://github.com/vertica/Vertica-Geospatial) 下载 place_output.csv.zip。需要使用此存储库中的数据集。

  1. 创建用于多边形的表。使用适合数据但不会过大的 GEOMETRY 列宽。合适的列宽有助于提高性能。此外，通过哈希处理对表进行分段有利于并行计算。

    => CREATE TABLE artworks (gid int, g GEOMETRY(700)) SEGMENTED BY HASH(gid) ALL NODES;

  2. 将 Copy 语句与 ST_Buffer 配合使用，创建并加载对其运行相交函数的多边形。通过在 Copy 语句中使用 ST_Buffer，可使用该函数创建多边形。

    => COPY artworks(gid, gx FILLER LONG VARCHAR, g AS ST_Buffer(ST_GeomFromText(gx),8)) FROM STDIN DELIMITER ',';
>> 1, POINT(10 45)
>> 2, POINT(25 45)
>> 3, POINT(35 45)
>> 4, POINT(35 15)
>> 5, POINT(30 5)
>> 6, POINT(15 5)
>> \.

  3. 创建用于位置数据（表示为点）的表。可以将点数据存储在大小为 100 字节的 GEOMETRY 列中。避免设置过大的 GEOMETRY 列。否则可能严重影响空间相交的性能。此外，通过哈希处理对表进行分段有利于并行计算。

    => CREATE TABLE usr_data (gid identity, usr_id int, date_time timestamp, g GEOMETRY(100))
     SEGMENTED BY HASH(gid) ALL NODES;

  4. 在执行 Copy 语句的过程中，将原始位置数据转换为 GEOMETRY 数据。由于位置数据需要使用 GEOMETRY 数据类型，因此必须执行此变换。使用函数 STV_GeometryPoint 变换源表的 x 和 y 列。

    => COPY usr_data (usr_id, date_time, x FILLER LONG VARCHAR,
                  y FILLER LONG VARCHAR, g AS STV_GeometryPoint(x, y))
   FROM LOCAL 'place_output.csv' DELIMITER ',' ENCLOSED BY '';

  5. 创建用于多边形的空间索引。此索引有助于加快相交计算的速度。

    => SELECT STV_Create_Index(gid, g USING PARAMETERS index='art_index', overwrite=true) OVER() FROM artworks;

  6. 编写用于返回每个多边形的相交数量的分析查询。指定 Vertica 忽略与给定多边形相交次数少于 20 次的任何 usr_id。

    => SELECT pol_gid,
       COUNT(DISTINCT(usr_id)) AS count_user_visit
   FROM
     (SELECT pol_gid,
       usr_id,
       COUNT(usr_id) AS user_points_in
    FROM
       (SELECT STV_Intersect(usr_id, g USING PARAMETERS INDEX='art_index') OVER(PARTITION BEST) AS (usr_id,
                                                        pol_gid)
    FROM usr_data
      WHERE date_time BETWEEN '2014-07-02 09:30:20' AND '2014-07-02 17:05:00') AS c
    GROUP BY pol_gid,
    usr_id HAVING COUNT(usr_id) > 20) AS real_visits
    GROUP BY pol_gid
    ORDER BY count_user_visit DESC;

示例查询中的优化

此查询采用了以下优化：

  • 断言的时间出现在子查询中。

  • 使用位置数据表避免需要巨大开销的联接操作。

  • 查询使用 OVER (PARTITION BEST)，通过对数据进行分区提高了性能。

  • user_points_in 提供了所有游客与艺术品相交所花的综合时间估算值。

2 - 创建或刷新索引之前确保多边形的有效性

当 Vertica 创建或更新空间索引时，它不会检查多边形的有效性。为防止查询空间索引时获得无效结果，您应在创建或更新空间索引之前检查多边形的有效性。

以下示例向您展示如何检查多边形的有效性。

  1. 创建表并加载空间数据。

    => CREATE TABLE polygon_validity_test (gid INT, geom GEOMETRY);
CREATE TABLE
=> COPY polygon_validity_test (gid, gx FILLER LONG VARCHAR, geom AS St_GeomFromText(gx)) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 2|POLYGON((-31 74,8 70,8 50,-36 53,-31 74))
>> 3|POLYGON((-38 50,4 13,11 45,0 65,-38 50))
>> 4|POLYGON((-12 42,-12 42,27 48,14 26,-12 42))
>> 5|POLYGON((0 0,1 1,0 0,2 1,1 1,0 0))
>> 6|POLYGON((3 3,2 2,2 1,2 3,3 3))
>> \.

  2. 使用 ST_IsValid 和 STV_IsValidReason 查找任何无效的多边形。

    => SELECT gid, ST_IsValid(geom), STV_IsValidReason(geom) FROM polygon_validity_test;
 gid | ST_IsValid |            STV_IsValidReason
-----+------------+------------------------------------------
   4 | t          |
   6 | f          | Self-intersection at or near POINT (2 1)
   2 | t          |
   3 | t          |
   5 | f          | Self-intersection at or near POINT (0 0)
(5 rows)

既然我们已经在表中识别出无效的多边形，因此在创建或刷新空间索引时，我们可以通过几种不同的方式来处理无效的多边形。

使用 WHERE 子句筛选无效多边形

此方法比创建索引之前进行筛选要慢，因为它在执行时将检查每个多边形的有效性。

以下示例向您展示如何使用 WHERE 子句排除无效多边形。

```
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index = 'valid_polygons') OVER()
   FROM polygon_validity_test
   WHERE ST_IsValid(geom) = 't';
```

创建或刷新索引之前筛选无效多边形

此方法比使用 WHERE 子句进行筛选更快，因为在构建索引之前会降低性能。

以下示例向您展示如何创建一个排除无效多边形的新表，以此来排除无效多边形。

```
=> CREATE TABLE polygon_validity_clean AS
   SELECT *
   FROM polygon_validity_test
   WHERE ST_IsValid(geom) = 't';
CREATE TABLE
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index = 'valid_polygons') OVER()
   FROM polygon_validity_clean;
```

3 - STV_Intersect：标量函数与转换函数

这些 STV_Intersect 函数的用途相似，但用法不同。

在下面的示例中，STV_Intersect 标量函数会将 points 表中的点与名为 my_polygons 的空间索引中的多边形进行比较。 STV_Intersect 将会返回所有完全匹配的点和多边形：


=> SELECT gid AS pt_gid
   STV_Intersect(geom USING PARAMETERS index='my_polygons') AS pol_gid
   FROM points ORDER BY pt_gid;
 pt_gid | pol_gid
--------+---------
    100 |       2
    101 |
    102 |       2
    103 |
    104 |
    105 |       3
    106 |
    107 |
 (8 rows)

以下示例显示了如何使用 STV_Intersect 变换函数来返回关于匹配的三个点-多边形对以及它们所匹配的每个多边形的信息：


=> SELECT STV_Intersect(gid, geom
   USING PARAMETERS index='my_polygons')
   OVER (PARTITION BEST) AS (pt_gid, pol_id)
   FROM points;
 pt_gid | pol_id
--------+--------
    100 |      1
    100 |      2
    100 |      3
    102 |      2
    105 |      3
(3 rows)

另请参阅

4 - 使用 STV_Intersect 函数执行空间联接

假定您想要处理一个大中型空间数据集，并确定哪些点与哪些多边形相交。这种情况下，请首先使用 STV_Create_Index 创建一个空间索引。空间索引提高了访问多边形集的效率。

然后，使用 STV_Intersect 标量或变换函数确定匹配的点-多边形对。

4.1 - 空间索引和 STV_Intersect

在使用 STV_Intersect 函数之一执行空间联接之前，必须首先运行 STV_Create_Index，创建一个包含多边形相关信息的数据库对象。此对象被称为多边形集的空间索引。空间索引缩短了 STV_Intersect 函数访问多边形数据所用的时间。

Vertica 在全局空间中创建空间索引。因此，任何有权访问 STV_*_Index 函数的用户都可以描述、重命名或删除其他任何用户创建的索引。

Vertica 提供了多个与空间索引配合使用的函数：

5 - 何时使用 ST_Intersects 与 STV_Intersect

Vertica 提供了两种功能来识别一组点是否与一组多边形相交。根据数据集的大小，选择可提供最佳性能的方法：

  • 在将一组几何图形与单个几何图形进行比较以查看其是否相交时，请使用 ST_Intersects 函数。

  • 要确定一组点是否与一个大中型数据集中的一组多边形相交，请首先使用 STV_Create_Index 创建空间索引。然后，使用 STV_Intersect 函数之一返回相交的点-多边形对集合。

5.1 - 使用 ST_Intersects 执行空间联接

ST_Intersects 确定两个 GEOMETRY 对象是否在一个点相交或接触。

当需要确定一个列中的小型几何图形集与给定的几何图形是否相交时，可使用 ST_Intersects。

示例

以下示例使用 ST_Intersects 将一列点几何图形与单个多边形进行比较。包含点的表有 1 百万行。

ST_Intersects 仅返回与多边形相交的点。这些点约占表中点数的 0.01%：

=> CREATE TABLE points_1m(gid IDENTITY, g GEOMETRY(100)) ORDER BY g;
=> COPY points_1m(wkt FILLER LONG VARCHAR(100), g AS ST_GeomFromText(wkt))
   FROM LOCAL '/data/points.dat';
 Rows Loaded
-------------
     1000000
(1 row)
=> SELECT ST_AsText(g) FROM points_1m WHERE
   ST_Intersects
     (
      g,
      ST_GeomFromText('POLYGON((-71 42, -70.9 42, -70.9 42.1, -71 42.1, -71 42))')
      );
         st_astext
----------------------------
 POINT (-70.97532 42.03538)
 POINT (-70.97421 42.0376)
 POINT (-70.99004 42.07538)
 POINT (-70.99477 42.08454)
 POINT (-70.99088 42.08177)
 POINT (-70.98643 42.07593)
 POINT (-70.98032 42.07982)
 POINT (-70.95921 42.00982)
 POINT (-70.95115 42.02177)
...
(116 rows)

Vertica 建议通过创建空间索引来测试两列几何图形的相交情况。使用STV_Intersect：标量函数与转换函数中所述的 STV_Intersect 函数之一。