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


2 Answers 2

SELECT grid.gid, count(kioskdhd3.geom) AS totale 
FROM grid
LEFT JOIN kioskdhd3 ON st_contains(grid.geom,kioskdhd3.geom)
GROUP BY grid.gid;
  • Hi Nicklas, got this answer from the mailing list, right? Thanks everyone
    – hoge6b01
    Mar 17, 2013 at 12:20
  • 2
    No, sorry, Is it similar? Well, an outer joins is no rocket science when dealing with relational databases ;-) Mar 17, 2013 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 :-) Mar 17, 2013 at 14:38

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.