Description

Context GreaterManchesterCrime.sql has 6 columns. The fields/columns are as follows CrimeID – this is a randomly generated text string to uniquely indentify each crime record, CrimeTS – this is the timestamp of the recorded crime or activity Location – this is the text description of the area in Greater Manchester where the crime occured, LSOA – this is text. Lower Layer Super Output Area (LSOA) is a GEOGRAPHIC AREA. Lower Layer Super Output Areas are a geographic hierarchy designed to improve the reporting of small area statistics in England and Wales, Type – this is text. It is the type or class of crime, Outcome – this is text. It is an indication of the outcome of the police investigation. TheGeom – this is the geographical location in WGS 84 (EPSG:4326) Longitude Latitude of where the crime was committed. Content GreaterManchesterCrime.sql is a geolocated listing of ALL crimes detected and investigated by the Greater Manchester Police Force in the United Kingdom, during August 2018. Acknowledgements https://data.police.uk/data/ Inspiration It was my first class assignment and my first step towards spatial database learning. Hence I am sharing this data set and some sample questions for practice. QUESTION 1: Write an SQL Query to find all of the crimes recorded in Greater Manchester in an LSOA containing the name Manchester and which occured in the final hour of August 2018. QUESTION 2: Look at the structure of the crimeid field in the database table. Write an SQL query to extract the following rows from the table where the first 3 blocks of the crimeid contain only alphabetical characters and the outcome contains the text 'no suspect identified'. QUESTION 3: The Old Trafford Football Stadium in Manchester has latitude,longitude (53.463056, -2.291389). Write an SQL query to return all of the rows (crimes) which are recorded less than or equal to 2000 meters of this exact coordinate position. You are encouraged to use UTM ESPG:32630 for the UK in your calculations. QUESTION 4: Manchester Picadilly is the largest railway station in Manchester. Coordinates (Lat, Lon): 53.477013,-2.229452. Unfortunately the area around the station is a particularly frequent location of crime. Write an SQL query which returns all of the rows (crimes) recorded within 2000 meters of this precise location. Only return rows which have the word 'theft' (case insensitive) in the crime type column.