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

[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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 

-- 
View this message in context: http://www.nabble.com/simple-point-in-polygon-problem-tp14373929p14399846.html
Sent from the PostGIS - User mailing list archive at Nabble.com.