In this tutorial I’m going to show you how you can take a spreadsheet containing addresses and display them as points on a map.
In order to display data on a map we first need the geographic coordinates of each location. The process of turning an address into a coordinate is known as geocoding, we will be doing this geocoding using a popular opensource GIS (geographic information system) program called QGIS.
For this tutorial we’re assuming you have QGIS already installed, if you don’t you can download the installer here. We’re using QGIS 2.0, but it should also work on earlier and newer versions.
If you don’t have any data but would like to follow along, you can use this example spreadsheet.
Installing QGIS and the MMQGIS Plugin
For the purpose of this tutorial you don’t need to be familiar with QGIS but if you would like to learn more then here is a great place to get started.
The first thing we need to do is install the MMQGIS plugin that ads geocoding capabilities to QGIS.
1. Choose “Plugins” from the menu bar and choose “Manage and install plugins…”
2. Choose “Get More” in the left panel, find and select MMQGIS by using search and press “Install Plugin”
Convert Spreadsheet into Comma Separated Values (CSV)
Now we need to prepare our data before we can begin the geocoding process. The geocoder needs the data provided as a CSV (comma separated values) which contains columns called “address”, “city”, “state” and “country”. e.g.
- Address: 29 Main Street
- City: Springfield
- State (or state equivalent outside U.S): NJ
- Country: USA
Most geocoders are quite smart and will be able to workout that NJ in the state column is New Jersey, so don’t worry if your data is in a slightly different format to that shown above.
If all of the address is contained in a single column then you will need to use Excel formulas to separate the address into separate fields as shown above.
You can convert your spreadsheet to a CSV file in Excel or any other spreadsheet program by opening the spreadsheet and pressing “Save As” then choosing “CSV (Comma delimited)” from the “Save as type” option.
Geocoding our Data
Now that we have our data in the correct format it’s time to geocode it in QGIS. In the menu bar goto: MMQGIS -> Geocode -> Geocode CSV with Google / OpenStreetMap.
Here you need to browse to select our CSV file and we will need to tell it which columns are the address, city, state and country.
Important! Make sure that both output paths are valid and you have permission to write to them. On Windows the default output path for the notfound.csv is often in a location that QGIS can’t write to and you will receive an error if you use that value.
We also have a choice of web services, we can choose from Google or OpenStreetMap. Google tends to provide the best results but only allows you to geocode 2500 records per day, whilst OpenStreetMap has no limit.
Pro Tip: If your CSV has more than 2500 records and you wish to use Google you can break the spreadsheet into separate files each with 2500 records and process one each day. Once complete join the output files back together using MMQGIS’s merge layer function.
The results will be stored as a Shapefile which despite it’s name is a collection of a minimum of four files (.shp, .shx, .dbf, .prj). The Shapefile is the de facto standard for storing GIS data and nearly all mapping applications support it.
A second file will be stored that contains a CSV of any records that couldn’t be successfully geocoded. These records usually have an incomplete address, spelling mistakes or are in remote areas not covered by the geocoder.
To begin the geocoding press Okay and go put the kettle on, geocoding can take quite a long time especially for larger datasets.
Once the geocoding is complete the matched results will be displayed on the screen as a series of points which won’t make any sense as there’s no base map with which to reference them.
Exporting Your Geocoded Data
Once you’ve logged into your account just complete the following steps:
- Press “Create Map”.
- Press “Add Layer → Upload Data” Select the four files of the Shapefile we made.
- View your map.
It’s that simple!
Like this tutorial? Take a look at our new video tutorial series on turning spreadsheet data into amazing interactive webmaps
Now you can go on to customize and share your map. If you’re new to Mango you can sign-up for a free account here.