Counting categorized points in polygons using PostGIS

Question

I want to do an operation i PostGIS similar to the count points in polygon in QGIS.

I have

• A multipolygon table (plan.polygon) consisting of several areas
• A point table (basemap.points)

in a PostGIS database.

The point layer is categorized by the column code_text.

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;

Answer 1

Are you looking for a simple filtered aggregate?

Try

SELECT ply.gid,
       COUNT(pts.*) FILTER (WHERE pt.code_text = <category_a>) AS count_category_a,
       COUNT(pts.*) FILTER (WHERE pt.code_text = <category_b>) AS count_category_b,
       COUNT(pts.*) FILTER (WHERE pt.code_text = <category_c>) AS count_category_c,
       ply.the_geom
FROM   plan.polygons AS ply
JOIN   basemap.points AS pts
  ON   ST_Intersects(ply.geom, pts.geom)
GROUP BY
       ply.gid, ply.geom
;