2016-03-04

How to work with DbGeography with .NET?

In this post I will demonstrate use ob DbGeography by using simple ADO.NET calls.

Before there was Entity Framework we had to manually code our business logic towards database. We were used to write connection strings and SQL statements. To be honest, for some utilities I still use this old fashioned approach. It is clean and quick way to deliver results fast.

I had to prepare utility script to geocode some of the addresses stored in database. This basically means converting physical address to GPS coordinates. My input data were stored as addresses. Something like White House is located at 1600 Pennsylvania Ave NW, Washington, DC 20500, USA. And based on this data I had to get GPS coordinates of the address, in this example: 38.897096° latitude and -77.036545° longitude.

I used Geocoding.Google.GoogleGeocoder library to geocode address.

How I did it?

// UPDATE LOCATION:
private void UpdateLocation(int id, string street, string city, string country)
{
  System.Globalization.CultureInfo customCulture = (System.Globalization.CultureInfo)System.Threading.Thread.CurrentThread.CurrentCulture.Clone();
  customCulture.NumberFormat.NumberDecimalSeparator = ".";
  System.Threading.Thread.CurrentThread.CurrentCulture = customCulture;

  // GEOCODE ADDRESS
  IGeocoder geocoder = new Geocoding.Google.GoogleGeocoder() { };
  Address[] addresses = geocoder.Geocode(country + "," + city + "," + street).ToArray();

  // use google maps geocoding
  int SridGoogleMaps = 4326;

  DbGeography location;

  if (addresses.Count() > 0)
    {
      // use firs one as this is probably most accurate hit
      location = DbGeography.PointFromText("POINT(" + addresses[0].Coordinates.Longitude.ToString() + " " + addresses[0].Coordinates.Latitude.ToString() + ")", SridGoogleMaps);

      // update database
      string _connectionString = "Data Source=;Initial Catalog=;Integrated Security=False;User ID=cent;Password=;Connect         Timeout=60;Encrypt=False;TrustServerCertificate=False";
      SqlConnection cn = new SqlConnection();
      cn.ConnectionString = _connectionString;

      SqlCommand cm = new SqlCommand();
      string SQL = "Update units set Location=@Location where UnitId=@UnitId";

      cm.Connection = cn;
      cm.CommandText = SQL;
      cm.Parameters.AddWithValue("@UnitId", id);

      cm.Parameters.Add("@Location", SqlDbType.NVarChar);
      cm.Parameters["@Location"].Value = location.AsText();

      cn.Open();
      cm.ExecuteNonQuery();
      cn.Close();
                
    };
}

Notice that I had to change Culture, since I live in a region that uses comma (,) as decimal character.

No comments:

Post a Comment