Traccar insertPosition SQL distance algorithm

store_device_position.sql

-- --------------------------------------------------------------------------------
-- Routine store_device_position
-- Note: This need to be saved as a stored procedure in your mysql database 
-- you can check this link how to set up stored procedure
-- http://stackoverflow.com/questions/6115573/how-do-i-view-my-stored-procedures-in-phpmyadmin
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`remote`@`%` PROCEDURE `store_device_position`(IN distance varchar(20), min_distance int(10), last_id bigint(20), 
device_id bigint(20), rec_time datetime, valid tinyint(1), latitude double, longitude double, altitude double, 
speed double, course double, power double, extended_info varchar(255)
)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
	START TRANSACTION;

	IF (distance > min_distance OR ISNULL(last_id) ) THEN
		
		INSERT INTO positions (`device_id`, `time`, `valid`, `latitude`, `longitude`, `altitude`, `speed`, `course`, `power`, `other`)
		VALUES (device_id, rec_time, valid, latitude, longitude, altitude, speed, course, power, extended_info);
		SET @last_positions_id = LAST_INSERT_ID();
		UPDATE devices SET latestPosition_id = @last_positions_id WHERE id = device_id;
	
	END IF;

	COMMIT;
END

traccar.cfg

 <!-- This Sql update is calculating the distance between last 
        stored gps coordinates and the new, then is skiping any 
        record that is smaller than the min_distance value. -->

<entry key='database.insertPosition'>
		-- Set the minimum distance that we don't want to store a position
		-- this is in meters
		SET @min_distance = 20;
		-- Make sure last id is null so we don't use old value.
		SET @last_id = null;
		-- Map the java variables to sql variables.
		SET @device_id = :device_id;
		SET @rec_time = :time;
		SET @valid = :valid;
		SET @latitude = :latitude;
		SET @longitude = :longitude;
		SET @altitude = :altitude;
		SET @speed = :speed;
		SET @course = :course;
		SET @power = :power;
		SET @extended_info = :extended_info;

		-- Let select the last position latitude and longitude for this device.
		SELECT id, latitude, longitude 
			INTO @last_id, @last_latitude, @last_longitude 
			FROM positions WHERE device_id = @device_id ORDER BY time DESC LIMIT 1;

		-- Let calculate the Geo Distance between the to position.
		SET @distance =	ROUND( DEGREES(ACOS(COS(RADIANS(@last_latitude)) * COS(RADIANS(@latitude)) *
					COS(RADIANS(@last_longitude) - RADIANS(@longitude)) +
					SIN(RADIANS(@last_latitude)) * SIN(RADIANS(@latitude)))) * 111.045 * 1000 );

		-- Call the stored procedure to store the values if the distance > min_distance
		CALL store_device_position(@distance, @min_distance, @last_id, @device_id, @rec_time, @valid, @latitude, @longitude, @altitude, @speed, @course, @power, @extended_info);
	</entry>

    <!-->
        device_id - Long
        id - Long
    <-->
    <entry key='database.updateLatestPosition'>
        -- Comment this Update query as we make the update in the stored procedure.
        --UPDATE devices SET latestPosition_id = :id WHERE id = :device_id;
    </entry>