Table schemas

CREATE TABLE `iphone_gps` (
  `latitude` DOUBLE NOT NULL,
  `longitude` DOUBLE NOT NULL,
  `elevation` DOUBLE NOT NULL,
  `gps_timestamp` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `gps_timezone` VARCHAR(255) DEFAULT NULL,
  `gps_UTC_timestamp` TIMESTAMP NULL DEFAULT NULL,
  `gps_point` geometry NOT NULL,
  PRIMARY KEY (`latitude`,`longitude`,`elevation`,`gps_timestamp`),
  KEY `timestamp_key` (`gps_timestamp`),
  KEY `latitude` (`latitude`),
  KEY `longitude` (`longitude`),
  KEY `elevation` (`elevation`),
  KEY `gps_timestamp` (`gps_timestamp`),
  KEY `gps_timezone` (`gps_timezone`),
  KEY `gps_UTC_timestamp` (`gps_UTC_timestamp`),
  SPATIAL KEY `gps_point` (`gps_point`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
CREATE TABLE `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=MyISAM DEFAULT CHARSET=latin1;
 
CREATE TABLE `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=MyISAM DEFAULT CHARSET=latin1;
 
CREATE TABLE `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=MyISAM DEFAULT CHARSET=latin1;

Queries

Load clustered data

After E-Log:K-mean GPS spatial classification code, load data from the text file.

TRUNCATE TABLE iphone_gps_clusters;
LOAD DATA INFILE '/Users/pilhokim/Documents/Research/Software/OpenCVLibrary/gpskmean/build/Debug/iphone_gps_clusters.txt'
INTO TABLE iphone_gps_clusters;
 
TRUNCATE TABLE iphone_gps_region;
LOAD DATA INFILE '/Users/pilhokim/Documents/Research/Software/OpenCVLibrary/gpskmean/build/Debug/iphone_gps_regions.txt'
INTO TABLE iphone_gps_region;
 
TRUNCATE TABLE iphone_gps_region_hierarchy;
INSERT IGNORE INTO iphone_gps_region_hierarchy
SELECT
	parent_region_id,
	region_id
FROM
	iphone_gps_clusters
ORDER BY parent_region_id, region_id;

Get all GPS regions with radius over 300 km

CREATE DEFINER=`root`@`localhost` PROCEDURE `ELOG_GetGPSRegionbyRadius`(fRadius DOUBLE)
BEGIN
/*
	ELOG_GetGPSRegionbyRadius
 
	It returns GPS regions least bigger than the input radius
*/
 
SELECT
	ir.*
FROM
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
	iphone_gps_region_hierarchy AS ih
	INNER JOIN 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
	iht.child_region_max_radius < fRadius
ORDER BY ir.count DESC;
 
END;

Example:

mysql> CALL ELOG_GetGPSRegionbyRadius(300.0);
+-----------+-------+------------+-----------------+------------------+--------------+---------------+--------------+---------------+
| region_id | COUNT | radius_km  | latitude_center | longitude_center | latitude_max | longitude_max | latitude_min | longitude_min |
+-----------+-------+------------+-----------------+------------------+--------------+---------------+--------------+---------------+
|         1 | 17916 | 384.719684 |       35.790039 |       128.621506 |    37.569607 |    129.407074 |    34.763618 |    126.894478 |
|         2 | 16004 | 343.154151 |       45.970379 |        10.996577 |    46.288132 |     11.255697 |    43.774719 |      8.716264 |
+-----------+-------+------------+-----------------+------------------+--------------+---------------+--------------+---------------+
2 ROWS IN SET (0.30 sec)
 
Query OK, 0 ROWS affected (0.30 sec)