[postgis-users] Increase query performance

[Andreas Forø Tollefsen]

Hi,
This query takes about 41 minutes per year. Doing this for every year from
1946 to 2008 takes a lot of time.
The grid consists of 64818 cell polygons, while the country table has about
210 polygons.

I will add indexes on year and date and try again. An do an explain analyze.

Cheers,
Andreas

2011/3/8 Paragon Corporation <lr at pcorp.us>

>  Andreas,
> I don't see anything glaringly wrong with your query, but hard to tell
> without seeing an explain plan or what you mean by takes a lot of time.  Is
> a lot of time minutes, hours, days?
> How many records are we talking about here?  What's the max number of
> points you have in any geometry.  Often times its just one huge mega
> geometry with a 100,000 points or more slowing everything down.
>
> Do you have indexes on your year fields and date columns?
>
> Hope that helps,
> Regina
> http://www.postgis.us
>
>
>  ------------------------------
> *From:* postgis-users-bounces at postgis.refractions.net [mailto:
> postgis-users-bounces at postgis.refractions.net] *On Behalf Of *Andreas Forø
> Tollefsen
> *Sent:* Tuesday, March 08, 2011 4:15 AM
> *To:* PostGIS Users Discussion
> *Subject:* [postgis-users] Increase query performance
>
> Hi all,
>
> Another question on postgis query performance. I did some discussion on the
> pgsql performance list on how i could increase the performance on my server.
> I did manage to increase the transactions per second, but came to the
> conclusion that this did not help the ST_Intersection query which i was
> trying to speed up.
>
> Any suggestions on how to speed up this query? Basically i want to create
> an intersection between a vector grid and country shapefiles. Then calculate
> the area of all the polygons in the intersected table, and finally selecting
> the country code for each cell which represents the largest area within each
> cell. I does work as it is, but i would like to increase the speed. The
> query which takes a lot of time is the ST_Intersection.
>
> "PostgreSQL 8.4.7 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real
> (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit"
>
> "POSTGIS="2.0.0SVN" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September
> 2009" LIBXML="2.7.7" USE_STATS"
>
>
>  DROP TABLE IF EXISTS cshapesgrid1946;
>
> SELECT ST_Intersection(priogrid_land.cell, cshapeswdate.geom) AS geom,
> priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, enddate,
> capname, caplong, caplat, col, row, xcoord, ycoord INTO cshapesgrid1946 FROM
> priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell,
> cshapeswdate.geom) AND cshapeswdate.gwsyear <=1946 AND cshapeswdate.gweyear
> >=1946 AND cshapeswdate.startdate <= '1946/1/1';
>
> ALTER TABLE cshapesgrid1946 ADD COLUMN area float;
>
> UPDATE cshapesgrid1946 SET area = ST_Area(cshapesgrid1946.geom);
>
> DROP TABLE IF EXISTS pg1946;
>
> SELECT * INTO pg1946 FROM cshapesgrid1946 a WHERE a.area IN (SELECT
> MAX(area) FROM cshapesgrid1946 b GROUP BY divider);
>
> CREATE INDEX idx_pg1946 ON pg1946 USING gist(geom);
>
>
> Best,
> Andreas
>
> _______________________________________________
> 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/20110308/89a01853/attachment.html>