Database Views

Author

Ben Best

Published

December 6, 2022

Modified

December 6, 2022

Overview

These databaase views make common queries easier by consolidating related tables. A regular VIEW is executed every time it’s called whereas a MATERIALIZED VIEW is cached and instantly available like a table.

By generating these views with this Quarto document, you’ll see when it was last rendered with the “MODIFIED” date stamp and any other diagnostic information can be displayed as a versioned record in Github.

view_ctd_casts

# load latest dev version of calcofi4r from parent folder
devtools::load_all(here::here("../calcofi4r"))
ℹ Loading calcofi4r
# get database connection (con)
con <- cc_db_connect()

# list tables in db
dbListTables(con)
 [1] "geography_columns"          "geometry_columns"          
 [3] "spatial_ref_sys"            "field_labels"              
 [5] "krill_abundances"           "stations_order"            
 [7] "r_sta_cnt"                  "species_groups"            
 [9] "dissolved_inorganic_carbon" "ctd_bottles"               
[11] "ctd_casts"                  "egg_counts"                
[13] "larvae_counts"              "scrippscast"               
[15] "species_codes"              "stations"                  
[17] "taxa_hierarchy"             "tow_types"                 
[19] "tsn_list"                   "tows"                      
[21] "stations_hull"              "aoi_fed_sanctuaries"       
[23] "grd_a"                      "ships"                     
[25] "grd_mer"                    "r_mer100km"                
[27] "stations_new"               "tows_new"                  
[29] "raster_columns"             "raster_overviews"          
[31] "test_rast"                  "cruises"                   
[33] "effort_zones"               "effort_grid"               
[35] "effort_ctrs"                "effort_areas"              
[37] "places"                    
# preview tables used for input
tbl(con, "ctd_casts")
# Source:   table<ctd_casts> [?? x 62]
# Database: postgres  [admin@localhost:5432/gis]
   cast_c…¹ cruis…² cruise cruz_…³ dbsta…⁴ castid sta_id quarter sta_c…⁵ dista…⁶
      <int> <chr>   <chr>  <chr>   <chr>   <chr>  <chr>    <int> <chr>     <dbl>
 1    25686 1988-0… 198809 198809… 9000730 19-88… 090.0…       3 NST       -182.
 2        1 1949-0… 194903 194903… 5400560 19-49… 054.0…       1 NST         NA 
 3        2 1949-0… 194903 194903… 5200750 19-49… 052.0…       1 NST         NA 
 4        3 1949-0… 194903 194903… 5100850 19-49… 051.0…       1 NST         NA 
 5        4 1949-0… 194903 194903… 5000950 19-49… 050.0…       1 NST         NA 
 6        5 1949-0… 194903 194903… 5001040 19-49… 050.0…       1 NST         NA 
 7        6 1949-0… 194903 194903… 4901140 19-49… 049.0…       1 NST         NA 
 8        7 1949-0… 194903 194903… 5671460 19-49… 056.7…       1 NST         NA 
 9        8 1949-0… 194903 194903… 5671360 19-49… 056.7…       1 NST         NA 
10        9 1949-0… 194903 194903… 5801270 19-49… 058.0…       1 NST         NA 
# … with more rows, 52 more variables: date <date>, year <int>, month <int>,
#   juliandate <int>, julianday <int>, time <time>, latitude <dbl>,
#   latdeg <int>, latmin <dbl>, lathem <chr>, longitude <dbl>, londeg <int>,
#   lonmin <dbl>, lonhem <chr>, rptline <dbl>, stline <dbl>, acline <dbl>,
#   rptsta <dbl>, ststa <dbl>, acsta <dbl>, bottomdepth <dbl>, secchi <int>,
#   foreiu <int>, shipname <chr>, shipcode <chr>, datatype <chr>,
#   orderocc <int>, eventnum <int>, cruzleg <int>, origstaid <chr>, …
# preview tables used for input
tbl(con, "effort_grid")
# Source:   table<effort_grid> [?? x 7]
# Database: postgres  [admin@localhost:5432/gis]
   sta_key sta_lin sta_pos sta_dpos sta_shore sta_pattern geom                  
   <chr>     <int>   <int>    <int> <chr>     <chr>       <pq_gmtry>            
 1 10,0         10       0       20 nearshore historical  0103000020E6100000010…
 2 10,20        10      20       20 nearshore historical  0103000020E6100000010…
 3 10,40        10      40       20 nearshore historical  0103000020E6100000010…
 4 10,60        10      60       20 nearshore historical  0103000020E6100000010…
 5 10,80        10      80       20 offshore  historical  0103000020E6100000010…
 6 10,100       10     100       20 offshore  historical  0103000020E6100000010…
 7 20,0         20       0       20 nearshore historical  0103000020E6100000040…
 8 20,20        20      20       20 nearshore historical  0103000020E6100000010…
 9 20,40        20      40       20 nearshore historical  0103000020E6100000010…
10 20,60        20      60       20 nearshore historical  0103000020E6100000010…
# … with more rows
# create view
dbSendQuery(
  con, 
  "DROP MATERIALIZED VIEW IF EXISTS view_ctd_casts")
<PqResult>
  SQL  DROP MATERIALIZED VIEW IF EXISTS view_ctd_casts
  ROWS Fetched: 0 [complete]
       Changed: 0
dbSendQuery(
  con,
  "CREATE MATERIALIZED VIEW view_ctd_casts AS
  SELECT 
    c.*,  
    g.sta_shore AS grid_shore, sta_pattern AS grid_pattern, sta_key AS grid_key, 
    g.sta_lin AS grid_lin, g.sta_pos AS grid_pos
  FROM ctd_casts AS c
  JOIN effort_grid AS g
  ON ST_Contains(g.geom, c.geom)")
Warning in result_create(conn@ptr, statement, immediate): Closing open result
set, cancelling previous query
<PqResult>
  SQL  CREATE MATERIALIZED VIEW view_ctd_casts AS
  SELECT 
    c.*,  
    g.sta_shore AS grid_shore, sta_pattern AS grid_pattern, sta_key AS grid_key, 
    g.sta_lin AS grid_lin, g.sta_pos AS grid_pos
  FROM ctd_casts AS c
  JOIN effort_grid AS g
  ON ST_Contains(g.geom, c.geom)
  ROWS Fetched: 0 [complete]
       Changed: 35078
tbl(con, "view_ctd_casts")
Warning in result_create(conn@ptr, statement, immediate): Closing open result
set, cancelling previous query
# Source:   table<view_ctd_casts> [?? x 67]
# Database: postgres  [admin@localhost:5432/gis]
   cast_c…¹ cruis…² cruise cruz_…³ dbsta…⁴ castid sta_id quarter sta_c…⁵ dista…⁶
      <int> <chr>   <chr>  <chr>   <chr>   <chr>  <chr>    <int> <chr>     <dbl>
 1    25686 1988-0… 198809 198809… 9000730 19-88… 090.0…       3 NST       -182.
 2        1 1949-0… 194903 194903… 5400560 19-49… 054.0…       1 NST         NA 
 3        2 1949-0… 194903 194903… 5200750 19-49… 052.0…       1 NST         NA 
 4        3 1949-0… 194903 194903… 5100850 19-49… 051.0…       1 NST         NA 
 5        4 1949-0… 194903 194903… 5000950 19-49… 050.0…       1 NST         NA 
 6        5 1949-0… 194903 194903… 5001040 19-49… 050.0…       1 NST         NA 
 7        6 1949-0… 194903 194903… 4901140 19-49… 049.0…       1 NST         NA 
 8        7 1949-0… 194903 194903… 5671460 19-49… 056.7…       1 NST         NA 
 9        8 1949-0… 194903 194903… 5671360 19-49… 056.7…       1 NST         NA 
10        9 1949-0… 194903 194903… 5801270 19-49… 058.0…       1 NST         NA 
# … with more rows, 57 more variables: date <date>, year <int>, month <int>,
#   juliandate <int>, julianday <int>, time <time>, latitude <dbl>,
#   latdeg <int>, latmin <dbl>, lathem <chr>, longitude <dbl>, londeg <int>,
#   lonmin <dbl>, lonhem <chr>, rptline <dbl>, stline <dbl>, acline <dbl>,
#   rptsta <dbl>, ststa <dbl>, acsta <dbl>, bottomdepth <dbl>, secchi <int>,
#   foreiu <int>, shipname <chr>, shipcode <chr>, datatype <chr>,
#   orderocc <int>, eventnum <int>, cruzleg <int>, origstaid <chr>, …
create_index(con, "view_ctd_casts", "cast_count", is_unique=T)
<PqResult>
  SQL  CREATE UNIQUE INDEX IF NOT EXISTS view_ctd_casts_cast_count_idx ON view_ctd_casts(cast_count)
  ROWS Fetched: 0 [complete]
       Changed: 0
create_index(con, "view_ctd_casts", "geom", is_geom=T)
Warning in result_create(conn@ptr, statement, immediate): Closing open result
set, cancelling previous query
<PqResult>
  SQL  CREATE  INDEX IF NOT EXISTS view_ctd_casts_geom_idx ON view_ctd_casts USING GIST (geom)
  ROWS Fetched: 0 [complete]
       Changed: 0
create_index(con, "view_ctd_casts", "quarter")
Warning in result_create(conn@ptr, statement, immediate): Closing open result
set, cancelling previous query
<PqResult>
  SQL  CREATE  INDEX IF NOT EXISTS view_ctd_casts_quarter_idx ON view_ctd_casts(quarter)
  ROWS Fetched: 0 [complete]
       Changed: 0
create_index(con, "view_ctd_casts", "date")
Warning in result_create(conn@ptr, statement, immediate): Closing open result
set, cancelling previous query
<PqResult>
  SQL  CREATE  INDEX IF NOT EXISTS view_ctd_casts_date_idx ON view_ctd_casts(date)
  ROWS Fetched: 0 [complete]
       Changed: 0
create_index(con, "view_ctd_casts", "grid_pattern")
Warning in result_create(conn@ptr, statement, immediate): Closing open result
set, cancelling previous query
<PqResult>
  SQL  CREATE  INDEX IF NOT EXISTS view_ctd_casts_grid_pattern_idx ON view_ctd_casts(grid_pattern)
  ROWS Fetched: 0 [complete]
       Changed: 0
create_index(con, "view_ctd_casts", "grid_shore")
Warning in result_create(conn@ptr, statement, immediate): Closing open result
set, cancelling previous query
<PqResult>
  SQL  CREATE  INDEX IF NOT EXISTS view_ctd_casts_grid_shore_idx ON view_ctd_casts(grid_shore)
  ROWS Fetched: 0 [complete]
       Changed: 0