0

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?

2

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 Jul 12 '17 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. – Michal Zimmermann Jul 12 '17 at 11:59

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.