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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
