Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
see… – HasT 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
up vote 8 down vote accepted
SELECT grid.gid, count(kioskdhd3.geom) AS totale 
FROM grid LEFT JOIN kioskdhd3 
ON st_contains(grid.geom,kioskdhd3.geom) 
GROUP BY grid.gid;
share|improve this answer
Hi Nicklas, got this answer from the mailing list, right? Thanks everyone – hoge6b01 Mar 17 '13 at 12:20
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
:-) 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

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;
share|improve this answer
The querry does not utilize spatial indexes: Nicklas answer does : – nickves Mar 13 '15 at 8:49

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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