PostGIS – Why Doesn’t ST_Intersects Work with Multiple Rows of Geom?

Question

I am new to PostGIS. I am trying to find the conditional intersection between two multi-polygons PostGIS tables (tab1 and tab2) when tab2 meets certain criteria (defined by row values of one of the columns). I am using the following SQL

SELECT *
FROM tab1 
WHERE ST_Intersects(tab1.geom, (SELECT geom FROM tab2 WHERE "cond_column" = 'selected_string'));

But I get the following error

ERROR: more than one row returned by a subquery used as an expression

I totally understand the error and I know I have selected multiple rows of multi-polygon from tab2. But my thinking is that ST_intersect will check for each row of both tab1.geom and the selected rows of tab2.geom. Why isn't this the case and what's the right way to do this? And in general, is it a good practice to use a subquery?

Answer 1

If you use a subquery, you can only use it in a function made for the number of line returned, so if your sub-query returns multiple rows you need to use it in an aggregate function. I think what you want to do is a join between the 2 tables. To do that, you can do it for example like this:

SELECT
  *
FROM tab1
INNER JOIN (SELECT geom FROM tab2 WHERE "cond_column" = 'selected_string') as tab2_filtered 
ON ST_Intersects(tab1.geom, tab2_filtered.geom)

Personally, I prefer to use CTE for easier to read code:

WITH tab2_filtered AS (
  SELECT geom FROM tab2 WHERE "cond_column" = 'selected_string'
)
SELECT
  *
FROM tab1
INNER JOIN tab2_filtered 
ON ST_Intersects(tab1.geom, tab2_filtered.geom)

EDIT:

This type of join is efficient because it will uses indexes on geometry (if you made them). If you only want a filtering on your tab1, you can also use the EXISTS command:

SELECT
  *
FROM tab1
WHERE EXISTS (
  SELECT 1 FROM tab2 WHERE "cond_column" = 'selected_string' AND ST_Intersects(tab1.geom, tab2.geom)
)

I just checked, this command will use the index on tab2 too (road_arc is tab1 and admin_boundary is tab2):