5

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)

enter image description here

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?

8

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.