[postgis-users] Increase query performance

[Andreas Forø Tollefsen]

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 <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.
>
>
> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110309/70d09692/attachment.html>