Employee Mapping

Introduction

Knowing where your employees live and how they commute can make all the difference in your Trip Reduction Program. It can help you identify carpool or vanpool matches, local bus route options or even a light rail connection that would get your employees to work without them driving alone. Many programs seek to get employees to figure these options out on their own, using incentives and prize drawings to encourage them to not only make the effort to research their options, but also make the effort to use them.

A proactive approach takes advantage of tools available to the TC, and the fact that employees are more likely to try a new commute with a friendly face they see at work each day. Using a current existing list of employee addresses, the TC and HR Director can map and match employees in a matter of minutes. Connecting employees with each other will reduce the drive alone rate at the company, improve employee morale and retention, reduce highway traffic and pollution, and stabilize incentive spendingfor the company’s trip reduction program.

Requirements

  1. A listing of addresses, to include:
  2. Unique point identifier (name, employee number, random number)
  3. Street Address
  4. City
  5. State
  6. Zip Code
  7. Optional text information (shifts, schedules, etc.)
  8. Internet connection
  9. Microsoft Excel
  10. Google Earth or Google Maps account (both free)

Directions

  1. Create a file folder on your computer called “Maps”
  2. You may choose another name if desired; this folder is where your Google Maps file will be stored.
  3. This directory name is required to type out the full file name. Example: C:\Maps\Employees0107.kml
  4. Open the “Employee Mapping.xls” file
  5. If not automatically enabled, enable the file macros – your computer will warn you if they are inactive.
  6. Click on “Settings” tab in the upper left hand corner of the spreadsheet. (See below)
  7. In “Save KML File As,” type in the full file name using your new directory, including the “.kml”
  8. In “Document Name,” type a name as you want it to appear in your Google Maps account.
  9. Optional: If you’ve installed Google Earth, the third line points to the “Google Earth\googleearth.exe” executable file you’ve placed on your computer. This is not required if you are using Google Maps, and you can leave the line blank. Google Earth users should contact Valley Metro for support if needed.
  10. Inside the Column Details block, most settings are fine unless you make major adjustments to the given Excel spreadsheet. If you elect to make major changes, please contact Valley Metro for support.
  11. The “Name column label” of Column Details is what will appear as the title of the paddle/pushpin on Google Maps. If you are using real names or employee numbers in the Excel sheet, you can leave this blank. For example, if you enter “Employee” here and in the “Name” column of the main spreadsheet you number your entries, the title will appear as “Employee1” and so on.
  12. Under “Map Symbol,” select the color paddle or pushpin you would like this file to generate. If you are overlaying multiple shifts of employees, different colors can be helpful.
  13. “Save Settings” to ensure your changes are recorded.

  1. Open the file containing your employee data.
  2. The “Name” column is for your employee’s names, or ID numbers, or other assignment you choose. It is your only way to track who is who on the map, so please consider this carefully.
  3. From your employee data files, copy and paste information as marked in the top yellow line of the Employee Mapping file.
  4. “Attribute 1” through 4 are items that will appear within the Google Maps bubble as text. Employee shifts, carpool partners, etc. can be included here.
  5. Once complete, select the button marked “(1b) Geocode All Rows” This process could take up to 15 minutes depending on the number of employees and your internet connection.
  6. You will see the fields marked “Latitude, Longitude, Precision” fill with numbers as the system works its way down.
  7. Troubleshoot imprecise data.
  8. P.O. boxes, typos and slang – “FLW” for “Frank Lloyd Wright” – will result in a precision ratingother than “address.” Lines marked address are complete.
  9. Make changes to the address, city or zip data in lines that have a precision of “city” or “zip.”
  10. You MUST delete the latitude and longitude of the imprecise address lines before the system will re-code it. Delete only the B and C cells for that line using your keyboard.
  11. To re-code, select that line by clicking on the Excel number in the left margin. For example, if row 16 had a precision of “zip,”you will fix the address data, delete cells 16B and 16C and click on the “16” to highlight the entire row.
  12. Once the row is highlighted, select the button marked “(1a) GeoCode Selection.” If the precision resets to “address” you have fixed the issue. If not, repeat troubleshooting, skip or delete the row.
  13. Create Google Maps file.
  14. Select option “(2) Generate Map File.”
  15. A successful map generation will save in the directory you specify, and provide you a prompt like you see below:
  1. For this example, I sent a file called newgoogle1.kml to a file folder called Mapping Masters. If not using Google Earth, disregard third line.
  2. If the system shows an error message or will not generate a file, it cannot find the directory you outlined in the “Settings” button. Return to “Settings” and confirm you have created a proper directory with a “.kml” file extension.
  1. Google Earth option only
  2. The option “(3) Show Map” will go to the directory you specified in “Settings” for where your Google Earth program is stored. It will activate the program and allow you to browse your directories for the map you just created.
  3. Google Maps option
  4. Open Google.com
  5. Click on “Maps” tab at the top.
  6. Click on “My Maps.”
  7. Select “Create new map.”
  8. Sign in to Google Maps, or create a new account (which will require you to start at 7b again.)
  9. Select “Import.”
  10. A new screen will open up – click “Browse”
  11. Find the directory where you stored your Google .kml file, and click on it to appear in the import screen. Click “Upload from file”
  12. Your file will upload and paddles/pushpins will appear on the screen.
  13. Important: Click “unlisted” in the options for privacy or sharing settings. This ensures that only your account may access this data.
  14. Click “Done” to save your map and your settings. This map is complete and saved in your Google Maps account. Your map will appear something like the figure below and provide you an overview of where your employees reside. (Note: this is not someone’s actual house – it’s a random business listing used for demo)