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).
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.