Bing Map REST Service: Address GPS coordinate lookup with C#

Have you ever wondered how to look up latitude and longitud for your own address using Google or Bing Maps? I’m currently working with Microsoft Dynamics CRM where addresses is an important part of the sales and marketing processes. Therefore I wanted to be able to look up GPS coordinates for addresses like invoice, delivery and visiting. Last week I had some spare time to make deep dive into the world of Bing Maps REST service and the Location API.

After some testing of examples, I found out that the “Find a Location by Query” was the best way for me. I already had Bing Map key that is required for all requests against the Bing Map REST Service. The request URL that is sent to Bing Map REST Service consist of 5 parts:

  1. Base URL = “http://dev.virtualearth.net/REST/v1/Locations?”
  2. Actual search parameters = My address = “Ringstadåsen 39, Rolvsøy, Norge”
  3. Culture parameter = “nb-NO” (Norwegian)
  4. Output format = “xml”
  5. Include Neighbourhood (inclnb=1)
  6. Bing Map Key = “xxx” (where ‘xxx’ in my case is a 101 character long string).

URL after base URL:

culture=nb-NO&query=Ringstadåsen%2039%20Rolvsøy%20Norge&inclnb=1&output=xml&key=xxx

The actual address is located in the query parameter and spaces must be replaced with %20. This URL is executed through a standard HttpWebRequest and the result is given from a standard HttpWebResponse and delivered to caller in an XmlDocument.

public XmlDocument Execute(string url)
{
    var request = WebRequest.Create(url) as HttpWebRequest;
    var response = request.GetResponse() as HttpWebResponse;
    var xmlDoc = new XmlDocument();
    xmlDoc.Load(response.GetResponseStream());
    return (xmlDoc);
}

The returned XML is not complicated. Full XML is located on my dropbox. Here is the “main section” for my query above:

Bing_Location
I wrote some extension methods to retrieve values from the XmlDocument

public static class XmlExtensions
{
    public static string GetTag(this XmlDocument doc, 
                                XmlNamespaceManager nsmgr, string tag)
    {
        var path = string.Format(".//rest:{0}", tag);
        var entry = doc.SelectSingleNode(path, nsmgr);
        return entry != null ? entry.InnerText : null;
    }

    public static string GetTag(this XmlNode  node, 
                                XmlNamespaceManager nsmgr, string tag)
    {
        var path = string.Format(".//rest:{0}", tag);
        var entry = node.SelectSingleNode(path, nsmgr);
        return entry != null ? entry.InnerText : null;
    }
}

The first thing you need to is to retieve the status code from the XmlDocument. With the extension method listed above this is done by the code:

var lat = location.GetTag(nsmgr, "Latitude");
var lon = location.GetTag(nsmgr, "Longitude");

This were the main points from the source code.

Full source code: BingMap3.zip

Happy Bing’ing 🙂

Advertisements

2012 – most popular blog posts

It’s time to summerize my blog year 2012.

Until my summer vacation, the number of visitors to my blog weren’t great at all. I had a monthly average on approxematly 100 hits per month. But after I started to write more posts from April/May, I got more hits. And when I started to become more active on the Microsoft MSDN forum, the monthly hits raised from 100 till 500. This is probably not great, but I’m satisfied. There are some souls that is following my blog and take time to read them.That is encouraging 🙂

My top 5 blogs, with most hits, for 2012 are:

  1. Read/write chunked data in C#
  2. Force TFS to sync with Active Directory
  3. CRM Email Router: enabling logging
  4. Report Builder 3.0 tutorial
  5. OpenXML SDK: reading Excel 2007/2010 documents

The post with “Chunked data” has been one of my posts with most hits – ever. It is not a great blog post IMO, but I have decied to write a follow-up with better examples during a few months to give the reader more value for their time and efford.

The post with forcing syncronisation of the TFS persmissions is just a refferal to another blog post with a few pointers. This is a common TFS problem, and referred post gives a solution. I will write a more detailed blog post on this to explain why and how this works for TFS 2012.

The last year I have started to work more with Microsoft Dynamics CRM. As a result, I have been writing more posts on this topic. The logging functionality in CRM can be a bit tricky and not easy to undertand fully, and it seems like people have troubles with debugging tasks in Dynamics CRM. I have done alot of debugging the last year for many customer and I have gained experience worth sharing. I will definitly wrote more blog post on CRM, installation/deployement and CRM in general in 2012.

The blog post on report builder 3.0 is just a referral to the Microsoft information, but many referrrals have been directed from google and bing during the last year, so it seems like Microsoft have some work to be done SQL Server Reporting Service to get this working properly. I haven’t worked with Reporting Serice alot the last year, but I have done some report with Dynamics CRM and FetchXML.

The last top post is a post on Office automation in Office 2007/2010 on how to read and write values in Excel. This a definitily a topic that I can write more post on. I’m kind of nerd when it come to reporting and prefer Excel to Reporting Serivice. I have used Excel a reporting client as a result that I have been more involved with technical people on-site with customers than middel and top management.

SQL – last backup time (db + transaction log)

I don’t normally setup much maintenance plans as developer, but today I had to do some monitoring and troublehooting. During this work, I discovered lots of errors and in the event viewer and large transaction log files on the disk, resulting in low disk space on the production SQL server.

Last backup time for transaction logs:

SELECT   
    d.name,
    MAX(b.backup_finish_date) AS backup_finish_date
FROM  master.sys.sysdatabases d
   LEFT OUTER JOIN msdb..backupset b
      ON       b.database_name = d.name
          AND  b.type = 'L'
GROUP BY d.name
ORDER BY backup_finish_date DESC

Last backup time for databases:

SELECT 
   sdb.Name AS DatabaseName,
   COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') 
      AS LastBackUpTime
 FROM sys.sysdatabases sdb
	LEFT OUTER JOIN msdb.dbo.backupset bus 
		ON bus.database_name = sdb.name
 GROUP BY sdb.Name

TFS – list files across team projects by SQL

When working with TFS you often need to list files from the source control. This can be done in many ways, but as a geek, I want a SQL script to list and search for files. Off course, I can use the TFS Power Tool and the “Find in source control…” function.

But here is a script for listing and searching for files accross team projects. The below example searches for all files with filename containing ‘ADSync’.

SELECT 
  [FileName]
  ,SUBSTRING([FilePath], 1, 
        CHARINDEX('/', [FilePath], 3)) as TeamProject
  ,[FilePath]
  ,[LastUpdatedDateTime]
FROM [Tfs_Warehouse].[dbo].[DimFile]
WHERE [FileName] LIKE '%ADSync%' 
  AND [FileExtension] LIKE '%';

The below statement will count number of aspx files across all team project.

SELECT 
  SUBSTRING([FilePath], 1, CHARINDEX('/', [FilePath], 3)), 
  FileExtension,
  COUNT(*)
FROM [Tfs_Warehouse].[dbo].[DimFile]
WHERE FileExtension in ('.aspx')
GROUP BY 
   SUBSTRING([FilePath], 1, CHARINDEX('/', [FilePath], 3)), 
   FileExtension

SQL Server: list transaction log query

Today, I came across a nice little SQL statement that list the content of the transaction log for a particular database. I haven’t investigated further, but there probably exists standard reports and/or 3rd party tools that list you the same thing, but as a geeky developer, I like SQL statements better 🙂

SELECT 
   year(a.[Begin Time]) as dbYear, 
   month(a.[Begin Time]) as db, 
   count(*) 
FROM sys.fn_dblog(NULL, NULL) a 
WHERE Operation IN (
	'LOP_INSERT_ROWS',
	'LOP_MODIFY_ROW', 
	'LOP_DELETE_ROWS',
	'LOP_BEGIN_XACT',
	'LOP_COMMIT_XACT') 
group by year(a.[Begin Time]), month(a.[Begin Time])
order by year(a.[Begin Time]), month(a.[Begin Time])