16

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

23
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 '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
-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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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