inst/extdata/Templates/Oracle.R

In jacciz/wisdotcrashdatabase: A package used for internal WisDOT crash database pulls and analysis

# Resource: https://www.r-bloggers.com/2017/05/connecting-r-to-an-oracle-database/
# Oracle: https://db.rstudio.com/databases/oracle/

# GIS tables: https://wigov.sharepoint.com/:w:/r/sites/dot-dbm/bits/es/gis/users/_layouts/15/Doc.aspx?sourcedoc=%7B177531F8-048F-431C-BC11-9FC195C15354%7D&file=SDE-Unabbreviated-Names.docx&action=default&mobileredirect=true&DefaultItemOpen=1&CID=2406774C-0DB6-40D3-AB8D-C30010639B70&wdLOR=cFF434E94-1E1E-4638-97C7-BD2EF044FEBB
# MetaData: https://wigov.sharepoint.com/sites/dot-dtsd/mydtsd/dotview/SitePages/HelpMetaDataLayer.aspx
# MetaData: https://wigov.sharepoint.com/sites/dot-dtim/bshp/stn/SitePages/State%20Trunk%20Network%20(STN)%20Inventory%20Database.aspx
# Meta-Manager: https://wigov.sharepoint.com/:w:/r/sites/dot-dtsd/ser/sp/corstudy/sth59-67/_layouts/15/Doc.aspx?sourcedoc=%7B5EA11335-2F2B-48AC-BA65-80BFCDB42D94%7D&file=Metadata.doc&action=default&mobileredirect=true&DefaultItemOpen=1

# Other package, RODBC, connection does not include 'DV_' tables. 'DV_' is material views, should use these as they have more variables.
# Chuck: You will see that because of how the unique key was set up for these tables, some records are duplicated in the DV_ views.
# You can also work with DOTSYS schema tables that correspond with the DOT1SDEA DT tables to build your own queries and relationships among them. 

# Non-GIS Tables
# Get list of tables
tables_all <- DBI::dbListTables(con) |> as.data.frame() |> dplyr::arrange()
# dplyr::tbl(con, "DT_WI_CNTY_PITC")
# x = sf::read_sf("c:/data/gis/DT_ST_WISLR_sth.gpkg")
# mapview::mapview(x)
# Get list of drivers
sort(unique(odbc::odbcListDrivers()[[1]]))

# DBI::dbDisconnect(con)

# ---- MAKE A FUNCTION - DBI ----

# library(magrittr)
# First, make the connection.
con <- DBI::dbConnect(odbc::odbc(),
                      # Driver = "Oracle in OraClient11g_home1",
                      Driver = "Oracle in instantclient_19_8",
                      dsn = "OraGISProdOLTP",
                      UID    = "DOT1SDEV",
                      PWD = "badger"
                      # dbname = "DOT1SDEA"
)

get_oracle_table <-
  function(oracle_table_name) {
    table_to_get = paste0("DOT1SDEA.", oracle_table_name)
    
    col_names <-
      DBI::dbGetQuery(
        con,
        paste0(
          "select column_name from all_tab_columns where table_name ='",
          oracle_table_name,
          # "DT_MMGR_FIPR_LINE",
          "';"
        )
      ) |> dplyr::filter(COLUMN_NAME != "SHAPE")
    col_names_list <- as.list(col_names)$COLUMN_NAME |> 
      paste(collapse = ", ")
    
    y = DBI::dbGetQuery(con,
                        paste0("SELECT ",
                               col_names_list,
                               " FROM ",
                               table_to_get))
    y
  }

get_oracle_geometry <-
  function(table_name,
           oracle_table_name,
           more_than_a_thousand = FALSE) {
    table_to_get = paste0("DOT1SDEA.", oracle_table_name)
    if (more_than_a_thousand) {
      table_geom <- DBI::dbGetQuery(con,
                                    paste0(
                                      "SELECT OBJECTID, sde.ST_AsText(shape) as GEOM FROM ",
                                      table_to_get
                                    ))
    } else  {
      rows_to_get <- table_name$OBJECTID |> paste(., collapse = ", ")
      table_geom <- DBI::dbGetQuery(
        con,
        paste0(
          "SELECT OBJECTID, sde.ST_AsText(shape) as GEOM FROM ",
          table_to_get,
          " WHERE OBJECTID IN (",
          rows_to_get,
          ")"
        )
      )
    }
    
    table_geom = sf::st_as_sf(table_geom,
                              wkt = "GEOM",
                              na.fail = TRUE,
                              crs = 3071)
    dplyr::left_join(table_geom, table_name, by = "OBJECTID")
  }

# Get list of tables
tables <-
  DBI::dbListTables(con, schema_name = "DOT1SDEA") |>  as.data.frame()
# dplyr::filter(tables, grepl(pattern = "^DV_", tables)) |> dplyr::arrange()

test = get_oracle_table(oracle_table_name = "DT_ST_WISLR_MAP_LINE")
# test5 = test[1:100,]
test6 <- get_oracle_geometry(table_name = test, oracle_table_name = "DT_ST_WISLR_MAP_LINE", more_than_a_thousand = TRUE)

colnames(test)

cleaned <- janitor::clean_names(test6) %>% dplyr::mutate_at(dplyr::vars(ends_with("_dt")), as.Date) #%>% sf::st_convex_hull() #st_collection_extract(., "LINESTRING")
# sf::st_write(cleaned, dsn = "C:/data/gis/DT_ST_WISLR_MAP_LINE_Oct2022.gpkg", append = FALSE, delete_dsn = TRUE, delete_layer = TRUE)

# DV_FFCL_LINE # functional class
test4= sf::read_sf("C:/data/gis/DT_ST_WISLR_MAP_LINE.gpkg")
test = test4[1:100,]
mapview::mapview(test)# zcol = "FFCL_DESC")
mapview::mapview(cleaned)

# WISLR file:///C:/Users/dotjaz/Downloads/WISLR_Overlay_Column_Descriptions.htm
# GET NON-DOT1SDEA.
y = DBI::dbGetQuery(con,"SELECT * FROM DOTSYS.DT_RWSF_TY_PITC")
# DOT1LRDB.DT_FNCT_CLS_TY func class
readr::write_rds(y, "aux_lane_codes.rds")

#--- BETTER WAY TO GET ORACLE SPATIAL DATA
table_name = "DT_RTE_RGN"

# DBI::dbColumnInfo(con, 'DOT1SDEA.DT_RTE_RGN')
# DBI::dbGetQuery(con, "SELECT * FROM DOTSYS.DT_RWSF_TY_PITC")

col_names <-
  DBI::dbGetQuery(
    con,
    paste0(
      "select column_name from all_tab_columns where table_name ='",
      table_name, "'"
    )
  ) |> dplyr::filter(COLUMN_NAME != "SHAPE")
col_names_list <- as.list(col_names)$COLUMN_NAME |> #pull(col_names, COLUMN_NAME)
  paste(collapse = ", ")


y = sf::st_read(con, query = paste0("SELECT ",
                                    col_names_list,
                                    ", sde.ST_AsBinary(shape) as GEOM FROM ",
                                    "DOT1SDEA." , table_name))
#---