Getting intersections the faster way
ST_Intersection is much slower than relation checks such as
ST_CoveredBy, and ,
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:
- geometry a is covered by geometry b -> intersection is geometry a
- geometry b is covered by geometry a -> intersection is geometry b
- 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
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
for geometries to be wholly within the boundary of another and tends to be a bit faster to compute than
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
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
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) );