Welcome to the Cumulus Support forum.

Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024

Cumulus MX V4 beta test release 4.0.0 (build 4018) - 28 March 2024

Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)

Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki

Davis airlink insert database

Discussion specific to Davis weather stations
Post Reply
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Davis airlink insert database

Post by The it guy »

Hi all I was just wondering I've just set up my new airlink and I was looking in cumulus settings to upload airlink data to the database like all the other I cannot see the option but I have seen a custom real-time upload is there anyway I can upload all my airlink data to the database via that by that setting so can somebody give me an idea or how this is done and I not nothing about SQL?

Thanks in advance
iandrews
Posts: 139
Joined: Sun 03 Jan 2010 10:57 am
Weather Station: Davis Pro2
Operating System: Windows 2012R2
Location: London

Re: Davis airlink insert database

Post by iandrews »

After getting my Airlink a month or so back (and WLL) I am wanting to do this as well, but haven't had time yet.

You are correct though and you can use the custom SQL load (say every 1 minute). Assume you have a SQL server to upload too, then you need to create you own table in it, and then in the custom SQL field write the query to insert the Airlink data using the web tags- see https://cumuluswiki.org/a/Webtags#Davis_AirLink

To get you started, here is a commend I used when I was logging the console stats (before I got the WLL).

INSERT INTO davis (LogDateTime,TotalPacketsReceived,NumCRCerrors,TotalPacketsMissed,NumberOfResynchs,MaxInARow) VALUES ('<#metdate format="yyyy-MM-dd"> <#timehhmmss>','<#DavisTotalPacketsReceived>','<#DavisNumCRCerrors>','<#DavisTotalPacketsMissed>','<#DavisNumberOfResynchs>','<#DavisMaxInARow>')
Mapantz
Posts: 1775
Joined: Sat 17 Dec 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 11 x64
Location: Dorset - UK
Contact:

Re: Davis airlink insert database

Post by Mapantz »

I used a custom command to add air quality to my database at 1 minute intervals. This is only for my recent graphs though - to show 4 days of data - hence retention string at the end.

Code: Select all

INSERT IGNORE INTO AirQuality (LogDateTime,PM1,PM2p5,PM10,Aqi24Pm2p5,Aqi24Pm10) VALUES ('<#date format=yyyy-MM-dd> <#time format=HH:mm:ss>',<#AirLinkPm1Out>,<#AirLinkPm2p5Out>,<#AirLinkPm10Out>,<#AirLinkAqiPm2p5_24hrOut>,<#AirLinkAqiPm10_24hrOut>); DELETE FROM AirQuality WHERE LogDateTime < DATE_SUB(NOW(), INTERVAL 96 HOUR);
Image
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Re: Davis airlink insert database

Post by The it guy »

Ok thank you for that that's a great help do you mind me using your line of code ?to get me started and is there anyway to import the data I've already got into that database from marks script as I've got nearly whole months worth of data I don't know whether to just start fresh in the database or is there a way to import it all at once?
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Re: Davis airlink insert database

Post by The it guy »

Ok so I got that database to work just a question can't I just add airlink into real-time column instead of making another table with custom upload command
User avatar
mcrossley
Posts: 12694
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Davis airlink insert database

Post by mcrossley »

You can, but then you have a non-standard table and have to be mindful of that in the future if the standard table changes - plus you cannot then use the catch-up tools to insert your airlink data if you ever need to. Better to keep it separate I think.
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Re: Davis airlink insert database

Post by The it guy »

Ok so I keep the table as it is then and just create another table for air Link so in your last message did you say I can use your script to import about a month's worth of airlink data into the new database table or is that not an option


if so can you help me mod the Script to import it?
User avatar
mcrossley
Posts: 12694
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Davis airlink insert database

Post by mcrossley »

The current import scripts do not support AirLink as there is no standard MySQL table for it.
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Re: Davis airlink insert database

Post by The it guy »

ok thank you for clearing that up sorry to take up your time is there another way I can import the data in or so just just to start fresh with it
iandrews
Posts: 139
Joined: Sun 03 Jan 2010 10:57 am
Weather Station: Davis Pro2
Operating System: Windows 2012R2
Location: London

Re: Davis airlink insert database

Post by iandrews »

Speaking of logging the Air Quality figures to a SQL DB, what would be the best ones to do, assume current PM1, PM2.5, PM10, then what, 24 hour figures, but should I do the PM figure or the AQI figure, and is there any need / reason to log the 1 and 3 hour figures. Or seeing as it's just a small amount of text, log it all.

Also, the cumulus wiki says the nowcast figure is 24 hour weighted, but the Davis Airlink API page says 12 hour weighted, are these nowcast the same figures, but one source of information just has the incorrect time frame.
User avatar
mcrossley
Posts: 12694
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Davis airlink insert database

Post by mcrossley »

MX nowcast just uses the value supplied by Davis which is the based on the US EPA Nowcast - a 12 hour weighted average.
Post Reply