[postgis-users] Neighborhood Function using PL/PGSQL waaay to slow?

[collin]

Folks,

 I posted earlier regarding getting statistics for every point on their 
neighboring points.  Currently I am trying to find the lowest alititude 
within a ~100 meter radius of each point.  To do so, I wrote a PL/PGSQL 
function (see below) which iterates through every row in a 10,000 row 
table and updates each row with the lowest altitude within a bounding 
box of 100meters. 

-----------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION collintest2() RETURNS integer AS '
DECLARE
  thisrow RECORD;
  zzmin double precision;
  ztot integer := 0;
BEGIN
  FOR thisrow IN SELECT * FROM test1 LOOP
     SELECT INTO zzmin min(z) FROM test1 WHERE
                                the_geom && 
EXPAND(GeometryFromText(''POINT('' || thisrow.x || '' '' || thisrow.y || 
'' '' || thisrow.z ||'')'',32610),100);
     EXECUTE ''UPDATE test1 SET zmin = '' || zzmin || '' WHERE x = '' || 
thisrow.x || '' AND y = '' || thisrow.y ;
     ztot := ztot + 1;
  END LOOP;
  RETURN ztot;
END;
' LANGUAGE 'plpgsql';
-------------------------------------------------------------------------------------------------------- 
 

EXPLAIN ANALYZE SELECT collintest2();
            QUERY PLAN
     
------------------------------------------------------------------------------------------------
     Result  (cost=0.00..0.01 rows=1 width=0) (actual 
time=3318131.024..3318131.027 rows=1 loops=1)
     Total runtime: 3318131.075 ms

This runs painfully slow, as you can see (331ms per row). Is there 
another way to do this? I am new to pl/pgsql, though very familiar with 
sql.  How do I optimize? Use cursors?  I built a gist index on the_geom, 
but it doesn't make much of a difference.

Thanks in advance,

Collin Bode
GIS Informatics Researcher, UC Berkeley


The here is the schema for the table being operated on:
CREATE TABLE test1 (
    the_geom geometry,
    zmin double precision,
    "time" double precision,
    x double precision,
    y double precision,
    z double precision,
    i double precision,
    xf double precision,
    yf double precision,
    zf double precision,
    if double precision
    CONSTRAINT "$1" CHECK ((srid(the_geom) = 32610)),
    CONSTRAINT "$2" CHECK (((geometrytype(the_geom) = 'POINT'::text) OR 
(the_geom IS NULL)))
);