Select records that intersect more than 3 polygons

Question

I am analyzing parcels in land use zones.

Some parcels overlap 3 or more zones = I am interested in selecting only those records (i.e. the purple-colored parcels)

At this point I have a query that joins zones in zoning to parcels in parcels when an intersection occurs

SELECT t.*, m.*
FROM parcels AS t
LEFT JOIN zoning AS m
ON ST_Intersects(t.geom, m.geom)

However, ST_Intersection() only returns true or false . Is there a way to "count" the intersections and specify 3 or more?

Answer 1

You should use aggregate function count and GROUP BY with HAVING. I supposed there is a primary key column (id) in parcels table.

SELECT t.id, count(*) 
  FROM parcels AS t INNER JOIN zoning AS m ON ST_Intersects(t.geom, m.geom)
  GROUP BY t.id HAVING count(*) > 2