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

[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: +351 96 17 20 188 Skype: pdoriam
http://www.madeiragps.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071217/7f63dd89/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071217/7f63dd89/attachment.pgp>