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

Made with Slides.com