Count number of points by name within distinct polygons

Try:

SELECT
  polyname,
  count(pid) FILTER (WHERE pid='w') AS "w",
  count(pid) FILTER (WHERE pid='x') AS "x",
  count(pid) FILTER (WHERE pid='y') AS "y",
  count(pid) FILTER (WHERE pid='z') AS "z"
FROM 
    (select polygons.polyname,points.pid
    from polygons
    left join points
    on st_intersects(points.geom, polygons.geom)) sub  
GROUP BY polyname

enter image description here enter image description here


You can use the following code (I work in the schema public):

-- Creation of a new table : I use distinct to get one row for each value of polyname
CREATE TABLE polytable2 AS
SELECT DISTINCT polyname 
FROM polytable1 
ORDER BY polyname; 

-- Creation of four new fields : one for each letter with 0 as default value
ALTER TABLE polytable2 
ADD COLUMN w INT DEFAULT 0,
ADD COLUMN x INT DEFAULT 0,
ADD COLUMN y INT DEFAULT 0,
ADD COLUMN z INT DEFAULT 0;

-- Updating my 4 fields using a subquery that calculates the point count in each polyname value 
-- and then making a join between my two polygon tables. 
-- 4 updates, each time I change the name of the column and my letter in the where clause.
-- for the letter w
UPDATE polytable2
SET w = count FROM (SELECT polyname, count(*)
FROM polytable1, pointtable
WHERE letter = 'w' AND st_intersects(polytable1.geom, pointtable.geom)
GROUP BY polyname) as temp WHERE polytable2.polyname = temp.polyname ;

-- for the letter x
UPDATE polytable2
SET x = count FROM (SELECT polyname, count(*)
FROM polytable1, pointtable
WHERE letter = 'x' AND st_intersects(polytable1.geom, pointtable.geom)
GROUP BY polyname) as temp WHERE polytable2.polyname = temp.polyname;

-- for the letter y
UPDATE polytable2
SET y = count FROM (SELECT polyname, count(*)
FROM polytable1, pointtable
WHERE letter = 'y' AND st_intersects(polytable1.geom, pointtable.geom)
GROUP BY polyname) as temp WHERE polytable2.polyname = temp.polyname ;

-- for the letter z
UPDATE polytable2
SET z = count FROM (SELECT polyname, count(*)
FROM polytable1, pointtable
WHERE letter = 'z' AND st_intersects(polytable1.geom, pointtable.geom)
GROUP BY polyname) as temp WHERE polytable2.polyname = temp.polyname ;

I can explain more if you need.


I think dynamic column names would be quite difficult. But if they don't need to be dynamic it sounds pretty simple:

select
po.poly_name,
sum(case when pt.point_name = 'w' then 1 else 0 end) as w,
sum(case when pt.point_name = 'x' then 1 else 0 end) as x,
sum(case when pt.point_name = 'y' then 1 else 0 end) as y,
sum(case when pt.point_name = 'z' then 1 else 0 end) as z
from
polytable1 po
left join pointtable pt on st_intersects(po.geom, pt.geom)
group by po.poly_name

Edit for OP:

select
po.poly_name,
sum(case when pt.point_name = 'w' then 1 else 0 end) as w,
sum(case when pt.point_name = 'x' then 1 else 0 end) as x,
sum(case when pt.point_name = 'y' then 1 else 0 end) as y,
sum(case when pt.point_name = 'z' then 1 else 0 end) as z
from
polytable1 po
left join pointtable pt on st_intersects(st_setsrid(po.geom, 27700), st_setsrid(pt.geom, 27700))
group by po.poly_name