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. You are not allowed to use this service for any commercial or profit oriented use cases. The complete wspr infrastructure is maintained by volunteers in their spare time, so there are no guarantees on correctness, availability or stability of this services.

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.

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 NOT allowed.

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

This example runs in plain python 3. An ipython notebook example using advanced async methodes is provided here.

import urllib.request
import json

def wsprlive_get(query):
    # put together the request url
    url = "https://db1.wspr.live/?query=" + urllib.parse.quote_plus(query + " FORMAT JSON")

    # download contents from wspr.live
    contents = urllib.request.urlopen(url).read()

    # return the json decoded data
    return json.loads(contents.decode("UTF-8"))["data"]

if __name__ == "__main__":
    print(wsprlive_get("SELECT * FROM rx LIMIT 1"))

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 see Wspr / FSt4W mode mappings below to find out what this values mean

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. This table is the basis for the Beacon Stations Dashboard.

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
antenna Antenna type

Monitors table

The monitors table contains information about the monitor stations of the Intl. WSPR Beacon Project. This table might be incomplete but gets regularly updated. This tables is the basis for the Monitor Stations Dashboard.

Column Description
id An unique monitor id
sign The beacon callsign
locator The beacon maidenhead locator
lat The monitor latitude in deg
lon The monitor longitude in deg
hagl Hight of antenna tip above ground in Meter
hamsl Hight of antenna tip above mean sea level in Meter
max_gain Max antenna gain in dBi
antenna Antenna type

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. The code field contains wsprdeamon “mode” values!

Wspr / FSt4W mode mappings

Actual mode wsprdaemon “mode” mode reported to wsprnet.org wsprnet.org “code” before 2023.01.16 wsprnet.org “code” after 2023.01.16 wspr.rocks “mode”
WSPR-2 2 2 1 1 2
WSPR-15 15 15 2 2 15
FST4W-120 3 3 1 3 2
FST4W-300 6 5 4 4 5
FST4W-900 16 16 1 5 2
FST4W-1800 31 30 8 8 30

wspr.rocks shows the mode as reported to wsprnet.org by reverse mapping the wsprnet.org “code” for spots reported before 2023.01.16.

Space Weather Tables

Sunspot Number

Source

WDC-SILSO, Royal Observatory of Belgium, Brussels

https://wwwbis.sidc.be/silso/home

License
SILSO data is under CC BY-NC4.0 license (https://goo.gl/PXrLYd) which means you can:

Share - copy and redistribute the material in any medium or format
Adapt - remix, transform, and build upon the material

As long as you follow the license terms:

Attribution - You must give appropriate credit, provide a link to the license, and indicate if changes were made. You may do so in any reasonable manner, but not in any way that suggests the licensor endorses you or your use.
NonCommercial - You may not use the material for commercial purposes.
No additional restrictions - You may not apply legal terms or technological measures that legally restrict others from doing anything the license permits.
Table Structure (weather_eisn)
Column Description
time Timestamp of data
spotcount Count of observed sunspots at given time.

Geomagnetic Data

Source

Geomagnetic Observatory Niemegk, GFZ German Research Centre for Geosciences, Potsdam, Germany.

License

CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/)

https://www.gfz-potsdam.de/startseite/

Table Structure (weather_kp_ap)
Column Description
time Timestamp of data
Kp Kp index
ap ap index

Solar Wind

Source

National Oceanic and Atmospheric Administration Space Weather Prediction Center

License

https://www.weather.gov/disclaimer

https://www.swpc.noaa.gov/products/real-time-solar-wind

Table Structure (weather_mag)
Column Description
time Timestamp of data
bt bt index
bx bx index
by by index
bz bz index
lat
lon
Table Structure (weather_plasma)
Column Description
time Timestamp of data
density
speed
temperature

Changelog

Date Changes
19. Jul 2024 Fixing a bug inside the data ingress pipeline which caused spot loss.
Reimporting the whole spot history from wsprnet.org which introduced some extra spot loss. Live data will be fine from now on.
19. Dec 2023 Removed support for Clickhouse result compression. Use HTTP stream compression instead.
Added CORS preflight compatibility.
29. Oct 2023 A limit of 20 requests per minute has been introduced to the query interface to make sure there are enough resources available for everyone at any time. Higher burst rates are still accepted.
28. Oct 2023 POST request are not longer accepted.
14. May 2023 After upgrading Clickhouse to the lastest version there was a little confusion regarding quotes in the real time spot import. Everything is fixed by now, sorry for that.
28. Jan 2023 Adding new wsprnet.org mode mapping to value mapping table.
1. Jan 2023 Adding wspr.rocks “mode” values to value mapping table.
Adding and improving some Grafana pages.
29. Oct 2022 Adding columns mode, ov_count and wsprnet_info to wsprdaemon_spots table.
Changing type of columns SNR, rx_az, tx_az in wsprdaemon_spots to float.
Fixing sunspot count import.
05. Jul 2022 Adding Wspr Mode Watch dashboard to gui and adding wspr mode / code field value mappings to this page.
20. Jun 2022 Adding wsprdaemon_spots_simple view of the wsprdaemon_spots table to be a drop in replacement for the rx spots table. Also improving wsprdaemon spot import to be more robust when handling bad spots.
29. Jun 2022 Grafana has been updated to version 9 and some minor improvements have been applied to some dashboards over the last week.
14. Apr 2022 rx_azimuth column is named correctly again.
17. Mar 2022 Introducing endpoints.txt file containing available database endpoints.
17. Jan 2022 Wspr.live does not longer import the wsprnet.org CSV dumps, because spots are missing in there. So currently the only spot source is the real time spot input which contains all spots as far as we know.
19. Nov 2021 Adding space weather data.
7. Nov 2021 Adding monitors table.
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 week 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.