Import wview data into Lightsoft Weather Center

The Background

When I first started storing data for this website, the software I used was wview (http://wviewweather.com) a pretty good and flexible solution for collecting the data from my Davis Station Vantage Vue.

I've recently moved a lot of my monitoring software to a Mac Mini Server and although wview will work on Mac OSX, I came across Lightsoft Weather Center (LWC) which is a native Mac OSX product and works very well.

As I already had almost a year of data in wview, I needed to migrate this data to bring my LWC historical stats up to date.

This document was written for LWC V2.00, Build 2112 and wview 5.17.2 .

The LWC Data

The LWC Software has a data importer but it only imports from the Davis Station Weather Display software files - wview stores data in sqlite databases in it's own format.

The LWC Data files which are outlined in the LWC documentation have some nice, clean documentation at the top of each monthly generated file.

So each LWC Data File, one for each month, will look like the this one below (from the start of November). The files are named Month_LWC_Data.lw2 (eg 11_LWC_Data.lw2 - months lke Febuary will be 2_LWC_Data.lw2).

 LWC data file ***If you modify this file, be sure to delete the relevent .hrs file***.
t and V are not optional, all other fields are.
t is the day, hour and minute (2 digits each), T is outside temperature, Ti is internal temperature, T1 to T8 is auxiliary temperatures, D is dew point, Pr is barometric pressure, W is wind speed,
Wd is wind direction, Wc is wind chill, Wg is wind gust, Ph is hourly precipitation, P is total precipitation,
H is outside humidity, Hi is internal humidity, H1 to H8 are auxiliary humidity sensors, S is solar,
Sm1 to Sm4 is soil moisture, Lw1 to Lw4 is leaf wetness, St1 to St4 is soil temperature,
Lt1 to Lt4 is leaf temperature, U is UV, C is current conditions (delimited by double quotes), V is validation.

VERS:3

 2290 t:021410 T:20.00 Ti:24.33 D:10.12 Pr:1017.2333 W:6.44 Wd:274 Wc:20.00 Wg:9.66 Ph:0.00 P:0.00 H:53 Hi:58 S:0 U:0.0 Lw4:0.0 C:"Light Clouds" V:4
    2 t:021412 T:19.89 Ti:24.33 D:10.02 Pr:1017.2333 W:6.44 Wd:272 Wc:19.89 Wg:14.48 Ph:0.00 P:0.00 H:53 Hi:58 S:0 U:0.0 Lw4:0.0 C:"Light Clouds" V:4
    4 t:021414 T:19.83 Ti:24.39 D:10.25 Pr:1017.3010 W:6.44 Wd:270 Wc:19.83 Wg:14.48 Ph:0.00 P:0.00 H:54 Hi:58 S:0 U:0.0 Lw4:0.0 C:"Light Clouds" V:4
   10 t:021420 T:19.78 Ti:24.44 D:10.20 Pr:1017.3010 W:4.83 Wd:272 Wc:19.78 Wg:14.48 Ph:0.00 P:0.00 H:54 Hi:58 S:0 U:0.0 Lw4:0.0 C:"Light Clouds" V:4
   13 t:021423 T:19.89 Ti:24.44 D:10.30 Pr:1017.3010 W:4.83 Wd:249 Wc:19.89 Wg:14.48 Ph:0.00 P:0.00 H:54 Hi:58 S:0 U:0.0 Lw4:0.0 C:"Light Clouds" V:4


and so on .....

A couple of things of note:

1. The comments at the top are not required if you are creating the files yourself (maybe a bit obvious)
2. The number before each row is not required
3. The VERS:3 is critical
4. The P (Precipitation) is a daily cumulative value rather than the value for the time period being shown.

Wview Data

The wview data that is most useful for our requirements here is the 'archive' table stored in the file wview-archive.sdb . In my Linux installation is was located with all of the other database files in /usr/local/var/wview/archive/ .

The structure of the archive table is as follows:

    dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY,
    usUnits INTEGER NOT NULL,
    interval INTEGER NOT NULL,
    barometer REAL,
    pressure REAL,
    altimeter REAL,
    inTemp REAL,
    outTemp REAL,
    inHumidity REAL,
    outHumidity REAL,
    windSpeed REAL,
    windDir REAL,
    windGust REAL,
    windGustDir REAL,
    rainRate REAL,
    rain REAL,
    dewpoint REAL,
    windchill REAL,
    heatindex REAL,
    ET REAL,
    radiation REAL,
    UV REAL,
    extraTemp1 REAL,
    extraTemp2 REAL,
    extraTemp3 REAL,
    soilTemp1 REAL,
    soilTemp2 REAL,
    soilTemp3 REAL,
    soilTemp4 REAL,
    leafTemp1 REAL,
    leafTemp2 REAL,
    extraHumid1 REAL,
    extraHumid2 REAL,
    soilMoist1 REAL,
    soilMoist2 REAL,
    soilMoist3 REAL,
    soilMoist4 REAL,
    leafWet1 REAL,
    leafWet2 REAL,
    rxCheckPercent REAL,
    txBatteryStatus REAL,
    consBatteryVoltage REAL,
    hail REAL,
    hailRate REAL,
    heatingTemp REAL,
    heatingVoltage REAL,
    supplyVoltage REAL,
    referenceVoltage REAL,
    windBatteryStatus REAL,
    rainBatteryStatus REAL,
    outTempBatteryStatus REAL,
    inTempBatteryStatus REAL

The main columns we need is the dateTime column (seconds since unixepoch - 01/01/1970) and the relevant sensor columns.

The data in the archive table are in US Imperial units so we also need to do some imperial to metric conversion.

So mapping between wview and LWC we need the following data:

So the challenge was to write some SQL code to extract the data, converting it to metric and calculate the cumulative rain total.

The Extraction Code

After a bit of trial and error and the belief I could do it all in SQL (SQLite is very much 'Oracle-like' in coding and some functionality) I came up with the following:

select  't:'||strftime('%d%H%M',datetime(aa.ddateTime,'unixepoch','localtime','-1 hour'))||
   ' T:'||outTemp||
    ' Ti:'||inTemp||
    ' D:'||dewpoint||
    ' Pr:'||barometer||
    ' W:'||windspeed||
    ' Wd:'||windDir||
    ' Wc:'||windchill||
    ' Wg:'||windGust||
    ' Ph:'||rainRate||
    ' P:'||round(sum(bb.rounded_rain),2)||
    ' H:'||outHumidity||
    ' Hi:'||inHumidity||
    ' V:4'
from
(select  strftime('%Y%m%d',datetime(a.dateTime,'unixepoch','localtime','-1 hour')) dday,
           datetime-3600 ddateTime,
    round(a.rain*25.4,2) rain,
        round((outTemp-32)/1.8,2) outTemp,
    round((inTemp-32)/1.8,2) inTemp,
    round((dewpoint-32)/1.8,2) dewpoint,
    round(barometer * 33.8639,4) barometer,
    round(windspeed*1.609344,2) windspeed,
    windDir,
    round((windchill-32)/1.8,2) windchill,
    round(windGust*1.609344,2) windGust,
    round(rainRate*25.4,2) rainRate,
    outHumidity,
    inHumidity
         from archive a
    where strftime('%Y%m',datetime(a.dateTime,'unixepoch','localtime','-1 hour')) = '201010'
) aa,
(select strftime('%Y%m%d',datetime(b.dateTime,'unixepoch','localtime','-1 hour')) dday,
    datetime-3600 ddateTime,
    round(rain*25.4,2) rounded_rain
         from archive b
    where strftime('%Y%m',datetime(b.dateTime,'unixepoch','localtime','-1 hour')) = '201010'
) bb
where bb.dday = aa.dday
  and strftime('%Y%m',datetime(aa.ddateTime,'unixepoch','localtime')) = '201010'
   and  bb.ddateTime <= aa.ddateTime
group by  aa.ddateTime

Some little things about the query:

1. It is for the month of October 2010 (hence the 201010 in the where clauses)
2. This example caters for daylight savings time.  To run it in non-DST see the full example below :- essentially remove the -3600 secs bit and the '-1 hour' in the date conversion.
3.  The second 'inline view' does the cumulative calculation for the rain and we join it back to the raw data query
4. Although not strictly required in each inline view , the date where clauses does improve the performance.

So running this query generates the data for the month - it could take 5-10 minutes depending on your host machine.

All we have to do is now bring it all together, create the first line heading and generate our files.

1. Create a file like this one ( LWC_Dump.sql - which is for March 2010.)
2. Edit it for the month you require.
3. Login to the wview archive database
4. Run the script
5. Do it again for each month you require

Example.

cd /usr/local/var/wview/archive/
[root@redhead archive]# sqlite3 wview-archive.sdb
SQLite version 3.3.3
Enter ".help" for instructions
sqlite> .read lwc_dump.sql
sqlite> .quit

Get the Data into LWC

There is lots of good information on how to do this in the LWC Manual as well as help available from the LWC forums.  The LWC Data files are stored in 'your home folder' -> Library -> LWCData -> Location1 (probably) -> Year (eg 2010).

The steps you need to do are:

1. Move the monthly generated files to your LWC machine (somewhere like your home or Downloads directory)
2. Make sure LWC is not running
3. Backup you data directory just in case everything goes wrong
4. Delete the files like *.lw2.hrs if you are overwriting (or have edited) a specific month. These will be re-generated when LWC is restarted.
5. Copy the Monthly Files generated from your wview database to the appropriate Year folder in the LWC data directory
6. Startup LWC

If there is a problem with any file, LWC will let you know and give you the option to Quit or Re-initialise the file (Quit and clean it up would be a good option :-) ).

 I had quite a few stumbling blocks on the way, so if you hit any yourself, let me know and I might be able to help out.