How to plot AVL History on a Google My Map

Overview

Currently, the AVL map in Easy Rides shows each vehicle’s last known (or current) position but no history of where it’s been. We’re currently working on adding breadcrumb functionality as well as other exciting features to the AVL map, but in the meantime here are instructions for viewing historical AVL data.

Outlined below are the directions to gather the data you’re looking for and display it on a Google Map where you can see all kinds of useful information such as driver, vehicle, route, speed, direction of travel, date and time down to the second and of course location. Coupled with Google’s other features like Satellite view, Terrain or Street View, we can glean a lot of information from this history.

Requirements

• Google account

• Spreadsheet with AVL data that includes lat/lon, and other data you wish to see

• Query to get the data for the spreadsheet

• Connection to the Easy Rides database (usually through a Microsoft Access query that we setup for you)

First time setup

  1. Contact Easy Rides support staff in advance to request the AVL Map Queries needed installed in your Mobilitat or Easy Rides folder, connected to your database and shortcuts made.

  2. Open a web browser and log into your Google account. If you don’t have a Google account, you can create one for free.

  3. Open Google Drive. Drive can be found by clicking the apps menu near your account icon in the upper right corner of the browser when you’re logged into Gmail or other Google account app.
    Google Apps

  4. Create a folder in Drive called “Maps” by right clicking on the My Drive item. Make a New Folder

  5. Create an empty Google Sheets called “Map Data Source” in the Map Data folder. Create a New Sheet

Name the Data Source

  1. Create a new map and name it something like “Map Data” Create a New Map

Title Your Map

To Use

Now that you’ve got it all setup, here are the directions for using it.

  1. Open the MS Access query that the Easy Rides staff have setup for you that will gather the data set you’re looking for. Usually, the shortcut to the query will be on your desktop or in the ERUtility under Reports>Executive Reports.
    Select the Query to Use

  2. Run the query and enter the required information at the prompts. This could include vehicle ID (not the name of the vehicle but the actual ID number), date, start and end hours entered as whole numbers with no minutes or minute separator (“15” to “16” if you are looking for records between 15:00 and 16:00)

  3. Click on the upper left hand corner of the results of the query to select all the records. Select Data in Source

  4. Copy the records into the clipboard (Ctrl+c) or right click then “Copy”.

  5. Go to your Google account and open Drive and go into the Maps folder.

  6. Open the Sheets file that you saved earlier – if you followed the example in this document, the file will be called “Map Data source”.

  7. Click on the upper left corner between the rows and the columns of the Map Data Source. This selects all records in the file (if there are any).
    Select Data in Sheet

  8. Press the Delete button on your keyboard while the data in the sheet is selected to clear out the old data.

  9. Press Ctrl+v (or right click and select “Paste” to paste in the copied data from your MS Access query. Depending on the query size and the speed of your internetet connection, it can take a little while to upload the data. It may not look like it’s working, but just be patient and wait for it a bit. It’s not uncommon to take 30 seconds or more to upload larger data sets. Pasted Data

  10. Open your saved map from Google My Maps.

  11. Delete the existing data import (if any) and click on Import Layer.

  12. Select from Google Drive

  13. Click on Map Data Source then click the Select button. Import Layer Select File

  14. The wizard will ask you to “Choose columns to position your placemarks” – Latitude and Longitude should already be checked - just click the Continue button. Check Lat Lon

  15. You will be asked to “Choose a column to title your markers”. Choose the one that will help you spot what you’re looking for the easiest on the map. For instance, if you are investigating a report of a vehicle that was in an area around a certain time, you might select Time, because then you can just read the time on all the pushpins then click on the pushpin to see more detail. If you already know which vehicle it is, and your data set that you got from the MSAccess query is for a single vehicle, then you don’t need Veh ID as the title of your markers because they will all be the same and that’s not very useful. Choose Columns to Plot

  16. After you click Finish, it will plot the data on the map and you’ll see something like this: Mapped Data

  17. You can customize the color of the pushpins by clicking on Uniform Style and changing it to different colors based on ranges of data such as speed, time, vehicle, etc.. Style Pushpins

You can set different colors, or shades of the same color, change the number of ranges, set labels, etc. You’re encouraged to play with it until you get the most useful looking mapped data.

Format Ranges

You can click on a pushpin to show more data. Depending on which query you’re using for the source data, you might see different information than this example – such as driver name, route name, vehicle name or the source of the position.

Info Label