Create a Web Map from Addresses in an Excel Spreadsheet

Create a Web Map from Addresses in an Excel Spreadsheet

Welcome to Part 6 of Mango's video tutorial series on sourcing and preparing map ready data. This is the final video in the series, and we've saved the best for last!

The series will help you to source third party geospatial data for your mapping projects, clean and prepare them for upload to an online GIS mapping platform, and visualizing your data on interactive web maps. 

This video focuses on taking a spreadsheet of addresses and converting those addresses to map-ready data in the form of lat/long coordinates. 

If this is your first time viewing this series, we recommend you start with Video 1: Why Maps are Superior to Spreadsheets.

To complete these tutorials, you'll need to download the free desktop GIS software QGIS, and sign up for a free 30-day trial of Mango.

 

Watch other videos in this series:

Exclusive freebie! The Beginners Guide to GIS eBook. 108 pages, completely free.
Grab your copy today.

VIDEO TRANSCRIPT

Hello and welcome back!

In the previous video, we showed you how to upload your cleaned up sales data to Mango and how to add supplemental layers to your map.

We also showed you a few small styling tricks, such as how to customize the pop-up window that's displayed when we click on an individual county, and we also showed you how to add supplemental layers, and supplemental layer groups which can be turned on and off in the legend.

Now the last thing that we need to add to this map is the location of our current dealership.

Now if you remember the original spreadsheet, you'll remember that we don't actually have the coordinates for each of these dealership locations, all we have is the address.

So what I'm going to be showing you in this video is how we can use QGIS to do a process called geocoding in order to get the lat/long coordinates for each one of these individual locations.

Once again we're going to do that using QGIS.

So in QGIS in order to do geocoding, you need to install a plug-in called MMQGIS.

Now to install a plug-in in QGIS you click on plugins and press manage and install plugins.

Now QGIS out of the box when you first install it has all of the basic functionality that you need for GIS, but it also has a rich and very vast plug-in library that allows you to do a lot of additional things.

So once you've opened up the plug-in library, we need to search for MMQGIS, you'll click on that now in my version of QGIS it's already installed, but in yours you'll click an install button down the bottom here, and then it will install the plug-in.

Once you've installed the plug-in, click on the MMQGIS menu at the top click Geocode and then choose Geocode CSV with Google or OpenStreetMap.

Once that's opened it’s going to ask you for the location of your CSV file.

Now remember CSV stands for comma separated values, and you will need to save your spreadsheet in Excel or OpenOffice as a CSV before you can work in QGIS.

So, I'm going to go to my data, and I'm going to select the dealership spreadsheet.

Now what will happen is QGIS will automatically try and identify columns within the data that contain address and location information.

So we can see here that automatically it has decided that the address field is the street address column, the city field is the city column from our data, and the state field would be state, and country field is none.

Okay so that's what we have in our data right now so what we're going to do is we're now going to press the OK button and it will save a new Shapefile dataset, hopefully with all of those addresses geocoded, okay and it's going to save it in the same location as a spreadsheet.

What it's also going to create is a not found CSV. Now what will happen sometimes is you’ll geocode your CSV and some of the addresses won't be located in the geocoding database.

All of these locations will be added to the not found CSV, what you'll need to do in those cases is possibly go and double check the address, make sure it's keyed in correctly, and then try and run the geocode again.

The other option that we have in here is whether to use Google Maps or OpenStreetMaps.

Now Google Maps is far more accurate, especially in locations outside of the US, so it gives extremely good results of the geocoding. What tends to happen, OpenStreetMap will have far more not found addresses.

Now the drawback with the Google Maps geocoding service is it only allows you to perform 2000 geocodes per day, so if you need to geocode a data set that's larger than 2,000 records, you're either going to need to break that spreadsheet up into separate spreadsheets of two thousand each, and you'll have to geocode those spreadsheets on sequential days; or if you open QGIS on a separate computer then you'll also be able to do two thousand on that computer.

Okay so the output Shapefile, we're going to save it in our desktop in data, okay and we're going to call this Shapefile “dealerships”. Let's press save, and let's press run.

Now in the bottom left corner you'll see how many geocodes have been completed already.

The geocodes are complete at around one geocode per second, so when we're geocoding a very small spreadsheet like this with only seven records, it happens very quickly.

If you're downloading larger data sets with thousands of records and then I advise you to go away and make a cup of tea or coffee and then come back in 30 minutes and it should be done.

Now we can see out of here that we have seven of seven addresses geocoded with Google Maps successfully, and will see that it's now added into the map as a Shapefile. So the Shapefile is now called dealership SHP.

So now that we have that data we can now upload it to our map in Mango.

So let's go back to the Mango map administration panel. So here's the map that we've built previously, and now we're going to upload our dealerships to the map.

So once again we click on layers, press add layer. We're going to add a new layer. Now let's add those dealerships, so we're going to add the DBF, the PRJ, the SHP, and the SHX, and press open, and then Mango maps will begin the upload.

Let's just capitalize this layer name, and we'll press the Save button to add it to the map.

Okay, so now those dealerships have been added to the map. As soon as you add a new layer to Mango, it always drops you straight into the layer settings. Okay just for the purpose of this video I'm just going to close that a second, just to show you that the dealerships have been added to the map.

Now what's happened is when you add a new layer or you upload a new dataset, it will always add it to the top layer group. This layer group is off by default, so what we actually want to do is we want to put those dealerships in their own layer group so we can turn them off individually in the legend.

And unlike the supplemental layers of the population and the median household income, we actually want these dealerships to show up by default when the maps first loaded, so we're just going to click the checkbox that says layer group visible on map load.

Okay so there we have it, we have the dealerships. So let's go ahead and let's change the styling of those dealership locations, let's match the styling of our sample map that I showed you at the beginning of the video series. So let's go for a green dot with a gray outline, and let's add some labels so when we zoom in to those locations we can actually see the label information and know which dealership we're looking at.

So to do that, we go into the layers, to dealerships we press the Settings button. Let's change the color to a green; let's change the outline to a dark gray; and let's add some labels. So we turn the labels on it's already selected the name; we want to label the name column. Let's go for white text with a black halo.

Okay and let's also go ahead and configure the pop-up. Let's use a custom pop up like we did on the sales data layer.

We're going to use the name of the dealership for the main column, and what we really want here is we just want to show the address information for this particular location.

So we'll just show street address, and then on the next line will show city, state, and the zip code, and then let's delete all of the information down here don't think we need anything else. Press save, we press Save again.

And now we have those green dealership locations displayed on the map, and when we click on the dealership location, we can see our customized pop-up that it will just have the name of the dealership location and the address.

Okay and we can see here because we clicked on two features - we clicked on the dealership and there's a county below it, we can see multiple features selected. If we press this right arrow here it will show the information for the county that's below.

So there we have it a completed interactive web mapping application that's ready to share with the public or other people inside your organization.

Now there are lots of other things that you could add to this map. You can go inside the toolbox to choose from the large library of tools that are available in Mango; you can change the branding of the interface to add your logo and match the color scheme of your company; you can also create teams that invite other users in to interact with this map and also to contribute.

All of those things are covered in our documentation, and also covered in other video series that are produced by Mango.

So in this course you learn how to find and download data; you learned how to join a spreadsheet to map data using QGIS. In QGIS you learn how to clean up your map data, how to delete columns and how to delete unwanted features; and you also learn how to geocode addresses using QGIS.

Hopefully this video course has given you the foundation of skills that you need in order to take all of your business data and spreadsheet data, and turn them into powerful web mapping applications.




Share this!
Chris Brown is the founder and CEO of Mango (simple online web GIS for everyone).