|
#!/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 |
|
|
|
|
|
|