[postgis-users] Count Points in Polygon with Postgis

[Carsten Hogertz]

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

[Francois Hugues]

Hello,

 

The following  query should work well with your data :

 

Select poly_id, count(*) as nb_point from

                (select poly_id, point_id from table_point, table_poly

                Where st_contains(point_geom, poly_geom)

                ) as foo

Group by poly_id

 

Hugues.

 

De : postgis-us...@lists.osgeo.org [mailto:postgis-us...@lists.osgeo.org] De la part de Carsten Hogertz
Envoyé : samedi 16 mars 2013 16:53
À : PostGIS Users Discussion
Objet : [postgis-users] Count Points in Polygon with Postgis

[Francois Hugues]

Sorry read too fast (and made a mistake using st_contains as st_within) !

 

You should also add an union :

 

Select poly_id, count(*) as nb_point from

                (select distinct poly_id, point_id from table_point, table_poly

                Where st_contains(poly_geom, point_geom)

                ) as foo

Group by poly_id

 

Union

 

Select poly_id, 0 from table_point, table_poly

Where not st_contains(poly_geom, point_geom)

 

Hugues

 

 

 

De : postgis-us...@lists.osgeo.org [mailto:postgis-us...@lists.osgeo.org] De la part de Francois Hugues
Envoyé : samedi 16 mars 2013 17:35
À : PostGIS Users Discussion
Objet : Re: [postgis-users] Count Points in Polygon with Postgis

[Stephen Mather]

Try unioning with the opposite condition.

Sent from my iPod

_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[Stephen V. Mather]

Like Hugues example... .  :)

Stephen V. Mather
GIS Manager
(216) 635-3243 (Work)
clevelandmetroparks.com

---

From: postgis-us...@lists.osgeo.org [postgis-us...@lists.osgeo.org] on behalf of Stephen Mather [mather....@gmail.com]
Sent: Saturday, March 16, 2013 1:47 PM
To: PostGIS Users Discussion
Cc: PostGIS Users Discussion
Subject: Re: [postgis-users] Count Points in Polygon with Postgis

[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

[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