STV_Intersect 变换函数
将点与多边形在空间上相交。STV_Intersect 变换函数返回包含匹配的点/多边形对的元组。对于每个点，Vertica 返回一个或多个匹配的多边形。
在多个节点上并行计算 STV_Intersect 变换函数可以提高性能。要执行并行计算，请使用 OVER(PARTITION BEST) 子句。
行为类型
不可变语法
STV_Intersect ( { gid | i }, { g | x , y }
USING PARAMETERS index='index_name')
OVER() AS (pt_gid, pol_gid)
参数
gid | i- 用于唯一标识 g 或 x 和 y 的空间对象的整数列或整数。
g- 包含点的几何或地理 (WGS84) 列。*g *列只能包含点几何图形或地理图形。如果该列包含其他几何或地理类型，STV_Intersect 将会终止并提示错误。
x- X 坐标或经度，FLOAT。
y- y 坐标或纬度，FLOAT。
参数
index = 'index_name'- 空间索引的名称，类型为 VARCHAR。
返回
pt_gid- 几何或地理点的唯一标识符，类型为 INTEGER。
pol_gid- 几何或地理多边形的唯一标识符，类型为 INTEGER。
示例
以下示例显示了如何使用 STV_Intersect 变换函数。
使用两个浮点数，返回匹配的点-多边形对。
=> CREATE TABLE pols (gid INT, geom GEOMETRY(1000));
CREATE TABLE
=> COPY pols(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.
>> 1|POLYGON((31 74,8 70,8 50,36 53,31 74))
>> \\.
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index='my_polygons_1', overwrite=true,
max_mem_mb=256) OVER() FROM pols;
type | polygons | SRID | min_x | min_y | max_x | max_y | info
----------+----------+------+-------+-------+-------+-------+------
GEOMETRY | 1 | 0 | 8 | 50 | 36 | 74 |
(1 row)
=> SELECT STV_Intersect(56, 12.5683, 55.6761 USING PARAMETERS index = 'my_polygons_1') OVER();
pt_gid | pol_gid
--------+---------
56 | 1
(1 row)
使用 GEOMETRY 列，返回匹配的点-多边形对。
=> CREATE TABLE polygons (gid int, geom GEOMETRY(700));
CREATE TABLE
=> COPY polygons (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.
>> 10|POLYGON((5 5, 5 10, 10 10, 10 5, 5 5))
>> 11|POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))
>> 12|POLYGON((1 1, 1 3, 3 3, 3 1, 1 1))
>> 14|POLYGON((-1 -1, -1 12, 12 12, 12 -1, -1 -1))
>> \\.
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index='my_polygons', overwrite=true, max_mem_mb=256)
OVER() FROM polygons;
type | polygons | SRID | min_x | min_y | max_x | max_y | info
----------+----------+------+-------+-------+-------+-------+------
GEOMETRY | 4 | 0 | -1 | -1 | 12 | 12 |
(1 row)
=> CREATE TABLE points (gid INT, geom GEOMETRY(700));
CREATE TABLE
=> COPY points (gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|POINT(9 9)
>> 2|POINT(0 1)
>> 3|POINT(2.5 2.5)
>> 4|POINT(0 0)
>> 5|POINT(1 5)
>> 6|POINT(1.5 1.5)
>> \\.
=> SELECT STV_Intersect(gid, geom USING PARAMETERS index='my_polygons') OVER (PARTITION BEST)
AS (point_id, polygon_gid)
FROM points;
point_id | polygon_gid
----------+-------------
5 | 14
1 | 14
1 | 10
4 | 14
4 | 11
6 | 12
6 | 14
6 | 11
2 | 14
2 | 11
3 | 12
3 | 14
(12 rows)
可通过在 WHERE 子句中使用 STV_Intersect 变换函数来提高查询性能。由于此语法会消除未与索引中的多边形相交的点，因此能够提高性能。
返回与 gid = 14 的多边形相交的点计数：
=> SELECT COUNT(pt_id) FROM
(SELECT STV_Intersect(gid, geom USING PARAMETERS index='my_polygons')
OVER (PARTITION BEST) AS (pt_id, pol_id) FROM points)
AS T WHERE pol_id = 14;
COUNT
-------
6
(1 row)