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.

Bands table

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

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.

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.

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

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

Link

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)

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/)

Link

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

Table Structure (weather_kp_ap)

Solar Wind

Source

National Oceanic and Atmospheric Administration Space Weather Prediction Center

License

https://www.weather.gov/disclaimer

Link

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

Table Structure (weather_mag)

Table Structure (weather_plasma)

Changelog

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