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