-- 
-- DATABASE: Shanghai
--

DROP DATABASE IF EXISTS shanghai;
SHOW WARNINGS;
CREATE DATABASE IF NOT EXISTS shanghai;
SHOW WARNINGS;
USE shanghai;
SHOW WARNINGS;

-- -- ------------------------------------------
-- Table DISTRICTS
--

DROP TABLE IF EXISTS districts;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS districts (
  id INT4,
  name VARCHAR(40),
  PRIMARY KEY (id)
);
SHOW WARNINGS;
INSERT INTO districts (id, name) VALUES
(1,'Changning'),
(2,'Chengjiaqiao'),
(3,'Dapuqiao'),
(4,'Hongqiao'),
(5,'Huamu'),
(6,'Huangpu'),
(7,'Jing An'),
(8,'Laoximen'),
(9,'Longhua'),
(10,'Lujiazui'),
(11,'Luwan'),
(12,'Nanshi'),
(13,'Ni Cheng Qiao'),
(14,'Qibao'),
(15,'Xuhui'),
(16,'Xujiahui');
SHOW WARNINGS;

-- ------------------------------------------
-- Table STATIONS
--

DROP TABLE IF EXISTS stations;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS stations (
  id INT4,
  name VARCHAR(60),
  latitude DOUBLE,
  longitude DOUBLE,
  PRIMARY KEY (id)
);
SHOW WARNINGS;
INSERT INTO stations (id, name, latitude, longitude) VALUES
(1,'An Shan Xin Cun',31.272893,121.509562),
(2,'Bao An Highway',31.369614,121.431069),
(3,'Bao Shan Road',31.250974,121.475980),
(4,'Bao Yang Road',31.396616,121.479650),
(5,'Bei Qiao',31.047033,121.409311),
(6,'Bei Xin Jing',31.216912,121.374035),
(7,'Bei Yang Jing Road',31.239499,121.552799),
(8,'Bo Xing Road',31.262760,121.586251),
(9,'Cao Bao Road',31.182351,121.436391),
(10,'Cao He Jing Hi-Tech Park',31.171079,121.399012),
(11,'Cao Xi Road',31.176706,121.438011),
(12,'Cao Yang Road',31.239747,121.418409),
(13,'Century Avenue',31.230151,121.525462),
(14,'Century Park',31.209884,121.552563),
(15,'Chang Jiang Road South',31.332323,121.492074),
(16,'Chang Pin Road',31.232803,121.443450),
(17,'Chang Qin Road',31.174494,121.485808),
(18,'Chang Shou Road',31.240453,121.437818),
(19,'Chang Shu Road',31.212948,121.449738),
(20,'Chi Feng Road',31.281943,121.482396),
(21,'Chun Shen Road',31.098714,121.385171),
(22,'Da Bai Shu',31.290360,121.483319),
(23,'Da Lian Road',31.258101,121.512984),
(24,'Da Mu Qiao Road',31.194264,121.463717),
(25,'Da Shi Jie',31.227133,121.479542),
(26,'De Ping Road',31.245058,121.563313),
(27,'Dong An Road',31.190841,121.454737),
(28,'Dong Bao Xing Road',31.260210,121.479907),
(29,'Dong Chang Road',31.233665,121.515183),
(30,'Dong Chuan Road',31.017375,121.420169),
(31,'Dong Jing',31.087395,121.230118),
(32,'Dong Jing Road',31.293899,121.590328),
(33,'Dong Ming Road',31.172401,121.510892),
(34,'Fang Dian Road',31.222637,121.559472),
(35,'Fu Jing Road',31.392953,121.424890),
(36,'Gang Cheng Road',31.353527,121.575007),
(37,'Gao Ke Road West',31.185628,121.509765),
(38,'Gao Qing Road',31.159677,121.516836),
(39,'Gong Fu Xin Cun',31.355653,121.434073),
(40,'Gong Kang Road',31.317531,121.447763),
(41,'Gu Cun Road',31.327320,121.364164),
(42,'Gui Lin Road',31.174586,121.416886),
(43,'Hai Lun Road',31.257853,121.488479),
(44,'Han Zhong Road',31.241866,121.458557),
(45,'Hang Jin Road',31.331774,121.596208),
(46,'Haung Xing Road',31.278514,121.527607),
(47,'He Chuan Road',31.166360,121.384377),
(48,'Heng Shan Road',31.204818,121.446497),
(49,'Hong Kou Football Stadium',31.269418,121.479692),
(50,'Hong Qiao Airport',31.194154,121.347685),
(51,'Hong Qiao Road',31.197100,121.421081),
(52,'Hu Lan Road',31.340223,121.437936),
(53,'Hu Nan Road',31.194540,121.550117),
(54,'Hu Tai Road',31.314818,121.408689),
(55,'Hua Ning Road',31.007205,121.393690),
(56,'Hua Xia Road West',31.154535,121.518896),
(57,'Huang Pi Road South',31.222876,121.473255),
(58,'Huang Xing Park',31.296558,121.532811),
(59,'Jian Chuan Road',31.027857,121.416113),
(60,'Jiang Pu Road',31.275195,121.518874),
(61,'Jiang Su Road',31.219371,121.431434),
(62,'Jiang Wan Town',31.305340,121.484950),
(63,'Jiang Yang Road North',31.403502,121.440511),
(64,'Jin Jiang Park',31.141883,121.413517),
(65,'Jin Ping Road',31.011233,121.410191),
(66,'Jin Qiao Road',31.256762,121.581488),
(67,'Jin Sha Jiang Road',31.232610,121.413227),
(68,'Jin Xiu Road',31.187895,121.540289),
(69,'Jing An Temple',31.224307,121.448075),
(70,'Jiu Ting',31.136207,121.314898),
(71,'Ju Feng Road',31.279853,121.589491),
(72,'Lan Cun Road',31.210168,121.526996),
(73,'Lan Gao Road',31.259917,121.422336),
(74,'Lao Xi Men',31.218922,121.483072),
(75,'Lian Hua Road',31.130477,121.401865),
(76,'Lin Yi Xin Cun',31.193457,121.516836),
(77,'Ling Pin Road',31.262017,121.500828),
(78,'Ling Yan Road South',31.147245,121.494648),
(79,'Long Cao Road',31.169381,121.444255),
(80,'Long Yang Road',31.201533,121.566017),
(81,'Lou Shan Guan Road',31.211590,121.402788),
(82,'Lu Ban Road',31.198890,121.475755),
(83,'Lu Jia Bang Road',31.211939,121.486205),
(84,'Lu Jia Zui',31.238197,121.501622),
(85,'Lu Xiang Road',31.334944,121.359358),
(86,'Luo Nan Xin Cun',31.378481,121.338029),
(87,'Min Hang Development Zone',30.998911,121.363285),
(88,'Min Sheng Road',31.235573,121.542542),
(89,'Nan Pu Bridge',31.209416,121.498307),
(90,'Nanjing Road East',31.237334,121.483855),
(91,'Nanjing Road West',31.230748,121.460767),
(92,'Nen Jiang Road',31.314827,121.532006),
(93,'Pan Guang Road',31.351108,121.353521),
(94,'Peng Pu Xin Cun',31.306312,121.448836),
(95,'People''s Square',31.232931,121.475251),
(96,'Pu Dian Road',31.222848,121.532339),
(97,'Pu Dong Avenue',31.240288,121.519990),
(98,'Pu Jiang Nan Pu',31.182553,121.458578),
(99,'Pu Jiang Yao Hua',31.173796,121.470423),
(100,'Pudong International Airport',31.150532,121.805871),
(101,'Qi Bao',31.155160,121.348693),
(102,'Qi Hua Road',31.319456,121.381845),
(103,'Qu Fu Road',31.242416,121.471474),
(104,'Qu Yang Road',31.276634,121.490668),
(105,'Shan Xi Road South',31.217298,121.460080),
(106,'Shang Da Road',31.321472,121.397166),
(107,'Shang Nan Road',31.148531,121.507083),
(108,'Shanghai Children''s Medical Center',31.204075,121.523627),
(109,'Shanghai Circus World',31.280385,121.452184),
(110,'Shanghai Indoor Stadium',31.182351,121.436391),
(111,'Shanghai Railway Station',31.249571,121.458685),
(112,'Shanghai Science and Technology Museum',31.219591,121.542993),
(113,'Shanghai South Railway Station',31.155059,121.427647),
(114,'Shanghai Stadium',31.185591,121.443461),
(115,'Shanghai University',31.320977,121.391158),
(116,'She Shan',31.105420,121.228766),
(117,'Shi Guang Road',31.321976,121.532038),
(118,'Shi Long Road',31.159673,121.440682),
(119,'Shui Chan Road',31.381815,121.488361),
(120,'Si Jing',31.118537,121.258142),
(121,'Si Ping Road',31.274966,121.501354),
(122,'Song Bin Road',31.371776,121.493018),
(123,'Song Fa Road',31.343961,121.500034),
(124,'Song Hong Road',31.218417,121.359057),
(125,'Song Jiang University Town',31.049772,121.231813),
(126,'Song Jiang Xin Cheng',31.029861,121.230204),
(127,'Tang Qiao',31.211196,121.517619),
(128,'Tie Li Road',31.407202,121.458063),
(129,'Tong He Xin Cun',31.331462,121.441455),
(130,'Wai Gao Qiao Free Trade Zone North',31.337620,121.592731),
(131,'Wai Gao Qiao Free Trade Zone South',31.324698,121.600220),
(132,'Wai Huan Road',31.121550,121.393046),
(133,'Wei Ning Road',31.215114,121.386223),
(134,'Wen Jing Road',31.003637,121.380515),
(135,'Wen Shui Road',31.293221,121.449995),
(136,'Wu Lian Road',31.273673,121.588676),
(137,'Wu Zhou Avenue',31.304588,121.590843),
(138,'Xi Zang Road North',31.263466,121.469725),
(139,'Xi Zang Road South',31.203020,121.489048),
(140,'Xiang Yin Road',31.305175,121.531996),
(141,'Xin Cun Road',31.265034,121.422851),
(142,'Xin Zha Road',31.238545,121.467934),
(143,'Xin Zhuang',31.111630,121.384742),
(144,'Xing Zhi Road',31.282530,121.423109),
(145,'Xing Zhong Road',31.158392,121.370215),
(146,'Xu Jia Hui',31.192539,121.438150),
(147,'Yan An Road West',31.209562,121.416929),
(148,'Yan Chang Road',31.271692,121.455531),
(149,'Yan Ji Road Central',31.288334,121.535000),
(150,'Yang Gao Road South',31.187675,121.524432),
(151,'Yang Shu Pu Road',31.252396,121.517072),
(152,'Yao Hua Road',31.178698,121.494305),
(153,'Yi Shan Road',31.185573,121.428055),
(154,'Yin Du Road',31.086256,121.391845),
(155,'Yin Gao Road',31.320409,121.485250),
(156,'You Yi Road',31.404638,121.476088),
(157,'You Yi Road West',31.381852,121.427894),
(158,'Yuan Shen Stadium',31.232876,121.534023),
(159,'Yun Shan Road',31.251167,121.573462),
(160,'Yun Tai Road',31.182590,121.500485),
(161,'Zhang Hua Bang',31.354773,121.500571),
(162,'Zhang Jiang High Technology Park',31.201459,121.584964),
(163,'Zhao Jia Bang Road',31.199367,121.450338),
(164,'Zhen Ping Road',31.247415,121.431423),
(165,'Zhong Chun Road',31.149302,121.338329),
(166,'Zhong Shan Park',31.218509,121.417465),
(167,'Zhong Shan Road North',31.259256,121.459308),
(168,'Zhong Tan Road',31.255203,121.441541),
(169,'Zhong Xing Road',31.253249,121.468889),
(170,'Zhou Hai Road',31.316743,121.603396),
(171,'Zhou Jia Du',31.189419,121.496086),
(172,'Zhuan Qiao',31.066849,121.401801);
SHOW WARNINGS;

-- -- ------------------------------------------
-- Table ROUTES_STATIONS
--

DROP TABLE IF EXISTS routes_stations;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS routes_stations (
  id INT4,
  s_id INT4,
  r_id INT4,
  chain INT4,
  status VARCHAR(40),
  distance FLOAT,
  duration INT4,
  PRIMARY KEY (id),
  FOREIGN KEY (s_id) REFERENCES stations(id),
  FOREIGN KEY (r_id) REFERENCES routes(id)
);
SHOW WARNINGS;
INSERT INTO routes_stations (id, s_id, r_id, chain, status, distance, duration) VALUES
(1,35,1,0,'operating',0.000,0),
(2,157,1,1,'operating',1.179,2),
(3,2,1,2,'operating',2.698,4),
(4,39,1,3,'operating',4.262,7),
(5,52,1,4,'operating',6.480,11),
(6,129,1,5,'operating',7.868,14),
(7,40,1,6,'operating',9.331,17),
(8,94,1,7,'operating',10.709,20),
(9,135,1,8,'operating',12.100,22),
(10,109,1,9,'operating',13.089,25),
(11,148,1,10,'operating',14.108,27),
(12,167,1,11,'operating',15.653,29),
(13,111,1,12,'operating',16.838,32),
(14,44,1,13,'operating',17.670,34),
(15,142,1,14,'operating',18.734,37),
(16,95,1,15,'operating',19.430,38),
(17,57,1,16,'operating',20.721,40),
(18,105,1,17,'operating',22.130,43),
(19,19,1,18,'operating',23.048,45),
(20,48,1,19,'operating',24.577,48),
(21,146,1,20,'operating',26.027,52),
(22,110,1,21,'operating',27.347,55),
(23,9,1,22,'operating',28.945,58),
(24,113,1,23,'operating',29.966,61),
(25,64,1,24,'operating',31.496,64),
(26,75,1,25,'operating',33.684,67),
(27,132,1,26,'operating',35.102,69),
(28,143,1,27,'operating',36.395,72),
(29,50,2,0,'operating',0.000,0),
(30,124,2,1,'operating',1.808,2),
(31,6,2,2,'operating',3.860,5),
(32,133,2,3,'operating',4.548,8),
(33,81,2,4,'operating',6.426,10),
(34,166,2,5,'operating',8.148,13),
(35,61,2,6,'operating',9.219,15),
(36,69,2,7,'operating',10.710,18),
(37,91,2,8,'operating',12.209,21),
(38,95,2,9,'operating',13.585,24),
(39,90,2,10,'operating',14.650,27),
(40,84,2,11,'operating',16.170,29),
(41,29,2,12,'operating',17.625,32),
(42,13,2,13,'operating',18.715,35),
(43,112,2,14,'operating',20.779,37),
(44,14,2,15,'operating',22.032,40),
(45,80,2,16,'operating',23.764,43),
(46,162,2,17,'operating',25.247,45),
(47,63,3,0,'operating',0.000,0),
(48,128,3,1,'operating',1.214,3),
(49,156,3,2,'operating',2.486,5),
(50,4,3,3,'operating',3.484,8),
(51,119,3,4,'operating',5.018,10),
(52,122,3,5,'operating',6.363,12),
(53,161,3,6,'operating',7.741,14),
(54,123,3,7,'operating',8.692,16),
(55,15,3,8,'operating',10.287,19),
(56,155,3,9,'operating',11.170,21),
(57,62,3,10,'operating',12.567,23),
(58,22,3,11,'operating',13.978,25),
(59,20,3,12,'operating',15.666,28),
(60,49,3,13,'operating',17.444,31),
(61,28,3,14,'operating',18.447,33),
(62,111,3,15,'operating',19.720,35),
(63,168,3,16,'operating',20.857,37),
(64,164,3,17,'operating',21.775,39),
(65,12,3,18,'operating',23.569,41),
(66,67,3,19,'operating',25.167,43),
(67,166,3,20,'operating',26.577,44),
(68,147,3,21,'operating',28.342,46),
(69,51,3,22,'operating',31.278,49),
(70,153,3,23,'operating',32.541,51),
(71,11,3,24,'operating',34.284,54),
(72,79,3,25,'operating',35.326,55),
(73,118,3,26,'operating',36.771,58),
(74,113,3,27,'operating',40.238,62),
(75,51,4,0,'operating',0.000,0),
(76,153,4,1,'operating',1.237,2),
(77,110,4,2,'operating',2.991,5),
(78,114,4,3,'operating',4.146,9),
(79,27,4,4,'operating',5.461,11),
(80,24,4,5,'operating',6.811,13),
(81,82,4,6,'operating',7.694,16),
(82,139,4,7,'operating',8.867,19),
(83,89,4,8,'operating',9.749,23),
(84,127,4,9,'operating',10.808,27),
(85,72,4,10,'operating',12.153,30),
(86,96,4,11,'operating',13.530,33),
(87,13,4,12,'operating',14.482,36),
(88,97,4,13,'operating',16.077,38),
(89,151,4,14,'operating',16.960,41),
(90,23,4,15,'operating',18.357,43),
(91,77,4,16,'operating',19.768,47),
(92,43,4,17,'operating',21.456,50),
(93,3,4,18,'operating',23.234,53),
(94,111,4,19,'operating',24.796,55),
(95,168,4,20,'operating',25.749,57),
(96,164,4,21,'operating',27.360,60),
(97,12,4,22,'operating',27.980,62),
(98,67,4,23,'operating',29.300,64),
(99,166,4,24,'operating',30.516,66),
(100,147,4,25,'operating',31.632,69),
(101,143,5,0,'operating',0.000,0),
(102,21,5,1,'operating',1.083,5),
(103,154,5,2,'operating',2.532,8),
(104,172,5,3,'operating',4.018,10),
(105,5,5,4,'operating',5.224,12),
(106,59,5,5,'operating',6.186,14),
(107,30,5,6,'operating',8.345,17),
(108,65,5,7,'operating',10.899,21),
(109,55,5,8,'operating',13.614,24),
(110,134,5,9,'operating',14.675,26),
(111,87,5,10,'operating',16.164,28),
(112,36,6,0,'operating',0.000,0),
(113,130,6,1,'operating',2.850,3),
(114,45,6,2,'operating',4.559,6),
(115,131,6,3,'operating',6.580,9),
(116,170,6,4,'operating',7.921,11),
(117,137,6,5,'operating',9.391,13),
(118,32,6,6,'operating',10.270,14),
(119,71,6,7,'operating',10.941,15),
(120,136,6,8,'operating',11.664,17),
(121,8,6,9,'operating',12.856,19),
(122,66,6,10,'operating',13.973,20),
(123,159,6,11,'operating',14.914,22),
(124,26,6,12,'operating',15.892,24),
(125,7,6,13,'operating',16.862,26),
(126,88,6,14,'operating',18.182,29),
(127,158,6,15,'operating',18.980,31),
(128,13,6,16,'operating',20.100,34),
(129,96,6,17,'operating',21.203,36),
(130,72,6,18,'operating',21.727,38),
(131,108,6,19,'operating',22.840,41),
(132,76,6,20,'operating',24.425,43),
(133,37,6,21,'operating',25.807,47),
(134,33,6,22,'operating',27.838,50),
(135,38,6,23,'operating',28.807,52),
(136,56,6,24,'operating',29.708,55),
(137,107,6,25,'operating',32.047,58),
(138,78,6,26,'operating',33.387,62),
(139,34,7,0,'under construction',0.000,NULL),
(140,80,7,1,'under construction',1.060,NULL),
(141,53,7,2,'under construction',2.523,NULL),
(142,68,7,3,'under construction',3.784,NULL),
(143,150,7,4,'under construction',5.104,NULL),
(144,37,7,5,'under construction',6.010,NULL),
(145,160,7,6,'under construction',7.081,NULL),
(146,152,7,7,'under construction',8.694,NULL),
(147,17,7,8,'under construction',9.268,NULL),
(148,99,7,9,'under construction',10.377,NULL),
(149,98,7,10,'under construction',11.856,NULL),
(150,27,7,11,'under construction',13.581,NULL),
(151,163,7,12,'under construction',15.032,NULL),
(152,19,7,13,'under construction',16.763,NULL),
(153,69,7,14,'under construction',18.264,NULL),
(154,16,7,15,'under construction',20.589,NULL),
(155,18,7,16,'under construction',21.984,NULL),
(156,164,7,17,'under construction',23.745,NULL),
(157,73,7,18,'under construction',25.361,NULL),
(158,141,7,19,'under construction',27.564,NULL),
(159,144,7,20,'under construction',29.124,NULL),
(160,54,7,21,'under construction',30.487,NULL),
(161,106,7,22,'under construction',32.104,NULL),
(162,115,7,23,'under construction',33.854,NULL),
(163,102,7,24,'under construction',36.458,NULL),
(164,41,7,25,'under construction',38.268,NULL),
(165,85,7,26,'under construction',40.481,NULL),
(166,93,7,27,'under construction',43.986,NULL),
(167,86,7,28,'under construction',45.102,NULL),
(168,117,8,0,'operating',0.000,0),
(169,92,8,1,'operating',1.193,2),
(170,140,8,2,'operating',2.251,4),
(171,58,8,3,'operating',2.754,5),
(172,149,8,4,'operating',3.846,8),
(173,46,8,5,'operating',4.692,9),
(174,60,8,6,'operating',5.604,10),
(175,1,8,7,'operating',6.504,12),
(176,121,8,8,'operating',7.541,14),
(177,104,8,9,'operating',8.760,17),
(178,49,8,10,'operating',9.817,19),
(179,138,8,11,'operating',11.381,21),
(180,169,8,12,'operating',12.634,24),
(181,103,8,13,'operating',13.679,26),
(182,95,8,14,'operating',14.361,29),
(183,25,8,15,'operating',15.723,31),
(184,74,8,16,'operating',16.430,34),
(185,83,8,17,'operating',17.118,36),
(186,139,8,18,'operating',18.736,38),
(187,171,8,19,'operating',19.468,41),
(188,152,8,20,'operating',21.858,45),
(189,126,9,0,'operating',0.000,0),
(190,125,9,1,'operating',2.248,3),
(191,31,9,2,'operating',4.269,6),
(192,116,9,3,'operating',6.452,10),
(193,120,9,4,'operating',9.292,13),
(194,70,9,5,'operating',12.168,17),
(195,165,9,6,'operating',16.701,21),
(196,101,9,7,'operating',21.675,25),
(197,145,9,8,'operating',24.719,29),
(198,47,9,9,'operating',26.894,32),
(199,10,9,10,'operating',28.571,34),
(200,42,9,11,'operating',29.148,37),
(201,80,10,0,'operating',0.000,0),
(202,100,10,1,'operating',30.500,8);
SHOW WARNINGS;

-- -- ------------------------------------------
-- Table ROUTES
--

DROP TABLE IF EXISTS routes;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS routes (
  id INT4,
  name VARCHAR(40),
  color VARCHAR(7),
  opened INT4,
  newest_ext INT4,
  PRIMARY KEY (id)
);
SHOW WARNINGS;
INSERT INTO routes (id, name, color, opened, newest_ext) VALUES
(1,'Linie 1','#FF0000', 1995, 2007),
(2,'Linie 2','#66FF00', 1999, 2006),
(3,'Linie 3','#FFE500', 2000, 2006),
(4,'Linie 4','#9900CC', 2005, 2007),
(5,'Linie 5','#FF00CC', 2003, 2003),
(6,'Linie 6','#FF99CC', 2007, 2007),
(7,'Linie 7','#CCD9D9', 2010, NULL),
(8,'Linie 8','#0000B2', 2007, 2007),
(9,'Linie 9','#B2E5FF', 2007, 2007),
(10,'MAGLEV','#7FFB32', 2004, NULL);
SHOW WARNINGS;

-- -- ------------------------------------------
-- Table SIGHTS
--

DROP TABLE IF EXISTS sights;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS sights (
  id INT4,
  name VARCHAR(60),
  districtnr INT4,
  cost INT4,
  opens TIME,
  closes TIME,
  latitude DOUBLE,
  longitude DOUBLE,
  PRIMARY KEY (id)
);
SHOW WARNINGS;
INSERT INTO sights (id, name, districtnr, cost, opens, closes, latitude, longitude) VALUES
(1,'The Tube',6,0,'09:00:00','22:00:00',31.230381,121.475980),
(2,'Oriental Pearl Tower',10,50,'08:00:00','21:00:00',31.239930,121.496612),
(3,'Jade Buddha Temple',7,15,'08:30:00','17:00:00',31.244214,121.439030),
(4,'Longhua Pagoda / Temple',9,10,'07:00:00','16:30:00',31.176965,121.446841),
(5,'Yuyuan Garden',12,40,'08:30:00','17:00:00',31.229094,121.487229),
(6,'Shanghai Museum',13,60,'09:00:00','17:00:00',31.230432,121.470691),
(7,'Science and Technology Museum',5,60,'09:00:00','17:00:00',31.223210,121.539399),
(8,'History Museum',10,0,'09:00:00','15:30:00',31.239298,121.499627),
(9,'Art Museum',13,20,'09:00:00','16:00:00',31.231573,121.471152),
(10,'Xujiahui Cathedral',16,0,'07:00:00','20:00:00',31.193047,121.431066),
(11,'Ocean Aquarium',10,110,'09:00:00','21:00:00',31.242685,121.496931),
(12,'Zoological Park',2,30,'06:30:00','17:30:00',31.228977,121.474661),
(13,'Wusa Monument',13,0,'00:00:00','24:00:00',31.235711,121.468470),
(14,'Century Plaza',6,0,'00:00:00','24:00:00',31.237286,121.475146),
(15,'Songqingling''s Mausoleum',2,0,'00:00:00','24:00:00',31.197457,121.406656),
(16,'Natural History Museum',6,0,'11:00:00','24:00:00',31.231665,121.486720);
SHOW WARNINGS;

-- -- ------------------------------------------
-- Table FREETIME
--

DROP TABLE IF EXISTS freetime;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS freetime (
  id INT4,
  name VARCHAR(60),
  districtnr INT4,
  latitude DOUBLE,
  longitude DOUBLE,
  PRIMARY KEY (id)
);
SHOW WARNINGS;
INSERT INTO freetime (id, name, districtnr, latitude, longitude) VALUES
(1,'Indoor Ski',14,31.136123,121.355894),
(2,'Jing''an District Stadium',7,31.227166,121.454113),
(3,'International Gymnastics Center',1,31.215361,121.408655),
(4,'Shanghai Sports Bath',15,31.181062,121.435839),
(5,'Shanghai Stadium',15,31.183380,121.437281),
(6,'Shanghai Gymnasium',15,31.183343,121.433472),
(7,'Hongqiao Golf Club',4,31.185867,121.388046),
(8,'Ruijin Cinema',7,31.226303,121.458137),
(9,'Yongle Cinema',1,31.215193,121.435330),
(10,'Cathay Guotai Theatre',3,31.220042,121.456643),
(11,'Paradise Warner Cinema City',16,31.196385,121.432565),
(12,'Kodak Cinema World',16,31.195222,121.434809),
(13,'IMAX 3D Cinemas',5,31.220161,121.537010),
(14,'Hengshan Cinema',16,31.199347,121.435706),
(15,'Shanghai Film Art Center',1,31.204328,121.424761),
(16,'UME International Cineplex',8,31.220844,121.470162),
(17,'Grand Theater',13,31.235190,121.466560),
(18,'Shanghai International Arts Center',3,31.216628,121.450327),
(19,'Shanghai Dramatic Arts Center',1,31.215857,121.437261),
(20,'Shanghai Opera Theater',1,31.218897,121.442501),
(21,'Shanghai Concert Hall',8,31.228188,121.473266);
SHOW WARNINGS;
