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

Martin Davis mbdavis at refractions.net
Fri Jun 20 10:05:28 PDT 2008

Also, the other thing that would help is to split the rain MultiPolygons 
into sets of single Polygons.

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

More information about the postgis-users mailing list