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.

 

openBmap cell location database

Add GSM and Wifi reverse geocode databases

Within a number of available cell location database, we chose openBmap and process their data to import into the database. This section briefly shows how we processed them.

As of now (Jan 10, 2012), openBmap data sets are composed of cellular map data and wifi map data  in which data structures are slightly different. So for this experiment we manage both using two different databases. 

Import GSM data

Data tables are prepared based on their log formats, which is in fact imcomplete, and thus inspecting actual files.

 

-- MySQL dump 10.13  Distrib 5.5.11, for Win64 (x86)
--
-- Host: localhost    Database: openbmap
-- ------------------------------------------------------
-- Server version	5.5.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `cell`
--

DROP TABLE IF EXISTS `cell`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cell` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `cellid` int(11) DEFAULT NULL,
  `mcc` int(11) DEFAULT NULL,
  `mnc` int(11) DEFAULT NULL,
  `lac` int(11) DEFAULT NULL,
  `ss` int(11) DEFAULT NULL,
  `act` varchar(50) DEFAULT NULL,
  `rxlev` int(11) DEFAULT NULL,
  `psc` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cellid` (`cellid`)
) ENGINE=InnoDB AUTO_INCREMENT=1512570 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `cell_neighbour`
--

DROP TABLE IF EXISTS `cell_neighbour`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cell_neighbour` (
  `servingid` int(11) NOT NULL,
  `neighborhoodid` int(11) NOT NULL,
  PRIMARY KEY (`servingid`,`neighborhoodid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `gps`
--

DROP TABLE IF EXISTS `gps`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `gps` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `time` varchar(15) DEFAULT NULL,
  `lng` double DEFAULT NULL,
  `lat` double DEFAULT NULL,
  `alt` double DEFAULT NULL,
  `hdg` double DEFAULT NULL,
  `spe` double DEFAULT NULL,
  `accuracy` double DEFAULT NULL,
  `hdop` double DEFAULT NULL,
  `vdop` double DEFAULT NULL,
  `pdop` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `time` (`time`),
  KEY `lng` (`lng`),
  KEY `lat` (`lat`)
) ENGINE=InnoDB AUTO_INCREMENT=268229 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `logfile`
--

DROP TABLE IF EXISTS `logfile`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `logfile` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `manufacturer` varchar(255) DEFAULT NULL,
  `model` varchar(255) DEFAULT NULL,
  `revision` varchar(50) DEFAULT NULL,
  `swid` varchar(255) DEFAULT NULL,
  `swver` varchar(50) DEFAULT NULL,
  `filepath` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9506 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `scan`
--

DROP TABLE IF EXISTS `scan`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `scan` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `logfileid` int(11) DEFAULT NULL,
  `time` varchar(15) DEFAULT NULL,
  `distance` double DEFAULT NULL,
  `gpsid` int(11) DEFAULT NULL,
  `servingid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `logfileid` (`logfileid`),
  KEY `time` (`time`),
  KEY `gpsid` (`gpsid`),
  KEY `serviningid` (`servingid`)
) ENGINE=InnoDB AUTO_INCREMENT=268228 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-01-10 13:55:35

query($query))) {
		printf("Table truncation error: %s\n", $mysqli->error);
		exit();
	}
}

// Recursively read all files in the selected directory 
$ite = new RecursiveDirectoryIterator($path);
foreach (new RecursiveIteratorIterator($ite) as $filename=>$cur) {
	if (strstr($cur, ".xml") != "") {
		echo "Processing: ".$cur->getPathName().PHP_EOL;

		$filepath = str_ireplace("\\", "/", $cur->getPathname());

		$doc = new DOMDocument();
		$doc->load($filepath);

		$xpath = new DOMXpath($doc);
		$elements = $xpath->query("//*");

		foreach ($elements as $element) {
			if ($element->nodeName == "logfile") {
				$query = "INSERT IGNORE INTO logfile (manufacturer, model, revision, swid, swver, filepath) VALUES "; 

				$manufacturer = $element->getAttribute('manufacturer');
				$model = $element->getAttribute('model');
				$revision = $element->getAttribute('revision');
				$swid = $element->getAttribute('swid');
				$swver = $element->getAttribute('swver');

				$query .= "('$manufacturer','$model','$revision','$swid','$swver','$filepath')";

				if (!($result = $mysqli->query($query))) {
			   	printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   }  

				$logfileid = $mysqli->insert_id;

				$scans = $element->childNodes;
				foreach ($scans as $scan) {
					if ($scan->nodeName != "scan") continue;

					$time = $scan->getAttribute('time');
					$distance = $scan->getAttribute('distance');

					$childs = $scan->childNodes;

					$gsmnieghbours = array();
					foreach ($childs as $child) {
						if ($child->nodeName == "gps") {
							$gps_time = $child->getAttribute("time");
							$lng = $child->getAttribute("lng");
							$lat = $child->getAttribute("lat");
							$alt = $child->getAttribute("alt");
							$hdg = $child->getAttribute("hdg");
							$spe = $child->getAttribute("spe");
							$accuracy = $child->getAttribute("accuracy");
							$hdop = $child->getAttribute("hdop");
							$vdop = $child->getAttribute("vdop");
							$pdop = $child->getAttribute("pdop");
							
							$query = "INSERT IGNORE INTO gps (time, lng, lat, alt, hdg, spe, accuracy, hdop, vdop, pdop) VALUES "; 
							$query .= "('$gps_time','$lng','$lat','$alt','$hdg','$spe','$accuracy','$hdop','$vdop','$pdop')";

							if (!($result = $mysqli->query($query))) {
			   				printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   			}  

							$gpsid = $mysqli->insert_id;
						}
						else if ($child->nodeName == "gsmserving" || $child->nodeName == "gsmneighbour") {
							$mcc = $child->getAttribute("mcc");
							$mnc = $child->getAttribute("mnc");
							$lac = $child->getAttribute("lac");
							$cellid = $child->getAttribute("id");
							$ss = $child->getAttribute("ss");
							$act = $child->getAttribute("act");
							$rxlev = $child->getAttribute("rxlev");
							
							$query = "INSERT IGNORE INTO cell (mcc, mnc, lac, cellid, ss, act, rxlev) VALUES "; 
							$query .= "('$mcc','$mnc','$lac','$cellid','$ss','$act','$rxlev')";

							if (!($result = $mysqli->query($query))) {
			   				printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   			}  
							else {
								if ($child->nodeName == "gsmserving") {
									$servingid =  $mysqli->insert_id;
								}
								else if ($child->nodeName == "gsmneighbour") {
									array_push($gsmnieghbours, $mysqli->insert_id);
								}
							}
						}
					}

					$query = "INSERT IGNORE INTO scan (logfileid, time, distance, gpsid, servingid) VALUES "; 
					$query .= "('$logfileid','$time','$distance','$gpsid','$servingid')";

					if (!($result = $mysqli->query($query))) {
			   		printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   	}  
					else $scanid = $mysqli->insert_id;

					$query_pattern = "INSERT IGNORE INTO cell_neighbour (servingid, neighborhoodid) VALUES "; 
					foreach ($gsmnieghbours as $neighborhoodid) {
						$query = $query_pattern;
						$query .= "('$servingid','$neighborhoodid')";

						if (!($result = $mysqli->query($query))) {
			   			printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   		}  
					}
				}
			}
		}
	}
}	

$mysqli->close();

?>

 

Import Wifi location data

Data tables are prepared based on their log formats, which is in fact imcomplete, and thus inspecting actual files.

-- MySQL dump 10.13  Distrib 5.5.11, for Win64 (x86)
--
-- Host: localhost    Database: openbmap_wifi
-- ------------------------------------------------------
-- Server version	5.5.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `gps`
--

DROP TABLE IF EXISTS `gps`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `gps` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `time` varchar(15) DEFAULT NULL,
  `lng` double DEFAULT NULL,
  `lat` double DEFAULT NULL,
  `alt` double DEFAULT NULL,
  `hdg` double DEFAULT NULL,
  `spe` double DEFAULT NULL,
  `accuracy` double DEFAULT NULL,
  `hdop` double DEFAULT NULL,
  `vdop` double DEFAULT NULL,
  `pdop` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `time` (`time`),
  KEY `lng` (`lng`),
  KEY `lat` (`lat`)
) ENGINE=InnoDB AUTO_INCREMENT=154027 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `logfile`
--

DROP TABLE IF EXISTS `logfile`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `logfile` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `manufacturer` varchar(255) DEFAULT NULL,
  `model` varchar(255) DEFAULT NULL,
  `revision` varchar(50) DEFAULT NULL,
  `swid` varchar(255) DEFAULT NULL,
  `swver` varchar(50) DEFAULT NULL,
  `filepath` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3408 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `scan`
--

DROP TABLE IF EXISTS `scan`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `scan` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `logfileid` int(11) DEFAULT NULL,
  `time` varchar(15) DEFAULT NULL,
  `distance` double DEFAULT NULL,
  `gpsid` int(11) DEFAULT NULL,
  `wifiapid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `logfileid` (`logfileid`),
  KEY `time` (`time`),
  KEY `gpsid` (`gpsid`),
  KEY `serviningid` (`wifiapid`)
) ENGINE=InnoDB AUTO_INCREMENT=77014 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wifiap`
--

DROP TABLE IF EXISTS `wifiap`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wifiap` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `bssid` varchar(12) DEFAULT NULL,
  `md5essid` varchar(31) DEFAULT NULL,
  `capa` varchar(255) DEFAULT NULL,
  `ss` int(11) DEFAULT NULL,
  `ntiu` int(11) DEFAULT NULL,
  `enc` int(11) DEFAULT NULL,
  `im` int(11) DEFAULT NULL,
  `enc_type` varchar(50) DEFAULT NULL,
  `chan` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cellid` (`bssid`)
) ENGINE=InnoDB AUTO_INCREMENT=268548 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-01-10 13:54:59

query($query))) {
		printf("Table truncation error: %s\n", $mysqli->error);
		exit();
	}
}

// Recursively read all files in the selected directory 
$ite = new RecursiveDirectoryIterator($path);
foreach (new RecursiveIteratorIterator($ite) as $filename=>$cur) {
	if (strstr($cur, ".xml") != "") {
		echo "Processing: ".$cur->getPathName().PHP_EOL;

		$filepath = str_ireplace("\\", "/", $cur->getPathname());

		$doc = new DOMDocument();
		$doc->load($filepath);

		$xpath = new DOMXpath($doc);
		$elements = $xpath->query("//*");

		foreach ($elements as $element) {
			if ($element->nodeName == "logfile") {
				$query = "INSERT IGNORE INTO logfile (manufacturer, model, revision, swid, swver, filepath) VALUES "; 

				$manufacturer = $element->getAttribute('manufacturer');
				$model = $element->getAttribute('model');
				$revision = $element->getAttribute('revision');
				$swid = $element->getAttribute('swid');
				$swver = $element->getAttribute('swver');

				$query .= "('$manufacturer','$model','$revision','$swid','$swver','$filepath')";

				if (!($result = $mysqli->query($query))) {
			   	printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   }  

				$logfileid = $mysqli->insert_id;

				$scans = $element->childNodes;
				foreach ($scans as $scan) {
					if ($scan->nodeName != "scan") continue;

					$time = $scan->getAttribute('time');
					$distance = $scan->getAttribute('distance');

					$childs = $scan->childNodes;

					$gsmnieghbours = array();
					foreach ($childs as $child) {
						if ($child->nodeName == "gps") {
							$gps_time = $child->getAttribute("time");
							$lng = $child->getAttribute("lng");
							$lat = $child->getAttribute("lat");
							$alt = $child->getAttribute("alt");
							$hdg = $child->getAttribute("hdg");
							$spe = $child->getAttribute("spe");
							$accuracy = $child->getAttribute("accuracy");
							$hdop = $child->getAttribute("hdop");
							$vdop = $child->getAttribute("vdop");
							$pdop = $child->getAttribute("pdop");
							
							$query = "INSERT IGNORE INTO gps (time, lng, lat, alt, hdg, spe, accuracy, hdop, vdop, pdop) VALUES "; 
							$query .= "('$gps_time','$lng','$lat','$alt','$hdg','$spe','$accuracy','$hdop','$vdop','$pdop')";

							if (!($result = $mysqli->query($query))) {
			   				printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   			}  

							$gpsid = $mysqli->insert_id;
						}
						else if ($child->nodeName == "wifiap") {
							$bssid = $child->getAttribute("bssid");
							$md5essid = $child->getAttribute("md5essid");
							$capa = $child->getAttribute("capa");
							$ss = $child->getAttribute("ss");
							$ntiu = $child->getAttribute("ntiu");
							$enc = $child->getAttribute("enc");
							$im = $child->getAttribute("im");
							$enc_type = $child->getAttribute("enc_type");
							$chan = $child->getAttribute("chan");
							
							$query = "INSERT IGNORE INTO wifiap (bssid, md5essid, capa, ss, ntiu, enc, im, enc_type, chan) VALUES "; 
							$query .= "('$bssid','$md5essid','$capa','$ss','$ntiu','$enc','$im','$enc_type','$chan')";

							if (!($result = $mysqli->query($query))) {
			   				printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   			}  
							else {
								$wifiapid =  $mysqli->insert_id;
							}
						}
					}

					$query = "INSERT IGNORE INTO scan (logfileid, time, distance, gpsid, wifiapid) VALUES "; 
					$query .= "('$logfileid','$time','$distance','$gpsid','$wifiapid')";

					if (!($result = $mysqli->query($query))) {
			   		printf("Data insert error: %s, %s\n", $query, $mysqli->error);
			   	}  
					else $scanid = $mysqli->insert_id;
				}
			}
		}
	}
}	

$mysqli->close();

?>