From 31cd003784dffcb0c0496b81d12d69952c693575 Mon Sep 17 00:00:00 2001 From: Dan White Date: Fri, 30 Dec 2022 13:49:56 -0600 Subject: [PATCH] stations.db updates --- do-updates.sh | 1 + observations_analysis.sqbpro | 17 ++- stations-audit.sh | 36 ++++++ stations-schema.sql | 26 +++++ stations.sqbpro | 218 +++++++++++++++++++++++++++++++++++ 5 files changed, 292 insertions(+), 6 deletions(-) create mode 100755 stations-audit.sh create mode 100644 stations-schema.sql create mode 100644 stations.sqbpro diff --git a/do-updates.sh b/do-updates.sh index fa55822..7220295 100755 --- a/do-updates.sh +++ b/do-updates.sh @@ -15,6 +15,7 @@ echo "*** Fetching database items" echo "*** Fetching stations info" ../python-files/get-stations.py stations.json +../python-files/update-stations stations.json stations.db echo echo "*** Getting observations" diff --git a/observations_analysis.sqbpro b/observations_analysis.sqbpro index 9bfdcdf..2f9398f 100644 --- a/observations_analysis.sqbpro +++ b/observations_analysis.sqbpro @@ -1,4 +1,4 @@ -
-- Indexes make the world go 'round ... faster. +
-- Indexes make the world go 'round ... faster. CREATE INDEX IF NOT EXISTS gs_norad_index ON observations(ground_station, norad_cat_id); @@ -85,13 +85,18 @@ FROM observations GROUP BY ground_station, observer ORDER BY ground_station, count(id) DESC, observer; -- How many different observers have scheduled on each GS? +--explain query plan SELECT ground_station, count(distinct observer) as num_observers, - count(id) AS num_obs -FROM observations + count(o.id) AS num_obs +FROM observations as o, times_index as i +WHERE o.id = i.id + AND i.start >= strftime("%s", "2018-01-01") + AND i.start < strftime("%s", "2019-01-01") GROUP BY ground_station -ORDER BY ground_station; +ORDER BY num_obs DESC +; -- How many different GS have observers scheduled on? SELECT observer, @@ -99,5 +104,5 @@ SELECT count(id) AS num_obs FROM observations GROUP BY observer -ORDER BY num_gs DESC; - +ORDER BY num_obs DESC; + diff --git a/stations-audit.sh b/stations-audit.sh new file mode 100755 index 0000000..2cedad8 --- /dev/null +++ b/stations-audit.sh @@ -0,0 +1,36 @@ +#!/bin/bash + +#for id in $(sqlite3 stations.db "SELECT id FROM stations WHERE rotator='';"); do +for id in $(awk -F, 'NR>1 {print $1}' < gs-observer-obs.csv); do + foo=$(sqlite3 stations.db "SELECT id FROM stations WHERE id=$id AND rotator NOT IN (0, 1, 2);") + if [[ "$foo" == "" ]]; then + continue + fi + echo + echo $id + json=$(sqlite3 stations.db "SELECT antenna FROM stations WHERE id=$id;") + desc=$(sqlite3 stations.db "SELECT description FROM stations WHERE id=$id;") + seen=$(sqlite3 stations.db "SELECT last_seen FROM stations WHERE id=$id;") + status=$(sqlite3 stations.db "SELECT status FROM stations WHERE id=$id;") + echo $json | jq + echo $desc | fmt -70 + + orig=$(sqlite3 stations.db "SELECT rotator FROM stations WHERE id=$id;") + echo $orig + echo $seen + echo $status + read -e -p 'N axes: ' -i "$orig" axes + + case $axes in + 0 | 1 | 2) + sql="UPDATE stations SET rotator=$axes WHERE id=$id;" + ;; + q) + break + ;; + *) + continue + ;; + esac + sqlite3 stations.db "$sql" +done diff --git a/stations-schema.sql b/stations-schema.sql new file mode 100644 index 0000000..f369ec8 --- /dev/null +++ b/stations-schema.sql @@ -0,0 +1,26 @@ + + +CREATE TABLE IF NOT EXISTS stations ( + id INT 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 use JQ to make a CSV to fill the table. +-- https://stackoverflow.com/a/46407771 +-- .mode csv +-- .import stations.csv stations + diff --git a/stations.sqbpro b/stations.sqbpro new file mode 100644 index 0000000..2fb8d38 --- /dev/null +++ b/stations.sqbpro @@ -0,0 +1,218 @@ +
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. +-- Translate antenna_type into a presumed number of motion axes + +-- turnstile +UPDATE stations SET rotator = 0 +WHERE json_extract(antenna, "$[0].antenna_type") = "turnstile"; + +-- yagi +UPDATE stations SET rotator = 2 +WHERE json_extract(antenna, "$[0].antenna_type") = "yagi"; + +-- quadrafilar +UPDATE stations SET rotator = 0 +WHERE json_extract(antenna, "$[0].antenna_type") = "quadrafilar"; + +-- vertical +UPDATE stations SET rotator = 0 +WHERE json_extract(antenna, "$[0].antenna_type") = "vertical"; + +-- cross-yagi +UPDATE stations SET rotator = 2 +WHERE json_extract(antenna, "$[0].antenna_type") = "cross-yagi"; + +-- dipole +UPDATE stations SET rotator = 0 +WHERE json_extract(antenna, "$[0].antenna_type") = "dipole"; + +-- v-dipole +UPDATE stations SET rotator = 0 +WHERE json_extract(antenna, "$[0].antenna_type") = "v-dipole"; + +-- discone +UPDATE stations SET rotator = 0 +WHERE json_extract(antenna, "$[0].antenna_type") = "discone"; + +-- eggbeater +UPDATE stations SET rotator = 0 +WHERE json_extract(antenna, "$[0].antenna_type") = "eggbeater"; + +-- other omni +UPDATE stations SET rotator = 0 +WHERE json_extract(antenna, "$[0].antenna_type") = "other omni"; + +-- helical +UPDATE stations SET rotator = 2 +WHERE json_extract(antenna, "$[0].antenna_type") = "helical"; + +-- ground +UPDATE stations SET rotator = 0 +WHERE json_extract(antenna, "$[0].antenna_type") = "ground"; + +-- lindenblad +UPDATE stations SET rotator = 0 +WHERE json_extract(antenna, "$[0].antenna_type") = "lindenblad"; + +-- other direct +UPDATE stations SET rotator = 2 +WHERE json_extract(antenna, "$[0].antenna_type") = "other direct"; + +-- parabolic +UPDATE stations SET rotator = 2 +WHERE json_extract(antenna, "$[0].antenna_type") = "parabolic"; + +-- patch +UPDATE stations SET rotator = 0 +WHERE json_extract(antenna, "$[0].antenna_type") = "patch"; + +-- paralindy +UPDATE stations SET rotator = 0 +WHERE json_extract(antenna, "$[0].antenna_type") = "paralindy"; +-- 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; +-- Modify the rotator column to better reflect what the description claims +SELECT DISTINCT + text.id, + text.name, + text.description, + stations.antenna, + stations.rotator +FROM text, stations +WHERE text.id = stations.id +AND stations.rotator = 2 +AND text MATCH 'fixed' +ORDER BY text.id; + +-- ok, look at the results above and decide which stations have only 0 or 1 axes or motion + +UPDATE stations +SET rotator = 0 +WHERE id IN (66, 212, 1351, 1787, 1810, 1868, 1950, 2080, 2107, 2246, 2263, + 2372, 2430, 2501, 2650); + + +-- 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' +AND rotator <> 2 +ORDER BY text.id; + +-- There were a couple +UPDATE stations SET rotator = 2 WHERE id IN (888, 2233); + + +-- Third search +SELECT DISTINCT + text.id, + text.name, + text.description, + stations.antenna, + stations.rotator +FROM text, stations +WHERE text.id = stations.id +AND text MATCH 'spid' +AND rotator <> 2 +ORDER BY text.id; + +-- Found another rotator +UPDATE stations SET rotator = 2 WHERE id IN (2500); + + +-- Fourth search +SELECT DISTINCT + text.id, + text.name, + text.description, + stations.antenna, + stations.rotator +FROM text, stations +WHERE text.id = stations.id +AND text MATCH 'azimuth' +AND rotator <> 5 +ORDER BY text.id; + +-- Found more rotators +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); + + +-- Fifth search +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 rotator <> 5 +ORDER BY text.id; + +-- More no-rotator stations +UPDATE stations SET rotator = 0 WHERE id IN (146, 484, 1640, 1710, 1789, 2029); + +-- Now would be a good time to find the stations with directional antennas but no rotator +-- What is remaining? +SELECT + json_extract(antenna, "$[0].antenna_type") as type, + count(antenna) as num_type +FROM stations +--WHERE rotator NOT IN (0, 1, 2) +GROUP BY type +ORDER BY num_type DESC;SELECT + json_extract(antenna, "$[0].antenna_type") as type, + count(antenna) as num_type +FROM stations +GROUP BY type +ORDER BY num_type DESC;-- Number of stations by motion axes +SELECT + rotator, + count(rotator) as num +FROM stations +GROUP BY rotator +ORDER BY rotator;-- How many GS were created +SELECT + 2022 as year, + status, + count(id) as num_new +FROM stations +WHERE strftime("%s", created) < strftime("%s", printf("%d-01-01", year+1)) + AND strftime("%s", created) >= strftime("%s", printf("%d-01-01", year)) +GROUP BY status +; -- 2.25.1