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).

enter image description here

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;

Are you looking for a simple filtered aggregate?


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,
FROM   plan.polygons AS ply
JOIN   basemap.points AS pts
  ON   ST_Intersects(ply.geom, pts.geom)
       ply.gid, ply.geom
  • Thanks! Exactly what I was looking for. If I want to add columns (counts) based on another spatial join, how can I put the code then? I have another point layer with categorization that needs to be added to the same table. Can I handle this bu using a subquery or the same join?
    – kwiwe
    Jan 26 at 9:20
  • 1
    @kwiwe Another JOIN on the same table and predicate would result in a (n highly inefficient) cartesian product between both joined tables, and would require to DISTINCT the counts. Correlated subqueries are one way, LATERAL queries probably the more performant. See my answer here to get you going; you'd want to move the filtered counts into the LATERAL sub-queries, and SUM in the outer.
    – geozelot
    Jan 26 at 11:25

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.