Getting intersections the faster way

Doing an ST_Intersection is much slower than relation checks such as ST_Intersects , ST_CoveredBy, and , ST_Within . In many situations you know the intersection of 2 geometries without actually computing an intersection. In these cases, you can skip the costly ST_Intersection call. Cases like this:

1. geometry a is covered by geometry b -> intersection is geometry a
2. geometry b is covered by geometry a -> intersection is geometry b
3. geometry a does not intersect geometry b -> intersection is empty geometry

This kind of question comes up a lot: As discussed in stackexchange Acquiring ArcGIS speed in PostGIS

Examples

For this exercise, we’ll grab the portion of each parcel that falls in a neighborhood. Unlike stack exchange one, we’ll use ST_CoveredBy instead of ST_Within check. Both constructs are similar except that ST_CoveredBy allows for geometries to be wholly within the boundary of another and tends to be a bit faster to compute than ST_Within. This subtlety becomes more important if you are comparing linestrings such as what portion of a road falls in a county as detailed in Subtleties OGC Covers Spatial.

SELECT p.parcel_id, n.nei_name
 , CASE
   WHEN ST_CoveredBy(p.geom, n.geom)
   THEN p.geom
   ELSE
    ST_Multi(
      ST_Intersection(p.geom,n.geom)
      ) END As geom
 FROM parcels As p
   INNER JOIN neighborhoods As n
    ON ST_Intersects(p.geom, n.geom);

In this particular case, we’d probably want to exclude the case where a parcel just borders a neighborhood (in spatial speak touches). We wouldn’t really consider a bordering parcel as having any part in the neighborhood, though it intersects the neighborhood. So a slightly more complicated but more accurate statement would be to exclude from consideration the case where a parcel borders a neighborhood using ST_Touches.

SELECT p.parcel_id, n.nei_name
 , CASE
   WHEN ST_CoveredBy(p.geom, n.geom)
   THEN p.geom
   ELSE
    ST_Multi(
      ST_Intersection(p.geom,n.geom)
      ) END As geom
 FROM parcels As p
   INNER JOIN neighborhoods As n
    ON (ST_Intersects(p.geom, n.geom)
      AND Not ST_Touches(p.geom, n.geom) );