In my blog post “https://rutg3r.com/gps-data-traccar-homeseer/” a calculation was made in script line 139 to calculate the distance between 2 locations. Because this is an aerial measurement, it’s not quite accurate. For defining a geofence it’s fine, but to have a more accurate travel duration and distance (in km) by road, Google’s Distance Matrix API is a better solution.
This API can handle more than 1 original and more than 1 destination address in a single API request, so that’s what I need to have. I actually want to measure the “travel duration” in the current traffic (with delays) for or the following 3 trips:
- From home to work
- From work to home
- Current location of my car to my home address.
Documentation of the API can be found here: https://developers.google.com/maps/documentation/distance-matrix/
Create key for Distance Matrix API
- First step is to login on Google developers console and create a new API project: https://console.developers.google.com/projectcreate
- Enter a catchy name
- After the project is created, assign the API ” Distance Matric API” to the new created project
- Copy the provided API key to the variable in line 13 from the php script from the download at the bottom of the page.
To test your API key, replace the last part of the following url “YOUR_API_KEY” with the one from your project and run the url in a browser window:
The result should be something like this:
Create MySQL table:
A php script is provided too in the “DistanceMatrix.zip download, to create the MySQl table.
Explanation of php script:
- Lines 8-12: Login credentials to MySQL database and table
- Line 13: DistanceMatrix API key
- Lines 17+18. Actual connection to MySQL table for saving the results from the script, so afterwards a nice chart can be created.
- Lines 30-33: fixed coordinates for both home and work addresses are defined.
- Line 34: departure time is calculated. This timestamp should be always in the future.
- Lines 37-38: Presents for calculating the results are defines. Extra options are possible, be aware the final url in the script should be changed for this as well: https://developers.google.com/maps/documentation/distance-matrix/intro
- Line 42: Sample url which contains 1 original address and 1 destination address.
The actual “duration” values are not thát interesting, only if you want to compare them later on in the chart.
- Line 46: Sample url which contains 1 original address and 2 destination addresses.
- Line 52: Final url which contains 3 original addresses and 3 destination addresses:
- home-work
- work-home
- car-home
Based on the variables, the final API url look like this:
$url = ‘https://maps.googleapis.com/maps/api/distancematrix/json?origins=’ . $lat_home . ‘,’ . $lon_home . ‘|’ . $lat_work . ‘,’ . $lon_work . ‘|’ . $lat_car . ‘,’ . $lon_car . ‘&destinations=’ . $lat_work . ‘,’ . $lon_work . ‘|’ . $lat_home . ‘,’ . $lon_home . ‘|’ . $lat_home . ‘,’ . $lon_home . ‘&departure_time=’ . $departure_time . ‘&travelmode=’ . $travelmode . ‘&traffic_model=’ . $traffic_model . ‘&key=’ . $api;
- Line 54: put the results of the url in a variable
- Line 56: Convert the results from JSON to a php array
- As shown in above screenshot that is added for line 42, three parts of results are visible. So for a results of 1 original and 1 destination address, the following information is available:
- Distance
- Duration
- Duration in traffic
- Line 61: To retrieve the “Distance” value from the array, add a variable with the following command: $distance = $result[‘rows’][0][‘elements’][0][‘distance’][‘value’];
- Line 64: Save result in device value of virtual device in Homeseer.
- Line 65: Save result in device string of virtual device in Homeseer.
- Line 68: To retrieve the “Duration” value from the array, add a variable with the following command: $durationN = $result[‘rows’][0][‘elements’][0][‘duration’][‘value’];
- Line 75: To retrieve the “Duration in traffic” value from the array, add a variable with the following command: $durationT = $result[‘rows’][0][‘elements’][0][‘duration_in_traffic’][‘value’];
- Lines 88-141: Because my API url contains 3 routes, for each route, the 3 variables has to be declared.
Be aware of the different row results and elements. For the first route result it’s row 0 and element 0. For the second route result it’s row 1 and element 1, etc, etc.
- When all variables results are done, the command for MySQL can be configured (sorry for the variable naming, but in first place I started with 1 original and 1 destination address):
$sql=”INSERT INTO $tbl_name(distance_work, durationn_work, durationt_work, distance_home, durationn_home, durationt_home, distanceF_ch, durationNF_ch, durationTF_ch, tijd, datum) VALUES(‘$distanceF’,’$durationNF’,’$durationTF’,’$distanceF_h’,’$durationNF_h’,’$durationTF_h’,’$distanceF_c’,’$durationNF_c’,’$durationTF_c’,’$tijd’,’$datum’)”;
Run PHP script
Because I can’t run PHP scripts from Homeseer, I’ve a recurring event configured in the Task Scheduler in Windows, that start the php.exe program with the parameters of the script location:
- Triggers: Will run every 5 minutes
- Action: Start program
- Program/script: C:\xampp\php\php.exe
- Parameters: -f C:\xampp\htdocs\duration.php
Finally when the script will run succesfully, the MySQL table will be updated:
Show MySQL results in chart
Always nice to have is making the dat amore readable, so a nice Highcharts chart is created. On the right side of the chart a selection can be done to make results better readable:
In the download file, the query will retrieve the results for the last 3 days.
To-do improvements:
- Setup fixed routes, which are not based on “best_guess” in the API
Downloads:
vb.NET version 1.5 (June 14th 2018):
DistanceMatrix 1.5.zip (247.7 KiB, 431 hits)
PHP:
- DistanceMatrix.zip (3.1 KiB, 598 hits)
- Highcharts-DistanceMatrix.zip (1.7 KiB, 488 hits)
