SQL | London Crime
- mellishamallikage
- Jul 3, 2022
- 5 min read
Updated: Aug 29, 2022
Understanding London's Crime statistics using SQL and python.
Introduction
Criminal analysis or intelligence analysis has garnered significant attention in recent years following key achievements in tackling criminality. In this domain, “analysts study criminal data to identify possible trends, relationships or connections between different crimes in different places, and use the analysis to support law enforcement activities” (Interpol 2014). One breakthrough in this field emerged after the 2011 London riots (The Guardian 2012) where researchers identified patterns in the riots and the origins of people who were arrested. (Fry 2014)
Subsequently, in this project, the London crime statistics will be evaluated using SQL.
Overview
In the Big Query database hosted by Google, there is a dataset consisting of crimes recorded in London from approx. 2008 to 2011. The dataset holds information on the types of crimes that were committed in various boroughs of London. For those unaware, London is divided into approx. 33 boroughs. They are "each governed by a council, who decide policies on services like libraries, waste collection and social services". (Dickinson 2022)
The crimes in this dataset are divided into major and minor categories. Each crime is categorised into one of 8 major and 31 minor types. According to this dataset the most common major crime in London is categorised as “Theft and handling” followed by "Violence against the persons".
Comparing this result to that of the data from Chicago indicates that “Theft” may be an issue not specific to London. Although laws and categorisation between Chicago and London vary, Chicago's most common crime is also "Theft". The Chicago crime dataset is also hosted on Big Query and this data was extracted via SQL.
Theft and Westminster
Exploring the overall figures further highlights that the borough of Westminster recorded the highest crime figures between 2008 and 2016, thus accounting for the high point in the strip plot.
The nature of theft in Westminster appears to be categorised as "Other theft".
Whilst this indicates that Westminster has an issue with Theft, over time, it appears that the rate of theft has undergone some changes. In general, there are around 2500 and 3000 incidents of theft in the borough. However in 2012, the rates of such crimes peaked significantly, recording around 3500 incidents. However, since 2013, the rates have seen a significant drop and by 2015 the rate dropped to approx. 2000 incidents.
Alas, it is unclear what may have caused the change in criminality and could include increased policing, new preventative strategies. Such preventative strategies may include hotspot mapping, where targeted policing is applied based on analysis of crime patterns. (Chainey, Tompson, and Uhlig 2008).
However as the book, Freakonomics highlights, crime can be affected by wider factors such as abortion rates (Dubner and Levitt 2006). Therefore, one may assume that the population and economic changes may also be contributing factors.
Theft in Westminster & the surrounding areas
Typically, such wider factors have a long term impact on crime and the general impact should also be generalised. That said, there was a major change in the demography of London and specifically the tourist heavy hotspot for boroughs such as Westminster in 2012 - The London Olympics (International Olympic Committee 2012). This may explain the 2012 spike. As for the decline, if the previously mentioned study by UCL is applicable, policing increased and therefore the rates of theft may have dropped.
Adding the theft rates of neighbouring boroughs, shows the following:
It indicates that in the months before the peak in Westminster, the crime rates particularly in Camden were rising. This suggest that crime from Camden may have moved to Westminster where it found a sweet spot and thus the 2012 spike in Westminster. Also as crime began to be tackled, rates in Camden followed by Westminster began to fall. However, this is speculation and further research is required to validate this assumption.
Sadly, it appears that the rates of crime in Lambeth and Southwark may be steadily increasing from 2009.
Plotting the fluctuating theft rates for each borough, highlights the following graph. It shows the scale of the issue in Westminster. as the majority of the rates in other boroughs fluctuate around 300 and 1000 incidents. Juxtaposing Westminster is City of London, which has an extremely low rate, in most occasions in single figures.
That said, in total theft remains an issue for the City of London compared to all other forms of major category of crime.
Crime and population density
Whilst the previous graph provides a comparison theft between the boroughs, it fails to account for any differences between them. For instance, every borough does not have the same population or cover a similar area.
That said, as this dataset does not hold population data, the data needs to be merged with a secondary data. In doing so using the data from the London Datastore, the crime rate with regards to the population density can be established. For reference, on average, the borough of Kensington and Chelsea and Islington has the highest population density whilst Havering and Bromley are the most sparsely populated boroughs.
Calculating yearly theft for each borough and then the ratio of the crime against the population, indicates that Bromley and Hillingdon has the highest theft ratio. This suggest that simply because an area is densely populated does not mean that the area will have a high rate of crime, perhaps because such high populations are also policed by more officers.
Conclusion
This was a SQL project examining the crime statistics in London. As it highlights similar to other cities, London has a particular issue with Theft. This is felt most acutely in the borough of Westminster. However, whilst its rate of theft remains high, following a peak in 2012, the rate appears to have fallen. In addition, there seems to be a relationship between the theft levels in Camden and Westminster, as in the months before Westminster's crime rate peaked, Camden's crime rates also hit its all time high.
Finally, each borough covers different area and service different populations. Taking these aspects into account highlights population alone does not explain high rates of crime, particularly for theft.
References
Chainey, Spencer, Lisa Tompson, and Sebastian Uhlig. 2008. “The utility of hotspot mapping for predicting spatial patterns of crime.” Security Journal (pre-print copy). https://discovery.ucl.ac.uk/id/eprint/112873/1/PREPRINT_-_Chainey,_Tompson_&_Uhlig_2008.pdf.
Dickinson, Sophie. 2022. “How many boroughs does London have and what are they?” Metro, May 5, 2022. https://metro.co.uk/2021/04/06/how-many-boroughs-does-london-have-and-what-are-they-14343910/.
Dubner, Stephen J., and Steven D. Levitt. 2006. Freakonomics: A Rogue Economist Explores the Hidden Side of Everything. Edited by Stephen J. Dubner. N.p.: Penguin Books Limited.
Fry, Hannah. 2014. “re:publica 2014 - Hannah Fry: I predict a riot!” YouTube. https://www.youtube.com/watch?v=ROnjZDdt8O8.
The Guardian. 2012. “Reading the London Riots: 'I have no doubt the riots will happen again.'” YouTube. https://www.youtube.com/watch?v=IF_AC0n_zjk&feature=youtu.be.
International Olympic Committee. 2012. “London 2012 Summer Olympics - Athletes, Medals & Results.” Olympics. https://olympics.com/en/olympic-games/london-2012.
Interpol. 2014. “Criminal intelligence analysis.” file:///C:/Users/melli/Downloads/27_CAS01_05_2014_EN_web.pdf.
London Datastore. 2018. “Datasets Land Area and Population Density, Ward and Borough.” London Datastore. https://data.london.gov.uk/dataset/land-area-and-population-density-ward-and-borough.
“The man who has a conscience suffers whilst acknowledging his sin. That is his punishment.” ― Fyodor Dostoyevsky, Crime and Punishment
Author's notes:
Sadly I had some major issues related to using Kaggle and working with Python and SQL. This caused a myriad of issues resulting in the early termination of the project.
Comments