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

[Paragon Corporation]

Steve,
It's kind of ugly but it will work on 83. and 8.4.  I think with 9.0 you can
do window since it supports the ROWS BETWEEN m PRECEDING AND n FOLLOWING
predicate you 
would need to make this work a bit more elegantly. The 8.4 incarnation of
windowing wouldn't support it.  

So here is the 8.3-8.4 solution - haven't tested it so sure it has bugs, but
hopefully you get the idea and its what you had in mind

SELECT device, ST_Makeline(p) as geom 
from
       (select p2.device, ST_Centroid(ST_Collect(p1.p)) As p
                 from (SELECT * FROM gpspoints  ORDER BY device, seq) As p1
INNER JOIN (SELECT * FROM gpspoints ORDER BY device,seq) As p2 
		ON (p1.device = p2.device AND p1.seq <= p2.seq) 
		GROUP BY p2.device, p2.seq 
		HAVING count(p2.seq) <= 3  
	order by p2.device, p2.seq) as foo
         group by device;
 

Also keep in mind in PostGIS 2.0, we've removed makeline so use ST_MakeLine.

Thanks,
Regina and Leo
http://www.postgis.us


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Stephen
Woodbridge
Sent: Wednesday, February 23, 2011 1:52 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Looking for a points to moving average path
function

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