Monthly Archives: August 2014

Violent crime at University

Uni Bath Chen Zhao

University of Bath by Chen Zhao, Flickr Creative Commons

Across the UK last week, on August 14th, thousands of students received their A-Level grades and will now making plans for starting university.

Once the euphoria has settled, it is time to face the practicalities. For many, they will be moving away from home to new towns or cities. One important issue is that of security – both on campus and in the new places the students find themselves in.

Violent crime is defined as those against the person (with injury) and covers a wide range of offences. In about half of all cases, fortunately the victim suffers no physical injury.

Using CartoDB, I’ve made a map showing violent crime rates across 29 university cities.

The UK data originally comes from the Home Office and ONS data (England and Wales); Justice Analytical Services (in Scotland); and the Central Survey Unit (Northern Ireland).

If you wish to see the rate of violent crime in say Leeds, simply click on the location (you should know the geography) – and in the info-window you should see the rate of violent crime per 1000 people.

The size of the bubble relates to how dangerous the city is in terms of violent crime.

CBD pic uni city

How dangerous is your university? Graphic by Namal Perera

Nottingham had the highest rate of violent crime in England, and also had high rates of burglary and robbery. Unfortunately, nobody from the university security was available to speak to me. Speaking to former students from Nottingham – they reported being aware of the city having a certain degree of notoriety attached to it, but had not experienced any particular violence against their person.

Mike Porter is the Security Manager at the University of Bath, which has been ranked as the safest university in the UK. There are probably many reasons for this – e.g. overall crime may be lower with a smaller population – but it’s useful to know how they address security issues there.

“At their induction, students usually receive a security lecture from student services” he said, “We [security] are present during Freshers’ Week and can give advice and additional information to students.”

Location is also important. Mr Porter says, “Being on top of a hill is beneficial from a crime prevention point of view.”

For more general information on England and Wales violent crime statistics click here.



How to make a map with CartoDB

World Map Parchment by Guy Sie

World Map Parchment by Guy Sie, Flickr Creative Commons

I must admit that I quite enjoyed the various tutorials on how to make maps. Learning to use different software for tabulating and geo-coding data had its moments, but ultimately it allowed me to develop some basic skills that I’ve applied in other parts of the course.

I found CartoDB was the easiest mapping software to use, and I’ve used it a couple of times for MA assignments. Although probably not as versatile as using Google pivot tables, it is a simple and user-friendly tool. Useful for someone like me.

So here is a stepwise guide to making a map. As always, the first step is sourcing a reliable set of data. From there it’s as easy as…

  1. Collate the data in a Microsoft Excel file
  1. Login to CartoDB. All that is needed is a valid email address, and once registered, there is the option for five free data tables (up to 50 megabytes).
  1. Click on ‘tables created’, then ‘add new table’: this allows direct import of data from a URL, Googledrive or Dropbox. Or you can simply upload your own file from a laptop.

Please NOTE: ensure your Excel file contains fully cleaned data – removing empty cells, deleting unwanted columns etc.

It also helps to have city geocodes in place, ideally in column next to the city. These can be found at

Alternatively, Google finds geocodes automatically through its mapping function, linked to pivot tables.

  1. Go to the dashboard, which shows how many and the names of existing data files. Upload your data file.
  • Believe it or not, you’re almost done. Once all the data is uploaded, there should be a complete table with same layout as the Excel document. The first column is automatically given a cartodb ID number. Again, please note that it is important to have the data in its finalised form before importing from Excel. It is difficult to edit data on CartoDB.

eg CDB chicago parking pay boxes by Steven Vance

Example of CartoDB table – Chicago parking pay boxes by Steven Vance, Flickr Creative Commons 

5. Once you have your complete table uploaded, go through the columns and under the heading choose whether the data is a string, date or number. You can generally ignore the Boolean option.

6. Go to map view – on the right hand side there is a task bar, where you can select wizards to present or highlight the data in different ways.

  • There are a number of different icons.
  • The info-window icon (“bubble”) allows you to choose what information appears in the window when you click on the map location.
  • I particularly liked the cluster wizard (paintbrush icon), which proportions the size of the bubble according to a particular data column. Please see my next post for an example of this.

7. Finally, click on ‘visualise’, give your map / URL a name and publish.

There you have it. Less than 10 steps to make a map. Simply click on the location, to access the relevant data.

If you have problems, the user support is fairly prompt. I tried to geocode regions of the UK once but failed.

Nick Jaremek from CartoDB Support initially thought the geocoding option might not be working because the codes did not have the right format. He later stated, “UK regions are something too specific to be geocoded right now.”

It does have its weaknesses, but for straightforward location mapping with attached numerical data, I found CartoDB to be a valuable and extremely easy open-source mapping tool.

Fatalities on the road

Crushed car RTA by Emilian Robert Vicol

Crushed-car by Emilian Robert Vicol, Flickr Creative Commons 

Ever so often, usually when stuck in traffic, I contemplate just how dangerous are roads in the UK compared to other countries. I suppose you do too. Probably at a similar time, whilst at a standstill on a motorway, when the police and ambulances go shrieking past.

In 2013 the World Health Organisation (WHO) published its Global Status Report on Road Safety, which again highlights that road traffic accidents (RTAs) are the leading cause of death for young people (aged 5-29), killing more people than malaria.

RTA chart

Chart showing fatalities from RTAs across different countries adjusted for population

Each year around 1.25m people are killed in traffic accidents globally. WHO Director-General, Margaret Chan, has previously said, “Road traffic crashes are a public health and development crisis,” adding,

The vast majority of those affected are young people in developing countries.

We are in the UN decade of Action for Road Safety. There is an ongoing drive (excuse the pun) to reduce deaths on the road by 50% by 2020, with experts estimating that five million lives could be saved. Currently, annual deaths are predicted to rise to 1.9m by the end of the decade.

So where are the world’s most dangerous roads? Using to make a tree-map, I’ve highlighted data pertaining to certain key countries at both ends of this fatal scale.

RTA fig

Road fatalities by country – Picture by Namal Perera on


Key points from WHO data:-

  • middle-income countries account for around 80% of RTA deaths but are home to only around 50% of the world’s registered vehicles: they therefore have a disproportionately high burden of deaths.
  • Eritrea is estimated to have the highest number of road deaths (48.4 per 100,000 people). This is taken from 2009 data however.
  • The world’s most populous countries, China and India, have the highest absolute number of recorded road deaths (275,983 and 243,475 respectively) but lie mid-table when adjusting for population.
  • In Africa, Nigeria has the largest population and buys the most cars. South Africa has the highest car ownership per capita. Both are in the top 10 (7th and 8th) when it comes to road fatalities (34 and 32 deaths per 100,000 population).
  • San Marino has the best record according to the WHO, with zero fatalities on its roads (2010 data). However, the tragic deaths of Formula 1 legend Ayrton Senna and Roland Ratzenberger during the 1994 grand prix weekend are more than enough for this tiny enclave to cope with.