Freigeben über

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

  • Artikel

Since I did https://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 https://www.hausderkunst.de/
Deutsches Museum The world's largest museum of technology and science. Museumsinsel 1 80538 Munich Germany https://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 https://en.wikipedia.org/wiki/Asamkirche
Frauenkirche The landmark of Munich Frauenplatz 1 80331 Munich Germany https://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 https://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>https://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>https://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>https://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.ServicesImports System.Web.Services.ProtocolsImports 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:="https://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 DoubleEnd 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" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="https://www.w3.org/1999/xhtml" ><head runat="server">    <title>How to show a list of locations on a Virtual Earth map.</title>    <script src="https://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 datafunction 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 failsfunction 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 mapfunction 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 instancefunction 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 https://msdn2.microsoft.com/en-us/library/aa491870.aspx.

Comments

  • Anonymous
    December 15, 2007
    PingBack from http://msdnrss.thecoderblogs.com/2007/12/15/

  • Anonymous
    January 23, 2008
    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.

  • Anonymous
    March 31, 2008
    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

  • Anonymous
    May 09, 2008
    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

  • Anonymous
    September 07, 2008
    This is terrific Daniel, thanks very much!

  • Anonymous
    March 28, 2010
    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.

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

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