I've got a simple problem: I want to count the number of points within a set of polygons.
I have a SQL already but it only gives back the gid of the polygone that actually contains points.
My tables: a polygon layer with 19.000 rows and a point layer with 450 rows.
The following SQL
select grid.gid, count(*) AS totale FROM grid, kioskdhd3 WHERE
st_contains(grid.geom,kioskdhd3.geom) GROUP BY grid.gid;
return only some 320 polygons that actually contain points. But I want all polygons returned, even thought the number of points is 0.
Of course it has to do with my WHERE-clause. Where do I have to put in my st_contains?
Thank you Carsten
Answer
SELECT grid.gid, count(kioskdhd3.geom) AS totale
FROM grid LEFT JOIN kioskdhd3
ON st_contains(grid.geom,kioskdhd3.geom)
GROUP BY grid.gid;
No comments:
Post a Comment