# Recursive st_intersects with self query

I'm trying to find the polygons a point is in and then the intersecting polygons of the result of the first query.

I'm trying along the lines of:

WITH RECURSIVE all_the_areas AS (
SELECT a.name,a.geom FROM areas a WHERE ST_Contains(a.geom, ST_SetSRID(ST_MakePoint(lat,lng),4326))
UNION ALL
SELECT b.name,b.geom FROM areas b, all_the_areas c
WHERE st_intersects(b.geom, c.geom)

)
SELECT * FROM all_the_areas;

The problem is that this query hangs plus I'm not experienced at all with this type of query so it's probably a poor way to solve the problem.

Can you help me find an efficient solution to this problem?

I suppose the point is always in one polygon only (it's the point after all). That polygon might intersect with n polygons from the same table.

SELECT
p3.name,
p3.geom
FROM points p1
JOIN polygons p2 ON (ST_Contains(p2.geom, p1.geom))
JOIN polygons p3 ON (ST_Intersects(p2.geom, p3.geom))
WHERE p1.id = 'your desired point ID';
• The complication is that the point can be in overlapping polygons. That's where I'm getting stuck.
– Bill
Commented Jul 12, 2017 at 11:25
• That doesn't matter for the query I posted. It just takes all the polygons intersecting with polygons the point is lying in. Commented Jul 12, 2017 at 11:59