Counting categorized points in polygons using PostGIS

Geographic Information Systems Asked on May 8, 2021

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;

One Answer

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

Correct answer by geozelot on May 8, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP