Extracting data from Google Maps

What would like to do today is talk you through how I think I have solved a problem of obtaining some of my data about the characteristics of place. It is with help of Google and it starts with a Google search. In this case I want the co-ordinates of all public houses or bars in the London Borough of Camden so that I can plot them along with other datasets. I reason that alcohol is a major cause of disorder and people being victims of crime. Also location of pubs and bars tend to indicate entertainment venues where people congregate. It would be nice to have every single pub and bar but for my purpose clusters are really what I am looking for. Of equal importance is where pubs and bars are not. A Google search using the "map" option for "Camden public houses" generates the map below.

Each of the red dots purport to be a public house. There are two problems to be overcome. Firstly, it seems that anyone can add a marker to this map- so accuracy is an issue, though most appear to have come from review sites or business advertising listings such as Thompson's local. Secondly the source code behind this map does not allow access to the co-ordinates of the red-dots.

There is a solution to both problems. Google allows users to create their own maps by clicking on "my maps". The map display remains the same but two important options become available. Firstly when you double click on a red dot or any other feature a dialog box appears as normal with information and usually a small picture, but now you have an option of pinning your own marker and retaining the name of the red dot. By going through this process and checking, where necessary, with Google Streetview a list of your own locations can be created. Secondly the option of "View in Google Earth" appears. This allows a .kml file to be created from the personalised layer you have made. This can be opened in Google Earth to display the markers there as shown below. This picture also shows another layer of the clipped Camden Borough grid that I created and converted to .kml file in ArcGIS

The clever thing is that within the "my maps" kml file there are the names and co-ordinates of the public houses I have marked. The way in which I extract this information is simple. I create a .xml file by renaming the .kml file (just replace k with x). This allows the file to be opened by MS Excel. A little bit of formatting and deleting allows a .csv file to be saved that can be loaded into ArcGIS to create the maps below after a spatial join.

Locations of Public Houses and Bars in the London Borough of Camden shown within Grid Cells

The Location of Public Houses and Bars in the London Borough of Camden shown with Lower Super Output Areas

I have deliberately left the dots of pubs and bars just outside Camden showing. Firstly to remind me that the projection of the grid and the dots are out by about 30 metres due to the use of different co-ordinate systems - I need to learn how to solve this. And as the borders of boroughs often follow historic main roads where pubs are located this is important. It also reminds me that the potential influence of a pub on crime and disorder is greater spatially than its actual location.

I have demonstrated the process with one set of relevant data. I now have access to the Google Data warehouse for my domains.