[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] simple point-in-polygon problem
From:       pere roca <peroc79 () gmail ! com>
Date:       2007-12-18 16:08:11
Message-ID: 14399846.post () talk ! nabble ! com
[Download RAW message or body]



   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@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.

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


[prev in list] [next in list] [prev in thread] [next in thread]