STV_Intersect: scalar function vs. transform function

The STV_Intersect functions are similar in purpose, but you use them differently.

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 `gid` value. The result is a polygon `gid` or, if no polygon contains the point, the result is NULL.	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)

STV_Intersect: scalar function vs. transform function

See also