Google mapping by STV.
Link to raw data near bottom.
CityCyclingEdinburgh Forum » General Edinburgh
"Cycle crash map: Five years of serious accidents on Edinburgh's roads"
(29 posts)-
Posted 13 years ago #
-
Nice. Any idea where it comes from? Whole of Scotland would be interesting and fun to play with.
Posted 13 years ago # -
Dunno, but -
"
Ally Tibbitt (@allytibbitt)01/05/2012 16:03
5yrs of cycle crashes mapped in #ediniburgh: bit.ly/JLhfez Mapped by me, as contribution to #pop28 #cyclesafe debate.
"
Posted 13 years ago # -
Thanks. I'll try to get the full dataset. It's probably STATS19 data which should be on the Transport Scotland website. It would be more useful to highlight the factors that make the difference between minor and serious / fatal injuries.
Posted 13 years ago # -
Map looks off to me, given that I know some of the accident points. Is it just the nearest postcode or something?
Posted 13 years ago # -
Original data has grid ref, so perhaps input error at some stage?
Posted 13 years ago # -
The stats 19 form has space for a grid reference but I'd doubt that's recorded at the time. Might be nearest postcode or a postcode associated with the junction or house nearest the spot.
Posted 13 years ago # -
Dave is right - some of the points are definitely off.
My route to work takes me past two fatality spots which often have flowers placed at the site. The nearest corresponding points on the map are literally streets away.
Posted 13 years ago # -
Stats19 data has OS grid references. The table (http://bit.ly/IhdcoO) used for the map uses lat/long. I'd guess the error has crept in during the conversion between formats (wrong version(s) of grid refs and lat/long?).
The grid refs used in Stats19 are initially supplied by the Police and are usually corrected by the local Council. Some mistakes will occasionally get through but if many points are out, then it will probably be when transferring to google maps.
Stats19 uses postcodes for driver/rider and casualty home addresses, rather than accident site.Posted 13 years ago # -
Hi folks.
I got the data by FOI from the council, with the locations identified by postcode. To get it to plot properly in Google Fusion tables I then had to convert the postcodes to Lat/Long, so yep, there's plenty of scope for error there.
Nevertheless, I hope despite the level of accuracy it gives a reasonable impression over all of where the accidents happen.
If anyone knows a more accurate way to geocode incomplete street addresses and/or post codes, into a Google friendly format that doesn't require masssively expensive software I'd love to know how, because although I did a bit of this at Uni, I'm still finding my way with the technicalities.
And on that note, if anyone knows how to batch convert OS Eastings and Northings into Lat /Long in a simple way, I'll buy 'em a pint or three.
Cheers,
@allytibbitt
Posted 13 years ago # -
I'm assuming the OS's conversion spreadsheet:
www DOT ordnancesurvey.co.uk/oswebsite/gps/docs/ ProjectionandTransformationCalculations.xls
only lets you do things one at a time?
Posted 13 years ago # -
Thank's for the link RJ :) Sounds promising. Will have a play when I get a chance.
Posted 13 years ago # -
Nope. Don't know. I can map them for you in Mapinfo if that's any use. There's readily available postcode to E/N lookups and Mapinfo is very happy with eastings and northings (and the maps are prettier).
Did you only get serious / fatal incidents? I started looking at it today but stopped because I realised what I really needed was minor injuries too. I wanted to look at modelling the factors that differentiate serious injuries from other injuries. Is it A roads, the type of junction etc. Basically a logistic regression or CHAID analysis.
I'll email an analyst in Transport Scotland tomorrow and see if it's possible to get all of the data. They should just hand it over.
Posted 13 years ago # -
Converting postcodes to lat/lon and then trying to work out distances between points, in bulk, limited to using only Excel, is the bane of my workday. But it is possible. There's some pretty neat trigonometry wrapped up in an array you can do to repeat things.
I asked one of our SQL programmers if it's possible to do similar between Lat / Lon and Eastings / Northings and his eyes glazed over at the prospect of such a challenge.
Posted 13 years ago # -
Can't you use Eastings and Northings? The distance is just subtraction and Pythagoras.
Posted 13 years ago # -
I might be talking cobblers, but thought issue was that Eastings and Northings require some sort of 3 dimensional bending around the curved surface of the earth (which isn't a perfect sphere) - they're not quite a flat, 2D grid?
Nobody at work must know this as my latitude/longitude distance calculator assumes that it is a sphere... Good enough for short distances but as they increase it becomes more and more inaccurate.
Posted 13 years ago # -
No, you're right but for most purposes Pythagoras is OK in the sense that unless you want a straight line, crow flies distance, any straight line is inaccurate and the curvature of the earth in negligible (I use most of it for local travel calculations, which are never straight line). And then there's spherical trigonometry, which I've succeeded in entirely forgetting.
Posted 13 years ago # -
There are 2 complicating factors, I think, in converting OSGB to lat/long:
First, lat/long is not a rectilinear grid with equal distance between lines; lines of latitude converge closer to the poles.
Secondly, OSGB grid north is neither true nor magnetic north (magnetic north varies with time anyway, so would not be a good basis for mapping), so the whole grid is rotated relative to "true" north, which will be at a slightly different angle to one's grid baseline depending how far east or west one is. As an aside, OSGB grid north is not the same as grid north used by OS Ireland; so grid north in NI points further east than grid north in the rest of GB.
Otherwise, it's (ahem, I imagine) relatively straightforward. I'm fairly sure I have an Excel spreadsheet stashed somewhere at work that will do this conversion, if the OS one won't. Certainly, I have a baffling array of projections and co-ordinate systems to choose from in my work GIS (MapInfo) when importing data.
Posted 13 years ago # -
For km-based calculations in Excel, I found and played with this;
=ACOS(COS(RADIANS(90-$A$1))*COS(RADIANS(90-K1))+SIN(RADIANS(90-$A$1))*SIN(RADIANS(90-K1))*COS(RADIANS($B$1-L1)))*6371
(where A1 and B1 are latitutde and longitude of point 1 and K1 and L1 are same for point 2.)
You can put it in an array to compare lists of points to see which is the closest, how many are within distance z etc. Quite useful.
Posted 13 years ago # -
I think I've used gps.ordnancesurvey.co.uk/convertbatch.asp to convert batches before. It was getting the results into google maps after that which I found too much hassle.
And if you register with http://www.data-archive.ac.uk http://www.esds.ac.uk all the Stats19 accident data for GB for 1998 to 2010 (so far) is available.Posted 13 years ago # -
Have you been in contact with MySociety - the collective of developers who have generated FixMyStreet and FixMyTransport They are still learning but have been facing similar data linking issues.
Worth looking up Mwfanwy Nixon or Tom Steinberg (www.mysociety.org) Thay have a pubmeet regulalry near to Euston/St Pancras, if you are in London.
One flaw with Stats 19 is serious under reporting of all types of injury, although deaths are a bit less likely to be missed. I don't think my hip breaking crash appears for example on Glasgow listings. Some studies of A&E presentations have managed to get a figure on the under reporting.
Posted 13 years ago # -
Chris Oliver promised to be able to get stats from the A&E depts, think that meant the Royal Infirmary of Edinburgh though, to gain a clearer idea of the numbers of cyclists injured. I am not sure how accurate their recording is...i.e. if the cyclist was knocked off, whether the cyclist lost control etc., or what injuries or extent of injuries were caused/sustained. I am sure that it could all be looked into.
Posted 13 years ago # -
@kaputnik
I'm slightly twitching and quivering at the recognition of spherical trigonometry.Posted 13 years ago # -
A useful detail from collating the A&E presentations by cyclists is to check how many actually relate to a Stats 19 record and how many have no report, and thus 'disappear' from official crash statistics.
A double edged sword, as it would possibly show serious under reporting of minor injuries, but also it would highlight clusters of crashes where a hazard needs to be sorted out, and one of my focus points - how many more crashes, pro rata on cycle activity, happen when cycling is on the footway or off the carriageway on a cycle path
Posted 13 years ago # -
Dear all,
The Council has a GIS based recording system with all accidents and can filter for those which involved cyclists. The data includes the day, time, weather conditions, direction of vehicles involved, etc.
I suggest Chris raises putting it up on the Council's website at the next Council's Cycle Forum. There's an analysis of all cyclist accident data going back 20 plus years just been completed - it's still in draft but should be available later in the summer.
Gordon
PS thanks for the messages of support. I'm doing fine and looking forward to life away from elected politics.
Posted 13 years ago # -
Still not had a chance to follow up on all the thoughts above, but will try next week.
Good idea to look for stats from the Royal infirmary too.
Gordon, all that 20 years of data (not just cyclists) could be a good Open Data project for the people in the council leading on that. Will drop a line to them too.
Posted 13 years ago # -
Did anything ever come of this, particularly the report that GordonMackenzie refers to?
Posted 12 years ago # -
Theres a report "Bicycle Incident Analysis" on Streets ahead edinburgh - "It gives an overview of pedal cyclist (P/C) casualties for the period 2004 to 2010" (although this isn't quite what was described earlier) http://www.streetsaheadedinburgh.org.uk/downloads/file/58/cycle_incident_analysis_2004-2010
Posted 12 years ago # -
That pdf is very interesting - it is the one where it says "For all incidents resulting in serious injury to [pedal cyclists], 72% of contributory factors were assigned to the other vehicle driver and 28% to the [cyclist]..." which is the most damning set of stats I've come across it terms of responsibility for accidents - I've seen sets where the proprotions are similar for adult cyclists, but child cyclists skew the overall figures. This is overall as far as I can see.
Posted 12 years ago #
Reply
You must log in to post.
posts
