[postgis-users] simple point-in-polygon problem

[pere roca]

hi!
please, I need some help with a query to extract points from polygons and
update the value (count points) in a field of the polygon table. This counts
gives me the name of polygon and counts points-in-polygon. Nice. 

select polygon.name, count(points.gid)  from points, polygon where
points.the_geom && polygon.the_geom and Contains
(polygon.the_geom,points.the_geom) group by polygon.name

Now I need to update the number_of_points field of polygon table.

update polygon set number_points=

(
select count(point.gid) from
     point, polygon 
  where point.the_geom && polygon.the_geom and 
   Contains (polygon.the_geom,point.the_geom) 
  group by polygon.name
) 

from point where point.the_geom && polygon.the_geom and
Contains(polygon.the_geom, point.the_geom) 

It just fills the "number_points" field of the polygons that intersect with
points, BUT the value assigned is the same for all (that's the total COUNT
of all points, because all of these points are inside a polygon). 

I've checked many more sentences with no success.
Of course if at the end of the sentence I add "where polygon.name="x", will
update ALL the values with the number of points that are only inside this
polygon. 
If I don't have a "link" between the two tables that says like: "where
polygon.name=point.poly_id"...do I HAVE TO construct a pgsql function to
loop over all the polygons?

Is Contains the fastest way to work with these kind of problems?
Thanks a lot,
Pere
-- 
View this message in context: http://www.nabble.com/simple-point-in-polygon-problem-tp14373929p14373929.html
Sent from the PostGIS - User mailing list archive at Nabble.com.