May 13, 2018|In API, Data, GPS, Hardware, MySQL, Scripts, Software|22 Minutes

GPS data from Traccar in Homeseer

Rutg3rBy Rutg3r

org_traccar_manager_w250In earlier posts I’ve written about different GPS trackers, and even a small preview was shown about retrieving the Traccar data into Homeseer3. In a nutshell: After a few Xexun GPS trackers and even some clones, I’ve running for almost 2,5 years the Meitrack MVT340 GPS tracker with success running in my car. A pre-paid sim-only card is inserted and it uses in my case appr 3-4 MB a month (20.000 km a year). Advantage of this MeiTrack GPS tracker is the setup for 2 time intervals. One for “ignition=ON” and the other when the “ignition=OFF”. Both intervals are configured, so during driving the gps tracker will send more frequent an update, then when the car is not used.

Cloud software was not good enough for me, or the API was not good enough to retrieve realtime data of gave data then I want to have. Mobile phone apps drained the battery too much. Therefor the Traccar GPS tracking software is now in use for several years.  A lot of gps trackers are supported and documentation and support is very good. I don’t use the webinterface at all, or use the options from the webinterface, like Geofence, overspeed alarms, etc. I use only the server side for retrieving the gps data into a MySQL database. In the at the bottom of the page, I’m using my own geofences and a lot more.

 

Traccar configuration

In the Traccar installation folder: C:\Program Files (x86)\Traccar\conf the traccar.xml file is changed for using my own MySQL database:

Old entry:

<entry key=’database.driver’>org.h2.Driver</entry>
<entry key=’database.url’>jdbc:h2:C:\Program Files (x86)\Traccar\data\database</entry>
<entry key=’database.user’>sa</entry>
<entry key=’database.password’></entry>

New entry (login credentials are removed)

<entry key=’database.driver’>com.mysql.jdbc.Driver</entry>
<entry key=’database.url’>jdbc:mysql://localhost:3306/traccar?allowMultiQueries=true&amp;autoReconnect=true&amp;useUnicode=yes&amp;characterEncoding=UTF-8&amp;sessionVariables=sql_mode=ANSI_QUOTES</entry>
<entry key=’database.user’>******</entry>
<entry key=’database.password’>******</entry>

In the section “Protocol Config”, I’ve disabled all ports for other GPS trackers (changed to comments with tags). It’s not neccessary to keep those ports open on the server, while they are not used.

<!–<entry key=’tr20.port’>5018</entry>
<entry key=’navis.port’>5019</entry>–>
<entry key=’meitrack.port’>5020</entry>
<!–<entry key=’skypatrol.port’>5021</entry>
<entry key=’gt02.port’>5022</entry>–>

Further settings are:

<entry key=’geocoder.enable’>true</entry>
<entry key=’geocoder.type’>google</entry>

<entry key=’web.port’>****</entry>

The web port is changed too. The Traccar software is running on my home automation server with several other web interfaces, so for this I’ve changed to port number to a free one.

 

MySQL database

For basic setup, installation and configuration see Traccar website.

In the end, Traccar will create all the MySQL tables like this:

traccar-mysql

For me the table “postitions” contains the most important data. Here in every row, the GPS data will be stored, for every interval:

traccar-positions317Traccar config file

Because in the traccar.xml config file, the option for Geocode is enabled, the address is visible too, based on latitude and longitude of the current location.

In the last column “attributes” additional gps device is visible (could be different per gps device), examples are:

  • Event (based on Meitrack, for example if car is moving, or ignition status is changed)
  • How many satellites are connected
  • How good the GPS reception is
  • Odometer (total distance done with that gps tracker)
  • Battery power level
  • Car battery level (not in volts)
  • IP address of GPS trackers
  • etc

 

****** IMPORTANT UPDATE 13-05-2018 ******

vb.NET scripting

Compared with the previous explanation, the vb.net script is now changed the a full automated process. In the past, all the virtual devices should be created manually, which was not a nice thing to do. Even if you want to have some nice icons, it should be addedd manually to the virtual devices as well.

In every script files a “user change” section is present, which has to be changed

‘ ********** START USER CHANGE **********

‘ ********** END USER CHANGE **********

==> Changes that has to be done for “Traccar.vb”

Debug true / false
UseMetrics

 

 true = Odometer + distances in Kilometers

false = Odometer + distances in Miles
UseGoogleStreetview true / false (API key necessary)
DBserver IP address or hostname where MySQL server is running
DBuser Database user for access “traccar”database
DBpassword Database password for access “traccar”database
DBtraccar Database name to access “traccar” database
lat_home Latitude coordinates of your home location
lon_home Longitude coordinates of your home location
lat_work Latitude coordinates of your work location
lon_work Longitude coordinates of your work location
GeofenceHome Geofence distance when “Presence Home” device would be triggered
GeofenceWork Geofence distance when “Presence Work” device would be triggered

 

==> Changes that has to be done for “Traccar_DailyDistance.vb”

Debug true / false
UseMySQLDB

 

 true=Create database, Update device values + Write to MySQL. The table will be updated once a day, with the driven daily distance.

false=update HS device values only
DBserver IP address or hostname where MySQL server is running
DBuser Database user for access “DailyDistance” database
DBpassword Database password for access “DailyDistance” database
DBdatabase Database name to access “DailyDistance” database
DBtable Table name to access “DailyDistance” database

 

==> Copy the “Traccar” folder from the download to the \Homeseer 3\html\images\ folder

 

==> In the new script setup, the following will happen:

  1. Create an recurring event with name “Traccar” (The name van be anything, a name change is up to you), which will run every 20 seconds (frequency change is up to you, 20 secs is oke for me) and that will run the script “MySQL_Traccar.vb”.
    • Name: Traccar
    • Will be automatically triggered
    • Every 20 seconds (change of frequency is up to you)
    • Run the script “MySQL_Traccar.vb”.
    • traccar-new1
  2. The “Traccar.vb” script will do the following:
    • It will get the column names and most recent row values from Traccar table ‘positions’ and store both in seperate arrays. The last column “Attributes” will be skipped and used in a separate step.
    • It will get the last column “Attributes” and add all the Keys and Values of the KVPair to seperate arrays. (Key= object name. Value= object value)
    • It will add additional virtual devices to the arrays, to provide seperate data, like:
      • Google Maps
      • Google Streetview (API key necessary)
      • Aerial distance car-home
      • Aerial distance car-work
      • Presence Home
      • Presence Work
    • It will get data from all 3 arays and create virtual devices of it. With the second run and later, only the virtual devices will be updated.
    • It will create a recurring event to store and calculate the daily driven distance. It will create 2 additional virtual devices as well:
      • Odometer daily (which will be updated every time the “Traccar.vb” script is running.
      • Odometer night (the “Daily Distance” event will run once a day, at 23:59:05 hrs (11:59:05 PM), to store the gps odometer value. Based on this value, the daily driven distance can be calculated during the day.
  3. Run the event.

At the Homeseer device status homepage, you will see that all devices are created, including the additonal ones (Google Maps, etc) and at the bottom (of sorted on column “Address” 2 extra from the script “Traccar_DailyDistance” as well.

Screenshots updated on January 17, 2021 (release 4.11.0.1):

 

 

Traccar_HS4_devicesDailyDistance table layout, based on all available gps trackers including API counters when Streetview is used:

traccar205-DD-table

To use the two script files, two dll files has to be placed in the \bin folder of Homeseer3 (both files are provided in the download):

  • MySql.Data.dll (=version 6.9.5.0) 
  • Newtonsoft.Json.dll (=version 7.0.1.18622)

The file “settings.ini” in folder \Config, has to be supplemented with at least (Stop Homeseer, make ini file change, Start Homeseer):

ScriptingReferences=Newtonsoft.Json;bin\Newtonsoft.Json.dll,System,MySql.Data.MySqlClient;bin\MySql.Data.dll

Release notes:

March 22, 2022 v 4.2.13.0-1

Tested with Homeseer 4 version 4.2.13.0 on Windows 10 x64

Script: Traccar.vb:

  • Optimized for Homeseer 4

Script: Traccar_DailyDistance.vb:

  • Optimized for Homeseer 4
January 17, 2021 v4.11.0.1

Tested with Traccar 4.11 and Homeseer 4.1.10.0 on Windows 8.1/10 x64

Script: Traccar.vb:

  • Optimized for Homeseer 4

Script: Traccar_DailyDistance.vb:

  • Fixed device parent child relationships
15-11-2019 v4.6.01

Tested with Traccar 4.11 and Homeseer 3.0.0.548

Script: Traccar.vb:

  • When a device “Battery” is available in Traccar, two extra sub devices are created:
    • “Battery Monitor”, contains a dropdown list to set a voltage level.
    • “Battery Alert”, contains the default On/Off buttons.

When the “Battery” value is getting lower than the value which is set in the “Battery Monitor” virtual device. the “Battery Alert” device gets triggered and the status will switch to On. ofcourse such a trigger can be done in a Homeseer Event as well.
27-01-2019 v4.2.01

Works only with Traccar installer 4.2. Tested with Homeseer 3.0.0.478

Works only when Traccar software version 4.2 is installed. Downlink link: https://www.traccar.org/download/

From now on the my script versions will we follow the Traccar GPS software releases.

Script: Traccar.vb:

  • Query update for getting gps_ids. When a new tracker is added to the Traccar webinterface and don’t have uploaded data yet, the Homeseer script was having an error. Now the query will check on “lastupdate” as well. In case “lastupdate” is empty, the tracker id will be skipped.
  • Some trackers, like the Teltonika FMB920 with firmware v03.18.16, are providing not all GPS objects in all drivingmodes. Depending on motion, a different amount of objects is available in the “attributes”column in the Positions table. When you let the script creating the devices when the less objects are available, the update value array will get ‘confused’ when there are more objects present with the next run. Now for every virtual device update, a check will be done if the array key is already present as virtual device. If not, the virtual device will be created dynamically, so a device update can be accomplished. In other words: when new objects are populated by the tracker, the new virtual device(s) will be created automatically with the next script run (when the object is providing data).
  • At the end of the first script run, the “Traccar_DailyDistance” script will be run as well, to create the necessary devices directly (and table will be created).

Script: Traccar_DailyDistance.vb:

  • The script is changed to save the Root counter value back to the Root device, in case Traccar.vb have to create new virtual devices.

 
10-06-2018 v2.08

Works only with Traccar installer 3.17

Works only when Traccar software version 3.17 is installed. Downlink link: https://www.traccar.org/download/

Script: Traccar.vb:

  • Sometimes, the Traccar server can’t provide a reversed geocode address, even when the location of the gps tracker is not changed. In case there is no value in the database (=NULL), in the old case, the empty string was written to HomeSeer. From now on the script will do a check on the result of the Address column. When empty, the current value from the Homeseer Device will remain used. If the DB result is present, a Homeseer device update will be done.
  • Saving of Streetview images will now be done in a separate folder instead of in the root of “\html”. The script will create the folder automatically when not exists.
  • Added virtual device with tracker name
  • Added virtual device with last database entry (in seconds). This value is not used for any calculations yet, but could be used in the future for changing the Moving device value. Example: When in the Android app the Server Status is switched off AND speed > 0, the Moving device will be kept on TRUE (because speed > 0)…and therefor unnecessary Streetview API calls are used).
  • Because the Traccar database is showing the Totaldistance standard in knots, the “Usemetrics” calculation is added to this object.
06-06-2018 v2.05

Script: Traccar.vb:

  • Generating Streetview images is fixed. If “UseGoogleStreetview” is set to true, the Streetview API call will be run with the first script run as well.
  • Homeseer installation path, ip address and port are now automatically retrieved from the system for saving Streetview images (reduced the user specific data)
  • Streetview device and API call counter device will now only be created when “UseGoogleStreetview” is true.
  • DailyDistance event will be run once after the Traccar script is finished.
  • When object “Odometer” is not available, the “Totaldistance” object will be taken.
  • Added additional device “Moving”. When “Motion” is not populated by the gps tracker, it wil track other devices and set “Moving” to true.

Script: Traccar_DailyDistance.vb:

  • MySQL table “DailyDistance” wil now be created dynamically (based on all GPS tracker names and values).
  • DailyDistance event will be run once after the Traccar script is finished. This will add the DailyDistance virtual devices directly. If “UseMySQLDB” is set to true in DailyDistance script, the MySQL table will be created as well.
  • With first script run, the current Odometer/Totaldistance value will be stored in the “Odometer Night” device, which means that the next Traccar.vb script run, can start calculating the “Odometer Daily” right away.
  • Store API counters per gps device to DailyDistance table as well (these counters will get a reset as well at script run (end of the day).
29-05-2018 v2.02

Script: Traccar.vb:

  • Fixed: API call requests where only done when the gps device was not moving..
27-05-2018 v2.01

Tested on Traccar release 3.17:

Script: Traccar.vb:

  • Major change from static gps device id to dynamic gps id’s that are available in Traccar. Based on the available objects for each gps tracker, the Homeseer virtual devices are created dynamically (all will be handled in a loop).
  • Device prefix based on gps tracker name.

Script: Traccar_DailyDistance.vb:

  • Based on all gps ids, the additional devices are created.
21-05-2018 v1.83

Script: Traccar.vb:

  • Virtual device ranges are now created with negative value ranges (important for handling negative coordinates)
19-05-2018 v1.82

Script: Traccar.vb:

  • Virtual device name “Traccar_Moving” added. Based on value of Attributes-Status, this device will be updated with value 0 or 100. Can be used for easy showing which vehicle is on the move.
  • Virtual devices name “Traccar_Streetview API” added. This will be a counter for the amount of API requests per day.
  • Due the limitation for requesting free Google API calls per June 11 2018, the Streetview API calls are now only requested when the car is moving (Attributes-Status object > 0).
  • Because of above point, the API call will be reqested till 120 seconds after the car is stopped. This will give the script enough time to generate a Streetview url for the current location of the vehicle. Before this change, (depending on the time interval of the script), the last API call could be requested with the location of the vehicle at that moment (when the car was still moving).

Script: Traccar_DailyDistance.vb:

  • Create table extended with extra column “api calls”. At script run (end of the day), the amount of used Streetview API calls will be saved to this table as well. In the end you have a clear overview about the API usage per day. At the end of the script, the virtual device value “Traccar_Streetview API” will be reset to zero.

Chart: Highcharts:

  • Besides the daily distance, now also the daily usage for Streetview API calls visible in the same chart.
13-05-2018 v1.71
  • Kilometers/miles switch.
  • Decimal comma/point removed. Script is working fine with decimal point only.

Downloads:

 

altitude api attributes column database distance ExecuteNonQuery Google gps key mysql MySqlConnection MySqlDataReader newtonsoft odometer reception row satellite speed traccar xml
Rutg3r
All author posts
Related Posts
9 Comments

Add comment

© 2024 RUTG3R.COM. All rights reserved

Privacy Preference Center

Privacy Preferences