K-mean GPS Spatial Database

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;

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;

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;

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)