Using the postgis data base

Introduction

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.

Fields

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

Example 1

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

query<-"select * from crimes 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

Plot a barchart of all outcomes

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()

Plot a barchart of outcomes mentioning an offender or court case

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()

Map the incidents involving an offender or court case

dof %>% mapview(zcol='last_outcome_category', burst=TRUE, legend=FALSE) ->mp
  mp@map %>% addSearchOSM(options = searchOptions(autoCollapse = TRUE, minLength = 2)) %>%
    addFullscreenControl()

Example 2

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

query<-"select * from crimes 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

Time series

dd %>% st_drop_geometry() %>% group_by(date) %>% summarise(n=n()) ->dts
library(xts)
library(dygraphs)
dygraph(xts(dts$n,dts$date))

Plot a barchart of all outcomes

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()

Plot a barchart of outcomes mentioning an offender or court case

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()

Map the incidents involving an offender or court case

dof %>% mapview(zcol='last_outcome_category', burst=TRUE, legend=FALSE) ->mp
  mp@map %>% addSearchOSM(options = searchOptions(autoCollapse = TRUE, minLength = 2)) %>%
    addFullscreenControl()