Accessing ADSM Data with R - NAVADMC/ADSM GitHub Wiki
Many thanks to Tiffany Timbers for getting us started on data exploration in R!
Review the items from Exporting to Statistical programs first to have a general understanding of the concepts behind ADSM. Also, when referring to the Result Interpretation, this is specifically the query and export portion that will be touched upon in a very simple fashion.
As noted previously, examples of queries written in the SQL language are provided in the Example Database Queries directory that is installed with ADSM and located in the ADSM Workspace. There are also R code examples in the Example R Code directory. R embeds the SQL at a specific point in the code. These examples are starting points for users to build on and customize. It is recommended that users pull an organized subset of data to start learning about their outputs, compared with dumping a large, disorganized dataset and attempting to sort it out.
The SQLite database behind ADSM can be directly accessed with R using the following code:
# # sign allows for a comment to be added in code
# Make a working directory to point at the ADSM Workspace
# May vary by user, but usually in Documents
setwd("C:/Users/MeSchoenbaum/My Documents/ADSM Workspace")
# A certain set of packages are needed how do I get packages when I don't have them?
#install.packages("RSQLite") #may be needed
#install.packages("DBI") #may be needed
library("RSQLite")
## connecting using an existing file,
## here's where you change the file name if you want to look at another database
con = dbConnect(SQLite(), dbname="Sample Scenario with Outputs.sqlite3")
# List tables in database
dbListTables(con)
# List fields in a given table
dbListFields(con, "Results_dailybyzone")
# Write a query
dbGetQuery(con,
"SELECT iteration, day, last_day, name, zonearea, zoneperimeter, numSeparateAreas
FROM Results_dailybyzone r Join ScenarioCreator_zone z on r.zone_id = z.id Where iteration = 1
Order by 1, ")
# send results into a variable
results <- dbSendQuery(con, "SELECT iteration, day, last_day, name, zonearea, zoneperimeter, numSeparateAreas
FROM Results_dailybyzone r Join ScenarioCreator_zone z on r.zone_id = z.id Where iteration = 1
Order by 1, 2")
# Call some results back
fetch(results,25)
R Core Team (2016). R: A language and environment for statistical computing. R Foundation for Statistical Computing, Vienna, Austria. URL https://www.R-project.org/.