Attached is an example of using Loess prediction in PL/R for smoothing xyzm data (flight tracks) with respect to the m (time) value. Compared to previous work that I had posted that used a time-weighted moving average (similar to McMaster method) with the windowing functions, using the Loess prediction is far less influenced by outliers. In the attached image the red track is the raw track (data from multilateration sensors). You can see that there is a lot of noise to this data. The blue line is the result of the following functions to smooth the data.<div>
<br></div><div>This is the workhorse PL/R function. It takes two arrays and a span value. The first array is the data you want to smooth (in the case of the 4d data this is an array of the x, y, or z values), the second array is the array of time values that are the steps used for the smoothing. The final value is the span value that controls what portion of the data is considered for each prediction.</div>
<div><br></div><div><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; ">
CREATE OR REPLACE FUNCTION loess(double precision[], double precision[], double precision)<br>RETURNS double precision[] AS<br>$BODY$<br>x <- arg1<br>y <- arg2<br>s <- arg3<br>x.loess <- loess(x ~ y, span=s, data.frame(x=x, y=y))<br>
x.predict <- predict(x.loess, data.frame(y=y))<br>$BODY$<br>LANGUAGE plr VOLATILE STRICT<br>COST 100;</blockquote></div><div><br></div><div><br></div><div>This function wraps the PL/R call and wraps it to call the loess function on each dimension. This function unwraps the data to and from the single dimension arrays to pass into the above PL/R function. The span value that this wrapper takes is an absolute unit in the units of the m value that get converted into the proportion that is used in the above function.</div>
<div><br></div><div><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; ">
CREATE OR REPLACE FUNCTION loess(geometry, double precision)<br> RETURNS geometry AS<br>$BODY$<br>SELECT<br><span class="Apple-tab-span" style="white-space:pre"> </span>makeline(setsrid(makepoint(x,y,z,m),srid($1))) from (<br>
<span class="Apple-tab-span" style="white-space:pre"> </span>select unnest(x) as x,unnest(y) as y,unnest(z) as z,unnest(m) as m from (<br><span class="Apple-tab-span" style="white-space:pre"> </span>select<br><span class="Apple-tab-span" style="white-space:pre"> </span>loess(array_agg(st_x(point)),array_agg(st_m(point)),$2/max(lastm)) as x,<br>
<span class="Apple-tab-span" style="white-space:pre"> </span>loess(array_agg(st_y(point)),array_agg(st_m(point)),$2/max(lastm)) as y,<br><span class="Apple-tab-span" style="white-space:pre"> </span>loess(array_agg(st_z(point)),array_agg(st_m(point)),$2/max(lastm)) as z,<br>
<span class="Apple-tab-span" style="white-space:pre"> </span>array_agg(st_m(point)) as m<br><span class="Apple-tab-span" style="white-space:pre"> </span>from (<br><span class="Apple-tab-span" style="white-space:pre"> </span>select dump_linestring_points($1) as point ,st_m(st_endpoint($1)) as lastm<br>
<span class="Apple-tab-span" style="white-space:pre"> </span>) as foo <br><span class="Apple-tab-span" style="white-space:pre"> </span>) as bar ) as baz<br>$BODY$<br> LANGUAGE sql VOLATILE<br> COST 100;</blockquote>
<div>
<br></div><div>dump_linestring_points is a hack to get around the performance limitations of st_dumppoints that simply parses the EWKT to create the list of points rather than creating a loop through the points.</div><div>
<br></div><div><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; ">
CREATE OR REPLACE FUNCTION dump_linestring_points(geometry)<br> RETURNS SETOF geometry AS<br>$BODY$SELECT<br><span class="Apple-tab-span" style="white-space:pre"> </span>(<br><span class="Apple-tab-span" style="white-space:pre"> </span>'SRID=' || <br>
<span class="Apple-tab-span" style="white-space:pre"> </span>bar ||<br><span class="Apple-tab-span" style="white-space:pre"> </span>';POINT(' || <br><span class="Apple-tab-span" style="white-space:pre"> </span>foo || <br>
<span class="Apple-tab-span" style="white-space:pre"> </span>')'<br><span class="Apple-tab-span" style="white-space:pre"> </span>)::geometry <br>FROM <br><span class="Apple-tab-span" style="white-space:pre"> </span>regexp_split_to_table(<br>
<span class="Apple-tab-span" style="white-space:pre"> </span>rtrim(<br><span class="Apple-tab-span" style="white-space:pre"> </span>substring(asewkt($1) from 23)<br><span class="Apple-tab-span" style="white-space:pre"> </span>,')'<br>
<span class="Apple-tab-span" style="white-space:pre"> </span>),<br><span class="Apple-tab-span" style="white-space:pre"> </span>','<br><span class="Apple-tab-span" style="white-space:pre"> </span>) as foo , srid($1) as bar<br>
WHERE st_geometrytype($1)='ST_LineString';$BODY$<br> LANGUAGE sql IMMUTABLE STRICT<br> COST 1<br> ROWS 100;</blockquote></div><div><br></div><div><br></div><div>Hopefully this can be of use to someone else who has noisy track type data! </div>
<div><br></div><div>David</div><br>-- <br>************************************<br>David William Bitner<br>
</div>