2

I am just learning PostGIS and have a simple problem I can't get the answer for.

I want to select the geometries that intersect with given multiple geometries.

To make it simpler, let's say I want to select the line(s) which intersect with polygon1 and polygon2 in the image below.

I came up with something like this, but it's not working. What am I missing?

SELECT lines.id, lines.geom 
FROM lines, polygons 
WHERE(st_intersects(lines.geom,polygons.geom) and polygons.id = 1) and
     (st_intersects(lines.geom,polygons.geom) and polygons.id = 2)

???

2

The query does not work because it looks for single polygons having both id 1 and 2, which is not possible.

Instead, you could restrict the polygons to either id 1 or 2, then to intersects with the line and at last keep the lines having 2 intersections:

SELECT lines.id, lines.geom 
FROM lines
 JOIN polygons ON st_intersects(lines.geom,polygons.geom)
WHERE polygons.id in (1,2)
GROUP BY lines.id, lines.geom 
HAVING count(*) = 2;

Let's note that it would be more efficient to only select and group by ID, then to fetch the geom for these lines.

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.