CREATE SCHEMA sputnik; SET search_path TO "$user", sputnik, public; CREATE TABLE sputnik.station23 ( id SERIAL PRIMARY KEY, address INET UNIQUE NOT NULL, position DOUBLE PRECISION ARRAY[3], plane POINT ); -- Stations on Level A INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.4.6', '{903, -4, 363}', '(903,363)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.2.3', '{450, -4, 897}', '(450,897)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.2.12', '{153, -4, 1164}', '(153,1164)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.2.10', '{486, -4, 1281}', '(486,1281)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.1.7', '{906, -4, 1023}', '(906,1023)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.3.13', '{594, -4, 1578}', '(594,1578)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.3.6', '{414, -4, 1416}', '(414,1416)'); -- Stations on Level B INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.3.9', '{1076, 1, 588}', '(1076,588)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.2.5', '{1256, 1, 386}', '(1256,386)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.1.5', '{1252, 1, 690}', '(1252,690)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.1.22', '{1254, 1, 1134}', '(1254,1134)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.3.21', '{874, 1, 948}', '(874,948)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.0.7', '{82, 1, 722}', '(82,722)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.4.12', '{840, 1, 274}', '(840,274)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.1.6', '{544, 1, 654}', '(544,654)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.1.12', '{544, 1, 486}', '(544,486)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.0.254', '{204, 1, 958}', '(204,958)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.5.2', '{202, 1, 276}', '(202,276)'); -- Stations on Level C INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.8.1', '{514, 5, 25}', '(514,25)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.8.14', '{25, 5, 217}', '(25,217)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.5.21', '{232, 5, 592}', '(232,592)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.4.11', '{537, 5, 592}', '(537,592)'); INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.7.14', '{768, 5, 379}', '(768,379)'); -- Unknown stations, not present on the map INSERT INTO sputnik.station23 (address, position, plane) VALUES ('10.254.3.5', '{414, -4, 1416}', '(414,1416)'); INSERT INTO sputnik.station23 (address) VALUES ('10.254.0.100'); INSERT INTO sputnik.station23 (address) VALUES ('10.254.1.16'); VACUUM FULL VERBOSE ANALYZE sputnik.station23; CREATE TABLE sputnik.station24 ( id CHARACTER(4) PRIMARY KEY, address INET UNIQUE NOT NULL, description TEXT, position DOUBLE PRECISION ARRAY[3], plane POINT ); -- Stations on Level A INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('D003', '10.254.4.3', 'Hackcenter', '{-1.8399998400000002, -4.5, -9.725713440000002}', '(-1.8399998400000002, -9.725713440000002)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('D010', '10.254.4.10', 'Hackcenter', '{-0.08761904000000001, -4.5, 8.06095168}', '(-0.08761904000000001, 8.06095168)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('D011', '10.254.4.11', 'Hackcenter', '{-15.15802, -4.5, 0.700}', '(-15.15802, 0.700)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('E006', '10.254.5.6', 'Hackcenter', '{18.31237936, -4.5, -4.205713920000001}', '(18.31237936, -4.205713920000001)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('E013', '10.254.5.13', 'Lounge', '{3.59238064, -4.5, 19.88952}', '(3.59238064, 19.88952)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('E017', '10.254.5.17', 'Lounge', '{9.0, -4.5, 12.7}', '(9.0, 12.7)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('C007', '10.254.3.7', 'Hardware_Lab', '{-3.32952352, -4.5, 13.756189280}', '(-3.32952352, 13.756189280)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('D021', '10.254.4.21', 'Workshop', '{-10, -4.5, 13.256189280}', '(-10, 13.256189280)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('F002', '10.254.6.2', 'Angel_Heaven', '{27, -4.5, -32.17142560000000}', '(27, -32.17142560000000)'); -- Stations on Level B INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('F012', '10.254.6.12', 'Foebud', '{15.2238082, 0, -19.2761888}', '(15.2238082, -19.2761888)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('F017', '10.254.6.17', 'Helpdesk', '{0.43, 0, -16.27}', '(0.43, -16.27)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('G002', '10.254.7.2', 'Entrance', '{-17.742855600000002, 0, -19.4952364}', '(-17.742855600000002, -19.4952364)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('B007', '10.254.2.7', 'Checkroom', '{-21.5714266, 0, 8.2}', '(-21.5714266, 8.2)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('B001', '10.254.2.1', 'Stairs_Speakers', '{-16.976189, 0, 15.1142844}', '(-16.976189, 15.1142844)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('E021', '10.254.5.21', 'Stairs_CERT', '{17.6333318, 0, 15.1142844}', '(17.6333318, 15.1142844)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('D005', '10.254.4.5', 'Saal_2', '{35.923806400000004, 0, -13.9095226}', '(35.923806400000004,-13.9095226)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('D015', '10.254.4.15', 'Saal_2', '{29.323806400000004, 0, -24.9095226}', '(29.323806400000004, -24.9095226)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('C005', '10.254.3.5', 'Saal_3', '{36.142854, 0, 0.5476190000000001}', '(36.142854, 0.5476190000000001)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('C023', '10.254.3.23', 'Saal_3', '{36.142854, 0, 23.766664600000002}', '(36.142854, 23.766664600000002)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('C150', '10.254.3.150', 'Saal_3', '{29.5, 0, 15.0}', '(29.5, 15.0)'); --INSERT INTO sputnik.station24 (id, address, description, position, plane) --VALUES ('C015', '10.254.3.15', 'Problem_Saal3_Canteen', '{0, 1, 0}', '(0, 0)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('C015', '10.254.3.15', 'Canteen', '{-8, 0, -1.5}', '(-8, -1.5)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('C006', '10.254.3.6', 'Canteen', '{0.43, 0, 0}', '(0.43, 0)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('C012', '10.254.3.12', 'Canteen', '{0.43, 0, -8.43}', '(0.43, -8.43)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('D001', '10.254.4.1', 'Canteen', '{7, 0, 1}', '(7, 1)'); -- Stations on Level C INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('A100', '10.254.1.100', 'Saal_1', '{0, 5, 0}', '(0, 0)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('F011', '10.254.6.11', 'Saal_1', '{8.5428564, 5, 11.0333322}', '(8.5428564, 11.0333322)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('G016', '10.254.7.16', 'Saal_1', '{4, 5, -5.7}', '(4, -5.7)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('G021', '10.254.7.21', 'Saal_1', '{-7.5571422, 5, 11.7047608}', '(-7.5571422, 11.7047608)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('G022', '10.254.7.22', 'Saal_1', '{-3, 5, -5.7}', '(-3, -5.7)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('I001', '10.254.9.1', 'Debian', '{17.06, 5, -6.7}', '(17.06, -6.7)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('I005', '10.254.9.5', 'Stairs_Press', '{16.24, 5, 6.6}', '(16.24, 6.6)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('I014', '10.254.9.14', 'Chaoswelle_CAcert', '{22.561902800000002, 5, -1.0952380000000002}', '(22.561902800000002, -1.0952380000000002)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('H011', '10.254.8.11', 'Wikipedia', '{-16.2999982, 5, -9.7190466}', '(-16.2999982, -9.7190466)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('J013', '10.254.10.13', 'Wikipedia', '{-20.6999982, 5, -9.7190466}', '(-20.6999982, -9.7190466)'); -- Position? INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('H017', '10.254.8.17', 'POC_Helpdesk_VOIP', '{-9.4, 5, -18}', '(-9.4, -18)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('H019', '10.254.8.19', 'NOC_Helpdesk', '{2, 5, -18}', '(2, -18)'); INSERT INTO sputnik.station24 (id, address, description, position, plane) VALUES ('J019', '10.254.10.19', 'Stairs_Hinterzimmer', '{-17.3999982, 5, 2.3}', '(-17.3999982, 2.3)'); VACUUM FULL VERBOSE ANALYZE sputnik.station24; CREATE TABLE sputnik.station_lasthope ( id CHARACTER(4) PRIMARY KEY, address INET UNIQUE NOT NULL, description TEXT, position DOUBLE PRECISION ARRAY[3], plane POINT ); -- Stations on 18th floor INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B016', '10.254.1.16', 'Engressia', '{-0.5, 1.2, 0.42}', '(-0.5, 0.42)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B032', '10.254.1.32', 'Engressia', '{-0.84, 1.2, 0.44}', '(-0.84, 0.44)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B018', '10.254.1.18', 'Hopper', '{-0.78, 1.2, -0.24}', '(-0.78, -0.24)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B019', '10.254.1.19', 'Hopper', '{-0.78, 1.2, -0.56}', '(-0.78, -0.56)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B020', '10.254.1.20', 'Hopper', '{-0.56, 1.2, -0.56}', '(-0.56, -0.56)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B021', '10.254.1.21', 'Hopper', '{-0.56, 1.2, -0.24}', '(-0.56, -0.24)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B022', '10.254.1.22', 'Turing', '{0.12, 1.2, -0.6}', '(0.12, -0.6)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B023', '10.254.1.23', 'Turing', '{-0.14, 1.2, -0.38}', '(-0.14, -0.38)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B024', '10.254.1.24', 'Turing', '{0.12, 1.2, -0.36}', '(0.12, -0.36)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B025', '10.254.1.25', 'Turing', '{-0.14, 1.2, -0.62}', '(-0.14, -0.62)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B026', '10.254.1.26', '18thFloorHallway', '{-0.48, 1.2, 0.26}', '(-0.48, 0.26)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B027', '10.254.1.27', '18thFloorHallway', '{-0.16, 1.2, 0.22}', '(-0.16, 0.22)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B028', '10.254.1.28', '18thFloorElevators', '{0.0, 1.2, 0.28}', '(0.0, 0.28)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('B030', '10.254.1.30', 'Zuse', '{0.54, 1.2, 0.6}', '(0.54, 0.6)'); -- Stations on 2nd floor INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A016', '10.254.0.16', 'RadioStation', '{-0.72, 0.0, -0.02}', '(-0.72, -0.02)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A017', '10.254.0.17', 'HackerspaceVillage', '{-0.72, 0.0, 0.42}', '(-0.72, 0.42)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A018', '10.254.0.18', 'HackerspaceVillage', '{-0.72, 0.0, 0.62}', '(-0.72, 0.62)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A019', '10.254.0.19', 'HackerspaceVillage', '{-0.48, 0.0, 0.48}', '(-0.48, 0.48)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A020', '10.254.0.20', 'HackerspaceVillage', '{-0.26, 0.0, 0.62}', '(-0.26, 0.62)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A021', '10.254.0.21', 'HackerspaceVillage', '{-0.26, 0.0, 0.4}', '(-0.26, 0.4)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A022', '10.254.0.22', 'Escalator', '{-0.26, 0.0, 0.18}', '(-0.26, 0.18)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A023', '10.254.0.23', 'ArtExhibitEast', '{0.3, 0.0, 0.2}', '(0.3, 0.2)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A024', '10.254.0.24', 'DemoArea', '{0.32, 0.0, -0.24}', '(0.32, -0.24)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A025', '10.254.0.25', 'LegoTable', '{-0.08, 0.0, -0.22}', '(-0.08, -0.22)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A026', '10.254.0.26', 'PhonesAndTerminals', '{-0.64, 0.0, -0.22}', '(-0.64, -0.22)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A027', '10.254.0.27', 'LaptopWorkArea', '{-0.72, 0.0, -0.42}', '(-0.72, -0.42)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A028', '10.254.0.28', 'Lockpicking', '{-0.62, 0.0, -0.6}', '(-0.62, -0.6)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A029', '10.254.0.29', 'Segways', '{-0.16, 0.0, -0.62}', '(-0.16, -0.62)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A030', '10.254.0.30', 'CTF', '{0.32, 0.0, -0.62}', '(0.32, -0.62)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A031', '10.254.0.31', 'CTF', '{0.2, 0.0, -0.48}', '(0.2, -0.48)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A032', '10.254.0.32', 'LaptopWorkArea', '{-0.38, 0.0, -0.42}', '(-0.38, -0.42)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A033', '10.254.0.33', 'NOC', '{-0.48, 0.0, 0.08}', '(-0.48, 0.08)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A034', '10.254.0.34', 'Hammocks', '{0.42, 0.0, 0.08}', '(0.42, 0.08)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A035', '10.254.0.35', 'VendorTables', '{-0.02, 0.0, -0.12}', '(-0.02, -0.12)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A036', '10.254.0.36', 'VendorTables', '{-0.12, 0.0, 0.08}', '(-0.12, 0.08)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A037', '10.254.0.37', 'VendorTables', '{-0.32, 0.0, -0.14}', '(-0.32, -0.14)'); INSERT INTO sputnik.station_lasthope (id, address, description, position, plane) VALUES ('A039', '10.254.0.39', 'AMD', '{-0.52, 0.0, -0.04}', '(-0.52, -0.04)'); VACUUM FULL VERBOSE ANALYZE sputnik.station_lasthope; CREATE TABLE sputnik.station25 ( id CHARACTER(4) PRIMARY KEY, address INET UNIQUE NOT NULL, mac macaddr UNIQUE NOT NULL, description TEXT, position DOUBLE PRECISION ARRAY[3], plane POINT ); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A101', '10.254.0.101', '00:de:ad:be:ef:65'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A102', '10.254.0.102', '00:de:ad:be:ef:66'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A103', '10.254.0.103', '00:de:ad:be:ef:67'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A104', '10.254.0.104', '00:de:ad:be:ef:68'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A105', '10.254.0.105', '00:de:ad:be:ef:69'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A106', '10.254.0.106', '00:de:ad:be:ef:6a'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A107', '10.254.0.107', '00:de:ad:be:ef:6b'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A108', '10.254.0.108', '00:de:ad:be:ef:6c'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A109', '10.254.0.109', '00:de:ad:be:ef:6d'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A110', '10.254.0.110', '00:de:ad:be:ef:6e'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A111', '10.254.0.111', '00:de:ad:be:ef:6f'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A112', '10.254.0.112', '00:de:ad:be:ef:70'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A113', '10.254.0.113', '00:de:ad:be:ef:71'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A114', '10.254.0.114', '00:de:ad:be:ef:72'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A115', '10.254.0.115', '00:de:ad:be:ef:73'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A116', '10.254.0.116', '00:de:ad:be:ef:74'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A117', '10.254.0.117', '00:de:ad:be:ef:75'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A118', '10.254.0.118', '00:de:ad:be:ef:76'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A119', '10.254.0.119', '00:de:ad:be:ef:77'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A120', '10.254.0.120', '00:de:ad:be:ef:78'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A121', '10.254.0.121', '00:de:ad:be:ef:79'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A122', '10.254.0.122', '00:de:ad:be:ef:7a'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A123', '10.254.0.123', '00:de:ad:be:ef:7b'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A124', '10.254.0.124', '00:de:ad:be:ef:7c'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A125', '10.254.0.125', '00:de:ad:be:ef:7d'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A126', '10.254.0.126', '00:de:ad:be:ef:7e'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A127', '10.254.0.127', '00:de:ad:be:ef:7f'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A128', '10.254.0.128', '00:de:ad:be:ef:80'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A129', '10.254.0.129', '00:de:ad:be:ef:81'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A130', '10.254.0.130', '00:de:ad:be:ef:82'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A131', '10.254.0.131', '00:de:ad:be:ef:83'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A132', '10.254.0.132', '00:de:ad:be:ef:84'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A133', '10.254.0.133', '00:de:ad:be:ef:85'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A134', '10.254.0.134', '00:de:ad:be:ef:86'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A135', '10.254.0.135', '00:de:ad:be:ef:87'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A136', '10.254.0.136', '00:de:ad:be:ef:88'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A137', '10.254.0.137', '00:de:ad:be:ef:89'); INSERT INTO sputnik.station25 (id, address, mac) VALUES ('A138', '10.254.0.138', '00:de:ad:be:ef:8a'); VACUUM FULL VERBOSE ANALYZE sputnik.station25; CREATE TABLE sputnik.sputnik ( id INTEGER, sequence BIGINT, strength INTEGER, time TIMESTAMP WITH TIME ZONE, tags TEXT ARRAY[1] ); ALTER TABLE sputnik.sputnik ALTER COLUMN id SET STATISTICS 1000; ALTER TABLE sputnik.sputnik ALTER COLUMN sequence SET STATISTICS 1000; ALTER TABLE sputnik.sputnik ALTER COLUMN time SET STATISTICS 1000; VACUUM FULL VERBOSE ANALYZE sputnik.sputnik; CREATE INDEX sputnik_id_idx ON sputnik.sputnik (id); CREATE INDEX sputnik_sequence_idx ON sputnik.sputnik (sequence); CREATE INDEX sputnik_time_idx ON sputnik.sputnik (time); INSERT INTO pg_catalog.pg_autovacuum (vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age) VALUES ((SELECT oid FROM pg_catalog.pg_class WHERE relname = 'sputnik' AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'sputnik')), true, 500, 0.1, 0, 0.005, 0, 500, 100000000, 200000000); CREATE TABLE sputnik.ccc23 ( station INTEGER NOT NULL REFERENCES sputnik.station23 (id) ) INHERITS (sputnik.sputnik); ALTER TABLE sputnik.ccc23 ALTER COLUMN id SET STATISTICS 1000; ALTER TABLE sputnik.ccc23 ALTER COLUMN sequence SET STATISTICS 1000; ALTER TABLE sputnik.ccc23 ALTER COLUMN time SET STATISTICS 1000; VACUUM FULL VERBOSE ANALYZE sputnik.ccc23; CREATE INDEX ccc23_id_idx ON sputnik.ccc23 (id) WHERE id IS NOT NULL; CREATE INDEX ccc23_sequence_idx ON sputnik.ccc23 (sequence); CREATE INDEX ccc23_time_idx ON sputnik.ccc23 (time); INSERT INTO pg_catalog.pg_autovacuum (vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age) VALUES ((SELECT oid FROM pg_catalog.pg_class WHERE relname = 'ccc23' AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'sputnik')), true, 500, 0.1, 0, 0.005, 0, 500, 100000000, 200000000); CREATE TABLE sputnik.ccc23xml ( position DOUBLE PRECISION ARRAY[3], plane POINT, direction DOUBLE PRECISION ARRAY[3], -- Not essential values, mostly the same through entire data set observer TEXT, observedobject TEXT, priority INTEGER, mindistance DOUBLE PRECISION, maxdistance DOUBLE PRECISION ) INHERITS (sputnik.sputnik); ALTER TABLE sputnik.ccc23xml ALTER COLUMN id SET STATISTICS 1000; ALTER TABLE sputnik.ccc23xml ALTER COLUMN sequence SET STATISTICS 1000; ALTER TABLE sputnik.ccc23xml ALTER COLUMN time SET STATISTICS 1000; VACUUM FULL VERBOSE ANALYZE sputnik.ccc23xml; CREATE INDEX ccc23xml_id_idx ON sputnik.ccc23xml (id); CREATE INDEX ccc23xml_sequence_idx ON sputnik.ccc23xml (sequence); CREATE INDEX ccc23xml_time_idx ON sputnik.ccc23xml (time); INSERT INTO pg_catalog.pg_autovacuum (vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age) VALUES ((SELECT oid FROM pg_catalog.pg_class WHERE relname = 'ccc23xml' AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'sputnik')), true, 500, 0.1, 0, 0.005, 0, 500, 100000000, 200000000); CREATE TABLE sputnik.ccc2007 ( station INTEGER NOT NULL ) INHERITS (sputnik.sputnik); ALTER TABLE sputnik.ccc2007 ALTER COLUMN id SET STATISTICS 1000; ALTER TABLE sputnik.ccc2007 ALTER COLUMN sequence SET STATISTICS 1000; ALTER TABLE sputnik.ccc2007 ALTER COLUMN time SET STATISTICS 1000; VACUUM FULL VERBOSE ANALYZE sputnik.ccc2007; CREATE INDEX ccc2007_id_idx ON sputnik.ccc2007 (id); CREATE INDEX ccc2007_sequence_idx ON sputnik.ccc2007 (sequence); CREATE INDEX ccc2007_time_idx ON sputnik.ccc2007 (time); INSERT INTO pg_catalog.pg_autovacuum (vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age) VALUES ((SELECT oid FROM pg_catalog.pg_class WHERE relname = 'ccc2007' AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'sputnik')), true, 500, 0.1, 0, 0.005, 0, 500, 100000000, 200000000); CREATE TABLE sputnik.ccc24 ( station CHARACTER(4) NOT NULL REFERENCES sputnik.station24 (id), moment INTEGER NOT NULL, flags INTEGER NOT NULL ) INHERITS (sputnik.sputnik); ALTER TABLE sputnik.ccc24 ALTER COLUMN id SET STATISTICS 1000; ALTER TABLE sputnik.ccc24 ALTER COLUMN sequence SET STATISTICS 1000; ALTER TABLE sputnik.ccc24 ALTER COLUMN time SET STATISTICS 1000; ALTER TABLE sputnik.ccc24 ALTER COLUMN moment SET STATISTICS 1000; COPY sputnik.ccc24(id, moment, station, strength, sequence, flags) FROM '/tmp/24c3' WITH DELIMITER AS ' '; UPDATE sputnik.ccc24 SET time = to_timestamp(moment); UPDATE sputnik.ccc24 SET tags = ARRAY['button0'] WHERE flags = 1; VACUUM FULL VERBOSE ANALYZE sputnik.ccc24; CREATE INDEX ccc24_id_idx ON sputnik.ccc24 (id); CREATE INDEX ccc24_sequence_idx ON sputnik.ccc24 (sequence); CREATE INDEX ccc24_time_idx ON sputnik.ccc24 (time); CREATE INDEX ccc24_moment_idx ON sputnik.ccc24 (moment); INSERT INTO pg_catalog.pg_autovacuum (vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age) VALUES ((SELECT oid FROM pg_catalog.pg_class WHERE relname = 'ccc24' AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'sputnik')), true, 500, 0.1, 0, 0.005, 0, 500, 100000000, 200000000); CREATE TABLE sputnik.lasthope ( station CHARACTER(4) NOT NULL REFERENCES sputnik.station_lasthope (id), moment INTEGER NOT NULL, flags INTEGER NOT NULL ) INHERITS (sputnik.sputnik); ALTER TABLE sputnik.lasthope ALTER COLUMN id SET STATISTICS 1000; ALTER TABLE sputnik.lasthope ALTER COLUMN sequence SET STATISTICS 1000; ALTER TABLE sputnik.lasthope ALTER COLUMN time SET STATISTICS 1000; ALTER TABLE sputnik.lasthope ALTER COLUMN moment SET STATISTICS 1000; COPY sputnik.lasthope(id, moment, station, strength, sequence, flags) FROM '/tmp/TLH' WITH DELIMITER AS ' '; UPDATE sputnik.lasthope SET time = to_timestamp(moment); UPDATE sputnik.lasthope SET tags = ARRAY['button0'] WHERE flags = 1; VACUUM FULL VERBOSE ANALYZE sputnik.lasthope; CREATE INDEX lasthope_id_idx ON sputnik.lasthope (id); CREATE INDEX lasthope_sequence_idx ON sputnik.lasthope (sequence); CREATE INDEX lasthope_time_idx ON sputnik.lasthope (time); CREATE INDEX lasthope_moment_idx ON sputnik.lasthope (moment); INSERT INTO pg_catalog.pg_autovacuum (vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age) VALUES ((SELECT oid FROM pg_catalog.pg_class WHERE relname = 'lasthope' AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'sputnik')), true, 500, 0.1, 0, 0.005, 0, 500, 100000000, 200000000); CREATE TABLE sputnik.ccc25 ( station CHARACTER(4) NOT NULL REFERENCES sputnik.station25 (id), packet_type INTEGER NOT NULL, last_seen INTEGER, was_near BOOLEAN, contacts INTEGER ARRAY[8] ) INHERITS (sputnik.sputnik); ALTER TABLE sputnik.ccc25 ALTER COLUMN id SET STATISTICS 1000; ALTER TABLE sputnik.ccc25 ALTER COLUMN sequence SET STATISTICS 1000; ALTER TABLE sputnik.ccc25 ALTER COLUMN time SET STATISTICS 1000; VACUUM FULL VERBOSE ANALYZE sputnik.ccc25; CREATE INDEX ccc25_id_idx ON sputnik.ccc25 (id); CREATE INDEX ccc25_sequence_idx ON sputnik.ccc25 (sequence); CREATE INDEX ccc25_time_idx ON sputnik.ccc25 (time); INSERT INTO pg_catalog.pg_autovacuum (vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age) VALUES ((SELECT oid FROM pg_catalog.pg_class WHERE relname = 'ccc25' AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'sputnik')), true, 500, 0.1, 0, 0.005, 0, 500, 100000000, 200000000); CREATE TABLE sputnik.reader ( station INTEGER NULL ) INHERITS (sputnik.sputnik); ALTER TABLE sputnik.reader ALTER COLUMN id SET STATISTICS 1000; ALTER TABLE sputnik.reader ALTER COLUMN sequence SET STATISTICS 1000; ALTER TABLE sputnik.reader ALTER COLUMN time SET STATISTICS 1000; VACUUM FULL VERBOSE ANALYZE sputnik.reader; CREATE INDEX reader_id_idx ON sputnik.reader (id); CREATE INDEX reader_sequence_idx ON sputnik.reader (sequence); CREATE INDEX reader_time_idx ON sputnik.reader (time); INSERT INTO pg_catalog.pg_autovacuum (vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age) VALUES ((SELECT oid FROM pg_catalog.pg_class WHERE relname = 'reader' AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'sputnik')), true, 500, 0.1, 0, 0.005, 0, 500, 100000000, 200000000); CREATE TABLE sputnik.adjacency23 ( a INTEGER NOT NULL REFERENCES sputnik.station23 (id), b INTEGER NOT NULL REFERENCES sputnik.station23 (id), number INTEGER NOT NULL ); INSERT INTO sputnik.adjacency23 (a, b, number) SELECT a.station, b.station, COUNT(*) FROM sputnik.ccc23 a INNER JOIN sputnik.ccc23 b ON a.time = b.time AND a.sequence = b.sequence WHERE a.station > b.station GROUP BY a.station, b.station; CREATE TABLE sputnik.adjacency24 ( a CHARACTER(4) NOT NULL REFERENCES sputnik.station24 (id), b CHARACTER(4) NOT NULL REFERENCES sputnik.station24 (id), number INTEGER NOT NULL ); INSERT INTO sputnik.adjacency24 (a, b, number) SELECT a.station, b.station, COUNT(*) FROM sputnik.ccc24 a INNER JOIN sputnik.ccc24 b ON a.time = b.time AND a.sequence = b.sequence WHERE a.station > b.station GROUP BY a.station, b.station; CREATE TABLE sputnik.room ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, shape PATH, ymin DOUBLE PRECISION, ymax DOUBLE PRECISION -- Should we create this to simplify calculations, -- or rather leave it to PosgreSQL/PostGIS -- bbox ); INSERT INTO sputnik.room (name) VALUES ('Saal 1'); INSERT INTO sputnik.room (name) VALUES ('Saal 2'); INSERT INTO sputnik.room (name) VALUES ('Saal 3'); INSERT INTO sputnik.room (name) VALUES ('Saal 4'); INSERT INTO sputnik.room (name) VALUES ('Workshop Area'); INSERT INTO sputnik.room (name) VALUES ('Shelter Foo'); INSERT INTO sputnik.room (name) VALUES ('Shelter Bar'); INSERT INTO sputnik.room (name) VALUES ('Engressia'); INSERT INTO sputnik.room (name) VALUES ('Hopper'); INSERT INTO sputnik.room (name) VALUES ('Turing'); INSERT INTO sputnik.room (name) VALUES ('Zuse'); CREATE TABLE sputnik.event ( id SERIAL PRIMARY KEY, organizerid INTEGER, track TEXT NOT NULL, language TEXT NOT NULL, name TEXT NOT NULL, place INTEGER NOT NULL REFERENCES sputnik.room (id), description TEXT, address TEXT, searchable tsvector NOT NULL, start TIMESTAMP WITH TIME ZONE NOT NULL, finish TIMESTAMP WITH TIME ZONE NOT NULL ); CREATE INDEX event_searchable_idx_gin ON sputnik.event USING gin(searchable); CREATE INDEX event_searchable_idx_gist ON sputnik.event USING gist(searchable); -- Which stations are covering which room CREATE TABLE sputnik.stationroom23 ( station INTEGER NOT NULL REFERENCES sputnik.station23 (id), room INTEGER NOT NULL REFERENCES sputnik.room (id), reach DOUBLE PRECISION NOT NULL ); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.5.21'), (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 1.0); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.4.11'), (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 1.0); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.8.14'), (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.8.1'), (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.7.14'), (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.1.5'), (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 1.0); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.1.22'), (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 1.0); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.3.9'), (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.2.5'), (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.3.21'), (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.4.12'), (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.2.5'), (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 1.0); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.3.9'), (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 1.0); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.1.5'), (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.1.22'), (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.3.21'), (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.4.12'), (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.3.6'), (SELECT id FROM sputnik.room WHERE name = 'Saal 4'), 1.0); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.3.13'), (SELECT id FROM sputnik.room WHERE name = 'Saal 4'), 1.0); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.1.7'), (SELECT id FROM sputnik.room WHERE name = 'Saal 4'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.2.3'), (SELECT id FROM sputnik.room WHERE name = 'Saal 4'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.2.12'), (SELECT id FROM sputnik.room WHERE name = 'Saal 4'), 0.5); INSERT INTO sputnik.stationroom23 (station, room, reach) VALUES ((SELECT id FROM sputnik.station23 WHERE address = '10.254.2.10'), (SELECT id FROM sputnik.room WHERE name = 'Saal 4'), 0.5); CREATE TABLE sputnik.stationroom24 ( station CHARACTER(4) NOT NULL REFERENCES sputnik.station24 (id), room INTEGER NOT NULL REFERENCES sputnik.room (id), reach DOUBLE PRECISION NOT NULL ); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('A100', (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 1.0); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('F011', (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 1.0); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('G016', (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 1.0); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('G021', (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 1.0); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('G022', (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 1.0); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('H011', (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('H017', (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('H019', (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('I001', (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('I005', (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('J013', (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('J019', (SELECT id FROM sputnik.room WHERE name = 'Saal 1'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('D005', (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 1.0); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('D015', (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 1.0); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('C005', (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('C023', (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('C150', (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('D001', (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('E021', (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('F012', (SELECT id FROM sputnik.room WHERE name = 'Saal 2'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('C005', (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 1.0); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('C023', (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 1.0); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('C150', (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 1.0); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('D005', (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('D015', (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('D001', (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('E021', (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 0.5); INSERT INTO sputnik.stationroom24 (station, room, reach) VALUES ('F012', (SELECT id FROM sputnik.room WHERE name = 'Saal 3'), 0.5); CREATE FUNCTION sputnik.BorderWidth(x DOUBLE PRECISION) RETURNS DOUBLE PRECISION LANGUAGE plpgsql IMMUTABLE STRICT EXTERNAL SECURITY INVOKER AS $$ BEGIN RETURN 0.01+0.09/(1+exp((x-500)/100)); END $$; CREATE AGGREGATE sputnik.array_accum(anyelement) ( sfunc = array_append, stype = anyarray, initcond = '{}' ); CREATE TYPE sputnik.Position AS (moment TIMESTAMP WITH TIME ZONE, x DOUBLE PRECISION, y DOUBLE PRECISION, z DOUBLE PRECISION, pings INTEGER); CREATE TYPE sputnik.Strengths AS (id CHARACTER(4), strength INTEGER, x DOUBLE PRECISION, y DOUBLE PRECISION, z DOUBLE PRECISION, number INTEGER); CREATE FUNCTION sputnik.Position24Linear(who INTEGER, moment TIMESTAMP WITH TIME ZONE, delta INTERVAL) RETURNS sputnik.Position LANGUAGE plpgsql STABLE STRICT SECURITY INVOKER AS $$ DECLARE result sputnik.Position; weight DOUBLE PRECISION; i sputnik.Strengths%ROWTYPE; BEGIN weight = 0.0; result.moment = moment; result.x = 0.0; result.y = 0.0; result.z = 0.0; result.pings = 0; FOR i IN SELECT a.station, MIN(a.strength), b.position[1], b.position[2], b.position[3], count(*) FROM sputnik.ccc24 a INNER JOIN sputnik.station24 b ON (a.station = b.id) WHERE a.id = who AND time BETWEEN moment AND moment+delta GROUP BY station, b.position LOOP weight = weight + (4-i.strength); result.x = result.x + i.x*(4-i.strength); result.y = result.y + i.y*(4-i.strength); result.z = result.z + i.z*(4-i.strength); result.pings = result.pings + i.number; END LOOP; IF NOT FOUND THEN RETURN NULL; END IF; result.x = result.x/weight; result.y = result.y/weight; result.z = result.z/weight; RETURN result; END $$; CREATE FUNCTION sputnik.Position24Quadratic(who INTEGER, moment TIMESTAMP WITH TIME ZONE, delta INTERVAL) RETURNS sputnik.Position LANGUAGE plpgsql STABLE STRICT SECURITY INVOKER AS $$ DECLARE result sputnik.Position; weight DOUBLE PRECISION; i sputnik.Strengths%ROWTYPE; BEGIN weight = 0.0; result.moment = moment; result.x = 0.0; result.y = 0.0; result.z = 0.0; result.pings = 0; FOR i IN SELECT a.station, MIN(a.strength), b.position[1], b.position[2], b.position[3], count(*) FROM sputnik.ccc24 a INNER JOIN sputnik.station24 b ON (a.station = b.id) WHERE a.id = who AND time BETWEEN moment AND moment+delta GROUP BY station, b.position LOOP weight = weight + (4-i.strength)*(4-i.strength); result.x = result.x + i.x*(4-i.strength)*(4-i.strength); result.y = result.y + i.y*(4-i.strength)*(4-i.strength); result.z = result.z + i.z*(4-i.strength)*(4-i.strength); result.pings = result.pings + i.number; END LOOP; IF NOT FOUND THEN RETURN NULL; END IF; result.x = result.x/weight; result.y = result.y/weight; result.z = result.z/weight; RETURN result; END $$; CREATE FUNCTION sputnik.Position24Exponential(who INTEGER, moment TIMESTAMP WITH TIME ZONE, delta INTERVAL) RETURNS sputnik.Position LANGUAGE plpgsql STABLE STRICT SECURITY INVOKER AS $$ DECLARE result sputnik.Position; weight DOUBLE PRECISION; i sputnik.Strengths%ROWTYPE; BEGIN weight = 0.0; result.moment = moment; result.x = 0.0; result.y = 0.0; result.z = 0.0; result.pings = 0; FOR i IN SELECT a.station, MIN(a.strength), b.position[1], b.position[2], b.position[3], count(*) FROM sputnik.ccc24 a INNER JOIN sputnik.station24 b ON (a.station = b.id) WHERE a.id = who AND time BETWEEN moment AND moment+delta GROUP BY station, b.position LOOP weight = weight + 2^(4-i.strength); result.x = result.x + i.x*2^(4-i.strength); result.y = result.y + i.y*2^(4-i.strength); result.z = result.z + i.z*2^(4-i.strength); result.pings = result.pings + i.number; END LOOP; IF NOT FOUND THEN RETURN NULL; END IF; result.x = result.x/weight; result.y = result.y/weight; result.z = result.z/weight; RETURN result; END $$; CREATE OR REPLACE FUNCTION sputnik.array_entropy(counts INTEGER[]) RETURNS DOUBLE PRECISION LANGUAGE plpgsql STABLE STRICT SECURITY INVOKER AS $$ DECLARE tally DOUBLE PRECISION; result DOUBLE PRECISION; i INTEGER; BEGIN tally = 0.0; i = array_lower(counts, 1); WHILE i <= array_upper(counts, 1) LOOP tally = tally+counts[i]; i = i+1; END LOOP; result = 0.0; i = array_lower(counts, 1); WHILE i <= array_upper(counts, 1) LOOP result = result-log(counts[i]/tally)*counts[i]/tally; i = i+1; END LOOP; RETURN result; END $$; CREATE AGGREGATE sputnik.entropy(INTEGER) ( sfunc = array_append, stype = INTEGER[], finalfunc = sputnik.array_entropy, initcond = '{}' ); CREATE OR REPLACE FUNCTION sputnik.Entropy(source TEXT, who INTEGER, time_begin TIMESTAMP WITH TIME ZONE, time_end TIMESTAMP WITH TIME ZONE, delta INTEGER) RETURNS DOUBLE PRECISION LANGUAGE plpgsql STABLE STRICT SECURITY INVOKER AS $$ DECLARE query TEXT; result DOUBLE PRECISION; BEGIN query = 'SELECT sputnik.entropy(c0) FROM '; query = query|| ' (SELECT COUNT(*)::INTEGER FROM '; query = query|| ' (SELECT a.a0, sputnik.array_accum(a.a1) FROM '; query = query|| ' (SELECT moment/'||delta; query = query|| ', MIN(strength)::TEXT || ''+'' || station FROM '; query = query|| source; query = query|| ' WHERE id = '||who||' AND time BETWEEN '; query = query|| quote_literal(time_begin)||' AND '||quote_literal(time_end); query = query|| ' GROUP by moment/'||delta||', station ORDER BY 2)'; query = query|| ' AS a (a0, a1) GROUP BY a.a0) AS b (b0, b1) GROUP BY b.b1) AS c (c0);'; EXECUTE query INTO STRICT result; RETURN result; END $$; CREATE FUNCTION rfun(a TIMESTAMP WITH TIME ZONE, b TIMESTAMP WITH TIME ZONE) RETURNS TIMESTAMP WITH TIME ZONE LANGUAGE plr IMMUTABLE STRICT SECURITY INVOKER AS $$ if (a>b) return(a) else return(b) $$; ----------------------------------------------------------------------- -- AMD HOPE data ----------------------------------------------------------------------- CREATE SCHEMA hope; SET search_path TO "$user", hope, public; SET datestyle TO MDY; SET TIME ZONE 'America/New_York'; CREATE TABLE hope.countries ( id INTEGER PRIMARY KEY, country TEXT UNIQUE NOT NULL ); COPY hope.countries (id, country) FROM '/tmp/countries.csv' WITH CSV HEADER; CREATE TABLE hope.interests_list ( id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL ); COPY hope.interests_list (id, name) FROM '/tmp/interests_list.csv' WITH CSV HEADER; CREATE TABLE hope.providers ( id INTEGER PRIMARY KEY, provider TEXT UNIQUE NOT NULL, address TEXT NOT NULL ); COPY hope.providers (id, provider, address) FROM '/tmp/providers.csv' WITH CSV HEADER; CREATE TABLE hope.creation ( -- I cannot use -- REFERENCES hope.person (id) -- because this table contains all RFID ids, and person only those that logged in -- But as this table contains all IDs, all other tables will point here id INTEGER PRIMARY KEY, pin TEXT NOT NULL, registered INTEGER, time TIMESTAMP WITH TIME ZONE NOT NULL ); COPY hope.creation (id, pin, registered, time) FROM '/tmp/creation.csv' WITH CSV HEADER; CREATE TABLE hope.person ( id INTEGER PRIMARY KEY REFERENCES hope.creation (id), location TEXT, email TEXT NOT NULL, handle TEXT NOT NULL, password TEXT, room INTEGER, phone INTEGER, age INTEGER, sex INTEGER, country INTEGER REFERENCES hope.countries (id), edited INTEGER, provider INTEGER REFERENCES hope.providers (id), speaker INTEGER, admin INTEGER, reminder INTEGER, validation INTEGER, logged INTEGER ); COPY hope.person FROM '/tmp/person.csv' WITH CSV HEADER; CREATE TABLE hope.interests ( id INTEGER NOT NULL REFERENCES hope.person (id), interest INTEGER NOT NULL REFERENCES hope.interests_list (id) ); COPY hope.interests (id, interest) FROM '/tmp/interests.csv' WITH CSV HEADER; CREATE TABLE hope.ping ( id INTEGER NOT NULL REFERENCES hope.person (id), target INTEGER NOT NULL REFERENCES hope.person (id), time TIMESTAMP WITH TIME ZONE NOT NULL, kind TEXT NOT NULL ); COPY hope.ping (id, target, time, kind) FROM '/tmp/ping.csv' WITH CSV HEADER; CREATE TABLE hope.position_snapshot ( -- I cannot use -- REFERENCES hope.person (id) -- because this table contains all RFID ids, and person only those that logged in -- I also cannot point to hope.creation because of garbage ID 328249585 id INTEGER NOT NULL, time TIMESTAMP WITH TIME ZONE NOT NULL, area TEXT NOT NULL, x DOUBLE PRECISION, y DOUBLE PRECISION, z DOUBLE PRECISION ); COPY hope.position_snapshot (time, id, area, x, y, z) FROM '/tmp/position_snapshot.csv' WITH CSV HEADER; CREATE TABLE hope.snapshot_summary ( -- I cannot use -- REFERENCES hope.person (id) -- because this table contains all RFID ids, and person only those that logged in -- I also cannot point to hope.creation because of garbage ID 328249585 id INTEGER NOT NULL, time TIMESTAMP WITH TIME ZONE NOT NULL, area TEXT NOT NULL, period DOUBLE PRECISION NOT NULL ); COPY hope.snapshot_summary (time, id, area, period) FROM '/tmp/snapshot_summary.csv' WITH CSV HEADER; CREATE TABLE hope.speakers ( name TEXT UNIQUE NOT NULL, bio TEXT NOT NULL ); -- Original file has problems with commas COPY hope.speakers (name, bio) FROM '/tmp/speakers.csv' WITH CSV HEADER; CREATE TABLE hope.talks ( id INTEGER PRIMARY KEY, -- As it can contain more than one name, I cannot put here -- REFERENCES hope.speakers (name) speaker TEXT, -- Titles can repeat - Featured Speakers and Keynotes title TEXT NOT NULL, abstract TEXT NOT NULL, time TIMESTAMP WITH TIME ZONE NOT NULL, track TEXT NOT NULL ); -- Original file has problems with commas COPY hope.talks (id, speaker, title, abstract, time, track) FROM '/tmp/talks.csv' WITH CSV HEADER; CREATE TABLE hope.talks_list ( id INTEGER NOT NULL REFERENCES hope.person (id), talk INTEGER NOT NULL REFERENCES hope.talks (id) ); COPY hope.talks_list (id, talk) FROM '/tmp/talks_list.csv' WITH CSV HEADER; CREATE TABLE hope.talk_presence ( -- I cannot use -- REFERENCES hope.person (id) -- because this table contains all RFID ids, and person only those that logged in id INTEGER NOT NULL REFERENCES hope.creation (id), talk INTEGER NOT NULL REFERENCES hope.talks (id), -- In theory this points to handles from person table so I could put -- REFERENCES hope.person (handle) -- But here are original handles, and in person are "privatized" ones handle TEXT, -- Cannot put here -- REFERENCES hope.talks (title) -- because titles of talks are not unique title TEXT NOT NULL, track TEXT NOT NULL, time TIMESTAMP WITH TIME ZONE NOT NULL ); COPY hope.talk_presence (id, talk, handle, title, track, time) FROM '/tmp/talk_presence.csv' WITH CSV HEADER;