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

[Paul Ramsey]

First, how many results is your 100m box returning? If the database has 
to repeatedly sequence scan a pile of values for each iteration, yes, it 
will be slow. Not much to do there, it is just a consequence of the fact 
that the "local minimum" problem is hard to optimize.
Second, you are doing an insert *and* an update for each iteration. Why 
not either (a) save the update for a batch transfer at the end or (b) 
combine the SQL into one huge update.


collin wrote:

> 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)))
> );
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 
       __
      /
      | Paul Ramsey
      | Refractions Research
      | Email: pramsey at refractions.net
      | Phone: (250) 885-0632
      \_