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();
}
}
}