--- a/maxious-canberra-transit-feed/03-locatetimepoints.rb +++ b/maxious-canberra-transit-feed/03-locatetimepoints.rb @@ -12,7 +12,7 @@ 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 - +pp url # we convert the returned JSON data to native Ruby # data structure - a hash result = JSON.parse(data) @@ -51,12 +51,11 @@ 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(/'/, "''") + time_point = time_point.gsub(/\\/, '\&\&').gsub(/'/, "''") # DON'T PUT MORE GSUB HERE res = connbus.exec("INSERT INTO timing_point (name) VALUES ('#{time_point}')") puts "Put '#{time_point}' into DB" rescue PGError => e @@ -67,164 +66,6 @@ end -if ask_if("Fill null Timing Points from OSM bus_stop database?") -# TODO Where there's a "Cnr" or a \/ or a &, Look for 2 ways or nodes and average the closest two! - 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 - search_name = ask("Hmm, if we're still looking, the name is probably wrong. What's the right name?", :string, :default => name) - matching_nodes = connosm.exec("Select * FROM (SELECT * from current_node_tags, - (Select id as ctagid FROM current_node_tags WHERE v LIKE '%#{search_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 - - puts "Hmm, so maybe this isn't a point? Maybe it's a way.... like a street or something? Is it a 'street' or a 'corner' or nothing?" - whatisit = ask("So what is it:", :string, :default => "corner") - if whatisit == "street" - begin - name = null_point_name.to_s.gsub(/\\/, '\&\&').gsub(/'/, "''") - pp "#{name} (ways)" - search_name = ask("Streets tend to have pretty bad quality data, What's the real name of the street?", :string, :default => name) - matching_ways = connosm.exec("Select avg(latitude), avg(longitude), name FROM ( - SELECT * from current_way_nodes,(Select id as ctagid, v as name FROM current_way_tags WHERE k = 'name' AND v LIKE - '%#{search_name}%') as a where a.ctagid = current_way_nodes.id) as ctags INNER JOIN current_nodes ON ctags.node_id=current_nodes.id - GROUP BY name") - rescue PGError => e - puts "Error selecting matching ways from DB #{e}" - #conn.close() if conn - end - suggested_ways = Hash.new() - - matching_ways.each do |matching_way_row| - #pp matching_way_row - # 0 = lat*100000 - # 1 = lng*100000 - # 2 = name - suggested_way = suggested_ways.fetch(matching_way_row[2], {'lat' => Float(matching_way_row[0])/10000000, - 'lng' => Float(matching_way_row[1])/10000000}) - suggested_way['name'] = suggested_way['name'] - suggested_ways[matching_way_row[2]] = suggested_way - end - pp suggested_ways - wayID = ask("Enter selected way ID:", :string) - if suggested_ways.has_key?(wayID) - way = suggested_ways.fetch(wayID) - guess = ask_if("Is this a guess?") - puts "Location #{way["lat"]},#{way["lng"]} for #{null_point_name}" - begin - res = connbus.exec("UPDATE timing_point SET lat = #{way["lat"]*10000000}, lng = - #{way["lng"]*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 - end - if whatisit == "corner" - # Where there's a "Cnr" or a \/ or a &, look for 2 ways and find the intersections - - name = null_point_name.to_s.gsub(/\\/, '\&\&').gsub(/'/, "''") - search_name = ask("I need this to look like STREETNAME1/STREETNAME2, okay? Can you do that for me?", :string, :default => name) - search_pieces = search_name.split("/") - pp search_pieces - if search_pieces.length == 2 - begin - matching_ways = connosm.exec("SELECT w.way_id, latitude, longitude, w.node_id from (Select current_way_nodes.id as way_id, * from current_nodes inner join current_way_nodes on current_nodes.id=current_way_nodes.node_id inner join current_ways on current_way_nodes.id=current_ways.id) as w, (select node_id, count(node_id) from - (Select * FROM (SELECT * from current_way_nodes, (Select id as ctagid, v as name FROM current_way_tags WHERE k = 'name' AND v LIKE '%#{search_pieces[0]}%') as a where a.ctagid = current_way_nodes.id ) as ctags INNER JOIN current_nodes ON ctags.node_id=current_nodes.id where sequence_id = 1 union Select * FROM ( SELECT * from current_way_nodes, (Select id as ctagid, v as name FROM current_way_tags WHERE k = 'name' AND v LIKE '%#{search_pieces[0]}%') as a where a.ctagid = current_way_nodes.id ) as ctags INNER JOIN current_nodes ON ctags.node_id=current_nodes.id where sequence_id = (select max(sequence_id) from current_way_nodes cnodes where cnodes.id = ctags.id) union Select * FROM ( SELECT * from current_way_nodes, (Select id as ctagid, v as name FROM current_way_tags WHERE k = 'name' AND v LIKE '%#{search_pieces[1]}%') as a where a.ctagid = current_way_nodes.id ) as ctags INNER JOIN current_nodes ON ctags.node_id=current_nodes.id where sequence_id = 1 union Select * FROM ( SELECT * from current_way_nodes, (Select id as ctagid, v as name FROM current_way_tags WHERE k = 'name' AND v LIKE '%#{search_pieces[1]}%') as a where a.ctagid = current_way_nodes.id ) as ctags INNER JOIN current_nodes ON ctags.node_id=current_nodes.id where sequence_id = (select max(sequence_id) from current_way_nodes cnodes where cnodes.id = ctags.id) ) as t GROUP BY node_id HAVING ( COUNT(node_id) > 1 ) ) as s where s.node_id = w.node_id") - rescue PGError => e - puts "Error selecting matching bus stops from DB #{e}" - #conn.close() if conn - end - suggested_ways = Hash.new() - - matching_ways.each do |matching_way_row| - pp matching_way_row - # 0 = way_id - # 1 = lat*100000 - # 2 = lng*100000 - # 3 = node_id - suggested_way = suggested_ways.fetch(matching_way_row[3], {'lat' => Float(matching_way_row[1])/10000000, - 'lng' => Float(matching_way_row[2])/10000000}) - suggested_way['way_id'] = " " + matching_way_row[0] - suggested_way['node_id'] = matching_way_row[3] - suggested_ways[matching_way_row[3]] = suggested_way - end - pp suggested_ways - wayID = ask("Enter selected way ID:", :string) - if suggested_ways.has_key?(wayID) - way = suggested_ways.fetch(wayID) - guess = ask_if("Is this a guess?") - puts "Location #{way["lat"]},#{way["lng"]} for #{null_point_name}" - begin - res = connbus.exec("UPDATE timing_point SET lat = #{way["lat"]*10000000}, lng = - #{way["lng"]*10000000},osm_node = #{wayID} ,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 - 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;') @@ -236,7 +77,7 @@ 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]) + results = cbr_geocode(null_point_name.to_s.gsub("Shops", "")) if !results.empty? results['features'].each_with_index { |feature,index| print "#{index}: #{feature['properties']['name']} (#{feature['location']}) => #{feature['centroid']['coordinates']}\n"