SQL search for wspr.rocks and general db queries.
=> expand the About and DB essential tabs for table columns and values.
See the 'rocks' demos if you want to execute the query in wspr.rocks.
5,000 spots is a hard limit.
And Saint Isidore of Seville said 'Let there be bugs'
Perform a search or load a file from the menu in the table view.
Select a chart from the menu above after the query data arrives.
Perform a search or load a file from the options menu above.
The spot table appears here as soon as the query data arrives.
about SQL ⌄
DB ref ⌄
demo queries ⌄
your queries ⌄
save query...
Advanced Search
Try the new "Easy" experimental search panel :-)
This app is a sandbox that directs SQL queries to Arne's amazingly fast database at wspr.live The demo examples are from casual testing and may be buggy. Check Arne's link for more info.
These are the db column names and spot values required for queries from Arne or Rob's [all spots] databases
Select "wspr.live [normal]" from wspr.rocks Prefs for access to this table
wspr.rx => Contains every WSPR spot since 2008, used by wspr.rocks for normal spot searches
wspr.beacons => Roland's WSPR beacon operators
wspr.monitors => Roland's WSPR monitor operators
query to view what is in a table: select * from wspr.rx limit 10
query to view the schema of a table: describe wspr.rx
Select "WD1 [normal]" or "WD2 [normal]" from wspr.rocks Prefs for access to these tables
wspr.rx => Used by wspr.rocks for normal spot searches by all wspr users
wspr.wsprdaemon_spots => extended spot and receiver info from WD software users
wspr.wsprdaemon_noise => receiver noise figures uploaded from WD software users
pskreporter.rx => spots uploaded by WSPR reporters to the PSK Reporter site
query to view what is in any of the tables: select * from table_name_above limit 10
• call signs must be uppercase single quoted 'VK7DIK' See the demos for more.
• power is in dBm (see conversion above). Distance is in kilometres. id is WSPRnet's unique spotnum.
• Arne's WSPR info includes the db field details, bands and mode mappings @ wspr.live
• The db vendor is ClickHouse, see their online reference
Compose your query here...
1. Edit your queries here. Click on a demo query to get started.
2. The [run query here] button
Displays results of tests and general queries in a table below
3. The [run query in wspr.rocks] button
Displays correctly formatted results in the wspr.rocks table, map and charts
• queries for wspr.rocks MUST return data from ALL 20 COLUMNS in the CORRECT ORDER
• use the demo 'rocks' templates to help with that
4. The simplest demo is 'rocks starter', have a look at it then click [run query here]
• because it contains results from all 20 columns you can [run query in wspr.rocks]
backup
restore
clear editor
run query here
run query in wspr.rocks
->
[copy results as CSV][copy results as HTML][save to disk as TSV]
Database query responses and SQL syntax errors are displayed here.
Click the table column labels to sort.
- save your own queries to the Saved list.
- saving an item using an existing name replaces the original.
Use Backup and Restore to save your queries to disk.
- the data is saved in JSON text format.
- there are free JSON text editors online.