[postgis-users] Re: Memory problem with ST_Within

[Shane Butler]

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
<birgit.laggner at vti.bund.de> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>