I want to do an operation i PostGIS similar to the
count points in polygon in QGIS.
- A multipolygon table (
plan.polygon) consisting of several areas
- A point table (
in a PostGIS database.
The point layer is categorized by the column
I want to count the number of each category for each of areas resulting in a multipolygon table with rows areas and columns with the count of each category.
Below you can se the areas (red lines) and the points with categorization (red, yellow, green dots).
I've been trying the following resulting in a query processing forever.
SELECT b.gid, b.the_geom, (SELECT COUNT(v.code_text) FROM basemap.points v, plan.polygon x WHERE entity = 120 AND ST_within(v.the_geom, x.the_geom) GROUP BY a.code_text) AS count_category_a, (SELECT count(y.code_text) FROM basemap.points y, plan.polygona z WHERE entity = 130 AND ST_within(y.the_geom, z.the_geom) GROUP BY a.code_text) AS count_category_b FROM basemap.points a, plan.polygons b;