Vehicle GPS Locator sensors ID

November 10 [Thu], 2016, 17:25
Core database data model structure. Devices are associated with cars for defined time ranges in the table ‘gps_sensors_cars’, which depends on the tables ‘cars’ and ‘gps_sensors’ (dependencies are represented by dashed arrows). Once GPS position data are uploaded into the database in the ‘gps_data’ table, they are assigned to a specific car (i.e. the car wearing the sensor at the acquisition time of the GPS position) using the information from the table ‘gps_sensors_cars’. Thus, a new table (‘gps_cars’) is filled (solid arrow) with the identifier of the individual, its position, the acquisition time and the identifier of the GPS Vehicle Tracking Device . The names of the fields that uniquely identify a record are marked in bold.

The GPS Mini Tracker positions acquired in near real-time are associated with the car, as the positions recorded between the death and its detection by researchers are not valid and must be ‘disassociated’ from the car. This approach also efficiently manages the redeployment of a GPS sensor recovered from an car (because of, e.g. the end of battery ) to another car, and the deployment of a new GPS sensor on an car previously monitored with another GPS sensor.

A possible approach to store the records that are associated with cars is to create a new table, which could be called gps_car, where a list of derived fields can be eventually added to the basic cars ID, Vehicle GPS Locator sensors ID, acquisition time and coordinates.illustrates a general picture of this database data model structure. This new table duplicates part of the information stored in the original gps_data table, and the two tables must be kept synchronised. On the other hand, there are many advantages of this structure over the alternative approach with a single table (gps_data) where all the original data from GPS sensors (including GPS positions not associated with cars) are stored together with other information (e.g. the car ID, environmental attributes). The key point is to properly store the information on the deployment of sensors on cars in a dedicated table in the database, taking into consideration that each car can be monitored with multiple GPS sensors (most likely at different times) and each sensor can be reused on multiple cars (no more than one at a time). This corresponds to a many-to-many relationship between cars and GPS sensors, where the main attribute is the time range . Making reference to the case of GPS (but with a general validity), this information can be stored in a gps_sensors_cars table where the ID of the sensor, the ID of the car and the start and end timestamps of deployment are stored.

More information at