[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-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at 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

 

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-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at 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

 

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

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