This is the multipage printable view of this section.
Click here to print.
Return to the regular view of this page.
When to use ST_Intersects vs. STV_Intersect
Vertica provides two capabilities to identify whether a set of points intersect with a set of polygons.
Vertica provides two capabilities to identify whether a set of points intersect with a set of polygons. Depending on the size of your data set, choose the approach that gives the best performance:

When comparing a set of geometries to a single geometry to see if they intersect, use the ST_Intersects function.

To determine if a set of points intersects with a set of polygons in a mediumtolarge data set, first create a spatial index using STV_Create_Index
. Then, use one of the STV_Intersect
functions to return the set of pairs that intersect.
Note
You can only perform spatial joins on GEOMETRY data.
1  Performing spatial joins with ST_Intersects
The ST_Intersects function determines if two GEOMETRY objects intersect or touch at a single point.
The ST_Intersects function determines if two GEOMETRY objects intersect or touch at a single point.
Use ST_Intersects when you want to identify if a small set of geometries in a column intersect with a given geometry.
Example
The following example uses ST_Intersects to compare a column of point geometries to a single polygon. The table that contains the points has 1 million rows.
ST_Intersects returns only the points that intersect with the polygon. Those points represent about 0.01% of the points in the table:
=> 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 recommends that you test the intersections of two columns of geometries by creating a spatial index. Use one of the STV_Intersect functions as described in STV_Intersect: scalar function vs. transform function.