count points for every polygon

[Alexander Strunck]

hello

i am new to postgis and sql. my problem is that i want to count the points that are in every polygon. the polygon table contains gid, id, geom. the points table contains id, geom.
i found in the internet this sql

UPDATE polygon SET number_points = foo.count
FROM (
SELECT polygon.name, count(point.gid) AS count
FROM point, polygon
WHERE ST_Contains(polygon.the_geom, point.the_geom)
GROUP BY polygon.name
) AS foo
WHERE polygon.name = foo.name;

but it don´t seem to work for me.

Has anyone an idea how to make this work??

thx

alex

[Astrid Emde]

Hello Alexander,

have a look at

http://postgis.org/documentation/manual-1.4/ch07.html

and try

* ST_NumPoints - to get the Number of Points of a Linestring
* ST_ExteriorRing - to get a linestring from your Polygon
* if you have MULTIPOLYGONS you may need ST_NumGeometries, ST_GeometryN

Best regards

Astrid

Post by Alexander Strunck
hello
i am new to postgis and sql. my problem is that i want to count the
points that are in every polygon. the polygon table contains gid, id,
geom. the points table contains id, geom. i found in the internet this sql

UPDATE polygon SET number_points = foo.count
FROM (
SELECT polygon.name, count(point.gid) AS count
FROM point, polygon
WHERE ST_Contains(polygon.the_geom, point.the_geom)
GROUP BY polygon.name
) AS foo
WHERE polygon.name = foo.name;

but it don´t seem to work for me.
Has anyone an idea how to make this work??
thx
alex --
Endlich! Das Debüt-Album von Pop-Diva Sarah Kreuz ist da!
Jetzt bei GMX Musik Downloads. http://portal.gmx.net/de/go/musik01
_______________________________________________
http://postgis.refractions.net/mailman/listinfo/postgis-users