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

[Kevin Neufeld]

Is this what you are after? (Note, this is untested)

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;

-- Kevin

pere roca wrote:
> 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
>