[postgis-users] point-in-polygon SQL sentence performance

[pere roca]

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.