Using the postgis data base

All the files from https://data.police.uk/data/ has been downloaded as CSV files and processed to form a single table in a postgis (spatial) databases. The individual files consist of around 50 csvs for each month of the year from October 2018 to September 2021 (i.e. over 1500 files). The data can be grouped in many different ways and queried much more rapidly than through using the online api.

dbListFields(con,"crimes2")
## [1] "reported_by"           "lsoa_code"             "crime_type"           
## [4] "last_outcome_category" "date"                  "geometry"
dbGetQuery(con,"select distinct crime_type from crimes2") %>% aqm::dt()

Find all the violent crimes that were recorded in April 2021. Filter the results by outcome.

query<-"select * from crimes2 where crime_type like '%Viol%' and date between '2021-04-01' and '2021-05-01'"
dd<-st_read(con, query=query)
dd %>% filter(str_detect(last_outcome_category, "Offender") | str_detect(last_outcome_category, "court") ) -> dof
dd %>% st_drop_geometry() %>%  group_by(last_outcome_category) %>% summarise(n=n()) %>% arrange(n) %>% mutate(last_outcome_category = factor(last_outcome_category, last_outcome_category)) -> df
ggplot(df, aes(x=last_outcome_category,y=n)) +geom_col() + coord_flip()

dof %>% st_drop_geometry() %>%  group_by(last_outcome_category) %>% summarise(n=n()) %>% arrange(n) %>% mutate(last_outcome_category = factor(last_outcome_category, last_outcome_category)) -> df
ggplot(df, aes(x=last_outcome_category,y=n)) +geom_col() + coord_flip()

dof %>% mapview(zcol='last_outcome_category', burst=TRUE, legend=FALSE) ->mp
  mp@map %>% addSearchOSM(options = searchOptions(autoCollapse = TRUE, minLength = 2)) %>%
    addFullscreenControl()
100 km
50 mi
Leaflet | © OpenStreetMap contributors © CARTO

Find all the violent crimes that were recorded by Dorset police. Filter the results by outcome.

query<-"select * from crimes2 where crime_type like '%Viol%' and reported_by like '%Dorset%'"
dd<-st_read(con, query=query)
dd %>% filter(str_detect(last_outcome_category, "Offender") | str_detect(last_outcome_category, "court") ) -> dof
dd %>% st_drop_geometry() %>% group_by(date) %>% summarise(n=n()) ->dts
library(xts)
library(dygraphs)
dygraph(xts(dts$n,dts$date))
1200
1300
1400
1500
1600
1700
1800
1900
2000
2100
Jan 2019
Jul 2019
Jan 2020
Jul 2020
Jan 2021
Jul 2021
Jan 2022
Jul 2022
dd %>% st_drop_geometry() %>%  group_by(last_outcome_category) %>% summarise(n=n()) %>% arrange(n) %>% mutate(last_outcome_category = factor(last_outcome_category, last_outcome_category)) -> df
ggplot(df, aes(x=last_outcome_category,y=n)) +geom_col() + coord_flip()

dof %>% st_drop_geometry() %>%  group_by(last_outcome_category) %>% summarise(n=n()) %>% arrange(n) %>% mutate(last_outcome_category = factor(last_outcome_category, last_outcome_category)) -> df
ggplot(df, aes(x=last_outcome_category,y=n)) +geom_col() + coord_flip()

dof %>% mapview(zcol='last_outcome_category', burst=TRUE, legend=FALSE) ->mp
  mp@map %>% addSearchOSM(options = searchOptions(autoCollapse = TRUE, minLength = 2)) %>%
    addFullscreenControl()
50 km
30 mi
Leaflet | © OpenStreetMap contributors © CARTO