[postgis-users] Looking for a points to moving average path function

[Stephen Woodbridge]

So if anyone cares this is what I came up with:

CREATE OR REPLACE FUNCTION movingaveragepoints(num integer, id integer)
   RETURNS SETOF geometry AS
$BODY$
/*
  * num - number of points in the moving average
  * id  - did for the device we want the path for
*/
DECLARE
     i    integer;
     cnt  integer;
     p    record;

BEGIN
     SELECT INTO cnt COUNT(*) FROM gpspoints WHERE did=id;

     -- assumes seq values are in the range 1..n
     FOR i IN 1..cnt LOOP
         SELECT INTO p AVG(lat) as y, AVG(lon) as x FROM
             (SELECT lat, lon FROM gpspoints
               WHERE did=id and seq > i-num and seq <= i) as foo;

         RETURN NEXT setsrid(makepoint(p.x, p.y),4326);
     END LOOP;

     RETURN;
END;
$BODY$
   LANGUAGE plpgsql STABLE
   COST 10;

-- this works great! get results
SELECT asewkt(movingaveragepoints) from movingaveragepoints(5, 1);

-- and so does this
SELECT astext(makeline(p1)) from
   (SELECT movingaveragepoints as p1
      from movingaveragepoints(5, 1)) as foo;

Open to better ideas if anyone has them.

Thanks,
   -Steve

On 2/23/2011 1:51 PM, Stephen Woodbridge wrote:
> Hi All,
>
> I have a table of points and I would like to generate a path from these
> points.
>
> This works very well for the raw points:
>
> select device, makeline(p) as the_geom from
> (select device, p from gpspoints order by seq) as foo
> group by device;
>
>
> I would like to smooth these points using a moving average where if the
> number of points in the average is say 3 then the points are:
>
> p[1]
> (p[1]+p[2])/2
> (p[1]+p[2]+p[3])/3
> (p[2]+p[3]+p[4])/3
> ...
> (p[n-2]+p[n-1]+p[n])/3
>
> and these points are then used in makeline().
>
> So I can do a brute force iterative function, but it seems like there
> should be a more elegant solution.
>
> Thoughts, code snippets, etc?
>
> I'm working with postgresql 8.3, 8.4 and postgis 1.5 at the moment.
>
> -Steve
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users