Suggest timepoints using OSM database of POIs
[bus.git] / maxious-canberra-transit-feed / 03-locatetimepoints.rb
blob:a/maxious-canberra-transit-feed/03-locatetimepoints.rb -> blob:b/maxious-canberra-transit-feed/03-locatetimepoints.rb
--- a/maxious-canberra-transit-feed/03-locatetimepoints.rb
+++ b/maxious-canberra-transit-feed/03-locatetimepoints.rb
@@ -3,6 +3,32 @@
 
 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
+class Array
+
+   def find_dups
+      inject(Hash.new(0)) { |h,e| h[e] += 1; h }.select { |k,v| v > 1 }.collect { |x| x.first }
+   end
+end
 
 require 'yaml'
 require 'pp'
@@ -31,7 +57,7 @@
 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(/'/, "''").gsub("St", "%")
 			res = connbus.exec("INSERT INTO timing_point (name) VALUES ('#{time_point}')")
 			puts "Put '#{time_point}' into DB"
 		rescue PGError => e
@@ -43,6 +69,7 @@
 
 
 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
@@ -54,8 +81,9 @@
 		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 '%#{name}%') as a 
+                        (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
@@ -99,6 +127,103 @@
 		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?")
@@ -111,6 +236,31 @@
 
 	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]
+				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 = true 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