[postgis-users] Re: Memory problem with ST_Within

[Birgit Laggner]

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"
>
>