Last active
August 29, 2015 14:13
-
-
Save djhocking/eff1072b54b6d8049270 to your computer and use it in GitHub Desktop.
Big Queries and collect with dplyr
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# fetch temperature data | |
tbl_values <- left_join(tbl_series, | |
select(tbl_variables, variable_id, variable_name), | |
by=c('variable_id'='variable_id')) %>% | |
select(-file_id) %>% | |
filter(location_id %in% df_locations$location_id, | |
variable_name=="TEMP") %>% | |
left_join(tbl_values, | |
by=c('series_id'='series_id')) %>% | |
left_join(select(tbl_locations, location_id, location_name, latitude, longitude, featureid=catchment_id), | |
by=c('location_id'='location_id')) %>% | |
left_join(tbl_agencies, | |
by=c('agency_id'='agency_id')) %>% | |
mutate(year = date_part('year', datetime)) | |
df_values <- collect(tbl_values) | |
df_values <- df_values %>% | |
mutate(datetime=with_tz(datetime, tzone='EST')) | |
summary(df_values) | |
# create climateData input dataset (too big without pre-filter or smaller join) | |
# tried to do the year filter within the postgres tbl query but was getting errors with recognizing | |
climate <- tbl_daymet %>% | |
mutate(year = date_part('year', date)) %>% | |
filter(featureid %in% df_locations$featureid) | |
tbl_climate <- climate %>% | |
filter(year %in% unique(df_values$year)) # distinct() doesn't work on numeric values | |
climateData <- collect(tbl_climate) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Ok I think this will work for getting the daily daymet values associated with only the catchments and years that have data. The first part (
WITH loc_years AS (...)
) is called a Common Table Expression (CTE) (read this) and basically creates a temporary table namedloc_years
that can then be used in the join with thedaymet
table. Note this table is not saved in the database, it is only used by the query when it is run.The
loc_year
CTE will be a table containing the unique combination of featureid, year, variable and number of values (n
). But this is also filtered to only include water temperature (so the variable column is kind of moot). The query below also filters for only two featureids('740020', '746011')
just for testing. You can remove the last line of the WHERE clause (AND l.catchment_id IN ('740020', '746011')
) to retrieve all the featureids with data.The multicolumn index (featureid, year) on daymet is still being created, might take a few hours. but after that is ready, this query should hopefully be at least somewhat faster.
Here's code to try running in R: