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 <[email protected]> > 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:* [email protected] [mailto: > [email protected]] *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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
