[postgis-users] need help optimizing spatial join / intersection query

[Martin Davis]

Another observation: in the rain dataset there are several very large 
polygons which effectively span the entire space.  These will get tested 
against every single basin polygon.  intersects() is almost as expensive 
as intersection(), so I suspect that the query is spending most of its 
time in intersects().

Gridding the rain polygons should help a lot with this.

Martin Davis wrote:
> Thanks for the data, Mark.
>
> I think your idea of spliitting the rainfall polygons could well 
> provide better speed. Processing time is non-linear in the number of 
> vertices in a polygon, so smaller polygons should have a much better 
> time for running intersection().  Also, as you say, the spatial index 
> will be able to reduce the number of intersections that you actually 
> have to make.
>
> Mark Phillips wrote:
>> Paul  & Martin,
>>
>> Thanks again for your great suggestions and feedback; I'll try 
>> setting up a script to spread the computations out over multiple 
>> cores and see what happens.
>>
>> In the meantime, in case you or anyone else wants to experiment with 
>> the data I've been using, I've packaged it up and made it available 
>> for download here:
>>
>> http://drought.nemac.unca.edu/download/basinrain.tgz
>>
>> That tarball contains two shapefiles --- one for the drainage basins 
>> and one for the rainfall polygons, and  README file summarizing what 
>> I'm trying to do.
>>
>> One idea that just occurred to me, which I haven't had time to 
>> actually try yet, would be to first split the rain polygons into 
>> smaller pieces, perhaps by intersecting them with some moderate-sized 
>> rectangular grid.    In general these "rain polygons" are large 
>> complicated multipolygons  with a lot of holes.  It seems conceivable 
>> to me that if they were in smaller chunks, the total processing time 
>> for my computation might possibly go down, because each actual 
>> intersection/area calculation would involve a lower number of 
>> vertices.  Of course this would be at the expense of a larger number 
>> of such calculations, so it's not totally clear that it would help, 
>> but it seems like it might, especially since the spatial index will 
>> eliminate a bunch of unnecessary calculations.  I'll give this a  try 
>> when I can find the time and see what happens.  In the meantime, if 
>> anyone has any thoughts about whether this kind of thing would help, 
>> let me know!
>>
>> Thanks again,
>>
>> --Mark
>>
>>
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>   
>

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022