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
#4 - Geolocation
By jottaviani
#4 - Geolocation
- 797