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

处理表中的空间对象

1: 定义空间数据的表列
2: 从表中导出空间数据
3: 识别 Null 空间对象
4: 从 Shapefile 中加载空间数据
5: 使用 COPY 将空间数据加载到表中
6: 从表中检索作为熟知文本 (WKT) 的空间数据
7: 使用 GeoHash 数据
8: 使用 ST_Intersects 和 STV_Intersect 执行空间联接

8.1: 空间联接的最佳实践
8.2: 创建或刷新索引之前确保多边形的有效性
8.3: STV_Intersect：标量函数与转换函数
8.4: 使用 STV_Intersect 函数执行空间联接

8.4.1: 空间索引和 STV_Intersect

8.5: 何时使用 ST_Intersects 与 STV_Intersect

8.5.1: 使用 ST_Intersects 执行空间联接

1 - 定义空间数据的表列

要定义包含 GEOMETRY 和 GEOGRAPHY 数据的列，请使用以下命令：

=> CREATE TABLE [[db-name.]schema.]table-name (
   column-name GEOMETRY[(length)],
   column-name GEOGRAPHY[(length)]);

如果忽略长度规范，则默认列大小为 1 MB。最大列大小为 10 MB。不强制执行上限，但地理空间函数只能接受或返回不超过 10 MB 的空间数据。

在创建后，无法修改 GEOMETRY 或 GEOGRAPHY 列的大小或数据类型。如果创建的列大小不足，请创建一个具有所需大小的新列。然后从旧列复制数据，并从表中删除旧列。

无法向包含另一个 Vertica 数据库的空间数据的表导入数据或从中导出数据。

重要

列宽过大可能会影响性能。使用适合数据但不会过大的列宽。请参阅STV_MemSize。

2 - 从表中导出空间数据

可以将 Vertica 数据库表中的空间数据导出到 shapefile。

要将表中的空间数据导出到 shapefile：

以超级用户的身份设置 shapefile 导出目录。

=> SELECT STV_SetExportShapefileDirectory(USING PARAMETERS path = '/home/geo/temp');
               STV_SetExportShapefileDirectory
------------------------------------------------------------
 SUCCESS. Set shapefile export directory: [/home/geo/temp]
(1 row)

将空间数据导出到 shapefile。

=> SELECT STV_Export2Shapefile(*
              USING PARAMETERS shapefile = 'visualizations/city-data.shp',
                            shape = 'Polygon') OVER() FROM spatial_data;
 Rows Exported |                          File Path
---------------+----------------------------------------------------------------
       185873 | v_geo-db_node0001: /home/geo/temp/visualizations/city-data.shp
(1 row)

星号 (*) 值等同于列出 FROM 子句中的所有列。
在导出 shapefile 时可以指定子目录。
Shapefile 必须以文件扩展名 .shp 结束。

确认现在有三个文件显示在 shapefile 导出目录中。
```
$ ls
city-data.dbf  city-data.shp   city-data.shx
```

3 - 识别 Null 空间对象

可使用 Vertica IS NULL 和 IS NOT NULL 结构来识别 null GEOMETRY 和 GEOGRAPHY 对象。

以下示例使用下面的表，其中 id=2 的行在 geog 字段中包含 null 值。


=> SELECT id, ST_AsText(geom), ST_AsText(geog) FROM locations
   ORDER BY 1 ASC;
 id |           ST_AsText              |                 ST_AsText
----+----------------------------------+--------------------------------------
  1 | POINT (2 3)                      | POINT (-85 15)
  2 | POINT (4 5)                      |
  3 | POLYGON ((-1 2, 0 3, 1 2, -1 2)) | POLYGON ((-24 12, -15 23, -20 27, -24 12))
  4 | LINESTRING (-1 2, 1 5)           | LINESTRING (-42.74 23.98, -62.19 23.78)
(4 rows)

识别具有 null geog 值的所有行：

=> SELECT id, ST_AsText(geom), (ST_AsText(geog) IS NULL) FROM locations
   ORDER BY 1 ASC;
 id |            ST_AsText             | ?column?
----+----------------------------------+----------
  1 | POINT (2 3)                      | f
  2 | POINT (4 5)                      | t
  3 | POLYGON ((-1 2, 0 3, 1 2, -1 2)) | f
  4 | LINESTRING (-1 2, 1 5)           | f
(4 rows)

识别 geog 值不为 null 的行：

=> SELECT id, ST_AsText(geom), (ST_AsText(geog) IS NOT NULL) FROM locations
   ORDER BY 1 ASC;
 id |            st_astext             | ?column?
----+----------------------------------+----------
  1 | POINT (2 3)                      | t
  2 | POINT (4 5)                      | f
  3 | LINESTRING (-1 2, 1 5)           | t
  4 | POLYGON ((-1 2, 0 3, 1 2, -1 2)) | t
(4 rows)

4 - 从 Shapefile 中加载空间数据

Vertica 提供了加载和解析 Shapefile 中存储的空间数据的功能。Shapefile 描述了点、线和多边形。Shapefile 由三个必要的文件构成：所有三个文件都必须存在，并且位于同一目录内以定义几何图形：

.shp—包含几何数据。
.shx—包含几何图形的位置索引。
.dbf—包含每个几何图形的属性。

要从 shapefile 加载空间数据：

使用 STV_ShpCreateTable 生成 CREATE TABLE 语句。

=> SELECT STV_ShpCreateTable ( USING PARAMETERS file = '/home/geo/temp/shp-files/spatial_data.shp')
                               OVER() AS spatial_data;
           spatial_data
----------------------------------
 CREATE TABLE spatial_data(
   gid IDENTITY(64) PRIMARY KEY,
   uniq_id INT8,
   geom GEOMETRY(85)
);
(5 rows)

创建表。

=> CREATE TABLE spatial_data(
   gid IDENTITY(64) PRIMARY KEY,
   uniq_id INT8,
   geom GEOMETRY(85));

加载 Shapefile。

=> COPY spatial_data WITH SOURCE STV_ShpSource(file='/home/geo/temp/shp-files/spatial_data.shp')
    PARSER STV_ShpParser();
 Rows Loaded
-------------
          10
(1 row)

支持的 Shapefile 图形类型

下表列出了 Vertica 支持的 Shapefile 图形类型。

5 - 使用 COPY 将空间数据加载到表中

可以使用 COPY 语句将空间数据加载到 Vertica 中的表。

要使用 COPY 语句将数据加载到 Vertica：

创建表。

=> CREATE TABLE spatial_data (id INTEGER, geom GEOMETRY(200));
CREATE TABLE

使用以下数据创建一个名为 spatial.dat 的文本文件。

1|POINT(2 3)
2|LINESTRING(-1 2, 1 5)
3|POLYGON((-1 2, 0 3, 1 2, -1 2))

使用 COPY 将数据加载到表中。
```
=> COPY spatial_data (id, gx FILLER LONG VARCHAR(605), geom AS ST_GeomFromText(gx)) FROM LOCAL 'spatial.dat';
 Rows Loaded
-------------
           3
(1 row)
```
该语句指定了一个 LONG VARCHAR(32000000) 填充符，它是 WKT 的最大大小。必须指定足够大的填充器值，以容纳想要插入表中的最大 WKT。

6 - 从表中检索作为熟知文本 (WKT) 的空间数据

GEOMETRY 和 GEOGRAPHY 数据在 Vertica 表中存储为用户不可读的 LONG VARBINARY。可使用 ST_AsText 返回熟知文本 (WKT) 格式的空间数据。

要返回 WKT 格式的空间数据：

=> SELECT id, ST_AsText(geom) AS WKT FROM spatial_data;
 id |               WKT
----+----------------------------------
  1 | POINT (2 3)
  2 | LINESTRING (-1 2, 1 5)
  3 | POLYGON ((-1 2, 0 3, 1 2, -1 2))
(3 rows)

7 - 使用 GeoHash 数据

Vertica 支持 GeoHash。GeoHash 是一种地理编码系统，用于对越来越细化的空间参照系进行分层编码。GeoHash 中的每个附加字符都会向下钻取到地图的较小部分。

您可以使用 Vertica 从 GeoHash 生成空间数据，以及从空间数据生成 GeoHash。Vertica 支持以下用于 GeoHash 的函数：

ST_GeoHash - 返回指定几何形状的 GeoHash。
ST_GeomFromGeoHash - 返回指定 GeoHash 形状的多边形。
ST_PointFromGeoHash - 返回指定 GeoHash 的中心点。

例如，从单点生成全精度和部分精度的 GeoHash。

=> SELECT ST_GeoHash(ST_GeographyFromText('POINT(3.14 -1.34)')), LENGTH(ST_GeoHash(ST_GeographyFromText('POINT(3.14 -1.34)'))),
                     ST_GeoHash(ST_GeographyFromText('POINT(3.14 -1.34)') USING PARAMETERS numchars=5) partial_hash;
      ST_GeoHash      | LENGTH | partial_hash
----------------------+--------+--------------
 kpf0rkn3zmcswks75010 |     20 | kpf0r
(1 row)

此示例说明如何从点集合点对象生成 GeoHash。返回的多边形是包含该 GeoHash 的最小图块的几何对象。

=> SELECT ST_AsText(ST_GeomFromGeoHash(ST_GeoHash(ST_GeomFromText('MULTIPOINT(0 0, 0.0002 0.0001)')))) AS region_1,
                    ST_AsText(ST_GeomFromGeoHash(ST_GeoHash(ST_GeomFromText('MULTIPOINT(0.0001 0.0001, 0.0003 0.0002)')))) AS region_2;
 -[ RECORD 1 ]---------------------------------------------------------------------------------------------
    region_1 | POLYGON ((0 0, 0.00137329101562 0, 0.00137329101562 0.00137329101562, 0 0.00137329101562, 0 0))
    region_2 | POLYGON ((0 0, 0.010986328125 0, 0.010986328125 0.0054931640625, 0 0.0054931640625, 0 0))

8 - 使用 ST_Intersects 和 STV_Intersect 执行空间联接

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

计算不同区域内的移动呼叫密度，以确定新蜂窝站的位置。
确定在飓风影响区域内的家庭。
计算在某个邮政编码区域内生活的用户数量。
计算零售店在任意指定时间的顾客数量。

8.1 - 空间联接的最佳实践

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

在 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 提供了所有游客与艺术品相交所花的综合时间估算值。

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

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

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

创建表并加载空间数据。

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

使用 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;
```

8.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)

另请参阅

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

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

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

8.4.1 - 空间索引和 STV_Intersect

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

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

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

STV_Create_Index— 在索引中存储关于多边形的信息以提高性能。
STV_Describe_Index— 检索关于索引的信息。
STV_Drop_Index— 删除空间索引。
STV_Refresh_Index— 刷新空间索引。
STV_Rename_Index— 为空间索引重命名。

8.5 - 何时使用 ST_Intersects 与 STV_Intersect

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

在将一组几何图形与单个几何图形进行比较以查看其是否相交时，请使用 ST_Intersects 函数。
要确定一组点是否与一个大中型数据集中的一组多边形相交，请首先使用 STV_Create_Index 创建空间索引。然后，使用 STV_Intersect 函数之一返回相交的点-多边形对集合。

注意
只能对 GEOMETRY 数据执行空间联接。

8.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 函数之一。