a simple query to do like the API does

@KarthikGopi1234 this helped me to figure out when the ozone in bathurst starts

PS NSW said there were about 7 years of ozone there, but this query returns first date in 2020. we should ask for all their ozone in the next napmd update

library(cardatdbtools)
if(!require("RPostgreSQL")) install.packages("RPostgreSQL"); library("RPostgreSQL")

username <- "ivan_hanigan"
hostip <- "swish5.tern.org.au"
dbname <- "postgis_car"
pwd <- get_pgpass(database = dbname,host = hostip,user = username,remote = TRUE,savePassword = TRUE)
ch <- connect2postgres(hostip = pwd[,1],db = pwd[,3],user = pwd[,4],p = pwd[,5])

cardat_ap_monitors_with_obs_by_var <- function(
    var = "o3"
    ,
    station = "bathurst"
    ,
    state = "NSW"
){
  sql <- paste0("select
l.station_id, l.station, l.state, l.altitude, l.lat,  l.lon, d.*
from (
select * 
from air_pollution_monitors.ap_monitor_locations_master l_all
where station = '",station,"' and state = '",state,"'
) l
left join
air_pollution_monitors.ap_monitor_data_master d
on l.state = d.state
and l.station = d.station
where d.variable = '",var,"'
and d.value is not null
order by d.date_time_utc
")

  print("With thanks to NSW DPIE, EPA Victoria, SA EPA, NT EPA, EPA Tasmania, ACT Health, Qld DES, WA DWER for provision of the air pollution monitoring data in this database.")
  print("Please note that this query will take a few minutes to complete.")
  return(sql)
}
sql <- cardat_ap_monitors_with_obs_by_var(var = "o3", station = "bathurst", state = "NSW")

cat(sql)
dat <- dbGetQuery(ch, sql)
head(dat)

cardat / cardatdbtools

a simple query to do like the API does #11