Hi,<br><br>I am a relative newcomer to postgis and am trying to figure out how to best optimize an interesting query.<br><br>I have two tables containing (multi)polygons, one representing drainage basins, and the other representing rainfall amounts. The rainfall table has an attribute giving the amount of rain in mm associated with each polygon.<br>
<br>    'basin' table:<br>         gid       integer,<br>         the_geom  geometry<br><br>    'rainfall' table:<br>        gid         integer,<br>        the_geom    geometry,<br>        rainamount  numeric<br>
<br>I want to compute the total volume of rain in each basin by taking the intersection of each basin with each rainfall polygon, multiplying the area of that intersection by the rain amount value for the corresponding rain polygon, and adding up all the resulting totals for each basin, storing the result in a new table.  I have spatial indexes on both tables, and I've tried the following query using the && operator to make use of the indexes:<br>
<br>    create table basinrain as<br>        select bgid,<br>               sum(arearain) as totrain<br>          from (<br>                 select b.gid as bgid,<br>                        r.gid as rgid,<br>                        r.rainamount * area(intersection(b.the_geom, r.the_geom)) as arearain<br>
                   from basin b,<br>                        rain  r<br>                  where b.the_geom && r.the_geom<br>                    and intersects(b.the_geom, r.the_geom)<br>                 ) foo<br>          group by bgid<br>
<br>This seems to work just fine, but it is much slower than I would expect.  My basin table has about 2200 rows; their size and geometric complexity is roughly comparable to US county polygons.  The rain table has about 10 rows, but each one represents a pretty complicated multipolygon with (many) holes.  The query "select count(*) from basin, rain where basin.the_geom && rain.the_geom" executes very quickly and returns 12746, which I take to mean that (a) my spatial indexes are in fact in place and working, and (b) there are 12746 "possible" intersections to be computed in the bigger query above.  On a dual quad-core 3GHz Xeon system with nothing else going on, though, the bigger query takes about 15 minutes to run, which seems to me like a long time for computing 12746 intersections / areas.  (I know that comes out to an average of about 14 intersection/area computations per second, which is way faster than I could do it by hand of course, but for some reason I would expect it to be even faster than that.)<br>
<br>Is this surprising to anyone else?  Can someone suggest other ways to optimize this?<br><br>Thanks in advance,<br><br>--Mark<br><br>