hi!
I wanna make a point-in-polygon operation but takes looong time (about 4
minutes!) and the CPU consuming of the server
becomes huge when performing this operation for 15000 points / polygons:
I need to count how many user points fall in each polygon and perform some
operations based on that.
We initially have a point_table that stores multi-user point data. We
extract from there only this-user-data and create new tables: user_points
and user_polygon. To the first one we will insert the code of the polygon in
which it falls; counting how many many times the same polygon_code is
repeated, we will calculate number-points_in_polygon...
The important code is commented below (calling SQL from PHP):
//we use the points bbox to narrow the future spatial queries (will only
select polygons falling inside this bbox; from 1991 initial polygons, only
85 are from now on used); it is useful when points are close each other (if
there is an outlayer then bbox is not useful...)
$extent="select astext(extent(points_table.the_geom)) from points_table
where userid='$user'";
$extent_result=pg_query($extent);
while ($row = pg_fetch_array($extent_result, NULL, PGSQL_ASSOC))
{
//creates a NEW polygon table that will store only polygon code and polygon
geometry that fall INSIDE the point's bbox
$poligon_sql2="INSERT INTO user_polygon (polygon_code,the_geom) (SELECT
distinct(polygon_code),the_geom FROM polygon where polygon."."the_geom &&
GeometryFromText('".$row['astext']."',4326));
UPDATE ONLY user_polygon SET userid ='$user';
//we previously created a new user point table, where we insert some info.
The table is created because we are taking data from a multi-user table and
now we want a single user-->a single point table
INSERT INTO user_points (genus, species, name, code, usuario, the_geom)
(SELECT genus, specie, codigo, oid, userid, the_geom FROM points WHERE
userid = '$user');
//in this user POINT table we set the code from the polygon's table where
the point falls inside;
//I expected this one to be the "big" operation , but it's not (takes "only"
1 min 18 sec)
UPDATE ONLY user_points SET code = (SELECT polygon_code FROM user_polygon
WHERE user_polygon.the_geom && user_points.the_geom AND CONTAINS
(user_polygon.the_geom,user_points.the_geom));
//updating user polygon table, counting how many times do we have a polygon
code in our POINT table --> number of points in each polygon
// this updating operation takes 1 min 20 seconds!
UPDATE ONLY user_polygon SET numreg = (SELECT COUNT (code) FROM user_points
WHERE name = polygon_code);
// this updating operation also takes about 1 min 20 seconds!
UPDATE ONLY user_polygon SET numtax = (SELECT COUNT (genus) FROM (SELECT
DISTINCT (genus) FROM user_points WHERE code = polygon_code) AS foo);
All important data is indexed; Any ideas to get better performance?
thanks!
Pere Roca
EDIT project (http://edit.csic.es/edit_geo/prototype/edit.html)
--
View this message in context:
http://www.nabble.com/point-in-polygon-SQL-sentence-performance-tp18190854p18190854.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
