Features

[vnbl]

Create StationReadings and RegionReadings tables to prepare data for inference/training

[vnbl]

@quanturtle estuve probando cómo agregar una tabla con los factores de calibración para los sensores. Aquí paso una imagen de cómo está quedando la DB

[quanturtle]

@quanturtle estuve probando cómo agregar una tabla con los factores de calibración para los sensores. Aquí paso una imagen de cómo está quedando la DB

PatternStations es un tipo especifico de Station nada mas, yo agregaria una columna nueva en Stations que indique is_pattern_station = True/False

[vnbl]

Aquí el código de dbdiagram que usé para generar el diagrama:

Table Regions {
  id integer [primary key]
  name string 
  region_code string [unique]

  latitude_north float
  longitude_south float

  longitude_east float
  longitude_west float

  has_weather_data bool
  has_pattern_data bool

}

Table Stations {
  id integer [primary key]
  name string 
  latitude float
  longitude float
  region string 
  is_station_on bool
}

Table WeatherStations {
  id integer [primary key]
  name string
  latitude float
  longitude float
  region string
}

Table PatternStations {
  id integer [primary key]
  name string
  bbox string
  region string
}

Table StationReadingsRaw {
  id integer [primary key]
  measurement_id integer
  station_id integer //foreignkey stations.id
  fecha varchar
  hora varchar
  mp2_5 varchar
  mp1 varchar
  mp10 varchar
  temperatura varchar
  humedad varchar
  presion varchar
  bateria varchar
}

Table WeatherReadings {
  id integer [primary key]
  weather_station integer
  date datetime
  temperature float
  humidity float
  pressure float
  wind_speed float
  wind_dir_cos float
  wind_dir_sin float

}

Table PatternStationReadings {
  id integer [primary key]
  pattern_station integer
  date datetime
  pm2_5 float
  latitude float
  longitude float 
}

Table StationReadings {
id integer [primary key]
station integer //foreignkey stations.id
date datetime

pm1 float
pm2_5 float
pm10 float

pm2_5_avg_6h float
pm2_5_max_6h float
pm2_5_skew_6h float
pm2_5_std_6h float

aqi_pm2_5 float
aqi_pm10 float

level integer

aqi_pm2_5_max_24h float
aqi_pm2_5_skew_24h float
aqi_pm2_5_std_24h float

temperature float
humidity float
pressure float

}

Table RegionReadings {
  id integer [primary key]
  region integer
  date datetime
  pm2_5_region_avg float
  pm2_5_region_max float
  pm2_5_region_skew float
  pm2_5_region_std float

  aqi_region_avg float
  aqi_region_max float
  aqi_region_skew float
  aqi_region_std float

  level_region_max integer
}

Table CalibrationFactors {
  id integer [primary key]
  region string
  station_id integer

  date_start_cal datetime
  date_end_cal datetime
  station_mean float
  pattern_mean float

  date_start datetime
  date_end datetime
}

Ref: Stations.region > Regions.region_code
Ref: WeatherStations.region - Regions.region_code
Ref: PatternStations.region - Regions.region_code
Ref: RegionReadings.region > Regions.region_code
Ref: StationReadings.station > Stations.id
Ref: StationReadingsRaw.station_id > Stations.id 
Ref: CalibrationFactors.station_id > Stations.id
Ref: WeatherStations.id < WeatherReadings.weather_station
Ref: PatternStations.id < PatternStationReadings.pattern_station

[vnbl]

muy violentos tus cambios amia

es que cambiamos tablas que ya estaban hace rato