Find all polygons containing a point in PostGIS
I was able to get a list of all the points contained in a particular polygon, but how can I query the PostGIS database to see which polygons contain a given point?
Suppose I have a table places
with locales lat / lon ( POINT
types) and a table areas
with a column of geometry
the types MULTIPOLYGON
.
How can I write a query that will return a list of locations and all areas containing each point? For example:
place_id, area_ids
1, {23,425,536}
2, {30,425}
-- etc...
source to share
You need to use array_agg
in conjunction with ST_Contains , see aggregate functions for more details. on array_agg and similar functions. If you combine this with a group over your point table, you end up with an array of all polygons containing those points, for example
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 place an order inside array_agg so that the area ids always appear in the same order. I named the primary keys of the table, id and both table geometries, geometry, so you might have to adjust to the setup.
source to share