public static void UpdateGeoCoding() { using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) { using (SqlCommand cmd = new SqlCommand("SELECT l1.[MLS #], [Address Number], [Address Direction], [Address Street], City, [State], Zip FROM [listings-residential] l1 LEFT JOIN [listings-residential-ext] l2 ON l1.[MLS #] = l2.[MLS #] WHERE l2.[MLS #] IS NULL", cn)) { cmd.CommandType = CommandType.Text; cmd.Connection.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { BingGeolocation?bl = BingGeocoder.ResolveAddress(dr[1].ToString() + " " + dr[2].ToString() + " " + dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(), "US"); if (bl != null) { float MLS = 0; float lat = 0; float lon = 0; float.TryParse(dr[0].ToString(), out MLS); float.TryParse(bl.Value.Lat.ToString(), out lat); float.TryParse(bl.Value.Lon.ToString(), out lon); if (lat != 0 && lon != 0) { UpdateLatLong(MLS, lat, lon); } } } cmd.Connection.Close(); } } }
public static void UpdateGeoCodingComm3() { using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) { string SQL = "SELECT l1.[OriginatingSystemKey], [UnparsedAddress] " + "FROM [listings-residential-onekey] l1 LEFT JOIN [listings-geo] l2 ON l1.[OriginatingSystemKey] = l2.[MLSNumber] " + "WHERE l2.[MLSNumber] IS NULL"; using (SqlCommand cmd = new SqlCommand(SQL, cn)) { cmd.CommandType = CommandType.Text; cmd.Connection.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { BingGeolocation?bl = BingGeocoder.ResolveAddress(dr[1].ToString()); if (bl != null) { float lat = 0; float lon = 0; float.TryParse(bl.Value.Lat.ToString(), out lat); float.TryParse(bl.Value.Lon.ToString(), out lon); if (lat != 0 && lon != 0) { UpdateLatLong3(dr[0].ToString(), lat, lon); } } } cmd.Connection.Close(); } } }