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