空间联接的最佳实践

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

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

通过表分段提高创建索引的速度
适当调整 Geometry 列的大小以存储点数据
在 COPY 语句中使用 STV_GeometryPoint，将熟知文本 (WKT) 直接加载到 Geometry 列中。
将 OVER (PARTITION BEST) 与 STV_Intersect 变换查询配合使用

最佳实践示例

注意

以下示例与博物馆空间数据的使用有关，最早发布在一篇 Vertica 博客帖子中。要阅读整个博客，请参阅在 Vertica Place 中使用位置数据

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

创建用于多边形的表。使用适合数据但不会过大的 GEOMETRY 列宽。合适的列宽有助于提高性能。此外，通过哈希处理对表进行分段有利于并行计算。
```
=> CREATE TABLE artworks (gid int, g GEOMETRY(700)) SEGMENTED BY HASH(gid) ALL NODES;
```

将 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)
>> \.

创建用于位置数据（表示为点）的表。可以将点数据存储在大小为 100 字节的 GEOMETRY 列中。避免设置过大的 GEOMETRY 列。否则可能严重影响空间相交的性能。此外，通过哈希处理对表进行分段有利于并行计算。
```
=> CREATE TABLE usr_data (gid identity, usr_id int, date_time timestamp, g GEOMETRY(100))
     SEGMENTED BY HASH(gid) ALL NODES;
```
在执行 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 '';
```

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

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

编写用于返回每个多边形的相交数量的分析查询。指定 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 提供了所有游客与艺术品相交所花的综合时间估算值。