[postgis-users] Memory problem with ST_Within

[Obe, Regina]

Birgit,

Adding to Franks point

It seems you are adding the_geom to your list since you need that as part of your result. You may want to leave it out of your list (in the select and group by)  and then join back with the polygon_id field to get it back once you are done.

But still that is a large set you are tackling with.  What do your postgresql.conf settings look like?

Sadly you may just have to partition your dataset into quadrants and then group each quadrant separately and union the results.

It would help to see an 

EXPLAIN ANALYZE SELECT ....

of  a smaller subset to see what it is doing.

You also of course want to make sure you have 

vacuum analyze bfn.ni_hoehendaten;
vacuum analyze bfn.ni_polygone2;

before you get started.  A recently loaded non-vacuumed set of data does yield very poor performance.

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Frank Koormann
Sent: Wednesday, August 20, 2008 11:50 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Memory problem with ST_Within

* Birgit Laggner <birgit.laggner at vti.bund.de> [080820 17:30]:
> Here is my query:
>
> SELECT
> polygon_id,
> count(hoehe) as hoehe_count,
> min(hoehe) as hoehe_min,
> max(hoehe) as hoehe_max,
> avg(hoehe) as hoehe_avg,
> count(neigung) as neig_count,
> min(neigung) as neig_min,
> max(neigung) as neig_max,
> avg(neigung) as neig_avg,
> count(exposition) as expos_count,
> min(exposition) as expos_min,
> max(exposition) as expos_max,
> avg(exposition) as expos_avg,
> p.the_geom
> FROM bfn.ni_hoehendaten h, bfn.ni_polygone2 p
> WHERE ST_Within(h.the_geom, p.the_geom)
> GROUP BY p.polygon_id, p.the_geom
> ORDER BY p.polygon_id ASC;
>
> Does anybody know a method to reduce the memory usage? Or are there other 
> suggestions how this problem could be solved?
 
Why do you group by p.the_geom? Just a wild guess that this may cause
your memory problems. 

HTH,

        Frank

-- 
Frank Koormann    |   ++49-541-335 08 30    |   http://www.intevation.net/
Intevation GmbH, Osnabrück, DE | Commercial Register Osnabrück, HR B 18998
Managing Directors: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner
PostGIS Support (http://www.intevation.net/geospatial/postgis-support.en.html)
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.