-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathCreek_select_tbeptools.html
594 lines (496 loc) · 20 KB
/
Creek_select_tbeptools.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="generator" content="pandoc" />
<meta http-equiv="X-UA-Compatible" content="IE=EDGE" />
<meta name="author" content="M. W. Beck" />
<meta name="date" content="2025-01-29" />
<title>Creek Select for tbeptools</title>
<script src="site_libs/header-attrs-2.29/header-attrs.js"></script>
<script src="site_libs/jquery-3.6.0/jquery-3.6.0.min.js"></script>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link href="site_libs/bootstrap-3.3.5/css/bootstrap.min.css" rel="stylesheet" />
<script src="site_libs/bootstrap-3.3.5/js/bootstrap.min.js"></script>
<script src="site_libs/bootstrap-3.3.5/shim/html5shiv.min.js"></script>
<script src="site_libs/bootstrap-3.3.5/shim/respond.min.js"></script>
<style>h1 {font-size: 34px;}
h1.title {font-size: 38px;}
h2 {font-size: 30px;}
h3 {font-size: 24px;}
h4 {font-size: 18px;}
h5 {font-size: 16px;}
h6 {font-size: 12px;}
code {color: inherit; background-color: rgba(0, 0, 0, 0.04);}
pre:not([class]) { background-color: white }</style>
<script src="site_libs/navigation-1.1/tabsets.js"></script>
<link href="site_libs/highlightjs-9.12.0/default.css" rel="stylesheet" />
<script src="site_libs/highlightjs-9.12.0/highlight.js"></script>
<style type="text/css">
code{white-space: pre-wrap;}
span.smallcaps{font-variant: small-caps;}
span.underline{text-decoration: underline;}
div.column{display: inline-block; vertical-align: top; width: 50%;}
div.hanging-indent{margin-left: 1.5em; text-indent: -1.5em;}
ul.task-list{list-style: none;}
</style>
<style type="text/css">code{white-space: pre;}</style>
<script type="text/javascript">
if (window.hljs) {
hljs.configure({languages: []});
hljs.initHighlightingOnLoad();
if (document.readyState && document.readyState === "complete") {
window.setTimeout(function() { hljs.initHighlighting(); }, 0);
}
}
</script>
<style type = "text/css">
.main-container {
max-width: 940px;
margin-left: auto;
margin-right: auto;
}
img {
max-width:100%;
}
.tabbed-pane {
padding-top: 12px;
}
.html-widget {
margin-bottom: 20px;
}
button.code-folding-btn:focus {
outline: none;
}
summary {
display: list-item;
}
details > summary > p:only-child {
display: inline;
}
pre code {
padding: 0;
}
</style>
<style type="text/css">
.dropdown-submenu {
position: relative;
}
.dropdown-submenu>.dropdown-menu {
top: 0;
left: 100%;
margin-top: -6px;
margin-left: -1px;
border-radius: 0 6px 6px 6px;
}
.dropdown-submenu:hover>.dropdown-menu {
display: block;
}
.dropdown-submenu>a:after {
display: block;
content: " ";
float: right;
width: 0;
height: 0;
border-color: transparent;
border-style: solid;
border-width: 5px 0 5px 5px;
border-left-color: #cccccc;
margin-top: 5px;
margin-right: -10px;
}
.dropdown-submenu:hover>a:after {
border-left-color: #adb5bd;
}
.dropdown-submenu.pull-left {
float: none;
}
.dropdown-submenu.pull-left>.dropdown-menu {
left: -100%;
margin-left: 10px;
border-radius: 6px 0 6px 6px;
}
</style>
<script type="text/javascript">
// manage active state of menu based on current page
$(document).ready(function () {
// active menu anchor
href = window.location.pathname
href = href.substr(href.lastIndexOf('/') + 1)
if (href === "")
href = "index.html";
var menuAnchor = $('a[href="' + href + '"]');
// mark the anchor link active (and if it's in a dropdown, also mark that active)
var dropdown = menuAnchor.closest('li.dropdown');
if (window.bootstrap) { // Bootstrap 4+
menuAnchor.addClass('active');
dropdown.find('> .dropdown-toggle').addClass('active');
} else { // Bootstrap 3
menuAnchor.parent().addClass('active');
dropdown.addClass('active');
}
// Navbar adjustments
var navHeight = $(".navbar").first().height() + 15;
var style = document.createElement('style');
var pt = "padding-top: " + navHeight + "px; ";
var mt = "margin-top: -" + navHeight + "px; ";
var css = "";
// offset scroll position for anchor links (for fixed navbar)
for (var i = 1; i <= 6; i++) {
css += ".section h" + i + "{ " + pt + mt + "}\n";
}
style.innerHTML = "body {" + pt + "padding-bottom: 40px; }\n" + css;
document.head.appendChild(style);
});
</script>
<!-- tabsets -->
<style type="text/css">
.tabset-dropdown > .nav-tabs {
display: inline-table;
max-height: 500px;
min-height: 44px;
overflow-y: auto;
border: 1px solid #ddd;
border-radius: 4px;
}
.tabset-dropdown > .nav-tabs > li.active:before, .tabset-dropdown > .nav-tabs.nav-tabs-open:before {
content: "\e259";
font-family: 'Glyphicons Halflings';
display: inline-block;
padding: 10px;
border-right: 1px solid #ddd;
}
.tabset-dropdown > .nav-tabs.nav-tabs-open > li.active:before {
content: "\e258";
font-family: 'Glyphicons Halflings';
border: none;
}
.tabset-dropdown > .nav-tabs > li.active {
display: block;
}
.tabset-dropdown > .nav-tabs > li > a,
.tabset-dropdown > .nav-tabs > li > a:focus,
.tabset-dropdown > .nav-tabs > li > a:hover {
border: none;
display: inline-block;
border-radius: 4px;
background-color: transparent;
}
.tabset-dropdown > .nav-tabs.nav-tabs-open > li {
display: block;
float: none;
}
.tabset-dropdown > .nav-tabs > li {
display: none;
}
</style>
<!-- code folding -->
</head>
<body>
<div class="container-fluid main-container">
<div class="navbar navbar-default navbar-fixed-top" role="navigation">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-bs-toggle="collapse" data-target="#navbar" data-bs-target="#navbar">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="index.html">SW FL Creek Resources</a>
</div>
<div id="navbar" class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>
<a href="index.html">Home</a>
</li>
<li>
<a href="Additional-Resources.html">Additional Resources</a>
</li>
</ul>
<ul class="nav navbar-nav navbar-right">
</ul>
</div><!--/.nav-collapse -->
</div><!--/.container -->
</div><!--/.navbar -->
<div id="header">
<h1 class="title toc-ignore">Creek Select for tbeptools</h1>
<h4 class="author">M. W. Beck</h4>
<h4 class="date">2025-01-29</h4>
</div>
<pre class="r"><code>knitr::opts_chunk$set(echo = TRUE, warning = F, message = F)
library(sf)
library(RODBC)
library(dplyr)
library(here)</code></pre>
<p>This workflow was developed to update data used to estimate tidal
creek assessment categories using FLDEP IWR run data. In this example,
we use IWR Run 66, but in theory it should work with any IWR run. The
only requirements are:</p>
<ul>
<li>Install the R package dependencies above</li>
<li>Download/extract the <code>.accdb</code> files for a selected run
from here: <a href="https://publicfiles.dep.state.fl.us/dear/iwr/"
class="uri">https://publicfiles.dep.state.fl.us/dear/iwr/</a></li>
<li>Have access to the source tidal creek line file with creek ID (JEI)
assignments created by MW (included in this <a
href="https://tbep-tech.github.io/tidalcreek-stats/">repository</a>).</li>
<li>A WBID polygon shapefile for the appropriate run, which can be
retrieved through web services (<a
href="https://geodata.dep.state.fl.us/datasets/FDEP::waterbody-ids-wbids/about"
class="uri">https://geodata.dep.state.fl.us/datasets/FDEP::waterbody-ids-wbids/about</a>.
The Run66 WBID shapefiles are also available from the folder here: <a
href="http://publicfiles.dep.state.fl.us/dear/kristin/"
class="uri">http://publicfiles.dep.state.fl.us/dear/kristin/</a> (no
need to download if using web services).</li>
</ul>
<p>Below we set a path to the extracted location for the IWR database,
load the tidal creek line file, and load the wbid polygon file.</p>
<pre class="r"><code># set database path
pth <- "~/Desktop/iwr2024_run66_2024-11-15/"
# # to explore
# con <- odbcConnectAccess2007('C:/Users/mbeck/Desktop/iwr2021_run61_2021-05-27/iwr_run61_05272021.accdb')
# sqlTables(con)
# sqlFetch(con, 'station list')
# source creek line file
tdlcrk <- st_read(here('shapes/TidalCreek_ALL_Line.shp'), quiet = T)
# wbid data, run 66 (url should not change between runs)
wbid <- st_read('https://ca.dep.state.fl.us/arcgis/rest/services/OpenData/WBIDS/MapServer/0/query?outFields=*&where=1%3D1&f=geojson', quiet = T) %>%
st_transform(crs = st_crs(tdlcrk))</code></pre>
<p>Now we import the IWR run data for the entire state using an ODBC
connection to the extracted folder. The following code just iterates
through the <code>.accdb</code> files in the extracted folders,
retrieves the data within a ten year window from the current run, and
pulls out parameters of interest. Note that 11 years are subset based on
the current year. This is done to accommodate IWR runs where incomplete
data are present for the current year. This is done in a loop using a
SQL request. It only takes a few minutes to run and does not use a lot
of memory.</p>
<pre class="r"><code># this retrieves most recent ten years of iwr data, including the current year and ten years prior
# filters only parameters in mcode below
# used ODBC connect to access file
# entire state
# .accdb files to query
dbs <- list.files(pth, pattern = 'rawDataDB.*\\.accdb$', full.names = T)
# the current year of interest, used to build the query for ten prior years
curyr <- 2024
# query building tools
mcode <- c("CHLAC","COLOR","COND","DO","DOSAT","NO3O2","ORGN","SALIN","TKN","TN","TP","TSS","TURB") %>%
paste0("masterCode = '", ., "'") %>%
paste0(., collapse = ' or ')
qry_fun <- function(tab, curyr){
out <- paste0("select sta, year, month, day, masterCode, result, rCode from ", tab, " where year <= ", curyr,
" and (", mcode, ")"
)
return(out)
}
# loop through the accdb files
iwr <- NULL
for(db in dbs){
cat(basename(db), '\n')
# setup the connection
con <- odbcConnectAccess2007(db)
# build the query
qry <- basename(db) %>%
gsub('DB|\\.accdb', '', .) %>%
qry_fun(curyr)
# retrieve the data
res <- sqlQuery(con, qry)
# add it to the output
iwr <- rbind(iwr, res)
}</code></pre>
<pre><code>## rawDataDB1.accdb
## rawDataDB2.accdb
## rawDataDB3.accdb
## rawDataDB4.accdb</code></pre>
<pre class="r"><code>head(iwr)</code></pre>
<pre><code>## sta year month day masterCode result rCode
## 1 112WRD 02231400 2021 9 1 COND 201.00000 <NA>
## 2 112WRD 02231400 2021 9 1 DO 5.30000 <NA>
## 3 112WRD 02231400 2021 9 1 ORGN 0.27500 U
## 4 112WRD 02231400 2021 9 1 TN 0.17425 +
## 5 112WRD 02234600 2015 8 24 COND 361.00000 <NA>
## 6 112WRD 02234600 2015 8 24 DO 6.00000 <NA></code></pre>
<p>Now we need to get station lat/lon data for intersection with the
tidal creek line layer. This is for the entire state and is retrieved
from an ODBC connection to the extracted folder.</p>
<pre class="r"><code># from geojson for run66
# https://geodata.dep.state.fl.us/datasets/FDEP::impaired-waters-rule-iwr-stations/about
staraw <- st_read('https://ca.dep.state.fl.us/arcgis/rest/services/OpenData/IMPAIRED_WATERS/MapServer/1/query?outFields=*&where=1%3D1&f=geojson', quiet = T)
stas <- staraw %>%
filter(STATION_ID %in% iwr$sta) %>%
st_transform(crs = st_crs(tdlcrk))
# pull from access, currently doesn't work
# stas <- list.files(pth, pattern = '^iwr\\_run', full.names = T) %>%
# odbcConnectAccess2007 %>%
# sqlFetch('station list') %>%
# filter(STA %in% unique(iwr$sta)) %>%
# st_as_sf(coords = c('LONG', 'LAT'), crs = 4326) %>%
# st_transform(crs = st_crs(tdlcrk))
head(stas)</code></pre>
<pre><code>## Simple feature collection with 6 features and 13 fields
## Geometry type: POINT
## Dimension: XY
## Bounding box: xmin: 406216.7 ymin: 2939554 xmax: 408028.2 ymax: 2941086
## Projected CRS: NAD83 / UTM zone 17N
## OBJECTID STATION_ID STATION_NAME WATERBODY_ID
## 1 1 1113S000120310 CAPE CORAL -1500FT FRM CANAL MOU 3240A1
## 2 2 1113S000120315 CAPE CORAL -1500FT FRM CANAL MOU 3240A
## 3 3 1113S000120320 CAPE CORAL -AT CANAL MOUTH 3240A1
## 4 4 1113S000120325 CAPE CORAL -4000FT FRM CANAL MOU 3240A1
## 5 5 1113S000120330 CAPE CORAL -2500FT FRM CANAL MOU 3240A1
## 6 6 1113S000120335 CAPE CORAL -3000FT FRM CANAL MOU 3240A1
## COMMENTS LATITUDE LONGITUDE RESULT_COUNT BEGIN_DATE END_DATE TMDL_RUN
## 1 submitted by PH 26.5875 -81.9319 150 1975 1975 Run 41
## 2 submitted by PH 26.5847 -81.9236 197 1975 1975 Run 41
## 3 submitted by PH 26.5847 -81.9278 208 1975 1975 Run 41
## 4 submitted by PH 26.5736 -81.9417 95 1975 1975 Run 41
## 5 submitted by PH 26.5764 -81.9417 93 1975 1975 Run 41
## 6 submitted by PH 26.5806 -81.9403 115 1975 1975 Run 41
## WATER_QUALITY_STATION BIOLOGY_STATION geometry
## 1 1 0 POINT (407203.9 2941086)
## 2 1 0 POINT (408028.2 2940770)
## 3 1 0 POINT (407610 2940773)
## 4 1 0 POINT (406216.7 2939554)
## 5 1 0 POINT (406219 2939864)
## 6 1 0 POINT (406361.8 2940328)</code></pre>
<p>The next step is to create a polyline file that is similar to the
source file, but includes an intersection with the WBID layer. It also
includes creek ID (JEI), class, and creek length (total across WBIDs).
It is specific to southwest Florida. This reproduces the
<code>tidalcreeks</code> sf data object in tbeptools for the current
wBIDs and must be saved to the <code>data</code> folder for the
package.</p>
<pre class="r"><code># create tidalcreeks polyline file with wbid, class, jei info -------------
# intersect creek lines with wbids
tidalcreeks <- st_intersection(tdlcrk, wbid) %>%
st_transform(4326) %>%
arrange(CreekID) %>%
mutate(
id = 1:nrow(.)
) %>%
select(id, name = Name, JEI = CreekID, wbid = WBID, class = CLASS, Creek_Length_m = Total_m)
# fix rownames
row.names(tidalcreeks) <- 1:nrow(tidalcreeks)
# save to Desktop for tbeptools data folder
save(tidalcreeks, file = '~/Desktop/tidalcreeks.RData', compress = 'xz')
head(tidalcreeks)</code></pre>
<pre><code>## Simple feature collection with 6 features and 6 fields
## Geometry type: GEOMETRY
## Dimension: XY
## Bounding box: xmin: -82.34154 ymin: 26.89042 xmax: -82.29655 ymax: 26.95527
## Geodetic CRS: WGS 84
## id name JEI wbid class Creek_Length_m
## 1 1 Rock Creek CC01 1983B 2 6443.083
## 2 2 Rock Creek CC01 2052 3M 6443.083
## 3 3 Oyster Creek CC02 1983B 2 10175.167
## 4 4 Oyster Creek CC02 2067 3M 10175.167
## 5 5 Buck Creek CC03 1983B 2 2251.188
## 6 6 Buck Creek CC03 2068A 3M 2251.188
## geometry
## 1 LINESTRING (-82.33869 26.92...
## 2 MULTILINESTRING ((-82.33006...
## 3 MULTILINESTRING ((-82.33188...
## 4 MULTILINESTRING ((-82.3194 ...
## 5 LINESTRING (-82.31512 26.89...
## 6 LINESTRING (-82.30026 26.89...</code></pre>
<p>The final step is to extract the IWR station data at the state level
to tidal creeks in southwest Florida. This is done by buffering the
original creek line file (200m, flat ends), intersecting the buffered
file with the station lat/lon file, and using an inner join by station.
Finally, the WBID class is added. This is reproducing the
<code>iwrraw</code> file for tbeptools and must be saved in the
<code>data</code> folder for the package.</p>
<pre class="r"><code># wbid classes from wbid, to join
classadd <- wbid %>%
select(wbid = WBID, class= CLASS) %>%
st_set_geometry(NULL) %>%
unique
# assigns stations to creek id basedon buffer, wbid already in dataset
# pull station data with the intersect, add class column
iwrraw <- st_buffer(tdlcrk, dist = 200, endCapStyle = 'FLAT') %>%
st_intersection(stas, .) %>%
select(sta = STATION_ID, wbid = WATERBODY_ID, JEI = CreekID) %>%
st_set_geometry(NULL) %>%
inner_join(., iwr, by = 'sta', relationship = 'many-to-many') %>%
mutate(
newComment = rCode
) %>%
left_join(classadd, by = c('wbid'))
# save to Desktop for tbeptools data folder
save(iwrraw, file = '~/Desktop/iwrraw.RData', compress = 'xz')
head(iwrraw)</code></pre>
<pre><code>## sta wbid JEI year month day masterCode result rCode newComment
## 1 21FLA 24010600 2052 CC01 1972 10 3 COLOR 15.0000 <NA> <NA>
## 2 21FLA 24010600 2052 CC01 1972 10 3 DO 4.0000 <NA> <NA>
## 3 21FLA 24010600 2052 CC01 1972 10 3 DOSAT 61.3873 $ $
## 4 21FLA 24010600 2052 CC01 1972 10 3 NO3O2 0.0400 + +
## 5 21FLA 24010600 2052 CC01 1972 10 3 ORGN 0.2800 <NA> <NA>
## 6 21FLA 24010600 2052 CC01 1972 10 3 SALIN 29.0000 <NA> <NA>
## class
## 1 3M
## 2 3M
## 3 3M
## 4 3M
## 5 3M
## 6 3M</code></pre>
<p>Finally, we want to compare the results from the previous year’s run
with the new results to see how the scores have changed. We use
functions from the tbeptools package to estimate creek scores, then
compare in a matrix.</p>
<pre class="r"><code>library(tbeptools)
# get new estimates
tmpnew <- anlz_tdlcrk(tidalcreeks, iwrraw, yr = 2024)
# get old estimates
tmpold <- anlz_tdlcrk(tidalcreeks, iwrraw, yr = 2023)
# compare
tmpnewcmp <- tmpnew %>%
select(wbid, JEI, class, scorenew = score)
tmpoldcmp <- tmpold %>%
select(wbid, JEI, class, scoreold = score)
levs <- c('No Data', 'Monitor', 'Caution', 'Investigate', 'Prioritize')
cmp <- full_join(tmpnewcmp, tmpoldcmp, by = c('wbid', 'JEI', 'class')) |>
mutate(
scorenew = factor(scorenew, levels = levs),
scoreold = factor(scoreold, levels = levs)
)
table(cmp[, c('scorenew', 'scoreold')])</code></pre>
<pre><code>## scoreold
## scorenew No Data Monitor Caution Investigate Prioritize
## No Data 392 2 0 0 0
## Monitor 1 163 0 1 2
## Caution 0 1 16 0 0
## Investigate 0 0 1 18 1
## Prioritize 0 0 0 1 21</code></pre>
</div>
<script>
// add bootstrap table styles to pandoc tables
function bootstrapStylePandocTables() {
$('tr.odd').parent('tbody').parent('table').addClass('table table-condensed');
}
$(document).ready(function () {
bootstrapStylePandocTables();
});
</script>
<!-- tabsets -->
<script>
$(document).ready(function () {
window.buildTabsets("TOC");
});
$(document).ready(function () {
$('.tabset-dropdown > .nav-tabs > li').click(function () {
$(this).parent().toggleClass('nav-tabs-open');
});
});
</script>
<!-- code folding -->
<!-- dynamically load mathjax for compatibility with self-contained -->
<script>
(function () {
var script = document.createElement("script");
script.type = "text/javascript";
script.src = "https://mathjax.rstudio.com/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML";
document.getElementsByTagName("head")[0].appendChild(script);
})();
</script>
</body>
</html>