Hi Pierre!
On 7 January 2012 12:20, Pierre C <lists@peufeu.com> wrote:
> I'm stuck home with flu, so I'm happy to help ;)
[...]
> I'll build an example setup to make it clearer...
[...]
That's almost identical to my tables. :-)
> Note that the "distance" field represents the distance (in time) between the
> interpolated value and the farthest real data point that was used to
> calculate it. Therefore, it can be used as a measure of the quality of the
> interpolated point ; if the distance is greater than some threshold, the
> value might not be that precise.
Nice idea!
> Although this query is huge, it's very fast, since it doesn't hit the big
> tables with any seq scans (hence the max() and min() tricks to use the
> indexes instead).
And it can easily be tamed by putting parts of it into stored pgpsql functions.
> I love how postgres can blast that huge pile of SQL in, like, 50 ms...
Yes, indeed. It's incredible fast. Brilliant!
> If there is some overlap between packet data and data already in the log,
> you might get some division by zero errors, in this case you'll need to
> apply a DISTINCT somewhere (or simply replace the UNION ALL with an UNION,
> which might be wiser anyway...)
I do have a unique constraint on the actual table to prevent duplicate
data in case of retransmission after a failed connect. It's easy
enough to delete the rows from packet that already exist in the main
table with a short one line SQL delete statement before the
interpolation and merge.
> Tada.
:-))))
> Enjoy !
I certainly will. Many thanks for those great lines of SQL!
Hope you recover from your flu quickly!
All the best,
Marc