Geolocation

How to geo-reference your dataset

@JacopoOttaviani

Geocoding is the process of converting addresses (like "Puerta del Sol, Madrid") into geographic coordinates (like latitude 40.416980 and longitude -3.704429), which you can use to place markers on a map.

Scenarios

• Location columns are lat/lon

• Location column is a city

• Location column is a postal code

• Location column is a province, region or country

• Location column is an address

Scenarios, i

• Location columns are lat/lon (free on CartoDB)

• Location column is a city (free on CartoDB)

• Location column is a postal code (free on CartoDB)

• Location column is a province, region or country

• Location column is an address

Scenarios, ii

• Location columns are lat/lon (free on CartoDB)

• Location column is a city (free on CartoDB)

• Location column is a postal code (free on CartoDB)

• Location column is a province, region or country (choropleth: shapefile merging required)

• Location column is an address

Scenarios, iii

• Location columns are lat/lon (free on CartoDB)

• Location column is a city (free on CartoDB)

• Location column is a postal code (free on CartoDB)

• Location column is a province, region or country (choropleth: shapefile merging required)

• Location column is an address (100 for free on CartoDB. Otherwise: workaround required). 

When for free:

More info: http://docs.cartodb.com/tutorials/how_to_georeference.html

When it is not for free, or it doesn't work... 

1st option (easy):

• Use Google Maps API in Google Spreadsheet with a macro script

 

2nd option (nerdy): 

• Use OpenRefine to geocode your data with Google and OpenStreetMap API
• Open the dataset you want to georeference on OpenRefine
• Fetch the geographic information from a geographic API
• Fill it in the dataset and re-import in CartoDB

https://github.com/nuket/google-sheets-geocoding-macro

• Open the Google Spreadsheet you want to geolocate

• Tools -> Script Editor

• Copy and paste the code into the editor 

• Save and reload your sheet 

• a new “Geocode” menu should appear after the reload

What's an API?

Application Programming Interface

 

It's just a different way to query a web application, such as Google Maps.

 

How? With a URL:

 

http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=the_address_im_looking_for

 

for example:

http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=puerta del sol madrid

JSON result returned by the API

(It's like a blob of data with geographic info)

Let's be a little bit more nerdish with OpenRefine

www.openrefine.org

OpenRefine

Open the dataset: 90_non_geolocated_libya_body_count

Coordinates extraction

Once queried the Google Maps API, we can extract the coordinates from the API blobs

Finally you have your coordinates

• Check empty/error rows
• Remove the columns you don't need
• Export the dataset in CSV or XLS format
• Open your new dataset on CartoDB
• Map it!

When the API doesn't find the coordinates, go manual

Geolocation via OpenRefine

Step-by-step guide:

http://bit.ly/openrefine-geolocation

 

Questions?

Exercise #4. Geolocation with OpenRefine

• Download dataset: 90_libya_body_count from http://bit.ly/cartography-madrid

• Georeference it with OpenRefine

  • Use Google Maps Geolocation API
  • Extract coordinates from blobs
  • Remove blobs

• Export the new dataset

• Import it on CartoDB and check the geometry