STV_Intersect: Scalar Function vs. Transform Function
The STV_Intersect
functions are similar in purpose, but you use them differently.
STV_Intersect Function Type |
Description | Performance |
---|---|---|
Scalar |
Matches a point to a polygon. If several polygons contain the point, this function returns a |
Eliminates points that do not intersect with any indexed polygons, avoiding unnecessary comparisons. |
Transform |
Matches a point to all the polygons that contain it. When a point does not intersect with any polygon in the index, the function returns no rows. |
Processes all input points regardless of whether or not they intersect with the indexed polygons. |
In the following example, the STV_Intersect
scalar function compares the points in the points
table to the polygons in a spatial index named my_polygons
. STV_Intersect
returns all points and polygons that match exactly:
=> 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)
The following example shows how to use the STV_Intersect
transform function to return information about the three point-polygon pairs that match and each of the polygons they match:
=> 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)