Find all polygons containing a point in PostGIS

Question

I've managed to get a list of all points contained in a specific polygon, but how can I query a PostGIS database to find out what polygons contain a given point?

Suppose I have a table places with lat/lon locations (POINT types) and a areas table with a geometry column with MULTIPOLYGON types.

How can I write a query that will return a list of places and all the areas that contain each point? For example:

place_id, area_ids
1, {23,425,536}
2, {30,425}
-- etc...

Answer 1

You need to use array_agg in conjunction with ST_Contains, see aggregate functions for more on array_agg and similar functions. If you combine this with a group by on your points table, you will get an array of all the polygons containing those points, eg,

SELECT pl.id, array_agg(ar.id ORDER BY ar.id) as area_list
FROM areas ar, places pl 
WHERE ST_Contains(ar.geom, pl.geom) 
GROUP BY pl.id
ORDER BY pl.id; 

Note, you can put an Order By inside array_agg, so that the area IDs always appear in the same order. I have called both table's primary keys, id, and both table's geometries, geom, so you might have to adjust for your set up.