shooting_environment_report.Rmd

---
title: Weekly Shooting Environmental Report
subtitle: ""
author: <a href=mailto:april.welch@baltimorecity.gov>april.welch@baltimorecity.gov</a>
email: "april.welch@baltimorecity.gov"
date: "`r format(Sys.time(), '%B %d, %Y')`"
output:
  html_notebook:
    code_folding: hide
    fig_height: 5
    fig_width: 8
    toc: yes
    toc_depth: 2
    toc_float: true
editor_options: 
  chunk_output_type: inline
---

<style>
@import url('https://fonts.googleapis.com/css2?family=Roboto:wght@300&display=swap');
</style>

```{css, echo=FALSE}
* {
  font-family: 'Roboto', sans-serif;
}

h1, .h1  {
    margin-top: 70px;
}

div.blue { 
  margin-top: 20px;
  margin-bottom: 20px;
  background-color: #B2DBE4; 
  border-radius: 5px; 
  padding: 3px;
}
```
<img src=`r "https://images.squarespace-cdn.com/content/5b32397bf79392af6b062048/1578532761811-Y6J7YANV433GI2O8YPXV/OPI_Logo.png?content-type=image%2Fpng"` width="200">

```{r setup, include = FALSE, echo = FALSE, message = FALSE, cache = FALSE}
knitr::opts_chunk$set(
  echo = F,
  warning = F,
  message = F,
  include = T,
  cache = F,
  fig.width = 6, 
  fig.height = 4,
  out.width = "100%", 
  out.height = "100%")

knitr::clean_cache(clean = FALSE, path = knitr::opts_chunk$get("cache.path"))
```

```{r load_packages}
library(sf)
library(ggiteam)
library(kableExtra)
library(htmltools)
library(leaflet)
library(geojsonsf)
library(DT)
library(rgdal)
```

Report includes **all open/new service requests as of `r format(Sys.time(), "%B %d, %Y")` at `r format(Sys.time(), "%I:%M %p")`** for the following service request types found in 500 foot buffers around homicides and shootings.

**DPW**

- SW-Cleaning
- SW-HGW
- SW-Dirty Alley
- SW-Dirty Street
- SW-SIU Clean Up
- SW-Mixed Refuse
- SW-Recycling
- SW-Graffiti Removal 

**DHCD**

- HCD-Illegal Dumping
- HCD-SIU
- HCD-Sanitation Property

**DOT**

- TRM-Street Light Out
- TRM-Fence Concern (Repair) 
- TRS-48 Hour Parking/Abandoned Vehicle 
- TRM-StLighting Inadequate/Too Bright


**BGE**

- BGE-StLight(s) Out 

**BCRP**

- RP-Street Lighting Repairs 

**Liquor Board**

- BCLB-Liquor License Complaint 


```{r}
buffer_size <- 500 * 0.3048 # 500 ft in meters
red_orange <- "#f05a28"
```

```{r}
sr_type_list <- c(
  "SW-Cleaning",
  "SW-HGW",
  "SW-Dirty Alley",
  "SW-Dirty Street",
  "SW-SIU Clean Up",
  "SW-Mixed Refuse",
  "SW-Recycling",
  "SW-Graffiti Removal",
  "HCD-Illegal Dumping",
  "HCD-SIU",
  "HCD-Sanitation Property",
  "TRM-Street Light Out",
  "TRM-Fence Concern (Repair)",
  "TRS-48 Hour Parking/Abandoned Vehicle",
  "TRM-StLighting Inadequate/Too Bright",
  "BGE-StLight(s) Out",
  "RP-Street Lighting Repairs",
  "BCLB-Liquor License Complaint" 
)
```


```{r}
url <- "https://opendata.baltimorecity.gov/egis/rest/services/Hosted/Police_Districts/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json"
districts <- readOGR("district_boundaries/Police_Districts.shp",verbose=F)
```

```{r}
recent_shooting_victim <- shootings %>% 
  mutate(
    crimedate = as.Date(crimedate),
    longitude = as.numeric(longitude),
    latitude = as.numeric(latitude),
  ) 
```

```{r}
# Some cleaning
recent_shootings <- recent_shooting_victim %>% 
  # ---- this converts to incident instead of victim
  group_by(crimedate, crimetime, streetaddress) %>%
  filter(row_number() == 1) %>%
  ungroup()

recent_shootings <- recent_shootings %>%
  filter(!is.na(latitude)) %>%
  st_as_sf(coords = c("longitude", "latitude"),
           crs = 4326) 
```


```{r}
sr1 <- sr %>% 
  filter(
    srtype %in% sr_type_list,
    srstatus %in% c("Open", "New")
    ) %>%
  mutate(
    latitude = as.numeric(latitude), 
    longitude = as.numeric(longitude),
    neighborhood = toupper(neighborhood),
    from_bpd_email = ifelse(grepl("@baltimorepolice.org", contactemail), "Yes", "No")
    ) %>%
  rename("srstreetaddress" = streetaddress)

sr1 <- sr1 %>%
  filter(!is.na(latitude)) %>%
  st_as_sf(coords = c("longitude", "latitude"), crs = 4326)
```




```{r}
st_crs(recent_shootings) <- 4326

recent_shootings_m <- recent_shootings %>% st_transform(2248)

recent_shooting_buffers <- st_buffer(recent_shootings_m, 500)
recent_shooting_buffers <- st_transform(recent_shooting_buffers, 4326)
```

```{r}
sr_near_shootings <- st_intersection(sr1, recent_shooting_buffers) 
```

```{r}
# write out all SRs for further tracking and analysis

csv_filename <- paste0("reports/shooting_environment_report_data_", today(), ".csv")
sr_near_shootings %>%
  as.data.frame() %>%
  mutate(
    latitude = unlist(map(sr_near_shootings$geometry,1)),
    longitude = unlist(map(sr_near_shootings$geometry,2))) %>%
  select(-hashedrecord, -geometry, -hashedrecordb4geo, -hashedrecord.1) %>%
  write_csv(csv_filename)

```

The following agencies are responsible for service requests in this report:

```{r}
sr_near_shootings %>%
  as.data.frame() %>%
  count(agency) %>%
  arrange(desc(n)) %>%
  rename("Number of Open SRs in Report" = n) %>%
  datatable()
```


# Shooting Locations

<div class = "blue">
### There have been `r nrow(recent_shooting_victim)` homicide or shooting victims at `r nrow(recent_shootings)` incidents between `r min(recent_shootings$crimedate)` and `r max(recent_shootings$crimedate)`.
</div>

Hover over red points to see homicide/shooting info, hover over blue points for service request info.

```{r}
shooting_labels <- paste0(
  "<b>", recent_shootings$description, "</b><br>",
  recent_shootings$streetaddress, "<br>",
  recent_shootings$crimedate, "<br>",
  recent_shootings$neighborhood
)

sr_labels <- paste0(
  sr_near_shootings$srtype, "<br>",
  "Status: ", sr_near_shootings$srstatus, "<br>",
  "Created on ", sr_near_shootings$createddate, "<br>",
  "SR #: ", sr_near_shootings$servicerequestnum
)

district_labels <- paste0(
  districts$dist_name, " District"
)
```


```{r fig.width = 6, fig.height = 10, out.height="100%", out.width="100%", include = T, results = "asis"}
map <- leaflet() %>%
  setView(lng = -76.6, lat = 39.3, zoom = 11) %>% 
  addProviderTiles(providers$CartoDB.Positron) %>%
    addPolygons(data = districts,
                label = ~lapply(district_labels, HTML),
                fillOpacity = 0,
                opacity = .7,
                color = iteam.colors[3],
                weight = 2) %>%
  addPolygons(data = st_transform(recent_shooting_buffers, 4326),
              color = red_orange,
              weight = 1.5,
              fill = NA) %>%
  addCircles(data = recent_shootings,
             color = red_orange,
             fillColor = red_orange,
             fillOpacity = 1,
             opacity = 1,
             label = ~lapply(shooting_labels, HTML)) %>%
  addCircles(data = sr_near_shootings,
             color = iteam.colors[5],
             fillColor = iteam.colors[5],
             fillOpacity = .8,
             opacity = 0,
             label = ~lapply(sr_labels, HTML))

tagList(map)
```

```{r}
recent_shootings %>% 
  as.data.frame() %>%
  select(
    crimedate, 
    crimetime,
    description,
    streetaddress,
    district
  ) %>%
  arrange(crimedate, crimetime) %>%
  # kable("html") %>%
  # kable_styling(bootstrap_options = "basic")
  datatable(filter = "top", options = list(scrollX = F))
```

# Open Service Request Summary Table


```{r results = "asis"}
sr_near_shootings %>%
  as.data.frame() %>%
  count(district, streetaddress, srtype) %>%
  spread(key = srtype, value = n) %>%
  mutate_all(~replace(., is.na(.), 0)) %>%
  mutate(`Total Open SRs` =  rowSums(.[-1:-4])) %>% 
  arrange(desc(`Total Open SRs`)) %>%
  # kable("html") %>%
  # kable_styling(bootstrap_options = "basic") 
  datatable(filter = "top", options = list(scrollX = F))
```

# Open Service Request Lists by Police District for Each Location {.tabset}

```{r results = "asis"}
district_list <- unique(sr_near_shootings$district)

for (dist in district_list) {
  
  cat("##", dist, '<br>', '\n')
  
  dist_sr_near_shootings <- sr_near_shootings %>% filter(district == dist)
  
  for (loc in unique(dist_sr_near_shootings$streetaddress)) {
    shooting_event <- dist_sr_near_shootings %>%
      filter(streetaddress == loc,
             district == dist) %>%
      as.data.frame() %>%
      filter(row_number() == 1)
    
    sr_table <- dist_sr_near_shootings %>%
      filter(streetaddress == loc) %>%
      as.data.frame() %>%
      select(
        agency,
        srtype,
        createddate,
        chipid,
        from_bpd_email,
        srstatus,
        srstreetaddress,
        servicerequestnum
      ) %>%
      arrange(srtype, createddate) 
    
    cat(paste("<h3>", loc, "</h3>",
              shooting_event$neighborhood.1, " // ",
              shooting_event$description, " // ",
              shooting_event$crimedate
              
    ))
    
    sr_table %>%
      kable("html") %>%
      kable_styling(bootstrap_options = "basic") %>%
      htmltools::HTML() %>% 
      print()
    
  } 
  
  cat('\n', '<br>', '\n\n')
}

```

```{r echo = F, include = F}
sessionInfo()
```