Hi Birgit, I followed your suggestion and wrote a plpgsql function that made one ST_Within() query per record and it worked!! Very helpful work around, thankyou!!
Shane On Thu, Oct 16, 2008 at 10:18 PM, Birgit Laggner <[EMAIL PROTECTED]> wrote: > Hi Shane, > > this is the function, our admin wrote to work through my query, row by row: > > CREATE OR REPLACE FUNCTION dh_test() > RETURNS void AS > $BODY$ > declare > i integer; > begin > for i in 1..15099748 loop > execute 'insert into bfn.ni_stat_hoehendaten (polygon_id, hoehe_count, > hoehe_min, hoehe_max, hoehe_avg,' || > ' hoehe_stdev,neig_count, neig_min, neig_max, neig_avg, expos_count, > expos_min, expos_max, expos_avg, expos_stdev, the_geom)' || > ' select polygon_id, count(hoehe) as hoehe_count, min(hoehe) as hoehe_min, > max(hoehe) as hoehe_max,' || > ' avg(hoehe) as hoehe_avg, stddev_samp(hoehe) as hoehe_stdev, 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, stddev_samp(exposition) as expos_stdev,' || > ' p.the_geom' || > ' from bfn.ni_hoehendaten h,bfn.ni_polygone2 p' || > ' where st_within(h.the_geom,p.the_geom) and p.polygon_id=' || i || ' group > by p.polygon_id, p.the_geom;'; > end loop; > end; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > ALTER FUNCTION dh_test() OWNER TO postgres; > > Precondition: pl/pgsql has to be installed. > > If you need any explanation to that, please ask. > > Regards, > > Birgit. > > > Shane Butler schrieb: >> >> Dear Birgit and List, >> >> I am getting an out of memory error (see below) when doing a big query >> that uses ST_Within(). This was described by Birgit on this list back >> in August. Is there a solution? >> >> Birgit, can you please explain the work around you mentioned on Aug 14: >> >> http://postgis.refractions.net/pipermail/postgis-users/2008-August/020984.html >> >> Any help would be greatly appreciated! >> >> Kind Regards, >> Shane >> >> --- >> >> Some details of the error: >> "ERROR: out of memory >> DETAIL: Failed on request of size 32. >> >> ********** Error ********** >> >> ERROR: out of memory >> SQL state: 53200 >> Detail: Failed on request of size 32." >> >> >> My system info: >> "POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec >> 2007" USE_STATS" >> >> > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
