STV_Intersect Transform Function
Spatially intersects points and polygons. The STV_Intersect transform function returns a tuple with matching point/polygon pairs. For every point, Vertica returns either one or many matching polygons.
You can improve performance when you parallelize the computation of the STV_Intersect transform function over multiple nodes. To parallelize the computation, use an OVER(PARTITION BEST) clause.
Behavior Type
Syntax
STV_Intersect ( { gid | i }, { g | x , y } USING PARAMETERS index='index_name') OVER() AS (pt_gid, pol_gid)
Arguments
gid | i |
An integer column or integer that uniquely identifies the spatial object(s) of g or x and y. |
g |
A geometry or geography (WGS84) column that contains points. The g column can contain only point geometries or geographies. If the column contains a different geometry or geography type, STV_Intersect terminates with an error. |
x |
x-coordinate or longitude, FLOAT. |
y |
y-coordinate or latitude, FLOAT. |
Parameters
index = 'index_name' |
Name of the spatial index, of type VARCHAR. |
Returns
pt_gid |
Unique identifier of the point geometry or geography, of type INTEGER. |
pol_gid |
Unique identifier of the polygon geometry or geography, of type INTEGER. |
Examples
The following examples show how you can use STV_Intersect transform.
Using two floats, return the matching point-polygon pairs.
=> 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)
Using a GEOMETRY column, return the matching point-polygon pairs.
=> 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)
You can improve query performance by using the STV_Intersect transform function in a WHERE clause. Performance improves because this syntax eliminates all points that do not intersect polygons in the index.
Return the count of points that intersect with the polygon, where 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)