Thanks for all the suggestions. I first thought about doing the simplify, but after some reconsideration I realized that this would change the geometry of the country borders, and Area measurements might be wrong.
Then i tried the query without the ST_Intersection and only the conditional ST_Intersects. This completed in 87 seconds, so this was very fast compared to 40-50 minutes per year. I also tried the UNION ALL suggestion by Nicklas which had almost similar performance as the original query. I will test this some more to see whether i can save some time. My guess is that the calculation time required to create the new intersected geometry is what takes so long, and this is probably something i need to accept (or get more cpu power :) Andreas 2011/3/9 Mark Cave-Ayland <[email protected]> > On 08/03/11 17:01, Andreas Forø Tollefsen wrote: > > After a suggestion from pgsql_performance i tried with ST_Simplify to >> speed things up. >> However this gives me a: >> NOTICE: ptarray_simplify returned a <2 pts array >> >> Then server connection terminates. >> >> Like this: >> >> SELECT ST_Intersection(priogrid_land.cell, >> ST_Simplify(cshapeswdate.geom,0.1)) AS geom, >> priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, >> enddate, capname, caplong, caplat, col, row, xcoord, ycoord >> FROM priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell, >> ST_Simplify(cshapeswdate.geom,0.1)) AND cshapeswdate.gwsyear <=1946 AND >> cshapeswdate.gweyear >=1946 AND cshapeswdate.startdate <= '1946/1/1'; >> > > Yup that's a bug related to the new code in trunk - I think we've already > got something similar filed in the bug tracker. This is probably going to > require a Paul to take a look at it. > > > ATB, > > Mark. > > -- > Mark Cave-Ayland - Senior Technical Architect > PostgreSQL - PostGIS > Sirius Corporation plc - control through freedom > http://www.siriusit.co.uk > t: +44 870 608 0063 > > Sirius Labs: http://www.siriusit.co.uk/labs > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
