<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7601.17537"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=282163511-08032011><FONT color=#0000ff
size=2 face=Arial>Andreas,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=282163511-08032011><FONT color=#0000ff
size=2 face=Arial>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?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=282163511-08032011><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=282163511-08032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=282163511-08032011><FONT color=#0000ff
size=2 face=Arial>Do you have indexes on your year fields and date
columns?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=282163511-08032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=282163511-08032011><FONT color=#0000ff
size=2 face=Arial>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=282163511-08032011><FONT color=#0000ff
size=2 face=Arial>Regina </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=282163511-08032011><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.us">http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=282163511-08032011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Andreas Forø Tollefsen<BR><B>Sent:</B> Tuesday, March 08, 2011 4:15
AM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> [postgis-users]
Increase query performance<BR></FONT><BR></DIV>
<DIV></DIV>Hi all,
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>"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"</DIV>
<DIV><BR></DIV>
<DIV>"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"</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>
<DIV>DROP TABLE IF EXISTS cshapesgrid1946;</DIV>
<DIV><BR></DIV>
<DIV>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';</DIV>
<DIV><BR></DIV>
<DIV>ALTER TABLE cshapesgrid1946 ADD COLUMN area float;</DIV>
<DIV><BR></DIV>
<DIV>UPDATE cshapesgrid1946 SET area = ST_Area(cshapesgrid1946.geom);</DIV>
<DIV><BR></DIV>
<DIV>DROP TABLE IF EXISTS pg1946;</DIV>
<DIV><BR></DIV>
<DIV>SELECT * INTO pg1946 FROM cshapesgrid1946 a WHERE a.area IN (SELECT
MAX(area) FROM cshapesgrid1946 b GROUP BY divider);</DIV>
<DIV><BR></DIV>
<DIV>CREATE INDEX idx_pg1946 ON pg1946 USING gist(geom);</DIV></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>Best,</DIV>
<DIV>Andreas</DIV></BODY></HTML>