Recursive st_intersects with self query

Question

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?

Answer 1

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';