Geocoding or How to transform a list of addresses into Latitude/Longitude values and display them on a map? Sourcecode included!

Geocoding or How to transform a list of addresses into Latitude/Longitude values and display them on a map? Sourcecode included!

  • Comments 8

Since I did http://www.woistdaniel.de/ and occasionally talk about Virtual Earth I almost always get asked the same question. Someone has a list of companies/stores/medics/pharmacies/clubs/museums/”u name it” with the address consisting of the street, zip code and the city, wants to display those items on a map and provide additional information for them. Unfortunately I never had a comprehensive reference to point to and therefore decided to write a step by step guide on the above topic on my own ;-)

Geocode the data

Let’s get started with some random locations in Munich that should be visualized on a Virtual Earth map and imagine, that we have the data in an excel sheet like this:

Name Description AddressLine PostalCode PrimaryCity CountryRegion URL
Haus der Kunst Art museum in Munich with changing exhibitions. Prinzregentenstrasse 1 80538 Munich Germany http://www.hausderkunst.de/
Deutsches Museum The world's largest museum of technology and science. Museumsinsel 1 80538 Munich Germany http://www.deutsches-museum.de/
Asamkirche One of the most splendid achievements of Bavarian late Baroque architecture or rococo. Sendlinger Straße 32 80331 Munich Germany http://en.wikipedia.org/wiki/Asamkirche
Frauenkirche The landmark of Munich Frauenplatz 1 80331 Munich Germany http://www.muenchen.de/Tourismus/Sehenswuerdigkeiten/Muenchen_in_Bildern_neu/Webcam_c/198612/Frauenkirche.html
Hofbräuhaus Brewery in Munich. No explanation needed ;-) Platzl 9 80331 Munich Germany http://www.hofbraeuhaus.de

In order to place those items on a map we first have to geocode them which basically is determining the latitude and longitude coordinates for a physical address. While there are multiple possibilities to geocode data we are going to use the Virtual Earth Platform Customer Services since those services allow batch converting of hundreds of thousands of locations at once (972,222 to be exactly => start thinking big ;-) ). To make things even better there is a Virtual Earth Platform Developer Account which allows one to use the aforementioned services free of charge. Sign up here https://mappoint-css.live.com/mwssignup/, validate the confirmation email and that’s it.

Now that we have a Virtual Earth Platform Developer Account let's go back to our sample data. Geocoding them is as simple as uploading the data to the Virtual Earth Platform Customer Services site as geocoding your data happens automatically. To upload your data the file containing the data has to be either a Microsoft Office Access 2002 or Microsoft Office Access 2003 XML file with an embedded schema or a delimited-field flat file where the first row defines the schema. Valid delimiters are commas, tabs, or pipes ( | ) and only a single delimiter must be used consistently throughout the file.
Simply create a database like the following containing one table to hold the locations or download the one I used. The column EntityID has to be of Type Long Integer, the Latitude and Longitude column have to be of type Double.

Fill the table with your data and leave the columns Latitude and Longitude empty. These columns will later be filled automatically by the Virtual Earth Platform Customer Services.

Now export your data to an xml file by right-clicking on the table in the left and select export >> XML-file. Be sure to include the schema in the xml file on the following dialog and save the file.

As we now have the location data in a format the Virtual Earth Platform Customer Services can understand, log in to the Virtual Earth Platform Customer Services site and upload the data. Simply click on the "Create" Button in the Data Sources section on the right-hand side, fill out the dialog like this and click Create.

Your data will now be uploaded and geocoded resulting in this image after a short moment.

To download the data click the download link in the Data Sources section,

choose the output format

and find the downloadable file in the Recent jobs list an instance later.

Besides the included schema at the top of the file the geocoded data looks like this:

<dataroot>WoIstDanielConnectionString
      <MapPoint>
            <EntityID>1</EntityID>
            <Name>Haus der Kunst</Name>
            <Description>Art museum in Munich with changing exhibitions.</Description>
            <AddressLine>Prinzregentenstrasse 1</AddressLine>
            <PostalCode>80538</PostalCode>
            <PrimaryCity>Munich</PrimaryCity>
            <CountryRegion>Germany</CountryRegion>
            <URL>http://www.hausderkunst.de/</URL>
            <Latitude>48.143927081912203</Latitude>
            <Longitude>11.5844456484113</Longitude>
            <MatchCode>Good</MatchCode>
            <MatchedMethod>Address</MatchedMethod>
            <MatchedAddress>Prinzregentenstraße 1, 80538 München</MatchedAddress>
      </MapPoint>
      <MapPoint>
            <EntityID>2</EntityID>
            <Name>Deutsches Museum</Name>
            <Description>The world's largest museum of technology and science.</Description>
            <AddressLine>Museumsinsel 1</AddressLine>
            <PostalCode>80538</PostalCode>
            <PrimaryCity>Munich</PrimaryCity>
            <CountryRegion>Germany</CountryRegion>
            <URL>http://www.deutsches-museum.de/</URL>
            <Latitude>48.130676514922399</Latitude>
            <Longitude>11.5836371247683</Longitude>
            <MatchCode>Ambiguous</MatchCode>
            <MatchedMethod>Address</MatchedMethod>
            <MatchedAddress>Museumsinsel 1, 80538 München</MatchedAddress>
      </MapPoint>
     
...
      <MapPoint>
            <EntityID>5</EntityID>
            <Name>Hofbräuhaus</Name>
            <Description>Famous Brewery in Munich.</Description>
            <AddressLine>Platzl 9</AddressLine>
            <PostalCode>80331</PostalCode>
            <PrimaryCity>Munich</PrimaryCity>
            <CountryRegion>Germany</CountryRegion>
            <URL>http://www.hofbraeuhaus.de/</URL>
            <Latitude>48.137615987119098</Latitude>
            <Longitude>11.579696959390899</Longitude>
            <MatchCode>Good</MatchCode>
            <MatchedMethod>Address</MatchedMethod>
            <MatchedAddress>Platzl 9, 80331 München</MatchedAddress>
      </MapPoint>
</dataroot>

Visualize the geocoded data on a Virtual Earth map

Lets start with some infrastructure and build a webservice that returns a List(Of Location) which we will use later.

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel

' ScriptService >> To allow this Web Service to be called from script, using ASP.NET AJAX.
<System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class Locations
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function GetLocations() As List(Of Location)
        Dim _xmlLocations = String.Format("{0}App_Data\MapPointOutput.xml", AppDomain.CurrentDomain.BaseDirectory)
        Dim query = From item In XElement.Load(_xmlLocations)...<MapPoint> _
                    Select New Location With {.Name = item.<Name>.Value, _
                                              .Description = item.<Description>.Value, _
                                              .MatchedAddress = item.<MatchedAddress>.Value, _
                                              .URL = item.<URL>.Value, _
                                              .Latitude = item.<Latitude>.Value.ReplaceDecimalSeparator, _
                                              .Longitude = item.<Longitude>.Value.ReplaceDecimalSeparator}
        Return query.ToList
    End Function

End Class

Public Class Location
    Public Name As String
    Public Description As String
    Public MatchedAddress As String
    Public URL As String
    Public Latitude As Double
    Public Longitude As Double
End Class

Public Module Tools
    ''' <summary>
    ''' This tiny function enables to call ReplaceDecimalSeparator on any String Type
    ''' </summary>
    ''' <param name="Value"></param>
    ''' <returns></returns>
    ''' <remarks>Needed to convert a value into a Double. Based on the decimalseparator the conversion from String to Double can sometimes return wrong values.</remarks>
    <Runtime.CompilerServices.Extension()> _
    Public Function ReplaceDecimalSeparator(ByVal Value As String) As Double

        If String.IsNullOrEmpty(Value) Then
            Return 0
        End If

        Return CDbl(Value.Replace(".", Globalization.NumberFormatInfo.CurrentInfo.NumberDecimalSeparator))

    End Function

End Module

Note that we are using LINQ to XML to query the data we want to show on the map and the use of the extension method ReplaceDecimalSeparator which makes programming much more intuitive. Sweet :-)

In order to be able to consume this webservice from our website with JavaScript we are using the ASP.NET Ajax Framework, or - to be more specific - the ScriptManager and include a reference to the webservice in the Scripts section of the ScriptManager. We also need to place a <div /> tag on the website to hold the Virtual Earth map. The final result can be seen here:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="ShowLocationsOnVirtualEarth._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>How to show a list of locations on a Virtual Earth map.</title>
    <script src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6" type="text/javascript"></script>

    <script type="text/javascript">

        function pageLoad() {
            // InitializeMap is located in VEMap.js and takes care
            // that the map gets initialized and loads the data
            // afterwards

            InitializeMap();
        }

    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
            <Scripts>
                <asp:ScriptReference Path="~/JavaScript/VEMap.js" />
            </Scripts>
            <Services>
                <asp:ServiceReference Path="~/WebServices/Locations.asmx" />
            </Services>
        </asp:ScriptManager>

        <div id='myMap' style="position:absolute; top:0px; left:0px; width:800px; height:600px;"></div>

    </div>
    </form>
</body>
</html>

Additionally some JavaScript is needed to actually draw the locations on the map (This is the file VEMap.js referenced in the above code).

var map = null;                 // this var holds the VE map

// Initializes the VE map and calls the method to load the data
function InitializeMap() {

    // make sure map is initialized
    if (map == null) {
        map = new VEMap('myMap');   // initialize map
        map.LoadMap();              // load it
        map.SetMapStyle('r');       // set the MapStype to "Road"
        map.SetCenter(new VELatLong("48.132241398091125", "11.575480699539175"));   // and zoom to Munich
    }

    // load the locations and display them on the map

    GetLocations();
}

 

// calls the WS and retrieves the Locations. Since this call is async we need
// to wire up a method which should be called when the call to the webservice
// succeeds and one if the call fails
function GetLocations() {

    // wire up the methods to call in case of success and failure
    ShowLocationsOnVirtualEarth.Locations.set_defaultSucceededCallback(OnSucceededCallbackRoute);
    ShowLocationsOnVirtualEarth.Locations.set_defaultFailedCallback(OnErrorCallbackRoute);

    // call the webservice
    ShowLocationsOnVirtualEarth.Locations.GetLocations();
}

 

// Callback function invoked when the call to
// the Web service methods succeeds.
function OnSucceededCallbackRoute(result) {

 

    // draw the locations on the map
    DrawLocationsOnMap(result);
}

 

// this function draws the locations on a map
function DrawLocationsOnMap(ListOfLocations) {

    if ((map != null) && (ListOfLocations != null) && (ListOfLocations.length > 0)) {

 

        // this array holds all the VELatLong values
        // used later to set the zoom of the map to display
        // all the locations 
        var points = new Array();
        var vELatLong;

 

        // draw the Locations on the map
        for (i = 0; i < ListOfLocations.length; i++) {          

            vELatLong = CreateVELatLongFromLocations(ListOfLocations[i]);
            points[i] = vELatLong;           

            // add the location to the map
            map.AddShape(AddDot(vELatLong, 
                                ListOfLocations[i].Name, 
                                ListOfLocations[i].Description, 
                                ListOfLocations[i].MatchedAddress,
                                ListOfLocations[i].URL));

 

            // Zoom the map according to all locations
            map.SetMapView(points);
        }
    }
}

 

// ************************************************************
// *** some tools ;-)                                       ***
// ************************************************************

// Converts a Location into a VELatLong object
// Parameters:
// Location: a Location
// return: a VELatLong instance
function CreateVELatLongFromLocations(Location) {
    return new VELatLong(Location.Latitude, Location.Longitude);
}

 

 

// Creates a custom VEShape (which represents a dot on the map)
function AddDot(Point, Name, Description, MatchedAddress, URL) {

 

    var shape = new VEShape(VEShapeType.Pushpin, Point);
    shape.SetTitle(Name);   

    var descriptionText = '<b>Description:</b> ' + Description + 
                          '<br /><br /><b>Address:</b> ' + MatchedAddress + 
                          '<br /><br /><b>Homepage:</b> <a href = "' + URL + '">' + URL + '</a>';   

    shape.SetDescription(descriptionText);
    shape.SetCustomIcon('<div><img src="images/info.png" /></div>');
    return shape;
}

 

 

// Callback function invoked when the call to
// the Web service methods fails.
function OnErrorCallbackRoute(error) {
    alert("Error (route): " + error.get_message());
}

 

if(typeof(Sys) !== "undefined") Sys.Application.notifyScriptLoaded();

And that's it resulting in

To make life even easier you can download the entire solution here and the database here.

Cheers. Be sure to let me know what you think!

   Daniel

P.S.: As always this code is "as is" and NOT intended to be used in product use! Note also that this approach might not be the best solution if you only want to display one map with no interaction. If this is your goal make sure to embed the location data in your site to prevent the additional postback to the server (probably using RegisterClientScriptBlock or similar).

P.P.S. You can geocode your data programmatically using the CustomerDataService class as well. A good place to start is http://msdn2.microsoft.com/en-us/library/aa491870.aspx.

Leave a Comment
  • Please add 1 and 7 and type the answer here:
  • Post
  • PingBack from http://msdnrss.thecoderblogs.com/2007/12/15/

  • Hi Daniel,

    Why do you create a function "ReplaceDecimalSeperator" while you could just use

    number = Double.Parse(Text,CultureInfo.InvariantCulture.NumberFormat)

    Messing around with modifying strings is not the way I would prefer to handle locale specific issues.

  • How might i do this where the user can search for an unlimited number of search locations.

    For example i have a form where i want to search for recycling centers in any city in the united states.  I'd like to just call an API a single time to pass in "city, state, zip" and then get the actual lat and long values for this location?

    You're help would much be appreciated.  thanks,

    aaron

  • Aaron,

    check the MapPoint Web Service (http://www.microsoft.com/mappoint/products/webservice/default.mspx) and the Resources for Developers (http://msdn.microsoft.com/mappoint/)

    Cheers!

      Daniel

  • This is terrific Daniel, thanks very much!

  • Its Nice to see Yoru Information. Many useful ideas and suggestions on the blogs through search make it easier to find. This blogs highly recommended.

  • Many useful ideas and suggestions on the blogs through search make it easier to find. This blogs starts here.

  • Its Nice to see this topic Information. Many useful ideas and suggestions on the blogs through search make it easier to find.

Page 1 of 1 (8 items)