--- a/maxious-canberra-transit-feed/03-locatetimepoints.rb
+++ b/maxious-canberra-transit-feed/03-locatetimepoints.rb
@@ -1,1 +1,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
+
+
+