simple point-in-polygon problem
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