Sunday, 4 March 2018

Count Points in Polygon with Postgis


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

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...