Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 gis.stackexchange.com/questions/42951/… –  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

1 Answer 1

up vote 6 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

Your Answer

 
discard

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.