public static GoogleGeocodeResponse GeoCode(string inputAddress, string apiKey) { try { string sURL = "https://maps.googleapis.com/maps/api/geocode/json?address=" + inputAddress + "&key=" + apiKey; WebClient webClient = new WebClient(); Stream stream = webClient.OpenRead(sURL); StreamReader reader = new StreamReader(stream); String responseJson = reader.ReadToEnd(); JavaScriptSerializer jsonSerializer = new System.Web.Script.Serialization.JavaScriptSerializer(); GoogleGeocodeResponse geocoderesult = jsonSerializer.Deserialize <GoogleGeocodeResponse>(responseJson); return(geocoderesult); } catch (Exception) { logger.Info("Exception. Geocoding address:" + inputAddress); throw; } }
public static void Main() { //--FOR EACH RECORD //---- make a web service call to google to get lat long //---- Parse the result //-- Update property table and set the Lat and Long fields in the databae to the result given by google //--END LOOP //--> Connect to Oracle <-- MyDBConnector DBConnector = new MyDBConnector("allproperty", "zacchi_star", "localdb"); string sql = "select * from allproperties where nvl(isgeocoded, 0) != 1"; DBConnector.OpenConnection(); OracleCommand cmd = new OracleCommand(sql, DBConnector.Connection); OracleDataReader dr = cmd.ExecuteReader(); //--- Connect to Oracle simpler way --- //OracleConnection con; //con = new OracleConnection(); //con.ConnectionString = "User Id=allproperty;Password=zacchi;Data Source=localdb"; //con.Open(); //string sql = "select * from allproperties where isgeocoded != 1 or isgeocoded is null"; //OracleCommand cmd = new OracleCommand(sql, con); //OracleDataReader dr = cmd.ExecuteReader(); // Get Geocode information from Google var apiKey = "AIzaSyD7FUliTBfIW4_TW4IFq9ZiJP23putrTeI"; int countKey = 0;// key change count while (dr.Read() == true) { long propertyId = (long)dr["PropertyId"]; string dbAddress = (string)dr["Address"]; string dbPostalCode = (string)dr["PostalCode"]; string dbCounty = (string)dr["County"]; string country = "Republic of Ireland"; string dbFullAddress = dbAddress + ", " + dbPostalCode + ", " + dbCounty + ", " + country; GoogleGeocodeResponse g = GeoCode(dbFullAddress, apiKey); logger.Info(g.Status); if (g.Status.Equals("OVER_QUERY_LIMIT")) { apiKey = "AIzaSyCahm8jN84LMOuAYbUEA6_Mquank8AQsR8"; //AIzaSyD7FUliTBfIW4_TW4IFq9ZiJP23putrTeI logger.Info("API Key has been changed to:" + apiKey); countKey++; logger.Info("count: " + countKey); if (countKey == 2) { logger.Error("Geocode limit has been reached. Program will shutdown."); break; } else { continue; } } // stop the loop after g.Status from google geocode has changed the key once. if (g.Results.Count == 0) { Console.WriteLine("Unable to geocode property: " + dbFullAddress); Console.WriteLine(g.Status); //string updateSQLFailure = "update allproperties set geocodenotfound = 1 where propertyid =" + propertyId; //OracleCommand updCmd1 = new OracleCommand(updateSQLFailure, con); //updCmd1.ExecuteScalar(); continue; } string updatesql = "update allproperties set lat = " + g.Results[0].Geometry.Location.Lat + ", lng =" + g.Results[0].Geometry.Location.Lng + ", isgeocoded = 1 where propertyid = " + propertyId; OracleCommand updCmd = new OracleCommand(updatesql, DBConnector.Connection); updCmd.ExecuteScalar(); } DBConnector.CloseConnection(); }