« Return to Thread: simple point-in-polygon problem

simple point-in-polygon problem

by pere roca :: Rate this Message:

Reply to Author | View in Thread

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

 « Return to Thread: simple point-in-polygon problem