Five years ago I wrote about using Google Fusion Tables to create your own maps. Unfortunately, Fusion Tables will be “turned down” (isn’t that a pleasant euphemism for something that’s going to simply stop working?) on December 3 of this year.
But there’s another option
Fortunately, there is still a relatively easy way to create a map of locations for those of us who aren’t skilled Geographic Information System (GIS) users.
Google offers My Maps, which works much like Fusion Tables by letting you import a list of addresses and populating pins on a Google Map for each address. Follow along to see how easy it is to create a map…
Let’s create a map
Start by creating a CSV (comma-separated values) file or an Excel spreadsheet (it must be an .XLSX file) with the data to be imported and mapped.
In a real-world scenario, you might want to map meter reading routes or all the accounts on the cut-off list. For this example, I didn’t want to compromise a customer’s actual data, so I chose data that is readily available on the internet. I bank with Bank of America and know they have a list of banking locations on their website. I harvested the location data from the bank’s website, and using some of the data manipulation tools I wrote about recently, I converted the data into a pipe-delimited text file:
I then opened this file in Excel and added column headings, which are required by My Maps:
Your file can have the full address in one column (as this file does), or it can have separate columns for Address, City, State, and ZIP Code.
In order to show an address error for illustration purposes, I changed the address for Lynnwood Collection to a PO Box.
Now we’re ready to import the file and create your map. Start by going to Google My Maps – https://www.google.com/mymaps.
Click on CREATE A NEW MAP.
Click on Import.
Drag your Excel spreadsheet or CSV file to the window or click on “Select a file from your device” to import your file with addresses to be mapped:
Click the column (or columns) containing the address. This is what My Maps will use to locate the placemarks on the map:
Choose a column to identify the title of the markers on the map. In a real-world scenario, this might be the service address or name of the account. For my illustration, this is the bank branch name.
Now, click Finish and My Maps will create your map.
Once the file is imported and the map created, if you have any bad addresses (for example, PO Boxes) that can’t be mapped, this message will be displayed:
Click on “Open data table” to correct the addresses. A grid similar to this will be displayed:
The addresses with errors will be listed at the top of the data table. You can correct the error right in the data table, without having to fix your original input file and begin the process all over again.
By default, My Maps will draw the map with all the markers having the same color:
However, you might want to see different color pins on the map for different addresses. For example, different colors based on the services provided, or to distinguish between manual and radio read meters on a meter reading route. In my example, I want to be able to visually see the difference between full-service banks and ATM-only locations.
To do this, click on the Uniform style hyperlink:
From the window that pops up, change Uniform style to the field in your data you want to use to determine the color of the marker. In my sample data, this is the Type field:
Google will randomly assign the colors of the map pins, based on the criteria you specified. If you want to change the colors, click on one of them, then click on the paint bucket icon at the bottom of the window and select your desired color.
I chose purple for full-service banking centers, blue for ATM-only locations, and green for Bank of America Advanced Center, whatever that is!
Congratulations, you’ve created your map!
Now, if you want to share your map with others, you can do so by clicking the share button for your map:
Then select the method you want to use to share the map:
Questions about creating your map?
If you have questions about creating your map, please give me a call at 919-673-4050, or email me at firstname.lastname@example.org and I’ll be glad to try to assist you.
© 2019 Gary Sanders
Before we get to the topic of this post, the last Utility Information Pipeline included a poll asking how you handle temporarily inactive accounts. Here is a recap of the responses to the poll (clicking on the chart will open a larger graphic in a new window):
I was pleasantly surprised to see that a majority of the poll responses do charge temporarily inactive accounts, whether it’s continuing to bill them or by assessing a fee.
Google Maps error
I was involved in a sales presentation last week and had an interesting experience when I clicked on the Mapping link within Logics’ Utility Management software. I always set the service address of my demo account to the prospect’s office address so they recognize the map.
The pushpin for the address appeared in the correct place, but the Google Business icon for the utility office was on the house next door! I commented on this and the utility staff laughed and said, yes, customers often drive past their office and have to turn around next door.
I said “Let’s fix that.” and clicked on the “Report a map error” link in the lower right corner of the inset map (this link is called “Send feedback” if you are in native Google Maps). I was able to drag the icon to the correct building on the map and, within minutes, received an email from Google confirming my correction had been made.
Step-by-step instructions to correct a map error
Back in 2012, I wrote about updating your listing in Google Places (now called Google Business). In case your Google Map listing is misplaced, as was the case with Auburn Water System, here are the steps to correct it.
As you can see from the screen shot below, the pushpin was between two buildings rather than directly on the Auburn Water System office:
- Click the “Send feedback” link in the lower right corner.
- Select “Edit the map” from the Send feedback menu.
- Click on the icon for the business you want to correct, in this case Auburn Water System.
- Check “Marker is placed incorrectly on the map” beside Location on the Suggest an edit menu.
- Drag the marker to the proper place on the map.
- Click submit.
- A message will pop up thanking you for improving Google Maps.
- Once your edit has been approved, you will receive an email from Google Maps letting you know it was a success!
The end result is the pushpin is now directly on the Auburn Water System office building! I guess now I should do the same thing for Choctawhatchee Electric Cooperative, who share the same building with Auburn Water System. (I’m unsure why they didn’t show on the “before” map…)
Last chance for the 2017 Utility Fee Survey
This is your last chance to participate in the 2017 Utility Fee Survey. The survey will be closing at the end of the day, June 30, so if you haven’t already done so, please click here to complete the survey. It should take less than five minutes to complete. For an idea of what to expect from the survey, here are the results of the 2015 Utility Fee Survey:
If you have any questions, please feel free to e-mail me at email@example.com or call me at 919-232-2320.
Thank you in advance for your participation in the 2017 Utility Fee Survey.
Reviewing your policies?
If you’re in the process of reviewing or updating your policies, please give me a call at 919-232-2320, or email me at firstname.lastname@example.org for more information about how a business review could help you review your entire office operation.
© 2017 Gary Sanders
A few weeks ago, my wife and I, along with three other couples, attended the Vineyards of Swan Creek Spring Herb Festival. While we were in the YadkinValley, we wanted to visit some other nearby wineries.
In order to see where other wineries in the area were located, I created a Google Fusion Table and displayed the data on an interactive map (clicking on the graphic below will open the interactive map):
The green pushpins represent the five vineyards that participated in the Herb Festival, the yellow pushpin our hotel and the red pushpins other wineries in the area. Clicking on any pushpin will display information about that location.
Why is this relevant?
By now, you’re probably thinking this is nice, but what if I don’t care about visiting wineries?
Perhaps you don’t have a GIS system and you’re interested, as one Logics customer was, in knowing where you have radio read meters installed and where you still have manual read meters?
Would it be helpful to see, on a map, where water main leaks have been reported?
Or maybe you’ve wondered if your meter reading routes are as efficient as they could be. Wouldn’t being able to see them visually on a map be helpful?
Creating a Fusion Table
Creating a Fusion Table is as easy as exporting data from a spreadsheet as a comma-separated values (CSV) file and uploading it to Google. For my winery map example, I used the name of the vineyard, location, phone number and icon style (to display the green, yellow and red pushpins).
One quirk of Fusion Tables is the location must be all in one column – it won’t accept Address, City, State and Zip Code in separate columns as they are stored in most databases. To remedy this, use the concatenate function in Excel.
Let’s assume you have a spreadsheet with Address in column A, City in column B, State in column C and Zip Code in column D. The following formula will merge them into a single field in another column:
=CONCATENATE(A2, ” “, B2, “, “,C2, ” “, D2)
Fusion tables allow for 10 icon (pushpin) styles – two sizes (large and small) and five colors (yellow, green, blue, purple and red). The icon style must be defined in your data or they can be dynamically assigned by ranges of numeric values. Unfortunately, they can’t be dynamically assigned by non-numeric data.
Viewing the data
Once the data has been imported into a Fusion Table, it can be viewed in one of three formats – rows, cards or on a map. The row view is displayed in a grid, much like a spreadsheet. The cards view reminds me of a rolodex card and the map view is just what you would expect.
Give it a try!
Go ahead – create your own Fusion Table and experiment with it. You will have to have a Google Account (you already have one if you have a Gmail e-mail address).
Once you have a Google Account, just follow this tutorial to start creating your first map. It’s as easy as that!
If you’ve got questions about using Fusion Tables, feel free to give me a call at 919-232-2320 or e-mail me at email@example.com.
© 2014 Gary Sanders
Smartphones with GPS capability and online mapping applications have made finding addresses an easy task. Does your utility take advantage of any of the many electronic mapping applications that are available?
Geographic information systems
Many utilities have implemented Geographic Information Systems (GIS) to replace their reliance on paper maps. In addition to creating digital images of paper maps, GIS maps include attributes of each mapping layer.
These attributes can include such things as water line size, pipe material and year installed. Or for a valve, the attributes might include manufacturer, model, year purchased and direction the valve opens.
Online mapping applications
Even if your utility hasn’t implemented a Geographic Information System, several online options exist for locating an address.
Google Maps, Mapquest and Bing Maps all will display a map of an address, using nothing more than the address itself. They also will provide turn-by-turn driving directions to the address.
Some utility billing systems have integrated mapping within their billing software. Logics’ Utility Management system is one such application.
Below is a screen shot of the Mapping tab from the Account Console of Logics’ Utility Management system. Clicking on the image will open a larger image in a new window.
As you can see, the system displays a map with a push pin at the exact service address.
Meters are often the last layer added to a GIS system, and they represent the point at which billing software and GIS systems intersect. For a utility with a GIS system mapped all the way to the meter level, the above map could be replaced by their GIS map.
Interested in integrating maps with your billing system?
If you are interested in learning more about integrating maps, either Google maps or your GIS system, with your billing system, please give me a call at 919-232-2320 or e-mail me at firstname.lastname@example.org.
© 2013 Gary Sanders