DROP DATABASE IF EXISTS singapore;
SHOW WARNINGS;

CREATE DATABASE IF NOT EXISTS singapore;
SHOW WARNINGS;

USE singapore;
SHOW WARNINGS;

DROP TABLE IF EXISTS line;
SHOW WARNINGS;

CREATE TABLE line (
   id int(8) unsigned NOT NULL auto_increment,
   color varchar(20) default NULL,
   name varchar(50) NOT NULL,
   PRIMARY KEY  (id)
) ENGINE=MyISAM AUTO_INCREMENT=77 DEFAULT CHARSET=utf8;
SHOW WARNINGS;

INSERT INTO line (id, color, name) VALUES
(69, 'red', 'North South Line'),
(70, 'green', 'East West Line'),
(71, 'purple', 'North East Line'),
(72, 'silver', 'Sengkang LRT West Loop'),
(73, 'silver', 'Sengkang LRT East Loop'),
(74, 'silver', 'Punggol LRT East Loop'),
(75, 'silver', 'Bukit Panjang LRT'),
(76, 'green', 'Changi Airport Shuttle');
SHOW WARNINGS;

DROP TABLE IF EXISTS poi;
SHOW WARNINGS;

CREATE TABLE poi (
   id int(8) unsigned NOT NULL auto_increment,
   name varchar(50) NOT NULL,
   type varchar(20) default NULL,
   utm_e int(6) NOT NULL,
   utm_n int(7) NOT NULL,
   PRIMARY KEY  (id)
) ENGINE=MyISAM AUTO_INCREMENT=95 DEFAULT CHARSET=utf8;
SHOW WARNINGS;

INSERT INTO poi (id, name, type, utm_e, utm_n) VALUES
(79, 'Victoria Concert Hall and Theatre', 'Theatre', 372202, 142443),
(80, 'Singapore City Hall', 'Sightseeing', 372202, 142698),
(81, 'Singapore Art Museum', 'Museum', 372134, 143432),
(82, 'New Supreme Court', 'Sightseeing', 372112, 142676),
(83, 'Singapore Cricket Club', 'Sport', 372304, 142560),
(84, 'Chinese Garden', 'Park', 358912, 148200),
(85, 'Esplanade Theatre', 'Theatre', 372655, 142601),
(86, 'SICE Center', 'Sightseeing', 372811, 143040),
(87, 'Raffles Hotel', 'Hotel', 372504, 143275),
(88, 'Little India', 'Sightseeing', 371941, 144699),
(89, 'Fullerton Hotel', 'Hotel', 372382, 142154),
(90, 'Singapore History Museum', 'Museum', 371873, 143344),
(91, 'Parliament House', 'Sightseeing', 372082, 142509),
(92, 'Raffles Statue', 'Sightseeing', 372281, 142342),
(93, 'Asian Civilisations Museum', 'Museum', 372177, 142357),
(94, 'Merlion Park', 'Park', 372428, 142305);
SHOW WARNINGS;

DROP TABLE IF EXISTS stop;
SHOW WARNINGS;

CREATE TABLE stop (
   id int(8) unsigned NOT NULL auto_increment,
   name varchar(50) NOT NULL,
   utm_e int(6) default NULL,
   utm_n int(7) default NULL,
   PRIMARY KEY  (id)
) ENGINE=MyISAM AUTO_INCREMENT=69 DEFAULT CHARSET=utf8;
SHOW WARNINGS;

INSERT INTO stop (id, name, utm_e, utm_n) VALUES
(1, 'Jurong East', 360020, 147497),
(2, 'Bukit Batok', 360924, 149176),
(3, 'Bukit Gombak', 361148, 150243),
(4, 'Choa Chu Kang', 360276, 153190),
(5, 'Yew Tee', 360613, 154491),
(6, 'Kranji', 362215, 157546),
(7, 'Marsiling', 363551, 158394),
(8, 'Woodlands', 364964, 158833),
(9, 'Admiralty', 366596, 159281),
(10, 'Sembawang', 368714, 160191),
(11, 'Yishun', 370386, 158034),
(12, 'Khatib', 370141, 156702),
(13, 'Yio Chu Kang', 371459, 152752),
(14, 'Ang Mo Kio', 372008, 151417),
(15, 'Bishan', 371846, 149342),
(16, 'Braddell', 371700, 148227),
(17, 'Toa Payoh', 371760, 147262),
(18, 'Novena', 371335, 145965),
(19, 'Newton', 370721, 145089),
(20, 'Orchard', 370064, 144204),
(21, 'Somerset', 370769, 143764),
(22, 'Dhoby Ghaut', 371610, 143579),
(23, 'City Hall', 372394, 142858),
(24, 'Raffles Place', 372181, 141828),
(25, 'Marina Bay', 372566, 140987),
(26, 'Pasir Ris', 383072, 151760),
(27, 'Tampines', 382588, 149638),
(28, 'Simei', 383595, 148433),
(29, 'Tanah Merah', 382705, 146762),
(30, 'Bedok', 380886, 146367),
(31, 'Kembangan', 379048, 146058),
(32, 'Eunos', 377914, 145894),
(33, 'Paya Lebar', 376803, 145729),
(34, 'Aljunied', 375681, 145549),
(35, 'Kallang', 374444, 145013),
(36, 'Lavender', 373483, 144530),
(37, 'Bugis', 372728, 143849),
(40, 'Tanjong Pagar', 371667, 141128),
(41, 'Outram Park', 370869, 141643),
(42, 'Tiong Bahru', 369530, 142199),
(43, 'Redhill', 368386, 142561),
(44, 'Queenstown', 367172, 143103),
(45, 'Commonwealth', 366260, 144030),
(46, 'Buona Vista', 365430, 144534),
(47, 'Dover', 364113, 145017),
(48, 'Clementi', 362624, 145466),
(50, 'Chinese Garden', 359026, 148373),
(51, 'Lakeside', 357692, 148618),
(52, 'Boon Lay', 356069, 148010),
(53, 'Expo', 384496, 147624),
(54, 'Changi Airport', 387546, 150054),
(55, 'Harbour Front', 368939, 139892),
(57, 'Chinatown', 371289, 141998),
(58, 'Clarke Quay', 371614, 142293),
(60, 'Little India', 371997, 144438),
(61, 'Farrer Park', 372427, 145053),
(62, 'Boon Keng', 373315, 145867),
(63, 'Potong Pasir', 374171, 147190),
(64, 'Serangoon', 374683, 149174),
(65, 'Kovan', 375880, 150345),
(66, 'Hougang', 376756, 151497),
(67, 'Sengkang', 377137, 153898),
(68, 'Punggol', 377842, 155319),
(69, 'Cheng Lim', 376890, 154385),
(70, 'Farmway', 376393, 154475),
(71, 'Kupang', 375505, 154588),
(72, 'Thanggam', 374880, 154490),
(73, 'Fernvale', 374949, 153894),
(74, 'Layar', 375370, 153913),
(75, 'Tongkang', 376014, 153614),
(76, 'Renjong', 376500, 153319),
(77, 'Compassvale', 377695, 154170),
(78, 'Rumbia', 378299, 153838),
(79, 'Bakau', 378229, 153451),
(80, 'Kangkar', 377881, 153013),
(81, 'Ranggung', 377341, 152982),
(82, 'Cove', 378282, 154701),
(83, 'Meridian', 378645, 154407),
(84, 'Coral Edge', 379019, 154099),
(85, 'Riviera', 379429, 154175),
(86, 'Kadaloor', 379450, 154727),
(87, 'Oasis', 379035, 155026),
(88, 'Damai', 378648, 155317),
(89, 'South View', 360405, 152606),
(90, 'Keat Hong', 360829, 152423),
(91, 'Teck Whye', 361261, 152224),
(92, 'Phoenix', 361787, 152406),
(93, 'Bukit Panjang', 362456, 152368),
(94, 'Petir', 362883, 152277),
(95, 'Pending', 363308, 152162),
(96, 'Bangkit', 363459, 152617),
(97, 'Fajar', 363288, 153097),
(98, 'Segar', 363175, 153387),
(99, 'Jelapang', 362574, 153330),
(100, 'Senja', 362300, 152940),
(101, 'Ten Mile Junction', 362040, 152669);
SHOW WARNINGS;

DROP TABLE IF EXISTS stop_has_line;
SHOW WARNINGS;

CREATE TABLE stop_has_line (
   id_line int(8) unsigned NOT NULL default '0',
   id_stop int(8) unsigned NOT NULL default '0',
   duration int(8) NOT NULL,
   distance int(8) NOT NULL,
   `order` int(8) unsigned NOT NULL,
   PRIMARY KEY  (id_line, id_stop, `order`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SHOW WARNINGS;

INSERT INTO stop_has_line (id_line, id_stop, duration, distance, `order`) VALUES
(69, 1, 0, 0, 0),
(69, 2, 3, 2020, 1),
(69, 3, 5, 3226, 2),
(69, 4, 9, 6432, 3),
(69, 5, 11, 7892, 4),
(69, 6, 16, 12007, 5),
(69, 7, 18, 13627, 6),
(69, 8, 21, 15197, 7),
(69, 9, 23, 16917, 8),
(69, 10, 26, 19237, 9),
(69, 11, 30, 22357, 10),
(69, 12, 32, 23731, 11),
(69, 13, 38, 28601, 12),
(69, 14, 40, 30113, 13),
(69, 15, 43, 32439, 14),
(69, 16, 45, 33529, 15),
(69, 17, 47, 34411, 16),
(69, 18, 49, 35866, 17),
(69, 19, 51, 36996, 18),
(69, 20, 53, 38236, 19),
(69, 21, 55, 39142, 20),
(69, 22, 56, 40102, 21),
(69, 23, 58, 41072, 22),
(69, 24, 60, 42202, 23),
(69, 25, 62, 43176, 24),
(70, 26, 0, 0, 0),
(70, 27, 3, 2362, 1),
(70, 28, 5, 3957, 2),
(70, 29, 7, 6407, 3),
(70, 30, 11, 8267, 4),
(70, 31, 14, 10137, 5),
(70, 32, 16, 11372, 6),
(70, 33, 18, 12528, 7),
(70, 34, 20, 13622, 8),
(70, 35, 22, 14963, 9),
(70, 36, 24, 16017, 10),
(70, 37, 26, 17104, 11),
(70, 23, 28, 18183, 12),
(70, 24, 30, 19171, 13),
(70, 40, 32, 20322, 14),
(70, 41, 34, 21227, 15),
(70, 42, 36, 22753, 16),
(70, 43, 38, 24057, 17),
(70, 44, 40, 25418, 18),
(70, 45, 42, 26640, 19),
(70, 46, 44, 27633, 20),
(70, 47, 46, 29089, 21),
(70, 48, 48, 30720, 22),
(70, 1, 51, 34344, 23),
(70, 50, 54, 35658, 24),
(70, 51, 57, 36928, 25),
(70, 52, 59, 38687, 26),
(71, 55, 0, 0, 0),
(71, 41, 4, 2633, 1),
(71, 57, 5, 3445, 2),
(71, 58, 7, 3989, 3),
(71, 22, 9, 5314, 4),
(71, 60, 11, 6185, 5),
(71, 61, 12, 6919, 6),
(71, 62, 15, 8175, 7),
(71, 63, 17, 9751, 8),
(71, 64, 21, 11939, 9),
(71, 65, 23, 13585, 10),
(71, 66, 26, 15140, 11),
(71, 67, 31, 17452, 12),
(71, 68, 33, 19221, 13),
(72, 67, 14, 6331, 9),
(72, 69, 12, 5505, 8),
(72, 70, 11, 4969, 7),
(72, 71, 10, 4071, 6),
(72, 72, 8, 3333, 5),
(72, 73, 6, 2719, 4),
(72, 74, 5, 2249, 3),
(72, 75, 4, 1470, 2),
(72, 76, 2, 884, 1),
(72, 67, 0, 0, 0),
(73, 67, 0, 0, 0),
(73, 77, 2, 853, 1),
(73, 78, 3, 1562, 2),
(73, 79, 5, 2014, 3),
(73, 80, 6, 2587, 4),
(73, 81, 8, 3427, 5),
(73, 67, 10, 4418, 6),
(74, 68, 0, 0, 0),
(74, 82, 2, 940, 1),
(74, 83, 3, 1410, 2),
(74, 84, 4, 1920, 3),
(74, 85, 5, 2465, 4),
(74, 86, 7, 3185, 5),
(74, 87, 8, 3695, 6),
(74, 88, 9, 4150, 7),
(74, 68, 10, 5275, 8),
(75, 4, 0, 0, 0),
(75, 89, 2, 567, 1),
(75, 90, 4, 1031, 2),
(75, 91, 5, 1511, 3),
(75, 92, 6, 2092, 4),
(75, 101, 7, 2560, 5),
(75, 93, 8, 2911, 6),
(75, 94, 10, 3369, 7),
(75, 95, 12, 3897, 8),
(75, 96, 13, 4396, 9),
(75, 97, 15, 5241, 10),
(75, 98, 16, 5718, 11),
(75, 99, 18, 6401, 12),
(75, 100, 19, 6886, 13),
(75, 93, 21, 7558, 14),
(75, 101, 22, 7909, 15),
(75, 92, 23, 8377, 16),
(75, 91, 24, 8958, 17),
(75, 90, 25, 9438, 18),
(75, 89, 27, 9902, 19),
(75, 4, 29, 10469, 20),
(76, 29, 0, 0, 0),
(76, 53, 2, 1965, 1),
(76, 54, 5, 5972, 2);
SHOW WARNINGS;
