Pattern similarity in time-series

Question

I have a PostgreSQL database and my data-set include time-series of temperature for two stations. I would like to look their pattern's similarity in similar time sequences.

For station number 1:

+------------+------------------+
|temperature |dt                |
+------------+------------------+
| 4.22       |9/15/2007 12:12:12|                  
| 5.11       |9/15/2007 13:14:16|
| 6.16       |9/15/2007 14:16:02|
| 6.01       |9/15/2007 15:18:23|
| 7.09       |9/15/2007 16:21:01|
+------------+------------------+

For station number 2:

+------------+------------------+
|temperature |dt                |
+------------+------------------+
| 3.12       |9/15/2007 12:12:12|                  
| 4.15       |9/15/2007 13:14:16|
| 5.26       |9/15/2007 14:16:02|
| 6.15       |9/15/2007 15:18:23|
| 7.23       |9/15/2007 16:21:01|
+------------+------------------+

I would like to ignore true values and compare time-series with respect to the pattern. Moreover, 1 time slice by 1 time slice comparison is enough for my work. Please help me on discrete Fourier transformation or slope to compare the patterns.

Answer 1

Ok so this is what I would recommend:

1. Use a custom aggregate for a moving average. the aggregate would use essentially an array to store values and return the average of the values in the array. New values would be appended to the end, and old values dropped off. One could use array_agg with unnest(), limit, and offset to do the work.

2. Use a windowing function to tie the moving average to the row.

3. Do a calculation between values in the above to generate your pattern....

4. Create another aggregate which gives you a moving pattern returning an array

5. Write an operator matching the array and use a windowing function call to tack this on to the returned record

I do think that the above can all be done in SQL with SQL-language functions. It is, however, way too complicated to even get into sample code here. However I can't help but wonder whether PL/R might not be helpful here as well.