About

The eLog library was initially developed as a research prototype and later published for lifelogging researchers in 2010 to help them easily analyze heterogenous data and complex visualization. It has been kept growing with the progress of mobile computing environments and recently its UI part is released with GPL v3 license for wider usage. The eLog UI library is optimized for mobile environment and can get easily integrated with existing Web services.

Who We Are

The original work was proposed by Pil Ho and later extended the work with collaboration with 28 researchers around the world who contributed their lifelogs, collaborated for lifelog analysis and share research results to build up an open lifelogging platform for the public. Pil Ho has been keeping the development updating the library following up the progress in mobile computing.

Updates

  • Nov. 2014: Change the web page skin using bootstrap.
  • Nov. 2014: Published elog UI library as GPL v3.
  • Oct. 2014: Version up eLog library and documentation.

 

eLifeLog Database Definitions

This page gives you the snapshot view on federated tables and stored procedures in your eLifeLog database. Last update: May 24th, 2011
/* eLifeLog.org Federated Table Definition */
/* Created at 2011-05-24T13:18:25+02:00 */

DROP TABLE IF EXISTS `elog_iphone_gps`;

CREATE TABLE `elog_iphone_gps` (
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `elevation` double NOT NULL,
  `gps_UTC_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  `gps_timezone` varchar(255) default NULL,
  `gps_UTC_unix_timestamp` bigint(20) default NULL,
  PRIMARY KEY  (`latitude`,`longitude`,`elevation`,`gps_UTC_timestamp`),
  KEY `timestamp_key` (`gps_UTC_timestamp`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`),
  KEY `elevation` (`elevation`),
  KEY `gps_timestamp` (`gps_UTC_timestamp`),
  KEY `gps_timezone` (`gps_timezone`),
  KEY `gps_UTC_unix_timestamp` (`gps_UTC_unix_timestamp`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_iphone_gps_clusters`;

CREATE TABLE `elog_iphone_gps_clusters` (
  `parent_region_id` int(11) NOT NULL,
  `region_id` int(11) default NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  KEY `parent_region_id` (`parent_region_id`),
  KEY `region_id` (`region_id`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`)
) ENGINE=FEDERATED; 

DROP TABLE IF EXISTS `elog_iphone_gps_region`;

CREATE TABLE `elog_iphone_gps_region` (
  `region_id` int(11) NOT NULL default '0',
  `count` int(11) default NULL,
  `radius_km` double default NULL,
  `latitude_center` double default NULL,
  `longitude_center` double default NULL,
  `latitude_max` double default NULL,
  `longitude_max` double default NULL,
  `latitude_min` double default NULL,
  `longitude_min` double default NULL,
  PRIMARY KEY  (`region_id`),
  KEY `count` (`count`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_iphone_gps_region_hierarchy`;

CREATE TABLE `elog_iphone_gps_region_hierarchy` (
  `parent_region_id` int(11) NOT NULL,
  `region_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`parent_region_id`,`region_id`),
  KEY `parent_region_id` (`parent_region_id`),
  KEY `region_id` (`region_id`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_images`;

CREATE TABLE `elog_sensecam_images` (
  `filename` varchar(255) default NULL,
  `filepath` varchar(255) default NULL,
  `image_UTC_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  `sensecam_UTC_unix_timestamp` bigint(20) default NULL,
  `reviewer` varchar(255) default NULL,
  PRIMARY KEY  (`image_UTC_timestamp`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_images_anonymize`;

CREATE TABLE `elog_sensecam_images_anonymize` (
  `id` int(11) NOT NULL auto_increment,
  `filepath` varchar(256) NOT NULL default '',
  `image_UTC_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  `reviewer` varchar(255) default NULL,
  `image_modified_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `region_x` int(11) NOT NULL default '0',
  `region_y` int(11) NOT NULL default '0',
  `region_width` int(11) NOT NULL default '0',
  `region_height` int(11) NOT NULL default '0',
  `sensecam_UTC_unix_timestamp` bigint(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `reviewer` (`reviewer`),
  KEY `image_UTC_timestamp` (`image_UTC_timestamp`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_images_anonymize_people`;

CREATE TABLE `elog_sensecam_images_anonymize_people` (
  `anonymize_id` int(11) NOT NULL,
  `gmail_contacts_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`anonymize_id`,`gmail_contacts_id`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_images_thumbnail_16`;

CREATE TABLE `elog_sensecam_images_thumbnail_16` (
  `filepath` varchar(255) default NULL,
  `image_UTC_timestamp` timestamp NULL default NULL,
  KEY `image_UTC_timestamp` (`image_UTC_timestamp`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_images_thumbnail_64`;

CREATE TABLE `elog_sensecam_images_thumbnail_64` (
  `filepath` varchar(255) default NULL,
  `image_UTC_timestamp` timestamp NULL default NULL,
  KEY `image_UTC_timestamp` (`image_UTC_timestamp`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_sensor_acc`;

CREATE TABLE `elog_sensecam_sensor_acc` (
  `sensecam_UTC_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  `axis_1` double NOT NULL,
  `axis_2` double NOT NULL,
  `axis_3` double NOT NULL,
  PRIMARY KEY  (`sensecam_UTC_timestamp`,`axis_1`,`axis_2`,`axis_3`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_sensor_cam`;

CREATE TABLE `elog_sensecam_sensor_cam` (
  `sensecam_UTC_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  `filename` varchar(255) NOT NULL,
  `switch` varchar(2) NOT NULL default '',
  PRIMARY KEY  (`sensecam_UTC_timestamp`,`filename`,`switch`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_sensor_clr`;

CREATE TABLE `elog_sensecam_sensor_clr` (
  `sensecam_UTC_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  `lightlevel` double NOT NULL,
  PRIMARY KEY  (`sensecam_UTC_timestamp`,`lightlevel`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_sensor_mag`;

CREATE TABLE `elog_sensecam_sensor_mag` (
  `sensecam_UTC_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  `mag_1` double NOT NULL,
  `mag_2` double NOT NULL,
  `mag_3` double NOT NULL,
  `direction` double NOT NULL default '0',
  PRIMARY KEY  (`sensecam_UTC_timestamp`,`mag_1`,`mag_2`,`mag_3`,`direction`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_sensor_pir`;

CREATE TABLE `elog_sensecam_sensor_pir` (
  `sensecam_UTC_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  `pir` int(11) NOT NULL,
  PRIMARY KEY  (`sensecam_UTC_timestamp`,`pir`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_sensor_tmp`;

CREATE TABLE `elog_sensecam_sensor_tmp` (
  `sensecam_UTC_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  `temperature` double NOT NULL,
  PRIMARY KEY  (`sensecam_UTC_timestamp`,`temperature`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_timestamp_clusters`;

CREATE TABLE `elog_sensecam_timestamp_clusters` (
  `parent_region_id` int(11) NOT NULL,
  `region_id` int(11) NOT NULL default '0',
  `unix_timestamp` bigint(20) NOT NULL,
  PRIMARY KEY  (`parent_region_id`,`region_id`,`unix_timestamp`),
  KEY `parent_region_id` (`parent_region_id`),
  KEY `region_id` (`region_id`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_timestamp_region`;

CREATE TABLE `elog_sensecam_timestamp_region` (
  `region_id` int(11) NOT NULL default '0',
  `count` int(11) default NULL,
  `unix_timestamp_duration` bigint(20) default NULL,
  `unix_timestamp_maxgap` bigint(20) default NULL,
  `unix_timestamp_average` bigint(20) default NULL,
  `unix_timestamp_max` bigint(20) default NULL,
  `unix_timestamp_min` bigint(20) default NULL,
  PRIMARY KEY  (`region_id`),
  KEY `count` (`count`),
  KEY `time_duration` (`unix_timestamp_duration`),
  KEY `time_maxgap` (`unix_timestamp_maxgap`),
  KEY `time_average` (`unix_timestamp_average`),
  KEY `time_max` (`unix_timestamp_max`),
  KEY `time_min` (`unix_timestamp_min`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `elog_sensecam_timestamp_region_hierarchy`;

CREATE TABLE `elog_sensecam_timestamp_region_hierarchy` (
  `parent_region_id` int(11) NOT NULL,
  `region_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`parent_region_id`,`region_id`),
  KEY `parent_region_id` (`parent_region_id`),
  KEY `region_id` (`region_id`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `oCacheTrentoGPSPath`;

CREATE TABLE `oCacheTrentoGPSPath` (
  `id` int(11) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `distance` double NOT NULL,
  `gps_UTC_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`latitude`,`longitude`,`gps_UTC_timestamp`),
  KEY `timestamp_key` (`gps_UTC_timestamp`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `time_iso3166`;

CREATE TABLE `time_iso3166` (
  `ISO3166_CountryCode` char(2) default NULL,
  `country_name` varchar(255) default NULL
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `time_zone`;

CREATE TABLE `time_zone` (
  `ISO3166_CountryCode` char(2) default NULL,
  `latitude` double default NULL,
  `longitude` double default NULL,
  `zonename` varchar(255) default NULL,
  `comments` varchar(255) default NULL
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `time_zone_leap_second`;

CREATE TABLE `time_zone_leap_second` (
  `Transition_time` bigint(20) NOT NULL,
  `Correction` int(11) NOT NULL,
  PRIMARY KEY  (`Transition_time`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `time_zone_name`;

CREATE TABLE `time_zone_name` (
  `Name` char(64) NOT NULL,
  `Time_zone_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`Name`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `time_zone_offset`;

CREATE TABLE `time_zone_offset` (
  `name` char(64) NOT NULL,
  `Time_zone_id` int(10) unsigned NOT NULL,
  `Transition_type_id` int(10) unsigned NOT NULL,
  `Offset` int(11) NOT NULL default '0',
  `Is_DST` tinyint(3) unsigned NOT NULL default '0',
  `Abbreviation` char(8) NOT NULL default '',
  `tt.Offset/3600` decimal(14,4) default NULL,
  `hour_offset_integer` varbinary(17) default NULL,
  `hour_offset` varbinary(20) default NULL,
  PRIMARY KEY  (`name`,`Transition_type_id`,`Is_DST`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `time_zone_transition`;

CREATE TABLE `time_zone_transition` (
  `Time_zone_id` int(10) unsigned NOT NULL,
  `Transition_time` bigint(20) NOT NULL,
  `Transition_type_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`Time_zone_id`,`Transition_time`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `time_zone_transition_type`;

CREATE TABLE `time_zone_transition_type` (
  `Time_zone_id` int(10) unsigned NOT NULL,
  `Transition_type_id` int(10) unsigned NOT NULL,
  `Offset` int(11) NOT NULL default '0',
  `Is_DST` tinyint(3) unsigned NOT NULL default '0',
  `Abbreviation` char(8) NOT NULL default '',
  PRIMARY KEY  (`Time_zone_id`,`Transition_type_id`)
) ENGINE=FEDERATED;

DROP TABLE IF EXISTS `tmpFoundPath`;

CREATE TABLE `tmpFoundPath` (
  `id` int(11) NOT NULL,
  `starttime` timestamp NULL default NULL,
  `endtime` timestamp NULL default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tmpPathEnd`;

CREATE TABLE `tmpPathEnd` (
  `id` int(11) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `distance` double NOT NULL,
  `gps_UTC_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tmpPathStart`;

CREATE TABLE `tmpPathStart` (
  `start_region_id` int(11) default NULL,
  `start_path_id` int(11) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `distance` double NOT NULL,
  `gps_UTC_timestamp` timestamp NULL default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tmpQueryPathEnd`;

CREATE TABLE `tmpQueryPathEnd` (
  `parent_region_id` int(11) NOT NULL,
  `region_id` int(11) default NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  KEY `parent_region_id` (`parent_region_id`),
  KEY `region_id` (`region_id`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tmpQueryPathStart`;

CREATE TABLE `tmpQueryPathStart` (
  `parent_region_id` int(11) NOT NULL,
  `region_id` int(11) default NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  KEY `parent_region_id` (`parent_region_id`),
  KEY `region_id` (`region_id`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tmpStartGPSPoints`;

CREATE TABLE `tmpStartGPSPoints` (
  `parent_region_id` int(11) NOT NULL,
  `region_id` int(11) default NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  KEY `parent_region_id` (`parent_region_id`),
  KEY `region_id` (`region_id`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



DROP PROCEDURE IF EXISTS `ELOG_GetEventCountbyHour`;

CREATE  PROCEDURE `ELOG_GetEventCountbyHour`()
label_proc:BEGIN


SET time_zone = "+2:00";


DROP TABLE IF EXISTS oCacheEventCountbyHour;
CREATE TABLE `oCacheEventCountbyHour` (
  `start` varchar(255) DEFAULT NULL,
  `durationEvent` varchar(10) DEFAULT '',
  `color` varchar(10) DEFAULT '',
  `title` varchar(255) DEFAULT '',
  `event_table` varchar(255) DEFAULT '',
  `event_count` bigint(21) DEFAULT '0',
  `image` varchar(255) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  KEY `event_table` (`event_table`),
  KEY `event_count` (`event_count`),
  KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO oCacheEventCountbyHour
(
SELECT
	DATE_FORMAT(FROM_UNIXTIME(CAST(elog_gmail_UTC_unix_timestamp / 3600 AS UNSIGNED)*3600), '%a, %d %b %Y %T' ) AS start,
	'false' AS durationEvent,
	'#CC0000' AS color,
	COUNT(*) AS title,
	'elog_gmail_allmail' AS event_table,
	COUNT(*) AS event_count,
	NULL AS image,
	NULL AS icon,
	NULL AS description
FROM elog_gmail_allmail
WHERE elog_gmail_UTC_date IS NOT NULL
GROUP BY CAST(elog_gmail_UTC_unix_timestamp / 3600 AS UNSIGNED)*3600
)
UNION
(
SELECT
	DATE_FORMAT(FROM_UNIXTIME(CAST(elog_sensecam_UTC_unix_timestamp / 3600 AS UNSIGNED)*3600), '%a, %d %b %Y %T' ) AS start,
	'false' AS durationEvent,
	'#CC0000' AS color,
	COUNT(*) AS title,
	'elog_sensecam_images' AS event_table,
	COUNT(*) AS event_count,
	NULL AS image,
	NULL AS icon,
	NULL AS description
FROM elog_sensecam_images
WHERE image_UTC_timestamp IS NOT NULL
GROUP BY CAST(elog_sensecam_UTC_unix_timestamp / 3600 AS UNSIGNED)*3600
)
UNION
(
SELECT
	DATE_FORMAT(FROM_UNIXTIME(CAST(gps_UTC_unix_timestamp / 3600 AS UNSIGNED)*3600), '%a, %d %b %Y %T' ) AS start,
	'false' AS durationEvent,
	'#33CC33' AS color,
	COUNT(*) AS title,
	'elog_iphone_gps' AS event_table,
	COUNT(*) AS event_count,
	NULL AS image,
	NULL AS icon,
	NULL AS description
FROM elog_iphone_gps
WHERE gps_UTC_timestamp IS NOT NULL
GROUP BY CAST(gps_UTC_unix_timestamp / 3600 AS UNSIGNED)*3600
)
UNION
(
SELECT
	DATE_FORMAT(FROM_UNIXTIME(CAST(sa.sensecam_UTC_unix_timestamp / 600 AS UNSIGNED)*600), '%a, %d %b %Y %T' ) AS start,
	'false' AS durationEvent,
	'#CCCCCC' AS color,
	NULL AS title,
	'elog_sensecam_images_anonymize' AS event_table,
	COUNT(*) AS event_count,
	CONCAT("id=", sa.id) AS image,
	CONCAT("id=", sa.id) AS icon,
	name AS description
FROM elog_sensecam_images_anonymize AS sa
INNER JOIN elog_sensecam_images_anonymize_people AS sp ON (sa.id = sp.anonymize_id)
INNER JOIN elog_gmail_contacts AS gc ON (sp.gmail_contacts_id = gc.id)
WHERE sa.image_UTC_timestamp IS NOT NULL
GROUP BY name, CAST(sa.sensecam_UTC_unix_timestamp / 600 AS UNSIGNED)*600
);

END;

DROP PROCEDURE IF EXISTS `ELOG_GetFaceImagePath`;

CREATE  PROCEDURE `ELOG_GetFaceImagePath`(iFaceID int)
label_proc:BEGIN

SELECT
CONCAT(
	REPLACE(
		REPLACE(sa.filepath, "images", "people"), 
		".jpg",
		""
	),
	" ",
	sa.region_x, " ",
	sa.region_y, " ",
	sa.region_width, " ",
	sa.region_height, ".crop.jpg"
)  AS filepath
FROM elog_sensecam_images_anonymize AS sa
WHERE sa.id = iFaceID;

END;

DROP PROCEDURE IF EXISTS `ELOG_GetFederatedStatus`;

CREATE  PROCEDURE `ELOG_GetFederatedStatus`()
BEGIN
/*
	Check foreign source connection status
*/

SET @sCount = (
	SELECT COUNT(*)
	FROM elog_iphone_gps
);

SELECT IF (@sCount > 1, "Ok", "Connection Error.") AS sConnectionStatus;

END;

DROP PROCEDURE IF EXISTS `ELOG_GetGPSLocationbyDistance`;

CREATE  PROCEDURE `ELOG_GetGPSLocationbyDistance`(
        IN fLatitude double,
        IN fLongitude double,
        IN fDistance double
    )
BEGIN



SELECT 
ig.latitude,
ig.longitude,
(
	DEGREES(
		ACOS(
			SIN(RADIANS(fLatitude)) * SIN(RADIANS(ig.latitude)) + 
			COS(RADIANS(fLatitude)) * COS(RADIANS(ig.latitude)) * 
			COS(RADIANS(fLongitude - ig.longitude))
		)
	) * 69.09 * 1.609344
) AS `distance` 
FROM elog_iphone_gps AS ig
HAVING distance <= fDistance 
ORDER BY distance ASC;

END;

DROP PROCEDURE IF EXISTS `ELOG_GetGPSRegionbyRadius`;

CREATE  PROCEDURE `ELOG_GetGPSRegionbyRadius`(
        fRadius double,
        fLat_center double,
        fLog_center double,
        fLat_NE double,
        fLog_NE double,
        fLat_SW double,
        fLog_SW double,
        fMaxRadius double
    )
BEGIN


DROP TABLE IF EXISTS oCacheSelectedGPSRegions;

CREATE TABLE oCacheSelectedGPSRegions
SELECT DISTINCTROW
	ir.*
FROM
elog_iphone_gps_region AS ir
INNER JOIN
(
	SELECT 
		MAX(it.radius_km) AS child_region_max_radius,
		ih.parent_region_id,
		ih.region_id
	FROM
	elog_iphone_gps_region_hierarchy AS ih
	INNER JOIN elog_iphone_gps_region AS it ON (ih.region_id = it.region_id)
	GROUP BY ih.parent_region_id
) AS iht ON (ir.region_id = iht.parent_region_id)
WHERE
	ir.radius_km >= fRadius AND
	ir.radius_km <= fMaxRadius AND
	iht.child_region_max_radius < fRadius AND
	IF(fMaxRadius > 1000, 1, 
		(ir.latitude_center BETWEEN fLat_SW AND fLat_NE) AND 
		(ir.longitude_center BETWEEN fLog_SW AND fLog_NE)
	)
ORDER BY ir.radius_km DESC
LIMIT 100;

SELECT * FROM oCacheSelectedGPSRegions;

END;

DROP PROCEDURE IF EXISTS `ELOG_GetGPSRegionbyTimespan`;

CREATE  PROCEDURE `ELOG_GetGPSRegionbyTimespan`(
        iTimespan bigint,
        iTimestart bigint,
        iTimecenter bigint,
        iTimeend bigint,
        iMaxTimespan bigint
    )
BEGIN

SET time_zone = "+2:00";


SELECT
	DATE_FORMAT(FROM_UNIXTIME(ir.unix_timestamp_min), '%a, %d %b %Y %T' ) AS start,
	DATE_FORMAT(FROM_UNIXTIME(ir.unix_timestamp_max), '%a, %d %b %Y %T' ) AS end,
	'true' AS durationEvent,
	'#CCCCCC' AS color,
	NULL AS title,
	'elog_iphone_gps_timestamp_region' AS event_table,
	ir.count AS event_count,
	CONCAT(
		"GPS points: ", ir.count, "
", "From: ", DATE_FORMAT(FROM_UNIXTIME(ir.unix_timestamp_min), '%a, %d %b %Y %T' ), "
", "To: ", DATE_FORMAT(FROM_UNIXTIME(ir.unix_timestamp_max), '%a, %d %b %Y %T' )) AS description FROM elog_iphone_gps_timestamp_region AS ir INNER JOIN ( SELECT MAX(it.unix_timestamp_duration) AS child_region_max_duration, ih.parent_region_id, ih.region_id FROM elog_iphone_gps_timestamp_region_hierarchy AS ih INNER JOIN elog_iphone_gps_timestamp_region AS it ON (ih.region_id = it.region_id) GROUP BY ih.parent_region_id ) AS iht ON (ir.region_id = iht.parent_region_id) WHERE ir.unix_timestamp_duration >= iTimespan AND ir.unix_timestamp_duration <= iMaxTimespan AND iht.child_region_max_duration < iTimespan AND IF(iTimespan > 1000, 1, (ir.unix_timestamp_average BETWEEN iTimestart AND iTimeend) ) ORDER BY ir.unix_timestamp_duration DESC; END; DROP PROCEDURE IF EXISTS `ELOG_GetImageList`; CREATE PROCEDURE `ELOG_GetImageList`(sServerURL varchar(255), sTimeFrom varchar(255), sTimeTo varchar(255)) label_proc:BEGIN CALL ELOG_SetTimezone(sTimeFrom); SELECT CONCAT(sServerURL, si.image_UTC_timestamp) AS sImageURL, si.image_UTC_timestamp AS sLastRecordingTime, DATE_FORMAT( si.image_UTC_timestamp, '%a, %d %b %Y %T' ) AS sLocalTime FROM elog_sensecam_images AS si WHERE si.image_UTC_timestamp BETWEEN sTimeFrom AND sTimeTo ORDER BY sLastRecordingTime ASC LIMIT 500; END; DROP PROCEDURE IF EXISTS `ELOG_GetImageListbyGPSRegionID`; CREATE PROCEDURE `ELOG_GetImageListbyGPSRegionID`( sServerURL varchar(255), iGPSRegionID bigint ) label_proc:BEGIN SELECT latitude_center, longitude_center, radius_km INTO @fLatidue, @fLongitude, @fRadius FROM elog_iphone_gps_region WHERE region_id = iGPSRegionID; DROP TABLE IF EXISTS tmpImageListbyGPSRegion; CREATE TEMPORARY TABLE `tmpImageListbyGPSRegion` LIKE elog_iphone_gps; INSERT IGNORE INTO tmpImageListbyGPSRegion SELECT DISTINCTROW ig.* FROM elog_iphone_gps AS ig WHERE gps_UTC_timestamp >= "2010-09-22" HAVING ( DEGREES( ACOS( SIN(RADIANS(@fLatidue)) * SIN(RADIANS(ig.latitude)) + COS(RADIANS(@fLatidue)) * COS(RADIANS(ig.latitude)) * COS(RADIANS(@fLongitude - ig.longitude)) ) ) * 69.09 * 1.609344 ) <= @fRadius; SET @sMinTime = ( SELECT MIN(gps_UTC_timestamp) FROM tmpImageListbyGPSRegion ); CALL ELOG_SetTimezone(@sMinTime); SELECT CONCAT(sServerURL, si.image_UTC_timestamp) AS sImageURL, si.image_UTC_timestamp AS sLastRecordingTime, DATE_FORMAT( si.image_UTC_timestamp, '%a, %d %b %Y %T' ) AS sLocalTime FROM tmpImageListbyGPSRegion AS gc, elog_sensecam_images AS si WHERE (si.sensecam_UTC_unix_timestamp BETWEEN gc.gps_UTC_unix_timestamp - 15 AND gc.gps_UTC_unix_timestamp + 15) ORDER BY gc.gps_UTC_unix_timestamp ASC LIMIT 500; END; DROP PROCEDURE IF EXISTS `ELOG_GetImagePath`; CREATE PROCEDURE `ELOG_GetImagePath`(sTime varchar(255), sThumbnail varchar(255)) label_proc:BEGIN CALL ELOG_SetTimezone(sTime); CASE sThumbnail WHEN '16' THEN SET @sTable = 'elog_sensecam_images_thumbnail_16'; WHEN '64' THEN SET @sTable = 'elog_sensecam_images_thumbnail_64'; ELSE SET @sTable = 'elog_sensecam_images'; END CASE; SET @sSQL = CONCAT( " SELECT sf.filepath ", " FROM ", @sTable, " AS sf ", " WHERE sf.image_UTC_timestamp = '", sTime, "'" ); PREPARE run_stmt FROM @sSQL; EXECUTE run_stmt; DEALLOCATE PREPARE run_stmt; END; DROP PROCEDURE IF EXISTS `ELOG_GetLocationbyTime`; CREATE PROCEDURE `ELOG_GetLocationbyTime`( sTimeFrom varchar(255) ) label_proc:BEGIN SET @sGPSTimestamp = ( SELECT ig.gps_timestamp FROM ( SELECT gps_timestamp, ABS(TIMESTAMPDIFF(SECOND, sTimeFrom, gps_timestamp)) AS iSecondGap FROM elog_iphone_gps WHERE ABS(TIMESTAMPDIFF(SECOND, sTimeFrom, gps_timestamp)) < 60*60*12 ORDER BY iSecondGap ASC LIMIT 1 ) AS ig ); SET @sSensorTimestamp = ( SELECT elog_sensecam_UTC_timestamp FROM ( SELECT elog_sensecam_UTC_timestamp, ABS(TIMESTAMPDIFF(SECOND, sTimeFrom, elog_sensecam_UTC_timestamp)) AS iSecondGap FROM elog_sensecam_sensor_acc WHERE ABS(TIMESTAMPDIFF(SECOND, sTimeFrom, elog_sensecam_UTC_timestamp)) < 60*60*12 ORDER BY iSecondGap ASC LIMIT 1 ) AS sacc ); SELECT sacc.sensecam_UTC_timestamp, sacc.axis_1, sacc.axis_2, sacc.axis_3, sclr.lightlevel, smag.mag_1, smag.mag_2, smag.mag_3, IF (smag.mag_2, 90.0 - ATAN(smag.mag_1/smag.mag_2)*180/PI(), IF (smag.mag_2 < 0, 270.0 - ATAN(smag.mag_1/smag.mag_2)*180/PI(), IF (smag.mag_2 = 0 AND smag.mag_1 < 0, 180.0, IF (smag_mag_2 = 0 AND smag.mag_1 > 0, 0.0, NULL ) ) ) ) AS direction, smag.sensecam_UTC_timestamp AS mag_UTC_timestamp, spir.pir, stmp.temperature, ig.gps_timestamp, ig.latitude, ig.longitude FROM elog_sensecam_sensor_acc AS sacc, elog_sensecam_sensor_clr AS sclr, elog_sensecam_sensor_mag AS smag, elog_sensecam_sensor_pir AS spir, elog_sensecam_sensor_tmp AS stmp, elog_iphone_gps AS ig WHERE sacc.sensecam_UTC_timestamp = @sSensorTimestamp AND sacc.sensecam_UTC_timestamp = sclr.sensecam_UTC_timestamp AND sclr.sensecam_UTC_timestamp = smag.sensecam_UTC_timestamp AND smag.sensecam_UTC_timestamp = spir.sensecam_UTC_timestamp AND spir.sensecam_UTC_timestamp = stmp.sensecam_UTC_timestamp AND ig.gps_timestamp = @sGPSTimestamp; END; DROP PROCEDURE IF EXISTS `ELOG_GetNextImageTimestamp`; CREATE PROCEDURE `ELOG_GetNextImageTimestamp`(sTimestamp varchar(255)) BEGIN CALL ELOG_SetTimezone(sTimestamp); SELECT MIN(image_UTC_timestamp) AS timestamp FROM elog_sensecam_images WHERE image_UTC_timestamp > sTimestamp; END; DROP PROCEDURE IF EXISTS `ELOG_GetPreviousImageTimestamp`; CREATE PROCEDURE `ELOG_GetPreviousImageTimestamp`(sTimestamp varchar(255)) BEGIN CALL ELOG_SetTimezone(sTimestamp); SELECT MAX(image_UTC_timestamp) AS timestamp FROM elog_sensecam_images WHERE image_UTC_timestamp < sTimestamp; END; DROP PROCEDURE IF EXISTS `ELOG_GetSensecamRegionbyGPSCluster`; CREATE PROCEDURE `ELOG_GetSensecamRegionbyGPSCluster`( iGPSRegionID bigint ) BEGIN SELECT latitude_center, longitude_center, radius_km INTO @fLatidue, @fLongitude, @fRadius FROM elog_iphone_gps_region WHERE region_id = iGPSRegionID; DROP TABLE IF EXISTS tmpGPSCluster; CREATE TABLE `tmpGPSCluster` LIKE elog_iphone_gps; INSERT IGNORE INTO tmpGPSCluster SELECT DISTINCTROW ig.* FROM elog_iphone_gps AS ig HAVING ( DEGREES( ACOS( SIN(RADIANS(@fLatidue)) * SIN(RADIANS(ig.latitude)) + COS(RADIANS(@fLatidue)) * COS(RADIANS(ig.latitude)) * COS(RADIANS(@fLongitude - ig.longitude)) ) ) * 69.09 * 1.609344 ) <= @fRadius; SET @sMinTime = ( SELECT MIN(gps_UTC_timestamp) FROM tmpGPSCluster ); CALL ELOG_SetTimezone(@sMinTime); SELECT DISTINCTROW DATE_FORMAT(si.image_UTC_timestamp, '%a, %d %b %Y %T' ) AS start, 'false' AS durationEvent, CONCAT( "Taken on ", DATE_FORMAT(si.image_UTC_timestamp, '%a, %d %b %Y %T' ), "

", "at (", gc.latitude, ", ", gc.longitude, ")

" ) AS description, 'elog_sensecam_images' AS event_table, gc.longitude AS lognitude, gc.latitude AS latitude, DATE_FORMAT(gc.gps_UTC_timestamp, '%a, %d %b %Y %T' ) AS gps_timestamp, CONCAT("id=", si.image_UTC_timestamp) AS image, CONCAT("id=", si.image_UTC_timestamp) AS icon FROM tmpGPSCluster AS gc, elog_sensecam_images AS si WHERE si.sensecam_UTC_unix_timestamp BETWEEN gc.gps_UTC_unix_timestamp - 15 AND gc.gps_UTC_unix_timestamp + 15; END; DROP PROCEDURE IF EXISTS `ELOG_GetSensecamRegionbyPath`; CREATE PROCEDURE `ELOG_GetSensecamRegionbyPath`( iStartRegionID bigint, iEndRegionID bigint ) BEGIN SET @sStartTime = ( SELECT MIN(path_start_gps_UTC_timestamp) FROM oCachePathes ); CALL ELOG_SetTimezone(@sStartTime); SELECT DISTINCTROW DATE_FORMAT( ir.start_UTC_timestamp, '%a, %d %b %Y %T' ) AS start, DATE_FORMAT( ir.end_UTC_timestamp, '%a, %d %b %Y %T' ) AS end, 'true' AS durationEvent, CONCAT( "Taken images: ", ir.count, "

", "Path ID: ", ir.path_id ) AS description, 'oCachePathes' AS event_table, ir.start_region_id, ir.end_region_id, ir.path_id AS path_id FROM ( SELECT cp.start_region_id, cp.end_region_id, cp.path_id, MIN(si.image_UTC_timestamp) AS start_UTC_timestamp, MAX(si.image_UTC_timestamp) AS end_UTC_timestamp, COUNT(DISTINCT si.image_UTC_timestamp) AS count FROM elog_sensecam_images AS si, oCachePathes AS cp WHERE (si.image_UTC_timestamp BETWEEN cp.path_start_gps_UTC_timestamp AND cp.path_end_gps_UTC_timestamp) AND cp.start_region_id = iStartRegionID AND cp.end_region_id = iEndRegionID GROUP BY cp.start_region_id, cp.end_region_id, cp.path_id ) AS ir; END; DROP PROCEDURE IF EXISTS `ELOG_GetSensecamRegionbyTimespan`; CREATE PROCEDURE `ELOG_GetSensecamRegionbyTimespan`(iTimespan bigint, iTimestart bigint, iTimecenter bigint, iTimeend bigint, iMaxTimespan bigint) BEGIN CALL ELOG_SetTimezone(FROM_UNIXTIME(iTimestart)); SELECT DISTINCTROW DATE_FORMAT(FROM_UNIXTIME(ir.unix_timestamp_min), '%a, %d %b %Y %T' ) AS start, DATE_FORMAT(FROM_UNIXTIME(ir.unix_timestamp_max), '%a, %d %b %Y %T' ) AS end, 'true' AS durationEvent, CONCAT( "Taken images: ", ir.count, "

", "From: ", DATE_FORMAT(FROM_UNIXTIME(ir.unix_timestamp_min), '%a, %d %b %Y %T' ), "

", "To: ", DATE_FORMAT(FROM_UNIXTIME(ir.unix_timestamp_max), '%a, %d %b %Y %T' )) AS description, 'elog_sensecam_timespamp_region' AS event_table, ir.count AS event_count FROM elog_sensecam_timestamp_region AS ir WHERE ir.unix_timestamp_duration >= iTimespan AND ir.unix_timestamp_duration <= iMaxTimespan AND IF(iTimespan > 1000, 1, (ir.unix_timestamp_average BETWEEN iTimestart AND iTimeend) ) ORDER BY ir.unix_timestamp_duration DESC; END; DROP PROCEDURE IF EXISTS `ELOG_GetTimeSpanbyGPSLocation`; CREATE PROCEDURE `ELOG_GetTimeSpanbyGPSLocation`( IN fLatitude double, IN fLongitude double, IN fDistance double, IN fDay double ) label_proc:BEGIN DROP TABLE IF EXISTS tmpGPSTimeplot; CREATE TABLE `tmpGPSTimeplot` ( `id` INT NOT NULL AUTO_INCREMENT, `latitude` double NOT NULL, `longitude` double NOT NULL, `distance` double NOT NULL, `gps_UTC_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `timestamp_key` (`gps_UTC_timestamp`), KEY `latitude` (`latitude`), KEY `longitude` (`longitude`) ) ENGINE=MyISAM; INSERT IGNORE INTO tmpGPSTimeplot (latitude, longitude, distance, gps_UTC_timestamp) SELECT ig.latitude, ig.longitude, ( (ACOS(SIN(RADIANS(fLatitude)) * SIN(RADIANS(ig.latitude)) + COS(RADIANS(fLatitude)) * COS(RADIANS(ig.latitude)) * COS(RADIANS(fLongitude) - RADIANS(ig.longitude))) ) * 6371 ) AS distance, ig.gps_UTC_timestamp FROM elog_iphone_gps AS ig HAVING distance <= fDistance AND UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(ig.gps_UTC_timestamp) <= fDay*86400 ORDER BY ig.gps_UTC_timestamp ASC; SET @iDataCount = (SELECT COUNT(*) FROM tmpGPSTimeplot); IF (@iDataCount < 1) THEN LEAVE label_proc; END IF; DROP TABLE IF EXISTS tmpGPSTimespandata; CREATE TABLE `tmpGPSTimespandata` ( `a_id` INT NOT NULL, `b_id` INT NOT NULL, `a_gps_UTC_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `b_gps_UTC_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `iTimeDifference` INT NOT NULL, `distance` double NOT NULL, PRIMARY KEY (`a_id`, b_id), KEY `a_timestamp_key` (`a_gps_UTC_timestamp`), KEY `b_timestamp_key` (`b_gps_UTC_timestamp`), KEY `iTimeDifference` (`iTimeDifference`), KEY `distance` (`distance`) ) ENGINE=MyISAM; INSERT IGNORE INTO tmpGPSTimespandata SELECT a.id, b.id, a.gps_UTC_timestamp, b.gps_UTC_timestamp, UNIX_TIMESTAMP(b.gps_UTC_timestamp) - UNIX_TIMESTAMP(a.gps_UTC_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 tmpGPSTimeplot AS a, tmpGPSTimeplot AS b WHERE b.id = a.id+1; DROP TABLE IF EXISTS tmpGPSTimespan; CREATE TABLE `tmpGPSTimespan` ( `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; INSERT IGNORE INTO tmpGPSTimespan (id_start, id_end, id_span) SELECT IF (a.id = 1, 1, a.id+1) AS id_start, b.id AS id_end, MIN(b.id - a.id) AS id_span FROM (SELECT a_id AS id FROM tmpGPSTimespandata WHERE (iTimeDifference > 3600 AND distance > 0.01) OR a_id = 1) AS a, (SELECT a_id AS id FROM tmpGPSTimespandata WHERE (iTimeDifference > 3600 AND distance > 0.01) 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 tmpGPSTimespan); IF (@iSpanDataCount < 1) THEN SET @iMaxEndID = (SELECT MAX(id) FROM tmpGPSTimeplot); SET @iMaxSpanEndID = (SELECT MIN(id) FROM tmpGPSTimeplot); ELSE SET @iMaxEndID = (SELECT MAX(id) FROM tmpGPSTimeplot); SET @iMaxSpanEndID = (SELECT MAX(id_end) FROM tmpGPSTimespan); END IF; IF (@iMaxEndID > @iMaxSpanEndID) THEN INSERT INTO tmpGPSTimespan (id_start, id_end, id_span) SELECT @iMaxSpanEndID+1, @iMaxEndID, @iMaxEndID - @iMaxSpanEndID; END IF; DROP TABLE IF EXISTS tmpGPSTimeSpanResult; CREATE TABLE `tmpGPSTimeSpanResult` ( `id` INT NOT NULL, `latitude` double NOT NULL, `longitude` double NOT NULL, `distance` double NOT NULL, `gps_UTC_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`latitude`, longitude, gps_UTC_timestamp), KEY `timestamp_key` (`gps_UTC_timestamp`), KEY `latitude` (`latitude`), KEY `longitude` (`longitude`) ) ENGINE=MyISAM; INSERT IGNORE INTO tmpGPSTimeSpanResult (id, latitude, longitude, distance, gps_UTC_timestamp) SELECT ts.id, tp.latitude, tp.longitude, tp.distance, tp.gps_UTC_timestamp FROM tmpGPSTimeplot AS tp, tmpGPSTimespan AS ts WHERE tp.id BETWEEN ts.id_start AND ts.id_end ORDER BY tp.gps_UTC_timestamp ASC; SELECT * FROM tmpGPSTimeSpanResult; END; DROP PROCEDURE IF EXISTS `ELOG_GetTimezone`; CREATE PROCEDURE `ELOG_GetTimezone`(sTime timestamp) label_proc:BEGIN SELECT tz.hour_offset_integer FROM elog_iphone_gps AS iz INNER JOIN time_zone_offset AS tz ON (iz.gps_timezone = tz.`name`) ORDER BY ABS(TIMEDIFF(gps_UTC_timestamp, sTime)) ASC LIMIT 1; END; DROP PROCEDURE IF EXISTS `ELOG_QueryPath`; CREATE PROCEDURE `ELOG_QueryPath`( iStartRegionID bigint, iEndRegionID bigint ) BEGIN DROP TABLE IF EXISTS tmpQueryPathStart; CREATE TABLE `tmpQueryPathStart` ( `parent_region_id` int(11) NOT NULL, `region_id` int(11) default NULL, `latitude` double NOT NULL, `longitude` double NOT NULL, KEY `parent_region_id` (`parent_region_id`), KEY `region_id` (`region_id`), KEY `latitude` (`latitude`), KEY `longitude` (`longitude`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO tmpQueryPathStart SELECT DISTINCTROW * FROM elog_iphone_gps_clusters WHERE region_id = iStartRegionID; DROP TABLE IF EXISTS tmpQueryPathEnd; CREATE TABLE `tmpQueryPathEnd` ( `parent_region_id` int(11) NOT NULL, `region_id` int(11) default NULL, `latitude` double NOT NULL, `longitude` double NOT NULL, KEY `parent_region_id` (`parent_region_id`), KEY `region_id` (`region_id`), KEY `latitude` (`latitude`), KEY `longitude` (`longitude`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO tmpQueryPathEnd SELECT DISTINCTROW * FROM elog_iphone_gps_clusters WHERE region_id = iEndRegionID; DROP TABLE IF EXISTS tmpPathStart; CREATE TABLE tmpPathStart SELECT gp.* FROM oCacheTrentoGPSPath AS gp, tmpQueryPathStart AS qp WHERE ( DEGREES( ACOS( SIN(RADIANS(qp.latitude)) * SIN(RADIANS(gp.latitude)) + COS(RADIANS(qp.latitude)) * COS(RADIANS(gp.latitude)) * COS(RADIANS(qp.longitude - gp.longitude)) ) ) * 69.09 * 1.609344 ) <= 0.1; DROP TABLE IF EXISTS tmpPathEnd; CREATE TABLE tmpPathEnd SELECT gp.* FROM oCacheTrentoGPSPath AS gp, tmpQueryPathEnd AS qp WHERE ( DEGREES( ACOS( SIN(RADIANS(qp.latitude)) * SIN(RADIANS(gp.latitude)) + COS(RADIANS(qp.latitude)) * COS(RADIANS(gp.latitude)) * COS(RADIANS(qp.longitude - gp.longitude)) ) ) * 69.09 * 1.609344 ) <= 0.1; DROP TABLE IF EXISTS tmpFoundPath; CREATE TABLE tmpFoundPath SELECT DISTINCT gs.id, MIN(gs.gps_UTC_timestamp) AS starttime, MAX(ge.gps_UTC_timestamp) AS endtime FROM tmpPathStart AS gs INNER JOIN tmpPathEnd AS ge ON (gs.id = ge.id) GROUP BY (gs.id) HAVING starttime < endtime; SET @sStartTime = ( SELECT MIN(starttime) FROM tmpFoundPath ); CALL ELOG_SetTimezone(@sStartTime); SELECT tp.id, tp.latitude, tp.longitude, tp.distance, tp.gps_UTC_timestamp FROM oCacheTrentoGPSPath AS tp, tmpFoundPath AS ts WHERE tp.id = ts.id AND tp.gps_UTC_timestamp BETWEEN ts.starttime AND ts.endtime; END; DROP PROCEDURE IF EXISTS `ELOG_QueryPathFromRegionID`; CREATE PROCEDURE `ELOG_QueryPathFromRegionID`( iStartRegionID bigint ) BEGIN DROP TABLE IF EXISTS tmpStartGPSPoints; CREATE TABLE `tmpStartGPSPoints` ( `parent_region_id` int(11) NOT NULL, `region_id` int(11) default NULL, `latitude` double NOT NULL, `longitude` double NOT NULL, KEY `parent_region_id` (`parent_region_id`), KEY `region_id` (`region_id`), KEY `latitude` (`latitude`), KEY `longitude` (`longitude`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO tmpStartGPSPoints SELECT DISTINCTROW * FROM elog_iphone_gps_clusters WHERE region_id = iStartRegionID; DROP TABLE IF EXISTS tmpPathStart; CREATE TABLE tmpPathStart SELECT qp.region_id AS start_region_id, gp.id AS start_path_id, gp.latitude, gp.longitude, gp.distance, MIN(gp.gps_UTC_timestamp) AS gps_UTC_timestamp FROM oCacheTrentoGPSPath AS gp, tmpStartGPSPoints AS qp WHERE ( DEGREES( ACOS( SIN(RADIANS(qp.latitude)) * SIN(RADIANS(gp.latitude)) + COS(RADIANS(qp.latitude)) * COS(RADIANS(gp.latitude)) * COS(RADIANS(qp.longitude - gp.longitude)) ) ) * 69.09 * 1.609344 ) <= 0.1 GROUP BY gp.id; SET @sStartTime = ( SELECT MIN(gps_UTC_timestamp) FROM tmpPathStart ); CALL ELOG_SetTimezone(@sStartTime); DROP TABLE IF EXISTS oCachePathes; CREATE TABLE oCachePathes SELECT sp.start_region_id, tp.end_region_id, sp.path_id, sp.start_gps_UTC_timestamp AS path_start_gps_UTC_timestamp, sp.end_gps_UTC_timestamp AS path_end_gps_UTC_timestamp, MAX(TIMEDIFF(sp.end_gps_UTC_timestamp, sp.start_gps_UTC_timestamp)) AS time_interval FROM ( SELECT gc.region_id AS end_region_id, gc.latitude, gc.longitude, gr.radius_km FROM oCacheSelectedGPSRegions AS gr, elog_iphone_gps_clusters AS gc WHERE gc.region_id = gr.region_id ) AS tp, ( SELECT ps.start_region_id, tp.id AS path_id, tp.latitude, tp.longitude, tp.distance, ps.gps_UTC_timestamp AS start_gps_UTC_timestamp, tp.gps_UTC_timestamp AS end_gps_UTC_timestamp FROM oCacheTrentoGPSPath AS tp, tmpPathStart AS ps WHERE ps.start_path_id = tp.id AND tp.gps_UTC_timestamp >= ps.gps_UTC_timestamp ) AS sp WHERE sp.latitude = tp.latitude AND sp.longitude = tp.longitude AND TIMEDIFF(sp.end_gps_UTC_timestamp, sp.start_gps_UTC_timestamp) > 10 GROUP BY sp.start_region_id, tp.end_region_id, sp.path_id; SELECT * FROM oCachePathes; END; DROP PROCEDURE IF EXISTS `ELOG_QueryPathTimespan`; CREATE PROCEDURE `ELOG_QueryPathTimespan`(iPathID bigint, sTimeFrom varchar(255), sTimeTo varchar(255)) BEGIN CALL ELOG_SetTimezone(sTimeFrom); SELECT * FROM oCacheTrentoGPSPath WHERE id = iPathID AND gps_UTC_timestamp BETWEEN sTimeFrom AND sTimeTo; END; DROP PROCEDURE IF EXISTS `ELOG_SetTimezone`; CREATE PROCEDURE `ELOG_SetTimezone`(sTime varchar(100)) label_proc:BEGIN SET @sLocalTimezone = ( SELECT tz.hour_offset FROM elog_iphone_gps AS iz INNER JOIN time_zone_offset AS tz ON (iz.gps_timezone = tz.`name`) ORDER BY ABS(TIMEDIFF(gps_UTC_timestamp, sTime)) ASC LIMIT 1 ); SET time_zone = @sLocalTimezone; END;

Comments

elog_sensecam_people is now replaced with elog_sensecam_images_anonymize and elog_sensecam_images_anonymize_people