Counting Points in Polygon with PostGIS

Question

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

SELECT grid.gid, count(*) AS totale
FROM grid, kioskdhd3
WHERE st_contains(grid.geom,kioskdhd3.geom)
GROUP BY grid.gid;

returns 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()?

see http://gis.stackexchange.com/questions/42951/how-fill-length-and-feature-count-in-postgis-table-for-each-polygons — spatialhast, Mar 16 '13 at 13:23
thanks, but they all have a WHERE-clause. I don't know if I need one. How would you re-write it? — hoge6b01, Mar 16 '13 at 13:56

score 33 · Accepted Answer · edited Sep 04 '20 at 05:38

33

SELECT grid.gid, count(kioskdhd3.geom) AS totale 
FROM grid
LEFT JOIN kioskdhd3 ON st_contains(grid.geom,kioskdhd3.geom)
GROUP BY grid.gid;

edited Sep 04 '20 at 05:38

Taras

answered Mar 16 '13 at 19:59

Nicklas Avén

Hi Nicklas, got this answer from the mailing list, right? Thanks everyone – hoge6b01 Mar 17 '13 at 12:20
2

No, sorry, Is it similar? Well, an outer joins is no rocket science when dealing with relational databases ;-) – Nicklas Avén Mar 17 '13 at 14:31
1

:-) I had to check the list. Look at the timing. My answer here was before list- answer , but as you said, very similar :-) – Nicklas Avén Mar 17 '13 at 14:38

score -1 · Answer 2 · edited May 23 '17 at 12:39

-1

Per this left joins are not supported in GiST indices.

May I recommend:

SELECT grid.gid, 
       SUM(CASE WHEN st_contains(grid.geom,kioskdhd3.geom) THEN 1 ELSE 0 END) AS total
FROM grid, kioskdhd3 
GROUP BY grid.gid;

edited May 23 '17 at 12:39

Community

answered Sep 25 '14 at 21:34

raphael

2

The querry does not utilize spatial indexes: http://explain.depesz.com/s/U6Iy. Nicklas answer does : http://explain.depesz.com/s/nhH – nickves Mar 13 '15 at 08:49

2 Answers2