[postgis-users] Increase query performance
Andreas Forø Tollefsen
andreasft at gmail.com
Wed Mar 9 07:28:28 PST 2011
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
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 :)
2011/3/9 Mark Cave-Ayland <mark.cave-ayland at siriusit.co.uk>
> 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.
> Mark Cave-Ayland - Senior Technical Architect
> PostgreSQL - PostGIS
> Sirius Corporation plc - control through freedom
> t: +44 870 608 0063
> Sirius Labs: http://www.siriusit.co.uk/labs
> postgis-users mailing list
> postgis-users at postgis.refractions.net
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users