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

[Pedro Doria Meunier]

Hey Pere,

Perhaps you're looking for a INNER JOIN clause? ;-)
Regards,

On Mon, 2007-12-17 at 09:19 -0800, 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

--
Pedro Doria Meunier
Ips da Olaria
Edf. Jardins do Garajau, 4 r/c Y
9125-163 Caniço
Madeira
Portugal
GSM: [phone] Skype: pdoriam
http://www.madeiragps.com

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[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
>  

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[pere roca]

   Kevin,Pedro
   this is not what I'm looking for...
   The problem is that....

> 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?
 
   Maybe sounds stupid, but isn't the spatial constraint (point-contained in polygon) a "link" itself? why can't I update row by row depending on the number of points each polygon has? I think the solution has to be a function or php programming...

  Cheers,
  Pere

Kevin Neufeld wrote:

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
>  
_______________________________________________
postgis-users mailing list
[e-mail]
http://postgis.refractions.net/mailman/listinfo/postgis-users