search

TruSat / trusat-backend

Apache License 2.0
32 stars 5 forks source link

Create queries for top-line stats #117

Open interplanetarychris opened 4 years ago

interplanetarychris commented 4 years ago

To support a trusat-frontend dashboard view, database.py will need new query(s) to support generation stats-on-demand such as the following

Satellites: 4282
Observations: 134,811
Users: 98
Satellites updated in the last 30 days: 826
Observations in the last 30 days: 3679
Active users in the last 30 days: 11

Consider creating a trigger to update this data (in a dedicated table) everytime IOD, TLE, Station, or Observer data is updated.

interplanetarychris commented 4 years ago

To support discussion (and perhaps for eventual documentation on learn.trusat.org), here's a little documentation on these stats:

"total_sat_count": 7590 Total number of unique norad objects in the catalog (all time)

"total_obs_count": 484727 Total number of observations in the catalog (all time)

"total_user_count": 322 Total number of registered users

"total_station_count": 169 Total number of registered stations (includes publicly-known COSPAR #### as well as Trusat T### stations)

"last30_sat_count": 3528 Unique number of satellites / norad objects observed in last 30 days.

"last30_tle_count": 213 Number of TruSat TLEs created in the last 30 days (this may include multiple TLEs per object)

"last30_obs_count": 19942 Number of TruSat observations made in the last 30 days. This may exclude observations which were submitted in the last 30 days, but the observation time is older.

"last30_active_stations": 17 Number of unique reporting stations in the last 30 days. (Sometimes, multiple stations per user)

"last30_active_users": 11 Number of unique reporting users in the last 30 days.

"current_month_obs_count": 15283 Cumulative number of observations, so far this calendar month (server time).

"previous_month_obs_count": 20255 Total number of observations last calendar month.

One finer point is that last30_tle_count doesn't reflect the number of objects, for which TLEs were generated -- which might be slightly more updated. For example, if we get 10,000 observations submitted for 10 objects, we might show 1000 TLEs being generated (for only 10 objects), which isn't really reflective of the usability of the info in the catalog.

I added an additional stat like last30_tle_sat_count which shows the number of satellites for which TLEs have been updated in the last 30 days.