Grafana GuiWspr Rocks!Wspr NetDB Structure

Welcome to WSPR Live

This page allows you to do analysis on the real time wspr spot data. The database contains all spots ever reported to wsprnet.org and allows public access to the data. Interested in this project or any ideas on improving it? Drop me a message to admin at wspr live.

Disclaimer

The data shown here is the raw data as reported, saved and published by wsprnet.org. So there might be duplicates, false spots and other errors in the data. Keep this in mind when seeing something strange. You are allowed to use the services provided on wspr.live for your own reasearch and projects, as long as the results are accessable free of charge for everyone. So you are not allowed to use this service for commercial services or apps.

Tools provided here

Graphical Interface (Grafana) You can use the graphical user interface located at /gui to start your research. It is a Grafana based dashboard which shows some basic visualisations of the spot data. If you are familiar with the amazing interfacy by VK7JJ you might try his new page wspr rocks! which is powered by this database.

Wspr Exporter The Wspr Exporter allows you to easily download large chunks of the wspr database filtered by some simple parameters.

WSPR GoogleEarth Visualizer This service allows you to add real time spots to Google Earth.

The database itself You can directly access the dabase using the domain db1.wspr.live. More details on how to access it are given below.

System components

Database The heart of this system is the database service containing all the spots ever reported to wsprnet.org. All the visualisation and processing is done here. It is a highly optimized database capable of processing the full dataset in an astonishing small amount of time. The processing power required to run it is kindly provided by the awesome Wspr Daemon team.

SQL Query interface The database provides a SQL based query interface which is used by all other services.

Scraping Service The Scraping service does query wsprnet.org every few minutes for new spots reported there. New spots are then put into the database module for future processing.

Data Update Service The Data Update Service downloads the wsprnet.org csv exports every 24 hours to sync up the Database with wsprnet.org. This is necessary because some spots might be missed by scraping or were reported long after they have been recorded.

   +---------------+  +---------------+
   |  wsprnet.org  |  |  wsprnet.org  |
   |  CSV Exports  |  |      API      |
   +---------------+  +---------------+
              \          / 
               \        /
           +---------------+
           |    Database   |
           +---------------+
          /        |        \
         /         |         \
        /          |          \
       /           |           \
+-----------+ +----------+ +--------------+
|  Grafana  | |   Wspr   | |  wspr.rocks  |  
| Dashboard | | Exporter | | and your app |  
+-----------+ +----------+ +--------------+

Database

The database can be accessed by a HTTP interface. There is no authentication required but only readonly requests are possible. The query language used is called SQL. Please take a look into the official Full SELECT Query Documentation to learn more about the supported parameters.

There are some limits to this queries. As long as everyone tries to keep their queries fast to process this API will stay as is. If however someone does not play fair and heavily degrades performance we might have to further limit the API access. If you are interested in using this database for your own applications or any questions arise please feel free to contact us under the email address given on the top of this page.

The database is optimized for queries which filter their results by time and band. So every query you take should limit the data you request by time and band to make them fast. When doing statistical queries on the whole dataset using as less columns as possible also improves the query speed. Even when the database can perform JOINs you should avoid them. Using GROUP BY and sub queries is the way to go with clickhouse. Not matching lines in JOINS might cause ambiguity because their field values are filled up with the fields type default value, not NULLs. This might be problematic for the band column where 0 is actually a valid wspr band.

Data access

The clickhouse http interface is exposed on http://db1.wspr.live:80/ and https://db1.wspr.live:443/. The database is called wspr and the spot table rx. You can run queries directly against it by using the http client of your coice and adding a query parameter:

http://db1.wspr.live/?query=SELECT * FROM wspr.rx LIMIT 1;

There is also a library for your favourite programming language. When searching for it make sure it uses the clickhouse http interface, not the native one normally located on port 9000.

To get started you can play around with the following examples on the wspr.rocks Playground.

Query examples

Get the amount of spots in the database: SELECT count(*) FROM wspr.rx

2732413985

Get a spot from the database: SELECT * FROM wspr.rx LIMIT 1

48854   2008-03-16 20:38:00 0   LA5VNA  59.688  10.792  JO59jq  G0NBD   53.438  -3.042  IO83lk  1092    45  502988  10  -18 0       0

You can specify the result format like this: SELECT * FROM wspr.rx LIMIT 1 FORMAT CSV. All supportet formats are listed here.

48854,"2008-03-16 20:38:00",0,"LA5VNA",59.688,10.792,"JO59jq","G0NBD",53.438,-3.042,"IO83lk",1092,45,502988,10,-18,0,"",0

Limitations

Wspr.live does only allow the ?query= parameter for database access. Everything else will be removed from the HTTP request. Also HTTP headers are filtered. POST requests are currently allowed but might be removed in the future, so please do not rely on them.

JavaScript example (nodejs)

# uses https://github.com/apla/node-clickhouse
# create the database connection
const ClickHouse = require('@apla/clickhouse')
const ch = new ClickHouse({ "host":"db1.wspr.live", "port":80, "readonly": true})

# query some data
var stream = ch.query("SELECT * from wspr.rx limit 1");

# handle data
stream.on('metadata', (columns) => { 
    console.log(columns);
});

stream.on('data', (row) => {
    console.log(row);
});

stream.on('error', (err) => { 
    console.log(err);
});

stream.on('end', () => {
    console.log("done");
});

JavaScript example (browser)

fetch('https://db1.wspr.live/?query=SELECT * FROM wspr.rx LIMIT 1 FORMAT JSONCompact')
  .then(function (data) {
    if (data.ok) {
        data.json().then(jso => console.log(jso));
    } else {
        data.text().then(text => console.log("Request error: " + text));
    }
});

Python example

A python example is provided here.

Unix example

wget -q -O - "http://db1.wspr.live/?query=SELECT * FROM wspr.rx LIMIT 1;"

Using your own Grafana

Of course you can use this database in your own Grafana installation to play around with basic visualizations. Just create a new Datasource of type Clickhouse (you need to install the Clickhouse datasource plugin) with URL https://db1.wspr.live/, and default database wspr.

Database Fields

The spots are ordered by band and time and are partioned by month. So you should always try to limit the amout of data you query by time and band. There is an aditional index for the spot id. The following table gives an overview of the table columns. Numeric column types are given as type (int/float) and with their size. So UInt64 for example is an unsigned integer with 64 bits. Float32 is a signed float using 32 bits. Time uses the clickhouse DateTime type but internally it is a unix timestamp with one second precision. The latitude and longitude fields are derived from the wsprnet.org locator fields and represent the center of the reported locator box.

Column Type Description
id UInt64 The unique spot id (same id as used by wsprnet.org)
time DateTime The time the spot was received and reported
band Int16 The band the transmission took place on (First digit of Frequency not the band in meter!) (-1: LF, 0: MF, 1: 160m, … see band table below for translations)
rx_sign String The receiver callsign
rx_lat Float32 The receiver latitude in degrees (derived from rx_loc)
rx_lon Float32 The receiver longitude in degrees (derived from rx_loc)
rx_loc String The receiver locator
tx_sign String The sender callsign
tx_lat Float32 The sender latitude in degrees (derived from tx_loc)
tx_lon Float32 The sender longitude in degrees (derived from tx_loc)
tx_loc String The sender locator
distance UInt16 The distance in km
azimuth UInt16 The sender azimuth (as on wsprnet.org angle of receiver as seen from the transmitter 0 to 359)
rx_azimuth UInt16 The receiver azimuth (angle of incoming short path as seen from the receiver 0 to 359)
frequency UInt32 The receive frequency in Hz
power Int8 The reported transmit power in dBm (might be wrong when used for other stats)
snr Int8 The reported snr in dB
drift Int8 The reported frequency drift (-3 to 3 unknown unit, might be Hz)
version String The receiver software version string as reported (see wsprnet.org forum for details on this)
code Int8 The code/mode flag (there are discussions on the wsprnet.org forum on how to interpret this field)

Bands table

There is also a table called bands containing the following static information about the wspr bands.

band frequency display
-1 136000 LF
0 474200 MF
1 1836600 160m
3 3568600 80m
5 5287200 60m
7 7038600 40m
10 10138700 30m
14 14095600 20m
18 18104600 17m
21 21094600 15m
24 24924600 12m
28 28124600 10m
50 50293000 6m
70 70091000 4m
144 144489000 2m
432 432300000 70cm
1296 1296500000 23cm

Beacons table

The beacons table contains information about the beacons of the Intl. WSPR Beacon Project. This table might be incomplete but gets regularly updated.

Column Description
id An unique beacon id
sign The beacon callsign
lat The beacon latitude in deg
lon The beacon longitude in deg
hagl Hight of antenna tip above ground in Meter
hamsl Hight of antenna tip above mean sea level in Meter
power Transmit power (power at antenna input) in dBm
max_gain Max antenna gain in dBi

Wsprdaemon tables

There also are both the wsprdaemon_spots and the wsprdaemon_noise tables containing aditional spot data collected by wspr deamon receivers. All spots in there are also reported to wsprnet.org table but this tables contain additional columns and noise data. They are exact matches of the official wsprdaemon tables and are synced with them once per minute. Please take a look into the documentation on wsprdaemon.org for table and column descriptions.

Changelog

Date Changes
12. Aug 2021 Adding wsprdaemon_spots and wsprdaemon_noise tables
8. Aug 2021 Adding information on how to use this database in Grafana instances
1. Jul 2021 Adding JavaScript browser example
Adding structure of beacons table
10. Jun 2021 Adding bands table and documentation
Adding rx_azimuth column to main spots table
27. May 2021 Improving documentation
1. Apr 2021 Adding Python and JavaScript data access examples and improving documentation
23. Mar 2021 Improving database scheme (searches by locator are now way faster)
21. Mar 2021 Improving many dashboards in grafana
10. Mar 2021 Wspr.live goes down for about one weak because the server hosting it went into the clouds forever (RIP)
6. Feb 2021 Adding spot id field
2. Feb 2021 The GUI now also uses the wsprdaemon.org hosted database
1. Feb 2021 Adding a description on how to access the wsprdaemon.org hosted database
The Wspr Exporter tool is now backed by the wsprdaemon hosted database
Jan 2021 Switching to wsprdaemon.org as primary real time data source
Adding two new dashboards to the gui: Distance plot and SNR Comparison
Mid 2019 The basic structure using grafana and clickhouse was up and running
Mar 2019 The initial idea for this project came up, inspired by wsprlive.net

Page created with Pandoc using a modificated version of killercup’s amazing style.