[postgis-users] Count Points in Polygon with Postgis

[Carsten Hogertz]

Thanks,

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

It works perfectly for me.
Thanks a lot.
Carsten

Am 17.03.2013 09:11, schrieb Tom van Tilburg:
> Hi Carsten,
>
> What you need is a *left join* :
> |select grid.gid, count(kioskdhd3.geom) AS totale
> FROM grid
> LEFT JOIN kioskdhd3
> ON st_contains(grid.geom,kioskdhd3.geom)
> GROUP BY grid.gid;|
> Note that I changed the * in count to a column from your points table 
> because you only want to count when you have a non-null value in your 
> points table.
>
> Regards,
>  Tom
>
> On 16-3-2013 16:53, Carsten Hogertz wrote:
>>
>> 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
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130318/3f2c3eb0/attachment.html>