SQL script to extract GPS information from Android sensor key-value storage. It performs the trajectory extraction from the GPS traces.

gps_trajectory_extractor_from_android_keyvalue.sql

-- Prepare temporary table
CREATE TABLE android_gpslocationevent LIKE elog_user_test3_android;

-- Extract GPS location data from the android sensor data table
INSERT IGNORE INTO android_gpslocationevent
SELECT *
FROM elog_user_test3_android
WHERE sensor = 'GPSLocationEvent';

-- Prepare GPS table
DROP TABLE IF EXISTS gps;
CREATE TABLE `gps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `eml_event_timestamp` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `unixtimestamp` int(11),
  `accuracy` int(11),
  `altitude` int(11),
  `bearing` double,
  `elapsedRealtimeNanos` long,
  `latitude` double,
  `longitude` double,
  `distance` double,
  `provider` varchar(100) DEFAULT '',
  `sampleRate` double,
  `speed` double,
  PRIMARY KEY (`id`,`eml_event_timestamp`),
  KEY `eml_event_timestamp` (`eml_event_timestamp`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`),
  KEY `altitude` (`altitude`),
  KEY `accuracy` (`accuracy`),
  KEY `speed` (`speed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- First insert timestamp. This will generate IDs automatically
INSERT IGNORE INTO gps (eml_event_timestamp)
SELECT
eml_event_timestamp
FROM android_gpslocationevent
ORDER BY eml_event_timestamp ASC;

-- Update the rest of field values
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.unixtimestamp = ag.unixtimestamp 
WHERE g.eml_event_timestamp = ag.eml_event_timestamp;
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.altitude = ag.`key_value` 
WHERE ag.sensor_key = 'altitude' AND
g.eml_event_timestamp = ag.eml_event_timestamp;
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.bearing = ag.`key_value` 
WHERE ag.sensor_key = 'bearing' AND
g.eml_event_timestamp = ag.eml_event_timestamp;
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.elapsedRealtimeNanos = ag.`key_value` 
WHERE ag.sensor_key = 'elapsedRealtimeNanos' AND
g.eml_event_timestamp = ag.eml_event_timestamp;
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.latitude = ag.`key_value` 
WHERE ag.sensor_key = 'latitude' AND
g.eml_event_timestamp = ag.eml_event_timestamp;
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.longitude = ag.`key_value` 
WHERE ag.sensor_key = 'longitude' AND
g.eml_event_timestamp = ag.eml_event_timestamp;
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.accuracy = ag.`key_value` 
WHERE ag.sensor_key = 'accuracy' AND
g.eml_event_timestamp = ag.eml_event_timestamp;
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.provider = ag.`key_value` 
WHERE ag.sensor_key = 'provider' AND
g.eml_event_timestamp = ag.eml_event_timestamp;
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.sampleRate = ag.`key_value` 
WHERE ag.sensor_key = 'sampleRate' AND
g.eml_event_timestamp = ag.eml_event_timestamp;
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.speed = ag.`key_value` 
WHERE ag.sensor_key = 'speed' AND
g.eml_event_timestamp = ag.eml_event_timestamp;
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.unixtimestamp = ag.`key_value` 
WHERE ag.sensor_key = 'unixtimestamp' AND
g.eml_event_timestamp = ag.eml_event_timestamp;
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.unixtimestamp = ag.`key_value` 
WHERE ag.sensor_key = 'unixtimestamp' AND
g.eml_event_timestamp = ag.eml_event_timestamp;
UPDATE gps AS g, android_gpslocationevent AS ag
SET g.accuracy = ag.`key_value` 
WHERE g.eml_event_timestamp = ag.eml_event_timestamp AND
ag.sensor_key = 'accuracy';
	
-- Update records with the distance 
UPDATE 
	gps AS t1
	INNER JOIN gps AS t2 ON (t2.id = t1.id+1)
SET t2.distance = 
	 (ACOS(SIN(RADIANS(t2.latitude)) * SIN(RADIANS(t1.latitude))
	 + COS(RADIANS(t2.latitude)) * COS(RADIANS(t1.latitude))
	 * COS(RADIANS(t2.longitude) - RADIANS(t1.longitude)))
	 ) * 6371
;

-- Prepare the time span table
DROP TABLE IF EXISTS gpsTimespanDataTable;
CREATE TABLE gpsTimespanDataTable (
	`a_id` INT NOT NULL,
	`b_id` INT NOT NULL,
	`a_eml_event_timestamp` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), 
	`b_eml_event_timestamp` timestamp(6) NOT NULL,
	`iTimeDifference` INT NOT NULL,
	`distance` double NOT NULL,
	PRIMARY KEY (`a_id`, `b_id`),
	KEY `a_timestamp_key` (`a_eml_event_timestamp`),
	KEY `b_timestamp_key` (`b_eml_event_timestamp`),
	KEY `iTimeDifference` (`iTimeDifference`),
	KEY `distance` (`distance`)
) ENGINE=MyISAM;

-- Measure each gps data time span and the distance
INSERT IGNORE INTO gpsTimespanDataTable
SELECT
	 a.id,
	 b.id,
	 a.eml_event_timestamp,
	 b.eml_event_timestamp,
	 UNIX_TIMESTAMP(b.eml_event_timestamp) - UNIX_TIMESTAMP(a.eml_event_timestamp) AS iTimeDifference,
	 (
		 (ACOS(SIN(RADIANS(b.latitude)) * SIN(RADIANS(a.latitude))
		 + COS(RADIANS(b.latitude)) * COS(RADIANS(a.latitude))
		 * COS(RADIANS(b.longitude) - RADIANS(a.longitude)))
		 ) * 6371
	 ) AS distance
FROM
	gps AS a,
	gps AS b
WHERE b.id = a.id+1;

-- Prepare GPS time span table
DROP TABLE IF EXISTS gpsTimeSpanTable;
CREATE TABLE gpsTimeSpanTable (
	`id` INT NOT NULL AUTO_INCREMENT,
	`id_start` INT NOT NULL,
	`id_end` INT NOT NULL,
	`id_span` INT NOT NULL,
	PRIMARY KEY (id),
	KEY `id_start` (`id_start`),
	KEY `id_end` (`id_end`),
	KEY `id_span` (`id_span`)
) ENGINE=MyISAM;

-- Investigate the GPS record to find the track by the rule (1 hour difference and the movement)
-- You can modify here to control the sensitivity
INSERT IGNORE INTO gpsTimeSpanTable (id_start, id_end, id_span)
SELECT
	IF (a.id = 1, 1, a.id+1) AS id_start,
	b.id AS id_end,
	ABS(b.id - a.id) AS id_span
FROM
	(SELECT a_id AS id FROM gpsTimespanDataTable WHERE (iTimeDifference > 3600 AND distance > 0.001) OR a_id = 1) AS a,
	(SELECT a_id AS id FROM gpsTimespanDataTable WHERE (iTimeDifference > 3600 AND distance > 0.001) OR a_id = 1) AS b
WHERE b.id > a.id
GROUP BY a.id
ORDER BY a.id ASC;

SET @iSpanDataCount = (SELECT COUNT(*) FROM gpsTimeSpanTable);
SET @iPathLinkCount = (SELECT COUNT(*) FROM gpsTimespanDataTable); 

INSERT IGNORE INTO gpsTimeSpanTable (id_start, id_end, id_span) 
SELECT 
	IF (@iPathLinkCount > 0 AND @iSpanDataCount = 0, MIN(a_id), NULL) AS id_start, 
	IF (@iPathLinkCount > 0 AND @iSpanDataCount = 0, MAX(b_id), NULL) AS id_end, 
	MAX(b_id) - MIN(a_id) AS id_span 
FROM gpsTimespanDataTable; 

SET @iSpanDataCount = (SELECT COUNT(*) FROM gpsTimeSpanTable); 
	    	 
-- Prepare the result table
DROP TABLE IF EXISTS gpsTimeSpanResultTable;
CREATE TABLE `gpsTimeSpanResultTable` (
  `id` int(11) NOT NULL,
  `eml_event_timestamp` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `unixtimestamp` int(11) DEFAULT NULL,
  `accuracy` int(11) DEFAULT NULL,
  `altitude` int(11) DEFAULT NULL,
  `bearing` double DEFAULT NULL,
  `elapsedRealtimeNanos` mediumtext,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `provider` varchar(100) DEFAULT '',
  `distance` double DEFAULT NULL,
  `sampleRate` double DEFAULT NULL,
  `speed` double DEFAULT NULL,
  PRIMARY KEY (`latitude`, longitude, eml_event_timestamp),
  KEY `eml_event_timestamp` (`eml_event_timestamp`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`),
  KEY `altitude` (`altitude`),
  KEY `accuracy` (`accuracy`),
  KEY `speed` (`speed`)
) ENGINE=InnoDB AUTO_INCREMENT=393211 DEFAULT CHARSET=utf8;

-- The final result that includes original fields with the found track ID
INSERT IGNORE INTO gpsTimeSpanResultTable (id, unixtimestamp, accuracy, altitude, bearing, elapsedRealtimeNanos, latitude, longitude, provider, distance, sampleRate, speed, eml_event_timestamp)
SELECT
	ts.id,
	tp.`unixtimestamp`,
	tp.`accuracy`,
	tp.`altitude`,
	tp.`bearing`,
	tp.`elapsedRealtimeNanos`,
	tp.`latitude`,
	tp.`longitude`,
	tp.`provider`,
	tp.`distance`,
	tp.`sampleRate`,
	tp.`speed`,
	tp.eml_event_timestamp
FROM
	gps AS tp,
	gpsTimeSpanTable AS ts
WHERE tp.id BETWEEN ts.id_start AND ts.id_end
ORDER BY tp.eml_event_timestamp ASC;