I have a polygons table and a points table. I want to get for each polygon the number of points located in it and have zero for polygons with no points. However with the following query I miss the polygons with zero points. I expected the LEFT JOIN to keep them though. I guess the st_transform is not causing this issue.
select plg.id,plg.geom,count(distinct pnt.id) as num_pnt from polygons plg left join points pnt on st_contains(plg.geom,st_transform(pnt.geom,3003)) where pnt.fieldx = "xx" group by plg.id,plg.geom;`
What am I doing wrong?