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 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 | #!/usr/bin/ruby require 'postgres' require 'highline.rb' include HighLine require 'rubygems' require 'json' require 'net/http' def cbr_geocode(query) base_url = "http://geocoding.cloudmade.com/daa03470bb8740298d4b10e3f03d63e6/geocoding/v2/find.js?query=" url = "#{base_url}#{URI.encode(query)}&bbox=-35.47,148.83,-35.16,149.25&return_location=true" resp = Net::HTTP.get_response(URI.parse(url)) data = resp.body # we convert the returned JSON data to native Ruby # data structure - a hash result = JSON.parse(data) # if the hash has 'Error' as a key, we raise an error if result.has_key? 'Error' raise "web service error" end return result end 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 name = null_point_name.to_s.gsub(/\\/, '\&\&').gsub(/'/, "''") results = cbr_geocode(null_point_name[0]) if !results.empty? results['features'].each_with_index { |feature,index| print "#{index}: #{feature['properties']['name']} (#{feature['location']}) => #{feature['centroid']['coordinates']}\n" } nodeID = ask("Enter selected node ID:", :integer) if results['features'].at(nodeID) != nil node = results['features'][nodeID] guess = ask_if("Is this a guess?") puts "Location #{node['centroid']['coordinates'][0]},#{node['centroid']['coordinates'][1]} for #{null_point_name}" begin res = connbus.exec("UPDATE timing_point SET lat = #{node['centroid']['coordinates'][0]*10000000}, lng = #{node['centroid']['coordinates'][1]*10000000},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 else puts "Uhh, there were no geocoding results. Try again next time!" end end end |