Performing spatial joins with ST_Intersects

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.