1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 | #!/usr/bin/ruby require 'postgres' require 'highline.rb' include HighLine require 'yaml' require 'pp' Dir.chdir("output") def getTimePoints() $time_points = [] $time_points_sources = Hash.new([]) Dir.glob("*.yml") { |file| timetable = YAML::load_file(file) $time_points = $time_points | timetable["time_points"] timetable["time_points"].each do |timepoint| $time_points_sources[timepoint] = $time_points_sources[timepoint] | [ file ] end } end getTimePoints() $time_points.sort! connbus = PGconn.connect("localhost", 5432, '', '', "bus", "postgres", "snmc") connosm = PGconn.connect("localhost", 5432, '', '', "openstreetmap", "postgres", "snmc") if ask_if("Insert Timing Point names to database?") $time_points.each do |time_point| begin time_point = time_point.gsub(/\\/, '\&\&').gsub(/'/, "''") res = connbus.exec("INSERT INTO timing_point (name) VALUES ('#{time_point}')") puts "Put '#{time_point}' into DB" rescue PGError => e puts "Error inserting '#{time_point}' to DB #{e}" #conn.close() if conn end end end if ask_if("Fill null Timing Points from OSM bus_stop database?") begin null_points = connbus.exec('SELECT name FROM timing_point WHERE lat IS null OR lng IS null;') rescue PGError => e puts "Error selecting null points from DB #{e}" #conn.close() if conn end null_points.each do |null_point_name| begin name = null_point_name.to_s.gsub(/\\/, '\&\&').gsub(/'/, "''") pp name matching_nodes = connosm.exec("Select * FROM (SELECT * from current_node_tags, (Select id as ctagid FROM current_node_tags WHERE v LIKE '%#{name}%') as a where a.ctagid = current_node_tags.id) as ctags INNER JOIN current_nodes ON ctags.id=current_nodes.id") rescue PGError => e puts "Error selecting matching bus stops from DB #{e}" #conn.close() if conn end suggested_nodes = Hash.new() matching_nodes.each do |matching_node_row| #pp matching_node_row # 0 = id # 1 = k # 2 = v # 3,4 = redundant ids # 5 = lat*100000 # 6 = lng*100000 suggested_node = suggested_nodes.fetch(matching_node_row[0], {'lat' => Float(matching_node_row[5])/10000000, 'lng' => Float(matching_node_row[6])/10000000}) if matching_node_row[1] == "ref" matching_node_row[1] = "loc_ref" end suggested_node[matching_node_row[1]] = matching_node_row[2] suggested_nodes[matching_node_row[0]] = suggested_node end pp suggested_nodes nodeID = ask("Enter selected node ID:", :string) if suggested_nodes.has_key?(nodeID) node = suggested_nodes.fetch(nodeID) guess = ask_if("Is this a guess?") puts "Location #{node["lat"]},#{node["lng"]} for #{null_point_name}" begin res = connbus.exec("UPDATE timing_point SET lat = #{node["lat"]*10000000}, lng = #{node["lng"]*10000000},osm_node = #{nodeID}" + (node.has_key?("loc_ref") ? ",loc_ref = #{node["loc_ref"]}" : "") + ",guess = #{guess} WHERE name = '#{name}'") puts "Put '#{null_point_name}' into DB" rescue PGError => e puts "Error inserting '#{null_point_name}' to DB #{e}" ask_if("Continue?") #conn.close() if conn end else puts "Uhh, there was no suggestion ID like that. Try again next time!" end end end if ask_if("Fill null Timing Points from geocoder?") begin null_points = connbus.exec('SELECT name FROM timing_point WHERE lat IS null OR lng IS null;') rescue PGError => e puts "Error selecting null points from DB #{e}" #conn.close() if conn end null_points.each do |null_point_name| pp null_point_name end end |