Consultas personalizadas de WSPRnet: TX

Para consultar la base de datos (B.D) de WSPRnet se necesita tener unas nociones básicas del lenguaje SQL, que es un estándar de manejo de BB.DD relacionales, y conocer la estructura de la misma.

Una B.D. se compone básicamente de tablas que tienen columnas o campos y filas o registros

Una web que facilita las consultas mediante QSL a WSPRnet es https://wspr.rocks/ 

La base de datos WSPRnetse llama "wspr.live", y la tabla donde se encuentran los spots recibidos "wspr.rx" , por lo que las consultas que se hacen con el comando "SELECT" deberán incluir la clausula "FROM wspr.rx"


ESTRUCTURA DE LA TABLA WSPR.RX

Los nombres de las columnas de la tabla wspr-rx son los siguientes ( muchos son evidentes): 

  • id: identificador único del registro.
  • time: fecha y hora de la recepción ("spot") en formato YYY-MM-DD HH:MM:SS.
  • band: banda de frecuencias usada (  -1:LF,  0:MF,  1:160m,  3:80m,  5:60m,  7:40m,  10:30m,  14:20m,  18:17m,  21:15m,  24:12m,  28:10m,  50:6m,  70:4m,  144:2m,  432:70cm,  1296:23cm).
  • rx_sign: indicativo de la estación RX que ha recibido el menaje WSPR.
  • rx_lat: latitud geográfica de la estación RX en formato decimal (GG.DDD según sea N o S - ).
  • rx_lon: longitud geográfica de la estación RX en formato decimal (GG.DDD con signo según sea E o W -).
  • rx_loc: grid o locator  geográfica de la estación RX con 6 posiciones XXNNxx.
  • tx_sign: indicativo de la estación TX que ha emitido el menaje WSPR.
  • tx_lat: latitud geográfica de la estación TX en formato decimal (GG.DDD según sea N o S - ).
  • tx_lon: longitud geográfica de la estación TX en formato decimal (GG.DDD con signo según sea E o W -).
  • tx_loc: grid o locator geográfica de la estación TX con 6 posiciones XXNNxx.
  • distance: distancia entre el TX y el RX en km.
  • azimuth: azimuth del RX visto por el TX expresado en grados
  • rx_azimuth: azimuth del RX visto por el TX expresado en grados
  • frequency: frecuencias concreta usada expresada en Hz.
  • power: potencia del TX expresada dBm (-23dBm=0,2 W)
  • snr: relación señal (s) / ruido(N) expresada en dB, como tienen valor negativo, cuanto menor sea mejor es la relación.
  • drift: deriva
  • version: del receptor
  • code: tipo de protocolo usado  code 1 = mode 2 (WSPR2,FST4W-120),   code 2 = mode 15 (FST4W-900),   code 4 = mode 5 (FST4W-300),   code 8 = mode 30 (FST4W-1800)


CONSULTAS - QUERYS

Para conocer el número de spots recibidos por la red en un día (p.e. 13/6/2025) se hace  "SELECT COUNT(*)" desde las 00h00 del 13/6/2025 a las 00h00 del 14/6/2025 "AND time > '2025-06-13' AND time < '2025-06-14'" de la siguiente forma:

  • SELECT COUNT(*) FROM wspr.rx WHERE time > '2025-06-13' AND time < '2025-06-14';

El número de spots recibidos en un día como el 13/6/2025 fue de 3.562.511

Si son los spots recibidos desde una fecha hasta el momento actual podemos usar la claúsula NOW() y el dia actual como CURDATE()

  • SELECT COUNT(*) FROM wspr.rx WHERE time > '2025-06-13' AND time < NOW();
  • SELECT COUNT(*) FROM wspr.rx WHERE time >= CURDATE() AND time < NOW();

Los spots de ayer mediante dos formulas equivalentes una de forma explicita con las fechas y otra de forma implicita resrandole 1 dia a la fecha actual DATEADD(day, -1, CURDATE()) y la fecha actual CURDATE()

  • SELECT COUNT(*) FROM wspr.rx WHERE time >= '2025-06-23' AND time < '2025-06-24';
  • SELECT COUNT(*) FROM wspr.rx WHERE time >= DATEADD(day, -1, CURDATE()) AND time < CURDATE();

Ultima hora

  • SELECT COUNT(*) FROM wspr.rx  WHERE time >= NOW() - INTERVAL 1 HOUR  AND time < NOW();

Si se quiere conocer el numero de estaciones TX WSPR de las que se han recibido spots en un día en concreto:

  • SELECT  COUNT(DISTINCT tx_sign) FROM wspr.rx WHERE  time > '2025-06-13' AND time < '2025-06-14' 

El número de estaciones WSPR TX de las cuales se reciben spots  en un día como el 13/6/2025 fue de 5.010, la media es de 711 spots/estación con un máximo de 167.328 de la estación WW0WWV (https://wwvarc.org/)

Si se quiere conocer el numero de spots recibidos de cada estación se puede hacer:

  • SELECT tx_sign, COUNT(*) FROM wspr.rx WHERE  time > '2025-06-13' AND time < '2025-06-14' GROUP BY tx_sign ORDER BY  COUNT(*) DESC;

Como hay mas de 2.000 filas de resultado se genera un fichero descargable.

Si queremos conocer el orden que ocupa cada estación hay que incluir una columna "row_number() OVER (ORDER BY COUNT(*) DESC) AS row_num":

  • SELECT  row_number() OVER (ORDER BY COUNT(*) DESC) AS row_num, tx_sign, COUNT(*) AS total FROM wspr.rx WHERE  time > '2025-06-13' AND time < '2025-06-14' GROUP BY tx_sign ORDER BY  COUNT(*) DESC;


Si se quiere conocer cuantas estaciones hay de EA(España)

  • SELECT tx_sign, COUNT(*) FROM wspr.rx WHERE tx_sign LIKE 'EA%' AND time > '2025-06-13' AND time < '2025-06-14' GROUP BY tx_sign ORDER BY COUNT(*) DESC; 

Hay 16 estaciones WSPR TX en EA (España)
Hay distritos sin ninguna estación WSPX TX como EA2, EA4, EA7, EA8 y EA9

Se puede limitar únicamente a los recibidos de una determinada estación (p.e. EA5JTT ) mediante "WHERE tx_sign='EA5JTT'" de la siguiente forma

  • SELECT COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-06-13' AND time < '2025-06-14';


Si se quiere es ver los registros completos (SELECT *) de un intervalo, por ejemplo desde las 03h00 a las 4h00 se puede usar

  • SELECT * FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-06-14 03:00:00' AND time < '2025-06-14 04:00:00';

El listado anterior ordenado por tiempo
  • SELECT * FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-06-22 15:00:00' AND time < '2025-06-22 17:00:00' ORDER BY HOUR(time);
El listado anterior ordenado por tiempo y descartando duplicados
  • SELECT DISTINCT rx_sign FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-06-23 00:00:00' AND time < '2025-06-23 23:59:59' ORDER BY HOUR(time);


El listado de  estaciones receptoras únicas con el numero de spots recibidos cada una
  • SELECT DISTINCT rx_sign,  COUNT(rx_sign) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-06-23 00:00:00' AND time < '2025-06-23 23:59:59' GROUP BY rx_sign HAVING COUNT(rx_sign) > 1 ORDER BY COUNT(rx_sign)  DESC;


Listado de spots recibidos por una estación desde una fecha y hora hasta la actualidad
  • SELECT * FROM wspr.rx WHERE rx_sign='EA5JTT' AND time > '2025-06-14 03:00:00' AND time < NOW() ORDER BY HOUR(time);




Estaciones receptoras /transmisoras de WSPR en una cuadricula determinada (Con 4 caracteres estan las que transmiten con 4 caracteres y las de que transmiten con 6)
  • SELECT tx_loc, COUNT(*) FROM wspr.rx WHERE tx_loc LIKE 'IM99%' AND time > '2025-06-13' AND time < '2025-06-25' GROUP BY tx_loc ORDER BY COUNT(*) DESC;


Si queremos concocer los spots de un dia concreto 23-6-2025 recibidos de los transmitidos en dos cuadriculas por ejemplo "IM" e "IN" 
  • SELECT tx_loc, COUNT(*) FROM wspr.rx  WHERE (tx_loc LIKE 'IM%' OR tx_loc LIKE 'IN%') AND time > '2025-06-23'  AND time < '2025-06-24'  GROUP BY tx_loc  ORDER BY COUNT(*) DESC;


Los recibidos en cada cuadricula ordenados por cuadriculas
  • SELECT rx_loc, COUNT(*)  FROM wspr.rx  WHERE (rx_loc LIKE 'IM%' OR rx_loc LIKE 'IN%') AND time > '2025-06-23'  AND time < '2025-06-24'  GROUP BY rx_loc  ORDER BY rx_loc DESC;
Si lo que se quiere son los subtotales por bandas (GROUP BY band) con indicación del valor de la misma entonces tenemos:
  • SELECT band, COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY band;
Rapidamente se entiende la popularidad de la banda de 14 MHz (20m)

Si se quieren los subtotales por días
  • SELECT DATE(time), COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY DATE(time);



Por horas para el dia 11/5/2025
  • SELECT HOUR(time), COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY HOUR(time);

Distribución por horas y bandas de los spots recibidos de EA5JTT en todas las bandas ordenado por horas y bandas
  • SELECT  HOUR(time), band, COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY HOUR(time), band ORDER BY HOUR(time), band;

Distribución por horas de los spots recibidos de EA5JTT en la banda de 14 MHz
  • SELECT  HOUR(time),  COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' AND band='14' GROUP BY HOUR(time), band ORDER BY HOUR(time), band;



Distribución por horas y bandas
  • SELECT HOUR(time) AS hora, COUNT(CASE WHEN band = '3' THEN 1 END)  AS 80m, COUNT(CASE WHEN band = '7' THEN 1 END)  AS 40m, COUNT(CASE WHEN band = '10' THEN 1 END) AS 30m, COUNT(CASE WHEN band = '14' THEN 1 END) AS 20m, COUNT(CASE WHEN band = '18' THEN 1 END) AS 17m, COUNT(CASE WHEN band = '21' THEN 1 END) AS 15m, COUNT(CASE WHEN band = '24' THEN 1 END) AS 12m, COUNT(CASE WHEN band = '28' THEN 1 END) AS 10m, COUNT(CASE WHEN band <> '99' THEN 1 END) AS TOTAL FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time > '2025-06-23' AND time <  '2025-06-24' GROUP BY HOUR(time) ORDER BY HOUR(time);


Distribución de spots recibidos en WSPRnet de una estación en lo que llevamos de día
  • SELECT HOUR(time) AS UTC_BAND, COUNT(CASE WHEN band = '3' THEN 1 END)  AS 80m, COUNT(CASE WHEN band = '7' THEN 1 END)  AS 40m, COUNT(CASE WHEN band = '10' THEN 1 END) AS 30m, COUNT(CASE WHEN band = '14' THEN 1 END) AS 20m, COUNT(CASE WHEN band = '18' THEN 1 END) AS 17m, COUNT(CASE WHEN band = '21' THEN 1 END) AS 15m, COUNT(CASE WHEN band = '24' THEN 1 END) AS 12m, COUNT(CASE WHEN band = '28' THEN 1 END) AS 10m, COUNT(CASE WHEN band <> '99' THEN 1 END) AS TOTAL FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time > CURDATE() AND time <  NOW() GROUP BY HOUR(time) ORDER BY HOUR(time);
Distribución por minutos en un día. En este ejemplo se observa el patron de emisión coordinado del proyecto International WSPR Project
  • SELECT MINUTE(time) AS Minutos, COUNT(CASE WHEN band = '3' THEN 1 END)  AS 80m, COUNT(CASE WHEN band = '7' THEN 1 END)  AS 40m, COUNT(CASE WHEN band = '10' THEN 1 END) AS 30m, COUNT(CASE WHEN band = '14' THEN 1 END) AS 20m, COUNT(CASE WHEN band = '18' THEN 1 END) AS 17m, COUNT(CASE WHEN band = '21' THEN 1 END) AS 15m, COUNT(CASE WHEN band = '24' THEN 1 END) AS 12m, COUNT(CASE WHEN band = '28' THEN 1 END) AS 10m, COUNT(CASE WHEN band <> '99' THEN 1 END) AS TOTAL FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time > '2025-06-24 00:00:00' AND time <  '2025-06-25 00:00:00' GROUP BY MINUTE(time) ORDER BY MINUTE(time);


Si se quieren los subtotales por dias y bandas y ordenados
  • SELECT DATE(time), band, COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY DATE(time), band ORDER BY DATE(time),band;

Si se quiere conocer los indicativos de las estaciones que han recibido nuestras transmisiones WSPR y el número de mensajes que han recibido y ordenada 
  • SELECT rx_sign, COUNT(rx_sign) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY rx_sign ORDER BY COUNT(rx_sign) DESC;

Con subtotales por bandas
  • SELECT rx_sign,band, COUNT(rx_sign) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY rx_sign, band  ORDER BY COUNT(rx_sign) DESC;



Se puede obtener un encolumnado con suma por fila 
  • SELECT DATE(time) AS fecha, COUNT(CASE WHEN band = '3' THEN 1 END)  AS band_3, COUNT(CASE WHEN band = '7' THEN 1 END)  AS band_7, COUNT(CASE WHEN band = '10' THEN 1 END) AS band_10, COUNT(CASE WHEN band = '14' THEN 1 END) AS band_14, COUNT(CASE WHEN band = '18' THEN 1 END) AS band_18, COUNT(CASE WHEN band = '21' THEN 1 END) AS band_21, COUNT(CASE WHEN band = '24' THEN 1 END) AS band_24, COUNT(CASE WHEN band = '28' THEN 1 END) AS band_28, COUNT(CASE WHEN band <> '99' THEN 1 END) AS total FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time >= '2025-05-11' AND time <  '2025-06-12' GROUP BY DATE(time) ORDER BY DATE(time);

y sumar las columnas
  • SELECT  toString(DATE(time)) AS fecha, COUNT(CASE WHEN band = '3'  THEN 1 END)  AS band_3, COUNT(CASE WHEN band = '7'  THEN 1 END)  AS band_7, COUNT(CASE WHEN band = '10' THEN 1 END) AS band_10, COUNT(CASE WHEN band = '14' THEN 1 END) AS band_14, COUNT(CASE WHEN band = '18' THEN 1 END) AS band_18, COUNT(CASE WHEN band = '21' THEN 1 END) AS band_21, COUNT(CASE WHEN band = '24' THEN 1 END) AS band_24, COUNT(CASE WHEN band = '28' THEN 1 END) AS band_28, COUNT(CASE WHEN band <> '99' THEN 1 END) AS total FROM wspr.rx WHERE  tx_sign = 'EA5JTT' AND time >= '2025-05-11' AND time <  '2025-06-12' GROUP BY DATE(time) UNION ALL SELECT  'TOTAL' AS fecha, COUNT(CASE WHEN band = '3'  THEN 1 END),  COUNT(CASE WHEN band = '7'  THEN 1 END), COUNT(CASE WHEN band = '10' THEN 1 END), COUNT(CASE WHEN band = '14' THEN 1 END), COUNT(CASE WHEN band = '18' THEN 1 END), COUNT(CASE WHEN band = '21' THEN 1 END), COUNT(CASE WHEN band = '24' THEN 1 END), COUNT(CASE WHEN band = '28' THEN 1 END), COUNT(CASE WHEN band <> '99' THEN 1 END) FROM wspr.rx WHERE    tx_sign = 'EA5JTT'  AND time >= '2025-05-11'    AND time <  '2025-06-12' ORDER BY fecha;


Obtener los modelos y versiones de los RX WAPR
  • SELECT version, COUNT(CASE WHEN version <> 'xxx' THEN 1 END)  AS Total FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12'  GROUP BY version  ORDER BY version DESC;


con suma de total
  • SELECT * FROM (  SELECT  version,  COUNT(*) AS Total FROM wspr.rx WHERE  tx_sign = 'EA5JTT'  AND time > '2025-05-11' AND time < '2025-06-12' AND version <> 'xxx'  GROUP BY version    UNION ALL SELECT 'TOTAL' AS version, COUNT(*) AS Total   FROM wspr.rx   WHERE tx_sign = 'EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' AND version <> 'xxx') ORDER BY  version = 'TOTAL',  Total DESC;
con porcentaje
  • SELECT * FROM ( SELECT  version,    count(*) AS Total,      round(count(*) / total.total_count * 100, 2) AS Porcentaje   FROM wspr.rx,(  SELECT count(*) AS total_count  FROM wspr.rx      WHERE  tx_sign = 'EA5JTT'   AND time > '2025-05-11'  AND time < '2025-06-12' AND version <> 'xxx' ) AS total  WHERE       tx_sign = 'EA5JTT'  AND time > '2025-05-11'  AND time < '2025-06-12' AND version <> 'xxx' GROUP BY version, total.total_count  UNION ALL  SELECT  'TOTAL' AS version, total_count AS Total, 100 AS Porcentaje  FROM (  SELECT count(*) AS total_count      FROM wspr.rx  WHERE   tx_sign = 'EA5JTT'  AND time > '2025-05-11'       AND time < '2025-06-12' AND version <> 'xxx'    ) ) ORDER BY version = 'TOTAL', Total DESC;
Una versión mas comparta que calcula los valores apra el día de ayer
  • SELECT * FROM ( SELECT  version,    count(*) AS Total,      round(count(*) / total.total_count * 100, 2) AS Porcentaje   FROM wspr.rx,(  SELECT count(*) AS total_count  FROM wspr.rx      WHERE   time > CURDATE()  AND time < NOW() AND version <> 'xxx' ) AS total  WHERE      time > CURDATE() AND time < NOW() AND version <> 'xxx' GROUP BY version, total.total_count  UNION ALL  SELECT  'TOTAL' AS version, total_count AS Total, 100 AS Porcentaje  FROM (  SELECT count(*) AS total_count      FROM wspr.rx  WHERE    time > CURDATE() AND time < NOW() AND version <> 'xxx'    ) ) ORDER BY version = 'TOTAL', Total DESC;


Vamos a hora a obtener las distancias máximas TX-RX para un dia concreto 23-6-2025, por horas y bandas (Recuerde que una gráfica, por ejemplo con EXCEL o una hoja de cálculo similar los datos se lven mejor)
  • SELECT HOUR(time) AS hora, MAX(CASE WHEN band = '3' THEN distance END) AS "80m", MAX(CASE WHEN band = '7' THEN distance END) AS "40m", MAX(CASE WHEN band = '10' THEN distance END) AS "30m",  MAX(CASE WHEN band = '14' THEN distance END) AS "20m", MAX(CASE WHEN band = '18' THEN distance END) AS "17m", MAX(CASE WHEN band = '21' THEN distance END) AS "15m", MAX(CASE WHEN band = '24' THEN distance END) AS "12m",  MAX(CASE WHEN band = '28' THEN distance END) AS "10m" FROM  wspr.rx WHERE  tx_sign = 'EA5JTT'  AND time >= '2025-06-23'   AND time < '2025-06-24' GROUP BY  HOUR(time) ORDER BY   HOUR(time);



Potencia de los los TX de los spots recibidos el dia 22-6-2025
  • SELECT power, COUNT(CASE WHEN power <> '999' THEN 1 END)  AS Total FROM wspr.rx WHERE  time > '2025-06-22' AND time < '2025-06-23'  GROUP BY power  ORDER BY power DESC;
De los recibidos ayer
  • SELECT  power,  COUNT(*) AS Total  FROM wspr.rx  WHERE  time > CURDATE() AND time < NOW() AND power <> '999' GROUP BY power  ORDER BY power DESC;
Ahora le añadimos una columna con el porcentaje
  • WITH ( SELECT count(*) FROM wspr.rx WHERE time > CURDATE() AND time < NOW() AND power <> '999') AS total_count SELECT * FROM (SELECT toString(power) AS power, count(*) AS Total, round(count(*) / total_count * 100, 2) AS Porcentaje FROM wspr.rx WHERE  time > CURDATE()  AND time < NOW()  AND power <> '999' GROUP BY power UNION ALL SELECT  'TOTAL' AS power, total_count AS Total, 100.00 AS Porcentaje) ORDER BY power = 'TOTAL', Total DESC;
Valor absoluto de recepciones por parte de la red WSPR RX agrupados por banda para una estación en concreto y su porcentaje sobre el total

  • SELECT * FROM ( SELECT  CAST(band AS String) AS band,  COUNT(*) AS Total,    ROUND(COUNT(*) / any(total.total_count) * 100, 2) AS Porcentaje FROM wspr.rx JOIN ( SELECT COUNT(*) AS total_count   FROM wspr.rx WHERE  tx_sign = 'EA5JTT' AND time > '2025-06-28 16:30:00' AND  time < '2025-06-29 16:30:00' AND band != '99'  ) AS total ON 1=1 WHERE  tx_sign = 'EA5JTT' AND time > '2025-06-28 16:30:00' AND time < '2025-06-29 16:30:00' AND band != '99' GROUP BY band UNION ALL SELECT  'TOTAL' AS band,  total_count AS Total, 100 AS Porcentaje FROM ( SELECT COUNT(*) AS total_count   FROM wspr.rx    WHERE tx_sign = 'EA5JTT' AND time > '2025-06-28 16:30:00' AND time < '2025-06-29 16:30:00' AND band != '99' ) AS total) ORDER BY band = 'TOTAL' DESC, Total DESC;


Esto es solamente una pequeña demostración didáctica de cómo explotar la base de datos de WSPRnet, pero con su imaginación y sabiduría llegará donde se proponga



-

No hay comentarios:

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)

ATS-mini SI4732: lenguaje de script

  El receptor ATS-mini SI4732  cuenta con su propio lenguaje de programación, compatible con CSS y JavaScript, que permite obtener resultado...

  • WSPR TX: Arduino + Si5351
    Para construir y poner en funcionamiento una baliza WSPR TX necesitamos: Autorización Las emisiones en bandas de radioaficionado, como es el...
  • WSPR Transmitter de Zachtek: caracteristicas
      WSPR Desktop Transmitter de   Zachtek   (80To10)   es un transmisor automático  de hasta  200 mW de potencia (puede regularse),  diseñado ...
  • WSPR & DXCC
    Esta es la lista oficial de territorios para ARRL (Marzo de 2022) , si lo que ahora queremos es conocer cuantos países DXCC hemos alcanzado...