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

Martin Davis mbdavis at refractions.net
Fri Jun 20 09:23:08 PDT 2008

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