Efficient combination of st_intersects and not st_touches

Question

I'm attempting to an efficient way to find "overlapping" polygons. Specifically we have a bunch of postal code/city/neighborhood data where I specifically don't want the default behavior of st_intersects that returns polygons that touch. We have an implementation using st_intersects that is quite performant but has the drawbacks of returning the polygons that touch as well. We also tried using st_buffer on the region with a negative value to "shrink" it before calling st_intersects. This functionality was what we want, but it was incredibly slow. Finally, we tried to run st_intersects and not st_touches which gives mostly the desired behavior (tiny overlaps would be nice to omit, but is acceptable). I tried st_overlaps based on the suggestion of JGH in the comments, but I do actually want the polygon to be returned when they are the same shape. Many postal cities consist of exactly 1 postal code. When running the query to see which zip codes intersect with those postal cities, I want that 1 and only 1 zip code (and not the neighboring zips)

I'm struggling to find other alternatives. Is there a way to combine the two underlying DE-9IM matrixes in a way to perform the 2 operations in a single step?

st_intersects T******** *T*******   ***T*****   ****T****
st_touches FT*******    F**T*****   F***T****

I'm not entirely sure what that looks like though? Any thoughts on a performant way to run this query (at scale), this service gets hit a lot.

Answer 1

I believe you want to use st_overlaps, which should return true if 1) the intersection geometry has the same dimension as the input and 2) the two geometries are not equal.

The first point is the important one here, as if two polygons only touch each others, the intersection would be a line and the function would return false. This is however poorly documented on the postgis doc so use with caution.

with src (geom1,geom2,cmt) as (values 
    ('polygon((0 0, 0 1, 1 1, 1 0, 0 0))' , 'polygon((0 0, 0 1, 1 1, 1 0, 0 0))','same'),
    ('polygon((0 0, 0 1, 1 1, 1 0, 0 0))' , 'polygon((0 0, 0 1, -1 1, -1 0, 0 0))','touch'),
    ('polygon((0 0, 0 1, 1 1, 1 0, 0 0))' , 'polygon((-.5 -.5, -.5 .5, .5 .5, .5 -.5, -.5 -.5))','overlap'))
select  cmt, 
    st_overlaps(geom1,geom2) , 
    st_intersects(geom1,geom2), 
    st_touches(geom1,geom2)
from src;

   cmt   | st_overlaps | st_intersects | st_touches
---------+-------------+---------------+------------
 same    | f           | t             | f
 touch   | f           | t             | t
 overlap | t           | t             | f

Answer 2

The DE9IM pattern for "shares some area" is the one where the top-left cell is 2, and you don't really care about any of the others.

SELECT zip.id, city.id
FROM zip, city
WHERE zip.geom && city.geom
  AND ST_Relate(zip.geom, city.geom, '2********');

Throw in the index operator to speed up the query.