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.
1SELECT p.parcel_id, n.nei_name 2 , CASE 3 WHEN ST_CoveredBy(p.geom, n.geom) 4 THEN p.geom 5 ELSE 6 ST_Multi( 7 ST_Intersection(p.geom,n.geom) 8 ) END As geom 9 FROM parcels As p 10 INNER JOIN neighborhoods As n 11 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
1SELECT p.parcel_id, n.nei_name 2 , CASE 3 WHEN ST_CoveredBy(p.geom, n.geom) 4 THEN p.geom 5 ELSE 6 ST_Multi( 7 ST_Intersection(p.geom,n.geom) 8 ) END As geom 9 FROM parcels As p 10 INNER JOIN neighborhoods As n 11 ON (ST_Intersects(p.geom, n.geom) 12 AND Not ST_Touches(p.geom, n.geom) );