2011/3/8 Andreas Forà Tollefsen <andreasft@xxxxxxxxx>
Andy. Thanks. That is a great tips. I tried it but i get the error:NOTICE: ptarray_simplify returned a <2 pts array.Query: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';2011/3/8 Andy Colson <andy@xxxxxxxxxxxxxxx>I have seen really complex geometries cause problems. ÂIf you have thousands of points, when 10 would do, try ST_Simplify and see if it doesnt speed things up.
-Andy
On 3/8/2011 2:42 AM, Andreas ForÅ Tollefsen wrote:
Hi. Thanks for the comments. My data is right, and the result is exactly2011/3/8 Tom Lane <tgl@xxxxxxxxxxxxx <mailto:tgl@xxxxxxxxxxxxx>>
what i want, but as you say i think what causes the query to be slow is
the ST_Intersection which creates the intersection between the vector
grid (fishnet) and the country polygons.
I will check with the postgis user list if they have any idea on how to
speed up this query.
Best,
Andreas
 Â<mailto:andreasft@xxxxxxxxx>> writes:
  > This is a query i am working on now. It creates an intersection
 Âof two
  > geometries. One is a grid of 0.5 x 0.5 decimal degree sized
 Âcells, while the
  > other is the country geometries of all countries in the world for
 Âa certain
  > year.
 ÂHm, are you sure your data is right? ÂBecause the actual rowcounts imply
 Âthat each country intersects about half of the grid cells, which doesn't
 Âseem right.
  > priogrid=# EXPLAIN ANALYZE SELECT priogrid_land.gid, gwcode,
  > ST_Intersection(pri
  > ogrid_land.cell, cshapeswdate.geom) FROM priogrid_land,
 Âcshapeswdate WHERE
  > ST_Intersects(priogrid_land.cell, cshapeswdate.geom);
  >
   QUERY
  > PLAN
  >
 Â--------------------------------------------------------------------------------
  > ------------------------------------------------------------------
  > ÂNested Loop Â(cost=0.00..12644.85 rows=43351 width=87704) (actual
  > time=1.815..7
  > 074973.711 rows=130331 loops=1)
  >  ÂJoin Filter: _st_intersects(priogrid_land.cell, cshapeswdate.geom)
  >  Â-> ÂSeq Scan on cshapeswdate Â(cost=0.00..14.42 rows=242
 Âwidth=87248)
  > (actual
  > Âtime=0.007..0.570 rows=242 loops=1)
  >  Â-> ÂIndex Scan using idx_priogrid_land_cell on priogrid_land
  > Â(cost=0.00..7.1
  > 5 rows=1 width=456) (actual time=0.069..5.604 rows=978 loops=242)
  >     ÂIndex Cond: (priogrid_land.cell && cshapeswdate.geom)
  > ÂTotal runtime: 7075188.549 ms
  > (6 rows)
 ÂAFAICT, all of the runtime is going into calculating the ST_Intersects
 Âand/or ST_Intersection functions. ÂThe two scans are only accounting for
 Âperhaps 5.5 seconds, and the join infrastructure isn't going to be
 Âterribly expensive, so it's got to be those functions. ÂNot knowing much
 Âabout PostGIS, I don't know if the functions themselves can be expected
 Âto be really slow. ÂIf it's not them, it could be the cost of fetching
 Âtheir arguments --- in particular, I bet the country outlines are very
 Âlarge objects and are toasted out-of-line. ÂThere's been some past
 Âdiscussion of automatically avoiding repeated detoastings in scenarios
 Âlike the above, but nothing's gotten to the point of acceptance yet.
 ÂPossibly you could do something to force detoasting in a subquery.
             Âregards, tom lane
