From d7b36671e3edaf0a9046a2d58c9be5b81316944b Mon Sep 17 00:00:00 2001 From: Dan White Date: Sat, 24 Dec 2022 17:29:11 -0600 Subject: [PATCH] cleanup analysis in sqlitebrowser --- observations_analysis.sqbpro | 81 +++++++++++++++++++++++++++++++----- 1 file changed, 70 insertions(+), 11 deletions(-) diff --git a/observations_analysis.sqbpro b/observations_analysis.sqbpro index 77a9813..29e5ba6 100644 --- a/observations_analysis.sqbpro +++ b/observations_analysis.sqbpro @@ -1,4 +1,5 @@ -
create index if not exists gs_norad_index +
-- 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 @@ -12,26 +13,84 @@ on observations(observer); create index if not exists observer_gs_index on observations(observer, ground_station); -CREATE VIRTUAL TABLE IF NOT EXISTS times_index + +create index if not exists gs_observer_index +on observations(ground_station, observer); +-- Special index for speeding up time range queries. +CREATE VIRTUAL TABLE IF NOT EXISTS times_index USING rtree(id, start, end); INSERT OR REPLACE INTO times_index SELECT id, strftime("%s", start), strftime("%s", end) FROM observations WHERE strftime("%s", start) <= strftime("%s", end); -select observer, count(vetted_status) from observations +-- 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) +BEGIN + INSERT OR REPLACE INTO times_index(id, start, end) + VALUES (NEW.id, strftime("%s", NEW.start), strftime("%s", NEW.end)); +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) + VALUES (NEW.id, strftime("%s", NEW.start), strftime("%s", NEW.end)); +END; + +CREATE TRIGGER IF NOT EXISTS times_delete_trigger +AFTER DELETE +ON observations +BEGIN + DELETE FROM times_index + WHERE id = OLD.id; +END; +-- Observers with un-vetted observations +-- LONG +SELECT + observer, + count(vetted_status) AS num_unknown +FROM observations WHERE vetted_status = "unknown" GROUP BY observer -ORDER BY observer;select ground_station, norad_cat_id, count(id) from observations +ORDER BY num_unknown;-- Which satellites are listened to by which GS? +select ground_station, norad_cat_id, count(id) from observations GROUP BY ground_station, norad_cat_id -ORDER BY ground_station, count(id);select observer, ground_station, count(id) from observations +ORDER BY ground_station, count(id);-- Favorite ground stations used by observers +select observer, ground_station, count(id) from observations GROUP BY observer, ground_station ORDER BY observer, count(id) DESC, ground_station; -select ground_station, observer, count(id) from observations +-- Observations on each ground station, by total number per observer +-- Largest number is likely the GS owner? +SELECT + ground_station, + observer, + count(id) +FROM observations +WHERE ground_station = 2 GROUP BY ground_station, observer ORDER BY ground_station, count(id) DESC, observer; --- WIP: how many different observers have used each GS? -select ground_station, count(*) from observations -GROUP BY ground_station, observer -ORDER BY count(distinct observer) DESC, ground_station; - +-- How many different observers have scheduled on each GS? +SELECT + ground_station, + count(distinct observer) as num_observers, + count(id) AS num_obs +FROM observations +GROUP BY ground_station +ORDER BY ground_station; +-- How many different GS have observers scheduled on? +SELECT + observer, + count(distinct ground_station) AS num_gs, +count(id) AS num_obs +FROM observations +GROUP BY observer +ORDER BY num_gs DESC; + -- 2.25.1