
%load_ext sql


%sql sqlite:///../data/stations.db

'Connected: @../data/stations.db'


%%sql
CREATE TABLE IF NOT EXISTS stations (
    id PRIMARY KEY,
    altitude REAL,
    antenna TEXT CHECK(json_valid(antenna)),  -- array of objects
    client_version TEXT,
    created TEXT,       --  YYYY-mm-ddTHH:MMZ
    description TEXT,
    last_seen TEXT,     --  YYYY-mm-ddTHH:MMZ
    lat REAL,
    lng REAL,
    min_horizon REAL,
    name TEXT,
    observations INT,
    qthlocator TEXT,
    status TEXT,
    target_utilization INT,
    rotator INT         -- 0, 1, or 2 axis rotation
    );

-- Then, add rows from the stations.json.  This was done via CSV import, but it should
-- be configured to directly ingest from the JSON in order to keep the DB up to date.

 * sqlite:///../data/stations.db
Done.
Done.

[]

"antenna": [
        {
            "frequency": 430000000,
            "frequency_max": 470000000,
            "band": "UHF",
            "antenna_type": "cross-yagi",
            "antenna_type_name": "Cross Yagi"
        },
        {
            "frequency": 135000000,
            "frequency_max": 150000000,
            "band": "VHF",
            "antenna_type": "cross-yagi",
            "antenna_type_name": "Cross Yagi"
        }
    ]


%%sql
-- Histogram of antenna types
SELECT
    json_extract(antenna, "$[0].antenna_type") as type,
    count(antenna) as num_type
FROM stations
GROUP BY type
ORDER BY num_type DESC;

 * sqlite:///../data/stations.db
Done.


guesses = (
    (0, "turnstile"),
    (2, "yagi"),
    (0, "quadrafilar"),
    (0, "vertical"),
    (2, "cross-yagi"),
    (0, "dipole"),
    (0, "v-dipole"),
    (0, "discone"),
    (0, "eggbeater"),
    (0, "other omni"),
    (2, "helical"),
    (0, "ground"),
    (0, "lindenblad"),
    (2, "other direct"),
    (2, "parabolic"),
    (0, "patch"),
    (0, "paralindy"),
)

for r, name in guesses:
    %sql UPDATE stations SET rotator = {r} \
         WHERE json_extract(antenna, "$[0].antenna_type") = "{name}";

 * sqlite:///../data/stations.db
358 rows affected.
 * sqlite:///../data/stations.db
315 rows affected.
 * sqlite:///../data/stations.db
291 rows affected.
 * sqlite:///../data/stations.db
251 rows affected.
 * sqlite:///../data/stations.db
229 rows affected.
 * sqlite:///../data/stations.db
181 rows affected.
 * sqlite:///../data/stations.db
153 rows affected.
 * sqlite:///../data/stations.db
140 rows affected.
 * sqlite:///../data/stations.db
140 rows affected.
 * sqlite:///../data/stations.db
92 rows affected.
 * sqlite:///../data/stations.db
80 rows affected.
 * sqlite:///../data/stations.db
69 rows affected.
 * sqlite:///../data/stations.db
38 rows affected.
 * sqlite:///../data/stations.db
35 rows affected.
 * sqlite:///../data/stations.db
18 rows affected.
 * sqlite:///../data/stations.db
10 rows affected.
 * sqlite:///../data/stations.db
5 rows affected.


%%sql
-- Setup for searching name, description fields for various text.
-- This is to find stations with different rotators than what may
-- be implied by their declared antenna_type(s)
CREATE VIRTUAL TABLE IF NOT EXISTS text 
USING FTS5(id, name, description);

INSERT OR REPLACE INTO text
SELECT id, name, description from stations;

 * sqlite:///../data/stations.db
Done.
2612 rows affected.

[]


%%sql
-- Modify the rotator column to better reflect what the description claims
SELECT DISTINCT
    text.id,
    text.name,
    text.description,
    stations.antenna
FROM text, stations
WHERE text.id = stations.id
AND stations.rotator = 2
AND text MATCH 'fixed'
ORDER BY text.id;

 * sqlite:///../data/stations.db
Done.


%%sql
UPDATE stations
SET rotator = 0
WHERE id IN (66, 212, 1351, 1787, 1810, 1868, 1950, 2080, 2107, 2246, 2263,     
             2372, 2430, 2501, 2650);

 * sqlite:///../data/stations.db
15 rows affected.

[]


%%sql
-- Find another pattern that implies a rotator
SELECT DISTINCT
    text.id,
    text.name,
    text.description,
    stations.antenna,
    stations.rotator
FROM text, stations
WHERE text.id = stations.id
AND text MATCH '5500 OR spid'
AND rotator <> 2
ORDER BY text.id;

 * sqlite:///../data/stations.db
Done.


%%sql
UPDATE stations SET rotator = 2 WHERE id IN (888, 2500);

 * sqlite:///../data/stations.db
2 rows affected.

[]


%%sql
SELECT DISTINCT
    text.id,
    text.name,
    text.description,
    stations.antenna,
    stations.rotator
FROM text, stations
WHERE text.id = stations.id
AND text MATCH 'azimuth'
ORDER BY text.id;

 * sqlite:///../data/stations.db
Done.


%%sql
UPDATE stations SET rotator = 2 WHERE id IN (357, 1146, 2221);

-- And an azimuth-only station!
UPDATE stations SET rotator = 1 WHERE id IN (1775);

 * sqlite:///../data/stations.db
3 rows affected.
1 rows affected.

[]


%%sql
SELECT DISTINCT
    text.id,
    text.name,
    text.description,
    stations.antenna,
    stations.rotator
FROM text, stations
WHERE text.id = stations.id
AND text MATCH 'elevation'
AND stations.rotator <> 0
ORDER BY text.id;

 * sqlite:///../data/stations.db
Done.


%%sql
-- More no-rotator stations
UPDATE stations SET rotator = 0 WHERE id IN (146, 484, 1423, 1466, 1640, 1710, 1789, 2029)

 * sqlite:///../data/stations.db
8 rows affected.

[]


%%sql
SELECT DISTINCT
    text.id,
    text.name,
    text.description,
    stations.antenna,
    stations.rotator
FROM text, stations
WHERE text.id = stations.id
AND text MATCH 'only'
AND stations.rotator <> 0
ORDER BY text.id;

 * sqlite:///../data/stations.db
Done.


%%sql
-- More no-rotator stations
UPDATE stations SET rotator = 0 WHERE id IN (1526, 1824, 2482);

-- And another azimuth-only station!
UPDATE stations SET rotator = 1 WHERE id IN (2837);

 * sqlite:///../data/stations.db
3 rows affected.
1 rows affected.

[]


%%sql
SELECT
    count(*),
    sum(observations)
FROM stations
WHERE rotator NOT IN (0, 1, 2) or rotator IS NULL;

 * sqlite:///../data/stations.db
Done.


%%sql
SELECT
    json_extract(antenna, "$[0].antenna_type") as type,
    count(antenna) as num_type
FROM stations
GROUP BY type
ORDER BY num_type DESC;

 * sqlite:///../data/stations.db
Done.


%%sql
-- Number of stations by motion axes
SELECT
    rotator,
    count(rotator) as num,
    round(100.0 * count(rotator) / (SELECT count(*) FROM stations), 1) as percent
FROM stations
GROUP BY rotator
ORDER BY rotator;

 * sqlite:///../data/stations.db
Done.


%%sql
-- Number of stations by motion axes
SELECT
    rotator,
    count(rotator) as num,
    round(100.0 * count(rotator) / (select count(*) from stations WHERE status IN ("Online", "Testing")), 1) as percent
    -- Can't figure out how to reuse the WHERE clause without just repeating it, please help!
FROM stations
WHERE status IN ("Online", "Testing") -- Only other option is "Offline"
GROUP BY rotator
ORDER BY rotator;

 * sqlite:///../data/stations.db
Done.


%%sql
-- Number of stations by motion axes
SELECT
    status,
    count(status) as num,
    round(100.0 * count(status) / (select count(*) from stations), 1) as percent
FROM stations
GROUP BY status
ORDER BY num DESC;

 * sqlite:///../data/stations.db
Done.


%%sql
SELECT
    strftime("%Y", created) as year,
    count(id) as num_new
FROM stations
WHERE strftime("%s", created) >= strftime("%s", printf("%d-01-01", year))
  AND strftime("%s", created) <  strftime("%s", printf("%d-01-01", year+1))
GROUP BY year
ORDER BY year;

 * sqlite:///../data/stations.db
Done.


%sql sqlite:///../data/observations.db

'Connected: @../data/observations.db'


%%sql
-- Indexes make the world go 'round ... faster.
CREATE INDEX IF NOT EXISTS gs_norad_index
ON observations(ground_station, norad_cat_id);

CREATE INDEX IF NOT EXISTS gs_index
ON observations(ground_station);

CREATE INDEX IF NOT EXISTS norad_index
ON observations(norad_cat_id);

CREATE INDEX IF NOT EXISTS observer_index
ON observations(observer);

CREATE INDEX IF NOT EXISTS observer_gs_index
ON observations(observer, ground_station);

CREATE INDEX IF NOT EXISTS gs_observer_index
ON observations(ground_station, observer);

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.
Done.
Done.
Done.
Done.
Done.

[]


%%sql
-- Special index for speeding up time range queries.
CREATE VIRTUAL TABLE IF NOT EXISTS times_index
USING rtree(
        id,
        start,
        end,
        +ground_station INT,
        +observer INT,
        +norad_cat_id INT);

-- (commented out since it takes a long time, and is only needed once anyway)
-- (un-comment if this is your first time)
-- INSERT OR REPLACE INTO times_index
-- SELECT
--     id,
--     strftime("%s", start),
--     strftime("%s", end),
--     ground_station,
--     observer,
--     norad_cat_id
-- FROM observations
-- WHERE strftime("%s", start) <= strftime("%s", end);

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.
Done.

[]


%%sql
-- Add triggers to keep the times Rtree updated

CREATE TRIGGER IF NOT EXISTS times_insert_trigger
AFTER INSERT
ON observations
WHEN strftime("%s", NEW.start) <= strftime("%s", NEW.end)  -- sanity check
BEGIN
    INSERT OR REPLACE INTO times_index(
            id,
            start,
            end,
            ground_station,
            observer,
            norad_cat_id)
    VALUES (NEW.id,
            strftime("%s", NEW.start),
            strftime("%s", NEW.end),
            NEW.ground_station,
            NEW.observer,
            NEW.norad_cat_id);
END;


CREATE TRIGGER IF NOT EXISTS times_update_trigger
AFTER UPDATE
ON observations
WHEN strftime("%s", NEW.start) <= strftime("%s", NEW.end)
BEGIN
    INSERT OR REPLACE INTO times_index(
            id,
            start,
            end,
            ground_station,
            observer,
            norad_cat_id)
    VALUES (NEW.id,
            strftime("%s", NEW.start),
            strftime("%s", NEW.end),
            NEW.ground_station,
            NEW.observer,
            NEW.norad_cat_id);
END;

CREATE TRIGGER IF NOT EXISTS times_delete_trigger
AFTER DELETE
ON observations
BEGIN
    DELETE FROM times_index
    WHERE id = OLD.id;
END;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
(sqlite3.OperationalError) incomplete input
[SQL: -- Add triggers to keep the times Rtree updated

CREATE TRIGGER IF NOT EXISTS times_insert_trigger
AFTER INSERT
ON observations
WHEN strftime("%s", NEW.start) <= strftime("%s", NEW.end)  -- sanity check
BEGIN
    INSERT OR REPLACE INTO times_index(
            id,
            start,
            end,
            ground_station,
            observer,
            norad_cat_id)
    VALUES (NEW.id,
            strftime("%s", NEW.start),
            strftime("%s", NEW.end),
            NEW.ground_station,
            NEW.observer,
            NEW.norad_cat_id);]
(Background on this error at: https://sqlalche.me/e/14/e3q8)


%%sql
SELECT
    ground_station,
    count(*) as num
FROM observations
WHERE ground_station IN (834, 841, 890)
GROUP BY ground_station;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.


%%sql
-- EXPLAIN QUERY PLAN
SELECT
    observer,
    count(distinct ground_station) AS num_gs,
    count(id) AS num_obs,
    ground_station
FROM times_index
WHERE start >= strftime("%s", "2022-01-01")
    AND start <  strftime("%s", "2023-01-01")
GROUP BY observer
ORDER BY num_obs DESC
LIMIT 10;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.


result = _


idx = [i for i, r in enumerate(result) if r[0] == 326][0]
observer, num_obs = result[idx][0:3:2]
observer, num_obs

(326, 58961)


min_in_year = 60 * 24 * 365

avg = min_in_year / num_obs
print(f"{avg:.1f} minutes, average time between observation starts")

8.9 minutes, average time between observation starts


%%sql
-- obs time for one station
SELECT
    ground_station,
    count(id) AS num_obs,
    round(sum(end - start)/60.0, 2) AS obs_min
FROM times_index
WHERE ground_station = 91
    AND start >= strftime("%s", "2022-01-01")
    AND start <  strftime("%s", "2023-01-01")
;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.


result = _


obs_min = result.dict()['obs_min'][0]

print(f'{obs_min:7.1f} minutes in observations')
print(f'{min_in_year:7.1f} minutes in a year')
utilization = (obs_min / min_in_year) * 100
print(f'{utilization:.1f} % utilization')

565239.5 minutes in observations
525600.0 minutes in a year
107.5 % utilization


%%sql
SELECT
    ground_station,
    count(id) AS num_obs
FROM times_index
WHERE start >= strftime("%s", "2022-01-01")
    AND start <  strftime("%s", "2023-01-01")
GROUP BY ground_station
ORDER BY num_obs DESC
LIMIT 10;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.


%%sql
-- obs time for one station

WITH topten as (
    SELECT
        ground_station,
        count(id) AS num_obs,
        sum(end - start)/60.0 AS obs_min
    FROM times_index
    WHERE start >= strftime("%s", "2022-01-01")
        AND start <  strftime("%s", "2023-01-01")
    GROUP BY ground_station
    ORDER BY num_obs DESC
    )
SELECT
    ground_station,
    num_obs,
    round(obs_min, 1) as minutes,
    round(obs_min / (60*24*365) * 100, 1) AS "%", 
    round(obs_min / num_obs, 1) AS min_per_obs
FROM topten
ORDER BY minutes DESC
LIMIT 10;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.


%%sql
-- obs time for one station

SELECT
    id,
    ground_station,
    round((end - start)/60.0, 1) AS minutes
FROM times_index
WHERE ground_station = 2490
    AND start >= strftime("%s", "2022-01-01")
    AND start <  strftime("%s", "2023-01-01")
-- observation IDs are assigned when *scheduled*
-- NOT when the pass itself starts
ORDER BY start DESC;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.


%%sql
-- How many different observers have scheduled on each GS?
SELECT
    ground_station,
    count(distinct observer) as num_observers,
    count(id) AS num_obs
FROM times_index
WHERE start >= strftime("%s", "2022-01-01")
    AND start < strftime("%s", "2023-01-01")
GROUP BY ground_station
ORDER BY num_observers DESC
LIMIT 10;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.


%%sql
-- How many different GS have observers scheduled on?
SELECT
    observer,
    count(distinct ground_station) as num_gs,
    count(id) AS num_obs
FROM times_index
WHERE start >= strftime("%s", "2022-01-01")
    AND start < strftime("%s", "2023-01-01")
GROUP BY observer
ORDER BY num_gs DESC
LIMIT 20;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.


%%sql
-- Which satellites are listened to by which GS?
--explain query plan
SELECT
    norad_cat_id as norad,
    count(distinct ground_station) as num_gs,
    count(id) AS num_obs
FROM times_index
WHERE start >= strftime("%s", "2022-01-01")
    AND start < strftime("%s", "2023-01-01")
GROUP BY norad
ORDER BY num_obs DESC
LIMIT 20;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.


result = _


import requests
import requests_cache
from datetime import datetime, timedelta

# SatNOGS servers are >100 ms away from me,
# so this is as much for me as for them :)
requests_cache.install_cache(expire_after=60*60)

print('NORAD days name')
for norad, num_gs, num_obs in result:
    json = requests.get(f'https://db.satnogs.org/api/satellites/?norad_cat_id={norad}').json()
    name = json[0]['name']
    
    fakedate = ''
    if json[0]['launched'] is not None:
        launched = datetime.strptime(json[0]['launched'], "%Y-%m-%dT%H:%M:%SZ")
    else:
        # fake it
        launched = datetime(2022, 1, 1)
        fakedate = '*'

    time_operational = datetime(2023, 1, 1) - launched
    days_thisyear = min(timedelta(days=365), time_operational).days
    
    print(f'{norad} {days_thisyear} {name} {fakedate}')

NORAD days name
33591 365 NOAA 19 
28654 365 NOAA 18 
25544 365 ISS 
40379 365 GRIFEX 
25338 365 NOAA 15 
48850 365 RamSat 
40069 365 METEOR M-2 
40014 365 BUGSAT-1 
47701 365 Nanoconnect-2 
48943 365 QMR-KWT 
47959 365 GRBAlpha *
43199 365 SHAONIAN XING 
20442 365 LUSAT 
47438 365 UVSQ-SAT 
40043 365 TIGRISAT 
39090 365 STRAND-1 *
51085 352 VZLUSAT-2 
40968 365 BISONSAT 
39444 365 FUNCUBE-1 *
39469 365 MCUBED-2 *


!curl --silent 'https://db.satnogs.org/api/satellites/?status=alive' | jq -c '.[]' | wc -l

847


%%sql
-- Which satellites are listened to by which GS?
--explain query plan
SELECT
    observer,
    count(distinct norad_cat_id) as num_sat,
    count(id) AS num_obs,
    round(1000.0*count(distinct norad_cat_id) / count(id), 1) as diversity
FROM times_index
WHERE start >= strftime("%s", "2022-01-01")
    AND start < strftime("%s", "2023-01-01")
GROUP BY observer
ORDER BY num_sat DESC
LIMIT 10;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.


%%sql
-- Which satellites are listened to by which GS?
--explain query plan
SELECT
    observer,
    count(distinct norad_cat_id) as num_sat,
    count(distinct ground_station) as num_gs,
    count(id) AS num_obs,
    round(1000.0*count(distinct norad_cat_id) / count(id), 1) as diversity
FROM times_index
WHERE start >= strftime("%s", "2022-01-01")
    AND start < strftime("%s", "2023-01-01")
GROUP BY observer
ORDER BY diversity ASC
LIMIT 25;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.


%%sql
-- Which satellites are listened to by which GS?
--explain query plan
SELECT
    ground_station,
    count(distinct norad_cat_id) as num_sat,
    count(id) AS num_obs
FROM times_index
WHERE start >= strftime("%s", "2022-01-01")
    AND start < strftime("%s", "2023-01-01")
GROUP BY ground_station
ORDER BY num_sat DESC
LIMIT 10;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.


%%sql
PRAGMA optimize;

 * sqlite:///../data/observations.db
   sqlite:///../data/stations.db
Done.

id	name	description	antenna
66	KE8FZT - UHF	Arrow UHF Corner Reflector, Fixed 20 Deg Elevation & Pointed 45 Deg NorthEast, AMSAT LNA,, MFJ Duplexer, Raspberry Pi3b	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "VHF", "frequency": 135000000, "frequency_max": 148000000}, {"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}]
212	KE8FZT - VHF	Arrow VHF Corner Reflector, Fixed 20 Deg Elevation & Pointed 45 Deg NorthEast, LNA, Raspberry Pi3b	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "VHF", "frequency": 135000000, "frequency_max": 146000000}]
1351	7el 70cm fixed Yagi	Restricted viewing angle is approximately 230 to 330 degrees.	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}]
1787	NCG	Double bi-quad (13.46 dB) fixed position directional antenna looking e (W passes will not be seen well), altitude 48 deg, LNA (gain 20 dB noise 0.36dB), Lime, RPi 4	[{"antenna_type": "other direct", "antenna_type_name": "Other Directional", "band": "VHF, UHF", "frequency": 130000000, "frequency_max": 600000000}]
1810	NCG2	V-dipole fixed, looking 90 deg E, LNA (gain 20dB noise 0.8dB), Lime, RPi	[{"antenna_type": "other direct", "antenna_type_name": "Other Directional", "band": "VHF", "frequency": 135000000, "frequency_max": 148000000}]
1868	HB9FXX S-Band	My little s-band station: 4G feed on fixed dish pointing roughly at 300° (WNW) and 30-45° elevation-> VLNA13 -> Coax -> 1.5GHz LPF -> HackRF SDR -> RPi 4 8GB. The HackRF uses a Leo Bodnar GPSDO mini reference. On a first floor balcony with mostly west opening. Not a terrible location but the best I can do living in a flat.	[{"antenna_type": "parabolic", "antenna_type_name": "Parabolic", "band": "L, S", "frequency": 1650000000, "frequency_max": 2400000000}]
1950	KG7ZVV Satnogs	Fixed (no rotator) 10 element UHF Yagi. Visibility to only portion of sky towards Southwest. Apartments and building on other side.	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}]
2080	USU GAS Yagi Fixed SW	Utah State University Get Away Special Team's second ground station. Wimo X-Quad (linear), No LNA currently. Fixed pointing SW at 45 deg elevation.	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 440000000}]
2107	JH4XSY-pi	Pi4 + Airspy Mini + 3ele(145MHz)/6ele(435MHz) YAGI Fixed beam to the SouthWest.+ LNA	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "VHF", "frequency": 135000000, "frequency_max": 148000000}, {"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 420000000, "frequency_max": 450000000}]
2246	HB9FXX S-Band 2	Fixed dish at 300°, pointing like 30-40° up	[{"antenna_type": "parabolic", "antenna_type_name": "Parabolic", "band": "L, S", "frequency": 1650000000, "frequency_max": 2500000000}]
2263	KO4BHX - Rougemont - NC - USA	Arrow 146/437-10 Fixed AZ:180, EL:90	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "VHF", "frequency": 135000000, "frequency_max": 148000000}, {"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}]
2372	FCNA2021-2	NOT IN SERVICE FIXED 90 ELEVATION	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}]
2430	SPACECLUB_TO1	Fixed Yagi 70cm 7dbi Nooelec LANA LNA Nooelec rtlsdr clone - NESDR SMArTee v2	[{"antenna_type": "cross-yagi", "antenna_type_name": "Cross Yagi", "band": "UHF", "frequency": 400000000, "frequency_max": 480000000}]
2501	CRD	UHF: Fixed 7 elements LFA Yagi Antenna VHF: LFA antenna	[{"antenna_type": "other direct", "antenna_type_name": "Other Directional", "band": "VHF", "frequency": 135000000, "frequency_max": 148000000}]
2650	9V1KG	Fixed 3/5 el Dual band Yagi beaming 210 deg (SW) 20 deg elevation	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "VHF", "frequency": 144000000, "frequency_max": 146000000}, {"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 440000000}]

id	name	description	antenna	rotator
146	RIYADH2	31 Oct 2020 moved down street - 90 degree elevation crossed Yagi+LNA 13 feb 2021 - wire antenna back - UHF LNA 27 feb 2021 - short cross yagi looking up 25 Sep 2022 - new location down the road - just monopole and no LNA - 3 stories 22 Oct 2022 - UHF Yagi looking straight up with LNA	[{"antenna_type": "cross-yagi", "antenna_type_name": "Cross Yagi", "band": "UHF", "frequency": 400000000, "frequency_max": 470000000}]	2
484	DParabole	Paris La Villette. Antenna is currently blocked at 90deg elevation	[{"antenna_type": "helical", "antenna_type_name": "Helical", "band": "L", "frequency": 1500000000, "frequency_max": 1750000000}]	2
1146	Ham Radio Ground Station	Ground station project specifically designed for communication with small satellites in Low Earth Orbit using VHF and UHF amateur radio frequencies and provide hands-on educational projects in the world The ground station can autonomously track satellites by using these equipments : VHF / UHF amateur radio transceiver (Amateur bands 144-16 MHZ and 430-440 MHZ) Rotator Controller Azimuth and elevation rotators UHF and VHF Yagi antennas (Amateur bands 144-16 MHZ and 430-440 MHZ) Modems...	[]	2
1312	ESOC UHF	437MHz Wimo cross yagi, phasing cable combiner, NooElec NESDR SMArt SMA 100700 RTL-SDR. V3.01 Satnogs Rotator, 3D printed elevation shafts, Raspberry Pi 3b. Radio Club + Cybernetics Club at ESA Space Operation Centre, Darmstadt.	[{"antenna_type": "cross-yagi", "antenna_type_name": "Cross Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}]	2
1423	OLIVIER F6OBT	Rpi 4 + 1 Yagis antenna VHF 3 elements direction N-E, 1 Yagis antenna VHF 3 elements direction N-W and 1 Yagis antenna UHF 5 elements direction N-W, 1 Yagis antenna UHF 5 elements direction N-E. All antennas have an elevation angle of 40°.	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "VHF", "frequency": 135000000, "frequency_max": 152000000}, {"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 400000000, "frequency_max": 500000000}]	2
1466	PE0SAT-12	HP NC6400 Laptop, Intel T7200 \| Debian Bullseye (amd64) \| SDR RX: AirSPY R2 \| Antenna: DK7ZB 9 Element Yagi Pointing bearing 315 / elevation 75.	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 400000000, "frequency_max": 470000000}]	2
1640	VA7EEX - UHF	Log periodic antenna with a 60 degree elevation, pointed southwest.	[{"antenna_type": "other direct", "antenna_type_name": "Other Directional", "band": "UHF", "frequency": 400000000, "frequency_max": 512000000}]	2
1710	EU1AEM	In maintenance now! 3-element yagi, vertical polarization, directed to east (100°), elevation 25° -> Uputronix 433-438 MHz filtred preamp -> RTL-SDR v3 -> Raspberry Pi 4. Please, do not use this station for west passes. Antenna is directed to east. Use station #2029 for west passes.	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 433000000, "frequency_max": 439000000}]	2
1789	jchem	RPi4 + preamp 20dB + sdrrtl-v3, with VHF-4el.-horizontal-yagi pointing NE at 35° elevation. (if UHF: )two UHF-Yagi_6-el, 180d apart at 40d elevation, di. NE-SW.	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "VHF", "frequency": 135000000, "frequency_max": 148000000}]	2
2029	EU1AEM	In maintenance now! 3-element yagi, vertical polarization, directed to west (270°), elevation 30° -> Uputronix 433-438 MHz filtred preamp -> RTL-SDR v3 -> Raspberry Pi 4. Please, do not use this station for east passes. Antenna is directed to west. Use station #1710 for east passes.	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 433000000, "frequency_max": 439000000}]	2
2221	Francis Ground Station	Antenna array of 2 elements yagi for UHF Antenna dish and S-Band The Antenna array is attached to the edge of the dish of the S-Band Antenna The Antenna has a servo motor that controls azimuth and another for elevation	[]	2

id	name	description	antenna	rotator
36	oe8rke	2022-04-03: station close being reopend after rotor replacement 2019-12-19: Still experiance issues with rotor - PLEASE ONLY EMERGENCY RECEPTIONS UNTIL ROTOR IS BACK, TNX! 2019-12-10: Shutdown to service rotor again (stall issue), offsite location in the alps with low noise, operating with pstrotator hamlib interface	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}, {"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "VHF", "frequency": 144000000, "frequency_max": 146000000}, {"antenna_type": "helical", "antenna_type_name": "Helical", "band": "S", "frequency": 2300000000, "frequency_max": 2450000000}, {"antenna_type": "helical", "antenna_type_name": "Helical", "band": "L", "frequency": 1230000000, "frequency_max": 1290000000}]	2
87	M0IEB/M	15.04.2018, Testing software with temporary home rig location, 23.04.2018, Home rig set, added LNA and filter for 145M band. Enjoy! 26.04.2018, Rig won't get 137M, 07.05.2018, Updated to 0.6.1, In case it was not clear, this station will not receive NOAA. 29.08.2018, now sitting in Surrey Space Centre temporarily. 14.12.2018: Tracking ESEO only on UHF, no tracking available for other sats. 08.02.2019: Added ISS for ARISS SSTV Event. 27.06.2019: Tracking ESA ESEO. From Nov 2019, tracking OPS-SAT.	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}, {"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "VHF", "frequency": 135000000, "frequency_max": 146000000}]	2
254	SP2ZIE - 70cm	Rotator damaged. PW-SAT2 Satellite only. 435.275MHz+/-25kHz bandpass filter. AZ-EL Wimo WX7036. High noise environment. 437-438MHz strong QRMs from LoRa like transmissions. Band limited to 435MHz-440MHz (bandpass filter). Test setup.	[{"antenna_type": "cross-yagi", "antenna_type_name": "Cross Yagi", "band": "UHF", "frequency": 432000000, "frequency_max": 439000000}]	2
527	Test1	This is only a test for a system later on	[{"antenna_type": "helical", "antenna_type_name": "Helical", "band": "L", "frequency": 1600000000, "frequency_max": 1800000000}, {"antenna_type": "helical", "antenna_type_name": "Helical", "band": "L", "frequency": 1500000000, "frequency_max": 1750000000}]	2
959	CE3VRT - VHF - UHF	Station on rural area (limited internet access). Please try to schedule Fox series sats only. Gear: - VHF Turnstile - FM Filter + LNA4ALL - Nooelec RTLSDR	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}]	2
1287	FOSSA-STATION	Test Tape Measure Yagi, Only North/West Passes	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}]	2
1526	Budapest UHF (west only)	2*3el UHF cross yagi facing 270deg AZ 45deg EL. Setup: Orange Pi Zero 256M, LimeSDR Mini, diy filter with ~2dB loss, SPF5043Z ~0.6NF 20dB LNA, diy antenna. Contact me on the community forms if you have any questions.	[{"antenna_type": "cross-yagi", "antenna_type_name": "Cross Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 440000000}]	2
1594	VK2PET #2 Trial Station	This station is a Trial to play around with antennas & SDR's. It will NOT be online all the time. Limited by myself entering the Satellite passes into PST-Rotator. Back online for testing only as I'm having a lot of issues with this station. For all others please use my other station.	[{"antenna_type": "other direct", "antenna_type_name": "Other Directional", "band": "VHF", "frequency": 135000000, "frequency_max": 148000000}, {"antenna_type": "other direct", "antenna_type_name": "Other Directional", "band": "UHF", "frequency": 430000000, "frequency_max": 450000000}]	2
1687	DL6KBG-AZEL	SPID RAS Rotator with 1 degree resolution. Still on the ground and only for testing. Wimo X-Quad for 2M. No LNA so far.	[{"antenna_type": "cross-yagi", "antenna_type_name": "Cross Yagi", "band": "VHF", "frequency": 130000000, "frequency_max": 148000000}]	2
1775	Vi3w Roy4l	Dual-Band Log Periodic Antenna or LPDA (log-periodic dipole array) VHF/UHF. 2 Meter gain is 6.8 dbd (8.9 dBi) and 440 Gain is 7 dBd (9 dBi). Set up on a 360° Rotator, Azimuth only with a set angle of 30°. The Azimuth changes from time to time when manually scheduling for Satellites on the Polar Plot. Rpi 3B+, RTL-SDR + RTL-LNA. Trying this all out and we'll see what happens, 1st Station on the Island. July: Added a Rotator Wifi controller on the LAN/WAN to control the Rotator on/off site.	[{"antenna_type": "other direct", "antenna_type_name": "Other Directional", "band": "VHF", "frequency": 136000000, "frequency_max": 148000000}, {"antenna_type": "other direct", "antenna_type_name": "Other Directional", "band": "UHF", "frequency": 400000000, "frequency_max": 429000000}, {"antenna_type": "other direct", "antenna_type_name": "Other Directional", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}]	1
1824	Budapest UHF2 (west only)	Test station running on a Nexus 5 Android phone, antenna: ATK-10.	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}]	2
2153	Langhecke	ON TRIAL AND INTERMITTENT ! RPi3B, Adalm Pluto: currently testing rotator and S-band reception chain ... Only passes between NE thru SE to SW - antenna has no sky view from west to north.	[{"antenna_type": "parabolic", "antenna_type_name": "Parabolic", "band": "S", "frequency": 2200000000, "frequency_max": 2435000000}]	2
2482	JO52-L	Helix pointing straight up \| sawbird GOES with ~3m of coax between feed and LNA, free for the satnogs station when not in use for HRPT \| only east passes	[{"antenna_type": "helical", "antenna_type_name": "Helical", "band": "L", "frequency": 1650000000, "frequency_max": 1720000000}]	2
2736	OH2AGS-1: Aalto Satellites	Aalto Satellites ground station is a station hosted and maintained by Aalto University's small satellites team. The station is also used for small satellites operations and thus it is not always available for the SatNOGS network. This station is a "co-station" of station #2708 (OH2AGS-0 - Aalto Satellites) meaning that they share the antenna and radio equipment. Both station can track simultaneously different satellites but only the primary station can turn the antenna rotator.	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 435000000, "frequency_max": 438000000}]	2
2837	KB7KY-UHF-EXPERIMENTAL	Building first ground station - note AZ rotator only - EL set at about 30 degrees	[{"antenna_type": "yagi", "antenna_type_name": "Yagi", "band": "UHF", "frequency": 430000000, "frequency_max": 450000000}]	2

year	num_new
2015	4
2016	1
2017	25
2018	349
2019	700
2020	608
2021	498
2022	426
2023	1

observer	num_gs	num_obs	ground_station
172	492	114450	1650
2740	166	91709	2051
1394	2	88821	704
3000	212	74776	1864
326	1	58961	91
3345	37	57493	2380
1501	1	57393	766
968	21	53185	1466
3122	2	44933	1936
2727	2	40501	1667

Year in Review – 2022

Table of Contents

Ground Stations database setup¶

Rotator information¶

Ground Stations Statistics¶

Popular antenna types¶

Number of rotation axes¶

Online status¶

Ground stations created by year¶

Observations database setup¶

Observations Statistics¶

Most active Observers¶

High utilization and large averages¶

91 is only in second place, station #6 has 12% more observations!¶

Popular ground stations¶

Observer diversity¶

Popular satellites this year¶

Observers with satellite fever¶

Stations watching all-the-sats¶

More stats¶

Your turn!¶

Housekeeping¶

type	num_type
turnstile	358
yagi	315
quadrafilar	291
vertical	251
cross-yagi	229
None	207
dipole	181
v-dipole	153
eggbeater	140
discone	140
other omni	92
helical	80
ground	69
lindenblad	38
other direct	35
parabolic	18
patch	10
paralindy	5

id	name	description	antenna	rotator
888	CCERES	Directionnal WIMO VHF 10 elements and WIMO UHF 18 elements crossed yagi antennas + circular polarization coupler + UHF MVV70cm LNA + Diplexer 2m/70cm + SDR Airspy R2. Rotator : Yaesu G-5500 + GS-232B. Actually no have LNA for VHF antenna.	[{"antenna_type": "quadrafilar", "antenna_type_name": "Quadrafilar", "band": "UHF", "frequency": 430000000, "frequency_max": 440000000}, {"antenna_type": "cross-yagi", "antenna_type_name": "Cross Yagi", "band": "VHF", "frequency": 135000000, "frequency_max": 150000000}]	0
2500	Wolfgang HB9RYZ	Swiss Amateur Radio Station (JN47FE). Building up a brand new Ground Station in Q1/2022 with two Wimo X-Quad Antennas for 2m and 70cm including pre-amps and a SPID-RAS rotor. Temp.: running a Diamond vertical X-50NA (2m/70cm) on top of my roof. www.hb9ryz.ch	[{"antenna_type": "vertical", "antenna_type_name": "Vertical", "band": "VHF", "frequency": 135000000, "frequency_max": 148000000}, {"antenna_type": "vertical", "antenna_type_name": "Vertical", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}]	0

id	name	description	antenna	rotator
357	ce3vna	estacion completa de radioaficionados con rotor de azimuth y elevacion	[{"antenna_type": "ground", "antenna_type_name": "Ground Plane", "band": "VHF", "frequency": 135000000, "frequency_max": 148000000}, {"antenna_type": "ground", "antenna_type_name": "Ground Plane", "band": "UHF", "frequency": 430000000, "frequency_max": 440000000}]	0
1146	Ham Radio Ground Station	Ground station project specifically designed for communication with small satellites in Low Earth Orbit using VHF and UHF amateur radio frequencies and provide hands-on educational projects in the world The ground station can autonomously track satellites by using these equipments : VHF / UHF amateur radio transceiver (Amateur bands 144-16 MHZ and 430-440 MHZ) Rotator Controller Azimuth and elevation rotators UHF and VHF Yagi antennas (Amateur bands 144-16 MHZ and 430-440 MHZ) Modems...	[]	2
1775	Vi3w Roy4l	Dual-Band Log Periodic Antenna or LPDA (log-periodic dipole array) VHF/UHF. 2 Meter gain is 6.8 dbd (8.9 dBi) and 440 Gain is 7 dBd (9 dBi). Set up on a 360° Rotator, Azimuth only with a set angle of 30°. The Azimuth changes from time to time when manually scheduling for Satellites on the Polar Plot. Rpi 3B+, RTL-SDR + RTL-LNA. Trying this all out and we'll see what happens, 1st Station on the Island. July: Added a Rotator Wifi controller on the LAN/WAN to control the Rotator on/off site.	[{"antenna_type": "other direct", "antenna_type_name": "Other Directional", "band": "VHF", "frequency": 136000000, "frequency_max": 148000000}, {"antenna_type": "other direct", "antenna_type_name": "Other Directional", "band": "UHF", "frequency": 400000000, "frequency_max": 429000000}, {"antenna_type": "other direct", "antenna_type_name": "Other Directional", "band": "UHF", "frequency": 430000000, "frequency_max": 470000000}]	2
2221	Francis Ground Station	Antenna array of 2 elements yagi for UHF Antenna dish and S-Band The Antenna array is attached to the edge of the dish of the S-Band Antenna The Antenna has a servo motor that controls azimuth and another for elevation	[]	2

ID	name	note
172	BOCTOC-1	The Human AutoScheduler :)
173	fredy	SatNOGS ops team lead
1542	wiredlab	me
2735	satnogs-auto	Auto Scheduler

ground_station	num_obs
6	67427
91	59955
766	58606
1864	54180
1936	45809
1126	44917
704	43908
2015	38615
901	37771
2029	33410

ground_station	num_obs	minutes	%	min_per_obs
766	58606	599340.8	114.0	10.2
91	59955	565239.5	107.5	9.4
1864	54180	557499.7	106.1	10.3
6	67427	554060.8	105.4	8.2
2015	38615	470562.1	89.5	12.2
1404	26768	448625.1	85.4	16.8
1936	45809	434865.1	82.7	9.5
2410	26278	434056.5	82.6	16.5
1126	44917	414250.7	78.8	9.2
704	43908	406346.7	77.3	9.3

id	ground_station	minutes
5263869	2490	12.8
5263130	2490	8.5
5263357	2490	14.9
5263682	2490	8.5
5262843	2490	12.8
5263731	2490	12.8
5264184	2490	14.9
5263101	2490	12.8
5261803	2490	264.5
5254255	2490	14.9
5261802	2490	228.3
5261801	2490	10.7
5261800	2490	8.5
5261799	2490	12.8
5261742	2490	6.4
5261741	2490	12.8

ground_station	num_observers	num_obs
None	54	8105
858	44	2364
183	41	1227
47	41	1706
2	40	2271
37	38	1506
1562	37	1190
657	37	6799
1698	36	1396
1663	36	2750

observer	num_gs	num_obs
172	492	114450
173	450	35905
1996	323	20634
837	320	12871
1220	234	13455
3800	221	4467
2649	220	1798
3000	212	74776
2492	200	9178
2735	195	24612
1600	195	2193
2740	166	91709
4307	145	6801
4183	142	1136
3307	134	2565
3308	132	1004
3602	118	9157
1542	107	1672
3002	106	3947
1227	101	856

norad	num_gs	num_obs
33591	439	30233
28654	447	28435
25544	494	27271
40379	353	24944
25338	405	24515
48850	335	20859
40069	359	19534
40014	351	18594
47701	294	18010
48943	259	17506
47959	302	16118
43199	323	15671
20442	367	15398
47438	301	15267
40043	349	13825
39090	244	13263
51085	323	13031
40968	334	12735
39444	347	12708
39469	318	12437

rotator	num	percent
None	0	0.0
0	1751	67.0
1	2	0.1
2	654	25.0

rotator	num	percent
None	0	0.0
0	308	77.0
1	2	0.5
2	89	22.3

ground_station	num
834	14070
841	818
890	855

observer	num_sat	num_obs	diversity
172	658	114450	5.7
2012	576	19035	30.3
2533	540	11825	45.7
2740	528	91709	5.8
3462	527	31484	16.7
3000	513	74776	6.9
326	484	58961	8.2
969	453	1173	386.2
2401	448	24139	18.6
3223	441	37198	11.9

observer	num_sat	num_gs	num_obs	diversity
3002	1	106	3947	0.3
750	11	1	6159	1.8
1984	1	2	447	2.2
3228	8	2	3717	2.2
792	11	3	4829	2.3
1891	2	1	830	2.4
1535	4	1	1592	2.5
2408	99	25	39978	2.5
1394	272	2	88821	3.1
1600	7	195	2193	3.2
3417	33	2	10172	3.2
3430	17	1	5373	3.2
1731	5	1	1524	3.3
180	7	1	2045	3.4
1093	6	1	1656	3.6
3433	9	1	2411	3.7
3525	54	1	13710	3.9
430	4	4	992	4.0
2302	8	1	1990	4.0
173	158	450	35905	4.4
3467	31	79	7096	4.4
1073	7	1	1491	4.7
3903	58	5	11165	5.2
172	658	492	114450	5.7
2740	528	166	91709	5.8

ground_station	num_sat	num_obs
6	597	67427
91	559	59955
1534	547	7503
2733	541	8838
1864	532	54180
432	525	2162
1133	517	7492
1667	510	20448
2134	490	32137
2015	488	38615