query

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_package_most_used_all`  AS  select `package_trx_171_1911`.`PACKAGE` AS `PACKAGE`,count(`package_trx_171_1911`.`PACKAGE`) AS `used`,`tb_package`.`colors` AS `colors` from (`package_trx_171_1911` join `tb_package` on(`package_trx_171_1911`.`PACKAGE` = `tb_package`.`package_name`)) group by `package_trx_171_1911`.`PACKAGE` order by count(`package_trx_171_1911`.`PACKAGE`) desc ;


CREATE TABLE `tb_package` (
  `_id` int(11) NOT NULL,
  `package_name` varchar(100) NOT NULL,
  `colors` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tb_package`
--

INSERT INTO `tb_package` (`_id`, `package_name`, `colors`) VALUES
(1, 'FREEDOM INTERNET 10GB', '#fc200352'),
(2, 'FREEDOM INTERNET 18GB', '#fc8c0352'),
(3, 'FREEDOM INTERNET 25GB', '#fce70352'),
(4, 'FREEDOM INTERNET 2GB', '#0ffc0352'),
(5, 'FREEDOM INTERNET 3GB', '#03fc9d52'),
(6, 'UNLIMITED JUMBO', '#03c2fc52'),
(7, 'Unlimited+10GB 30hr', '#0367fc52'),
(8, 'Unlimited+15GB 30hr', '#0307fc52'),
(9, 'UNLIMITED+1GB /7hr', '#5e03fc52'),
(10, 'Unlimited+2GB 30hr', '#c203fc52'),
(11, 'UNLIMITED+3GB 30hr', '#f803fc52'),
(12, 'UNLIMITED+500MB/2hr', '#fc03c652'),
(13, 'UNLIMITED+7GB 30hr', '#524b4f52');

CREATE TABLE `tb_outer_point` (
  `_id` int(11) NOT NULL,
  `cluster` varchar(200) NOT NULL,
  `lat` double NOT NULL,
  `lng` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tb_outer_point`
--

INSERT INTO `tb_outer_point` (`_id`, `cluster`, `lat`, `lng`) VALUES
(1, 'MC-SEMARANG KOTA_2', -6.97386, 110.4364),
(2, 'MC-SEMARANG KOTA_2', -6.98538, 110.44853),
(3, 'MC-SEMARANG KOTA_2', -6.99927, 110.45064),
(4, 'MC-SEMARANG KOTA_2', -7.00714, 110.45164),
(5, 'MC-SEMARANG KOTA_2', -7.01688, 110.45936),
(6, 'MC-SEMARANG KOTA_2', -7.04065, 110.46071),
(7, 'MC-SEMARANG KOTA_2', -7.04936, 110.46464),
(8, 'MC-SEMARANG KOTA_2', -7.05654, 110.4667),
(9, 'MC-SEMARANG KOTA_2', -7.05442, 110.45764),
(10, 'MC-SEMARANG KOTA_2', -7.04605, 110.45164),
(11, 'MC-SEMARANG KOTA_2', -7.04194, 110.43964),
(12, 'MC-SEMARANG KOTA_2', -7.03724, 110.42985),
(13, 'MC-SEMARANG KOTA_2', -7.03501, 110.42664),
(14, 'MC-SEMARANG KOTA_2', -7.03255, 110.41664),
(15, 'MC-SEMARANG KOTA_2', -7.03242, 110.40464),
(16, 'MC-SEMARANG KOTA_2', -7.03683, 110.40164),
(17, 'MC-SEMARANG KOTA_2', -7.03233, 110.39564),
(18, 'MC-SEMARANG KOTA_2', -7.0436, 110.38264),
(19, 'MC-SEMARANG KOTA_2', -7.05636, 110.38464),
(20, 'MC-SEMARANG KOTA_2', -7.05174, 110.36964),
(21, 'MC-SEMARANG KOTA_2', -7.04119, 110.36164),
(22, 'MC-SEMARANG KOTA_2', -7.03331, 110.35364),
(23, 'MC-SEMARANG KOTA_2', -7.02672, 110.34864),
(24, 'MC-SEMARANG KOTA_2', -7.01766, 110.33764),
(25, 'MC-SEMARANG KOTA_2', -6.99777, 110.34546),
(26, 'MC-SEMARANG KOTA_2', -6.9928, 110.35518),
(27, 'MC-SEMARANG KOTA_2', -6.99072, 110.36164),
(28, 'MC-SEMARANG KOTA_2', -6.97876, 110.39564),
(29, 'MC-SEMARANG KOTA_2', -6.98268, 110.40659),
(30, 'MC-SEMARANG KOTA_1', -6.99152, 110.28098),
(31, 'MC-SEMARANG KOTA_1', -7.00333, 110.26264),
(32, 'MC-SEMARANG KOTA_1', -7.04141, 110.26364),
(33, 'MC-SEMARANG KOTA_1', -7.06891, 110.26964),
(34, 'MC-SEMARANG KOTA_1', -7.10936, 110.27864),
(35, 'MC-SEMARANG KOTA_1', -7.11799, 110.28264),
(36, 'MC-SEMARANG KOTA_1', -7.1271, 110.30028),
(37, 'MC-SEMARANG KOTA_1', -7.13406, 110.35864),
(38, 'MC-SEMARANG KOTA_1', -7.13601, 110.38264),
(39, 'MC-SEMARANG KOTA_1', -7.10192, 110.43064),
(40, 'MC-SEMARANG KOTA_1', -7.09787, 110.44318),
(41, 'MC-SEMARANG KOTA_1', -7.09222, 110.45464),
(42, 'MC-SEMARANG KOTA_1', -7.07563, 110.45164),
(43, 'MC-SEMARANG KOTA_1', -7.05443, 110.44464),
(44, 'MC-SEMARANG KOTA_1', -7.04727, 110.43564),
(45, 'MC-SEMARANG KOTA_1', -7.04329, 110.42764),
(46, 'MC-SEMARANG KOTA_1', -7.03896, 110.42261),
(47, 'MC-SEMARANG KOTA_1', -7.0325862, 110.3865),
(48, 'MC-SEMARANG KOTA_1', -7.0138, 110.33264),
(56, 'ALL', -6.93018, 110.13664),
(57, 'ALL', -6.93765, 110.11512),
(58, 'ALL', -6.94866, 110.09064),
(59, 'ALL', -6.95472, 110.06364),
(60, 'ALL', -6.96451, 110.01864),
(61, 'ALL', -7.07776, 109.95864),
(62, 'ALL', -7.13045, 109.93764),
(63, 'ALL', -7.16666, 109.96564),
(64, 'ALL', -7.12116, 110.11564),
(65, 'ALL', -7.18247, 110.16764),
(66, 'ALL', -7.21171, 110.26064),
(67, 'ALL', -7.17132, 110.28764),
(68, 'ALL', -7.13601, 110.38264),
(69, 'ALL', -7.09222, 110.45464),
(70, 'ALL', -7.05654, 110.4667),
(71, 'ALL', -7.01688, 110.45936),
(72, 'ALL', -6.98538, 110.44853),
(73, 'ALL', -6.97386, 110.4364),
(74, 'ALL', -6.98268, 110.40659),
(75, 'ALL', -6.97876, 110.39564),
(76, 'ALL', -6.98468, 110.37578),
(77, 'ALL', -6.9928, 110.35518),
(78, 'ALL', -6.99777, 110.34546),
(79, 'ALL', -6.99152, 110.28098),
(80, 'ALL', -6.97999, 110.23364),
(81, 'ALL', -6.9399, 110.17564),
(84, 'MC-KENDAL', -6.93018, 110.13664),
(85, 'MC-KENDAL', -6.93765, 110.11512),
(86, 'MC-KENDAL', -6.94866, 110.09064),
(87, 'MC-KENDAL', -6.95472, 110.06364),
(88, 'MC-KENDAL', -6.96451, 110.01864),
(89, 'MC-KENDAL', -6.99974, 110.02164),
(90, 'MC-KENDAL', -7.07776, 109.95864),
(91, 'MC-KENDAL', -7.13045, 109.93764),
(92, 'MC-KENDAL', -7.16666, 109.96564),
(93, 'MC-KENDAL', -7.12116, 110.11564),
(94, 'MC-KENDAL', -7.18247, 110.16764),
(95, 'MC-KENDAL', -7.21171, 110.26064),
(96, 'MC-KENDAL', -7.17132, 110.28764),
(97, 'MC-KENDAL', -7.12447, 110.31564),
(98, 'MC-KENDAL', -7.08543, 110.22664),
(99, 'MC-KENDAL', -7.00277, 110.24327),
(100, 'MC-KENDAL', -6.97999, 110.23364),
(101, 'MC-KENDAL', -6.9399, 110.17564);