<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<blockquote cite="mid44341017.8070604@swoodbridge.com" type="cite">You
probably need to post the results of
<br>
<br>
select postgis_full_version();
<br>
<br>
and the explain on the select you posted below.
</blockquote>
Whoops, I should have realized that this would be helpful. Of note, I
realize<br>
that I a behind on versions here. Thanks for taking a look at this.<br>
<br>
Regards,<br>
<br>
Jim<br>
<br>
<tt>beta4=# select postgis_full_version();<br>
postgis_full_version <br>
------------------------------------------------------------------------------------------------------<br>
POSTGIS="0.9.1" GEOS="2.1.1" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS
DBPROC="0.0.1" RELPROC="0.0.1"<br>
(1 row)<br>
<br>
beta4=# EXPLAIN SELECT a.icell, a.jcell,
SUM(AREA(INTERSECTION(a.the_geom,b.the_geom)) / <br>
b.area * area_fraction * AREA(TRANSFORM(b.the_geom,32768))) as
area <br>
from regular_grid a, plant_area b <br>
WHERE b.the_geom && a.the_geom and <br>
DISTANCE(a.the_geom,b.the_geom) = 0 <br>
GROUP BY a.icell, a.jcell <br>
ORDER BY a.icell, a.jcell;<br>
QUERY
PLAN <br>
<small>-------------------------------------------------------------------------------------------------------------------------------------<br>
GroupAggregate (cost=7834473491.51..7835087937.70 rows=16352 width=543)<br>
-> Sort (cost=7834473491.51..7834627021.30 rows=61411915 width=543)<br>
Sort Key: a.icell, a.jcell<br>
-> Nested Loop (cost=1344.52..7697000461.50 rows=61411915
width=543)<br>
Join Filter: (("outer".the_geom && "inner".the_geom)
AND (distance("inner".the_geom, "outer".the_geom) = 0::double
precision))<br>
-> Seq Scan on plant_area b (cost=0.00..379664.42
rows=7511242 width=287)<br>
-> Materialize (cost=1344.52..2083.04 rows=16352 width=256)<br>
-> Seq Scan on regular_grid a (cost=0.00..769.52
rows=16352 width=256)</small><br>
(8 rows)</tt><br>
<br>
<blockquote cite="mid44341017.8070604@swoodbridge.com" type="cite"><br>
<br>
James G Wilkinson wrote:
<br>
<blockquote type="cite">All,
<br>
<br>
I have been monitoring this forum for some time, and it has been
<br>
very helpful in my efforts. I have run into a problem that I simply
<br>
cannot seem to get a handle on. I have two spatial data sets:
<br>
regular_grid (gid integer, area double, icell integer, jcell integer,
<br>
the_geom geometry) and plant_area (gid integer, area double,
<br>
area_fraction double, the_geom geometry). regular_grid represents
<br>
a 36 km by 36 km set of grid cells (polygons) that covers the
<br>
continental United States and is about 10 MB in size. plant_area is
<br>
a polygon coverage that covers North America which contains
<br>
the area covered by a single plant species and is about 6 GB in
<br>
size (each polygon in plant_area is roughly on the order of
<br>
4,000,000 square meters). Both coverages are in LAT/LON
<br>
coordinates. I am trying to perform the following:
<br>
<br>
CREATE TABLE intersect_results AS
<br>
SELECT a.icell, a.jcell,
SUM(AREA(INTERSECTION(a.the_geom,b.the_geom)) /
<br>
b.area * area_fraction *
<br>
AREA(TRANSFORM(b.the_geom,32768)))) as area
<br>
FROM regular_grid a, plant_area b
<br>
WHERE b.the_geom && a.the_geom AND
<br>
DISTANCE(a.the_geom,b.the_geom) = 0
<br>
GROUP BY a.icell, a.jcell
<br>
ORDER BY a.icell, a.jcell;
<br>
<br>
The TRANSFORM to 32768 gives me the area in square meters
<br>
in a Lambert Conformal Conic projection that I routinely use in my
<br>
work. Both tables are indexed using GIST on the geometries. I have
<br>
run VACUUM ANALYZE. I aborted the query on my Opteron
<br>
machine after 24 hours of CPU time (it was the only thing running).
<br>
<br>
I have not spent anytime trying to diagnose the query plan results
<br>
as I am almost completely ignorant of how to use them -- and maybe
<br>
this is the time to fix that gap in my knowledge. Regardless, I am
holding
<br>
out some hope that some expert on this list can provide some advice on
<br>
how better to render this query.
<br>
<br>
Regards,
<br>
terrakit
<br>
_______________________________________________
<br>
postgis-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
<br>
<br>
</blockquote>
<br>
_______________________________________________
<br>
postgis-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
<br>
<br>
<br>
</blockquote>
</body>
</html>