Skip to content
Snippets Groups Projects

database statistics can be retrieved from REST API

    • Closed Issue created by Sabine Schröder @schroeder5

    The following database statistics can be retrieved from REST API
    (to be displayed on the TOAR data portal web page):

    • users
    • stations
    • time-series
    • data records

    Regarding "data records":
    If you don't need an exact count, the current statistic from the catalog table pg_class might be good enough and is much faster to retrieve for big tables (see: https://wiki.postgresql.org/wiki/Count_estimate).

    For the time being the numbers are fetched from the operational database as follows:

    • users:
      (on zam278 database: django_join) SELECT count(*) FROM account_emailaddress;
    • stations: (on zam10131 database: surface_observations_toar) SELECT count(*) FROM stations;
    • time-series: (on zam10131 database: surface_observations_toar) SELECT count(*) FROM parameter_series;
    • data records: using script determine_number_of_data_records.sh

    content of determine_number_of_data_records.sh:

    #!/bin/bash

HOURLY_SPEC="co no pm1 o3 no2 so2 ox aswdir pm10 rn ch4 wdir pm2p5 nox temp wspeed press cloudcover pblheight relhum totprecip u v albedo aswdifu humidity irradiance"
EVENT_SPEC="benzene toluene ethane propane mpxylene oxylene"
MONTHLY_SPEC="ch4 co"

rm -f data_records_count.txt
for sampling in hourly event monthly
do
  if [ "$sampling" = "hourly" ]
  then
    speclist=$HOURLY_SPEC
  elif [ "$sampling" = "event" ]
  then
    speclist=$EVENT_SPEC
  else
    speclist=$MONTHLY_SPEC
  fi
  for SPEC in $speclist
  do
#   psql -h localhost -U s.schroeder surface_observations_toar -c "\COPY (SELECT count(*) FROM ${SPEC}_${sampling}) TO PROGRAM 'cat >>data_records_count.txt'"
    psql -h localhost -U s.schroeder surface_observations_toar -c "\COPY (SELECT reltuples::BIGINT as approximate_row_count FROM pg_class WHERE relname='${SPEC}_${sampling}') TO PROGRAM 'cat >>data_records_count.txt'"
  done
done

awk '{ sum += $0 } END { print sum }' data_records_count.txt

    These queries have to be adapted to the new database structure (especially user accounts) when it is in operation.
    For the moment, the REST API will just be a place holder in the new REST API and will return fixed numbers (obtained on September 2nd, 2020).

    Designs

      Child items ...

      2. Activity

      Please register or sign in to reply