Freddys: Integration to Virtual Earth – Part 1 (out of 4)

[Blog bot]

Disclaimer
Please note that this is an example on how to do geocoding and Virtual Earth integration. There are probably a lot of different ways to do the same, and this might not work in all areas of the world. I do however think that there is enough information in the post that people can make it work anywhere and the good thing about the samples and demos I post in my blog is, that they are free to be used as partners and customers see fit. My samples doesn’t come with any warranty and if installed at customer site, the partner and/or customer takes full responsibility.

Following this sample also does not release you from following any license rules of the products used in the blog (note that I don’t know these rules)


Mappoint or Virtual Earth
As you probably know there already is an integration to Mappoint in NAV 2009. This integration makes it possible to open a map for a given customer (or create route description on how to get there). The way it works is, that when you request an online map, a URL is created which will open a map centered on the requested customer. It is a one way integration – meaning that we can see a map and/or calculate routes.

But… – it is a one way integration. Wouldn’t it be cool if we could request NAV for all customers in a range of 10 miles from another customer – display all customers on a map and have information included directly on Virtual Earth with phone numbers, orders and other things.

That is what this is all about…

But in order to do that, we need more information on our customers than just an address, a city and a country, as this information is hard to query. How would NAV know that Coventry is near Birmingham – if we don’t tell it.

The idea is off course to add geocode information to all customers in our customer table.

We can do this the hard way (typing them in), the other “easy” way (create an automation object which does the trick for you).


Latitude and Longitude
I am not (and I wouldn’t be capable of) trying to describe in details what Latitude and Longitude is – if you want this information you should visit

http://en.wikipedia.org/wiki/Latitude

and

http://en.wikipedia.org/wiki/Longitude

Not that it necessarily helps a lot, but there you have it.

A simpler explanation can be found on http://www.worldatlas.com, which is also, where this image is from

 

Click the image to take you directly to the description.

In this map, coordinates are described as degrees, minutes and seconds + a direction in which this is from the center (N, S, E, W).

There are different ways to write a latitude and a longitude – in my samples I will be using decimal values, where Latitude is the distance from equator (positive values are on the northern hemisphere and negative values are on the southern) and Longitude is the distance from the prime meridian (positive values are going east and negative values are going west). This is the way Microsoft Virtual Earth uses latitude and longitude in the API.

Underneath you will find a map with a pushpin in 0,0.



Another location (well known to people in the Seattle area) is Latitude = 47.6 and Longitude = -122.33, which on the map would look like:



Yes – the Space Needle.

I think this is sufficient understanding to get going.


Preparing your customer table
First of all we need to create two fields in the customer table, which will hold the geocode information of the customer.



Set the Decimalplaces for both fields to 6:8 and remember to create a key, including the two fields (else your searches into the customer table will be slow)













You also need to add the fields to the Customer Task Page, in order to be able to edit the values manually if necessary.


Virtual Earth Web Services
In order to use the Microsoft Virtual Earth Web Services you need an account. I do not know the details about license terms etc., but you can visit

https://mappoint-css.live.com/MwsSignup

for signing up and/or read about the terms. I do know that an evaluation developer license is free – so you can sign up for getting one of these – knowing of course that you probably cannot use this for your production data – please contact maplic@microsoft.com for more information on this topic.

Having signed up for a developer account you will get an account ID and you will set a password which you will be using in the application working with the Virtual Earth Web Services. This account ID and Password is used in your application when connecting to Web Services and you manage your account and/or password at

https://mappoint-css.live.com/CscV3

You will also find a site in which you can type in your Account ID and password to test whether it is working.

On these sites there are a number of links to Mappoint Web Services – this is where the confusion started for me…

I wrote some code towards Mappoint and I quickly ran into problems having to specify a map source (which identifies the continent in which I needed to do geocoding). I really didn’t want this, as this would require setup tables and stuff like that in my app. After doing some research I found out that Virtual Earth exposes Web Services to the Internet, which are different from Mappoint (I do not have any idea why). A description of the Virtual Earth Web Services API can be found here:

http://msdn.microsoft.com/en-us/library/cc980922.aspx

and a description of the geocode service can be found here:

http://msdn.microsoft.com/en-us/library/cc966817.aspx

and yes, your newly assigned account and password for Mappoint services also works for Virtual Earth Web Services.

The way it works is, that when connecting to Virtual Earth Web Services you need to supply a valid security token. This token is something you request from a different web service and when requesting this token, you specify the number of minutes the token should be valid (Time-To-Live).

Confused?


Creating a COM automation object for geocoding addresses
If you think you have the grasp around the basics of the Virtual Earth Web Services – let’s get going…

First of all – fire up your Visual Studio 2008 SP1 and create a new Class Library (I called mine NavMaps).

Add the CLSCompliant(true) to the AssemblyInfo.cs file (I usually to this after the ComVisible(false) line).

// Setting ComVisible to false makes the types in this assembly not visible
// to COM components.  If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(false)]
[assembly: CLSCompliant(true)]

After this you need to sign the assembly. Do this by opening properties of project, go to the Signing TAB,  check the “Sign the assembly” checkbox and select new – type in a filename and password protect the key file if you want to (I usually don’t).

Next thing is to create the COM interface and Class – the interface we want is:

[ComVisible(true)]
[Guid("B1F26FE7-0EA0-4883-BD6A-0398F8D2B139"), InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface INAVGeoCode
{
    string GetLocation(string query, int confidence, ref double latitude, ref double longitude);
}

For the implementation we need a Service Reference to:

http://staging.dev.virtualearth.net/webservices/v1/geocodeservice/geocodeservice.svc?wsdl ://http://staging.dev.virtualearth.net...rvice.svc?wsdl

called

GeocodeService

Note that the configuration of this Service Reference will be written in app.config – I will touch upon this later.

The implementation could be:

[ComVisible(true)]
[Guid("9090DF4C-FB24-4a4b-9E49-3924353A6040"), ClassInterface(ClassInterfaceType.None)]
public class NAVGeoCode : INAVGeoCode
{
    private string token = null;
    private DateTime tokenExpires = DateTime.Now;

    /// <summary>
    /// Geocode an address and return latitude and longitude
    /// Low confidence is used for geocoding demo data - where the addresses really doesn't exist:-)
    /// </summary>
    /// <param name="query">Address in the format: Address, City, Country</param>
    /// <param name="confidence">0 is low, 1 is medium and 2 is high confidence</param>
    /// <param name="latitude">returns the latitude of the address</param>
    /// <param name="longitude">returns the longitude of the address</param>
    /// <returns>Error message if something went wrong</returns>
    public string GetLocation(string query, int confidence, ref double latitude, ref double longitude)
    {
        try
        {
            // Get a Virtual Earth token before making a request
            string err = GetToken(ref this.token, ref this.tokenExpires);
            if (!string.IsNullOrEmpty(err))
                return err;

            GeocodeService.GeocodeRequest geocodeRequest = new GeocodeService.GeocodeRequest();

            // Set the credentials using a valid Virtual Earth token
            geocodeRequest.Credentials = new GeocodeService.Credentials();
            geocodeRequest.Credentials.Token = token;

            // Set the full address query
            geocodeRequest.Query = query;
            // Set the options to only return high confidence results
            GeocodeService.ConfidenceFilter[] filters = new GeocodeService.ConfidenceFilter[1];
            filters[0] = new GeocodeService.ConfidenceFilter();
            switch (confidence)
            {
                case 0:
                    filters[0].MinimumConfidence = GeocodeService.Confidence.Low;
                    break;
                case 1:
                    filters[0].MinimumConfidence = GeocodeService.Confidence.Medium;
                    break;
                case 2:
                    filters[0].MinimumConfidence = GeocodeService.Confidence.High;
                    break;
                default:
                    return "Wrong value for confidence parameter";
            }

            GeocodeService.GeocodeOptions geocodeOptions = new GeocodeService.GeocodeOptions();
            geocodeOptions.Filters = filters;

            geocodeRequest.Options = geocodeOptions;

            // Make the geocode request 
            GeocodeService.IGeocodeService geocodeService = new ChannelFactory<GeocodeService.IGeocodeService>(new BasicHttpBinding(), new EndpointAddress("http://staging.dev.virtualearth.net/webservices/v1/geocodeservice/GeocodeService.svc")).CreateChannel();
            GeocodeService.GeocodeResponse geocodeResponse = geocodeService.Geocode(geocodeRequest);

            if (geocodeResponse.Results.Length == 0 || geocodeResponse.Results[0].Locations.Length == 0)
            {
                return "No locations found";
            }
            latitude = geocodeResponse.Results[0].Locations[0].Latitude;
            longitude = geocodeResponse.Results[0].Locations[0].Longitude;

            return "";
        }
        catch (Exception ex)
        {
            return ex.Message;
        }
    }
}

Before we add the last function – GetToken – I would like to draw attention to the line:

GeocodeService.IGeocodeService geocodeService = new ChannelFactory<GeocodeService.IGeocodeService>(new BasicHttpBinding(), new EndpointAddress("http://staging.dev.virtualearth.net/webservices/v1/geocodeservice/GeocodeService.svc")).CreateChannel();

This isn’t normally the way you would instantiate the service class. In fact normally you would see:

GeocodeService.GeocodeServiceClient geocodeService = new GeocodeService.GeocodeServiceClient();

which is simpler, looks nicer and does the same thing – so why bother?


Which configuration file to use?
The primary reason is to avoid using the configuration file. The standard way of instantiating the Service Client is looking for a number of settings in the appSettings section in the config file – and you wouldn’t think that should be a problem – but it is. The problem is that it uses the application configuration file – NOT the DLL config file, and I couldn’t find any way to make it read the DLL config file for these settings.

So if my NavMaps.dll should be accessible from the classic client, I would have to create a finsql.exe.config with the right configuration. Microsoft.Dynamics.Nav.Client.exe.config would be the configuration file for the Roletailored Client (if we are running the automation client side) and I would have to find a way to merge the config settings into the service tier configuration if we are running the automation server side.

So, to avoid all that crap (and severe deployment problems), I instantiate my WCF Client manually through code – meaning that no app.config is necessary.


Requesting a Virtual Earth Security Token
First you need to add a Web Reference to

https://staging.common.virtualearth.net/fin...ommon.asmx?wsdl

called

TokenWebReference

The code for the GetToken could look like this:

/// <summary>
/// Check validity of existing security token and request a new
/// Security Token for Microsoft Virtual Earth Web Services if necessary
/// </summary>
/// <param name="token">Security token</param>
/// <param name="tokenExpires">Timestamp for when the token expires</param>
/// <returns>null if we have a valid token or an error string if not</returns>
private string GetToken(ref string token, ref DateTime tokenExpires)
{
    if (string.IsNullOrEmpty(token) || DateTime.Now.CompareTo(tokenExpires) >= 0)
    {
        // Set Virtual Earth Platform Developer Account credentials to access the Token Service
        TokenWebReference.CommonService commonService = new TokenWebReference.CommonService();
        commonService.Credentials = new System.Net.NetworkCredential("<your account ID>", "<your password>");

        // Set the token specification properties
        TokenWebReference.TokenSpecification tokenSpec = new TokenWebReference.TokenSpecification();
        IPAddress[] localIPs = Dns.GetHostAddresses(Dns.GetHostName());
        foreach (IPAddress IP in localIPs)
        {
            if (IP.AddressFamily == System.Net.Sockets.AddressFamily.InterNetwork)
            {
                tokenSpec.ClientIPAddress = IP.ToString();
                break;
            }
        }
        // Token is valid an hour and 10 minutes
        tokenSpec.TokenValidityDurationMinutes = 70;

        // Get a token
        try
        {
            // Get token
            token = commonService.GetClientToken(tokenSpec);
            // Renew token in 1 hour
            tokenExpires = DateTime.Now.AddHours(1);
        }
        catch (Exception ex)
        {
            return ex.Message;
        }
    }
    return null;
}

Note that I am giving the token a TTL for 70 minutes – but renew after 60 – that way I shouldn’t have to deal with tokens being expired.

<your account ID> should be replaced by your account ID and <your password> should be replaced with your password.

Using the geocode automation object

Having build the assembly we need to put it to play.

After building the assembly we need to place it in the “right” folder – but what is the right folder?

It seems like we have 4 options

1. In the Classic folder
2. In the RoleTailored Client folder
3. In the Service Tier folder
4. Create a Common folder (next to the Classic, RoleTailored and Service Tier folders) and put it there

Let me start by excluding the obvious choice, the Service Tier folder. The reason is, that if you have multiple Service Tiers, they will all share the same COM automation object and you don’t really know which directory the assembly is read from (the one where you did the last regasm). You could accidently delete the Service Tier in which the registered assembly is located and thus end up having denial of service.

RoleTailored Client folder also seems wrong – there is absolutely no reason for running this object Client side – it should be on the Service Tier and there might not be a RoleTailored Client on the Service Tier. The same is case with the Classic folder – so I decided to create a Common folder and put the DLL there. Another reason for selecting the Common folder is, that you can install it on the Client (for being able to run it in the Classic Client) in a similar way as on the server.

Your situation might be different and you might select a different option.

After copying the DLL to the Common folder, we need to register the DLL and make it available as a COM automation object.

C:WindowsMicrosoft.NETFrameworkv2.0.50727regasm NAVMaps.dll /codebase /tlb

Is the command to launch.


Creating a codeunit for geocoding your customers
What I normally do in situations like this is, to create a codeunit, which initializes itself when you Run it.

In this case Running the codeunit should run through all customers and geocode them.

OnRun()
IF cust.FIND('-') THEN
BEGIN
  REPEAT
    err := UpdateLatitudeAndLongitude(cust);
    IF (err <> '') THEN
    BEGIN
      IF NOT CONFIRM('Customer '+cust."No."+ ' - Error: '+err + ' - Continue?') THEN EXIT;
    END;
  UNTIL cust.NEXT = 0;
END;

Whether or not you want an error here or not is kind of your own decision.

The function, that does the job looks like:

UpdateLatitudeAndLongitude(VAR cust : Record Customer) error : Text[1024]
CREATE(NavMaps, TRUE, FALSE);
country.GET(cust."Country/Region Code");
query := cust.Address+', '+cust."Address 2"+', '+cust.City+', '+', '+cust."Post Code"+', '+country.Name;
error := NavMaps.GetLocation(query, 2, cust.Latitude, cust.Longitude);
IF (error = '') THEN
BEGIN
  cust.MODIFY();
END ELSE
BEGIN
  query := cust.City + ', ' + country.Name;
  error := NavMaps.GetLocation(query, 0, cust.Latitude, cust.Longitude);
  IF (error = '') THEN
  BEGIN
    cust.MODIFY();
  END;
END;

Local variables looks like this



Now we can discuss whether this is the right way to go around it – problem for me is, that the demodata are not real addresses – meaning that the street names are fine, city names are fine – but the street and number doesn’t exist in the city.

So – what I do here is to start building a query with Address, City, Post code, Country Name – and tell Virtual Earth to make a High confidence search – this will return the location of correct addresses. In the demo database, this returns absolutely nothing. Next thing is to just give me the location of the city in the country – whether you want to do this for your customer table is kind of your own decision, but it works for the demo data.

BTW – i tried this with addresses in Denmark (my former) and the US (my current) – and the format seems to suit Virtual Earth in these countries, but I didn’t try a lot of other addresses. The advantage of using the query is really to give Virtual Earth the freedom to interpret and look at the address – and it does a pretty good job. If anybody experiences problems with this in certain countries – please let me know (with a suggestion as to what should be changed) and I will include this.


Can’t make it work?
If (for some reason) this code doesn’t work for you – but you really want to get on with the next two parts of this walkthrough, I have included a table here with Latitudes and Longitudes for all the customers in the W1 database.

Copy the entire table into a clean excel spreadsheet and use Edit In Excel to copy the data from the two columns to the two columns in your customer table in one go and save it back to NAV. Note that the Latitude and Longitude is only available in Excel if you

1. Have added the fields to the customer card
2. Updated the Web Reference in the Edit In Excel project to the customer card after doing step 1 (if you already had Edit In Excel running)

If you don’t have the Edit In Excel – you can find it here http://blogs.msdn.com/freddyk/archive/tags...el/default.aspx

 

No Name Latitude Longitude 01121212 Spotsmeyer's Furnishings 25.72898470 -80.23741968 01445544 Progressive Home Furnishings 41.86673200 -87.70100800 01454545 New Concepts Furniture 33.77397700 -84.38731800 01905893 Candoxy Canada Inc. 48.38170029 -89.24547985 01905899 Elkhorn Airport 49.97715327 -101.23461867 01905902 London Candoxy Storage Campus 42.98689485 -81.24621458 10000 The Cannon Group PLC 52.47865520 -1.90859489 20000 Selangorian Ltd. 52.42190337 -1.53778508 20309920 Metatorad Malaysia Sdn Bhd 3.08329985 101.64999984 20312912 Highlights Electronics Sdn Bhd 3.15021023 101.71284467 20339921 TraxTonic Sdn Bhd 1.54907294 110.34416981 21233572 Somadis 34.01504517 -6.83272026 21245278 Maronegoce 33.60242486 -7.61274353 21252947 ElectroMAROC 33.91666643 -6.91666670 27090917 Zanlan Corp. -26.35520540 27.40158677 27321782 Karoo Supermarkets -29.11835074 26.22492447 27489991 Durbandit Fruit Exporters -29.83637005 30.94218850 30000 John Haddock Insurance Co. 53.47962007 -2.24880964 31505050 Woonboulevard Kuitenbrouwer 52.14019530 6.19148992 31669966 Meersen Meubelen 51.98542312 5.90462968 31987987 Candoxy Nederland BV 52.37311967 4.89319481 32124578 Nieuwe Zandpoort NV 51.17786638 4.83266278 32656565 Antarcticopy 51.22171506 4.39739518 32789456 Lovaina Contractors 50.88170014 4.71750006 33000019 Francematic 48.81669022 1.94925517 33002984 Parmentier Boutique 48.85692470 2.34120972 33022842 Livre Importants 48.90484169 2.81284869 34010100 Libros S.A. 41.38566770 2.16993861 34010199 CorporaciГіn Beta 39.43432257 -0.38737597 34010602 Helguera industrial 40.41576270 -3.70385108 35122112 Bilabankinn 64.11111474 -21.90939903 35451236 Gagn &Gaman 64.92900006 -18.96200001 35963852 Heimilisprydi 64.13533777 -21.89521417 38128456 MEMA Ljubljana d.o.o. 46.05124690 14.50306222 38546552 EXPORTLES d.o.o. 46.05124690 14.50306222 38632147 Centromerkur d.o.o. 46.55813813 15.65098330 40000 Deerfield Graphics Company 51.86390832 -2.24978395 41231215 Sonnmatt Design 47.42380030 8.55140001 41497647 Pilatus AG 47.05957649 8.30785449 41597832 MГ¶bel Scherrer AG 47.69385177 8.63503763 42147258 BYT-KOMPLET s.r.o. 49.03722882 17.81008579 42258258 J &V v.o.s. 48.98017220 17.21433230 42369147 PLECHKONSTRUKT a.s. 48.85557219 16.05438888 43687129 Designstudio Gmunden 47.91861087 13.79814081 43852147 Michael Feit - MГ¶belhaus 47.58900024 14.13999975 43871144 MГ¶bel Siegfried 48.16780201 16.35428691 44171511 Zuni Home Crafts Ltd. 52.49931332 -2.13111123 44180220 Afrifield Corporation 51.27380021 0.52508533 44756404 London Light Company 52.20986970 0.11156514 45282828 Candoxy Kontor A/S 56.15704469 10.20700961 45282829 Carl Anthony 56.15704469 10.20700961 45779977 Ravel MГёbler 55.31117991 10.79238497 45979797 Lauritzen KontormГёbler A/S 57.03462996 9.92748722 46251425 Marsholm Karmstol 56.67225949 12.85753034 46525241 Konberg Tapet AB 57.78593438 14.22523925 46897889 Englunds KontorsmГ¶bler AB 58.59301685 16.17726378 47523687 Slubrevik Senger AS 59.85794865 10.47694914 47563218 Klubben 59.91503946 10.56067966 47586954 SjГёboden 71.07307523 24.70469333 49525252 Beef House 51.21562980 6.77605525 49633663 Autohaus Mielberg KG 53.55334528 9.99244496 49858585 Hotel Pferdesee 50.04764177 8.57851513 50000 Guildford Water Department 51.23708010 -0.57051592 60000 Blanemark Hifi Shop 51.51777074 -0.15552994 61000 Fairway Sound 51.50632493 -0.12714475 62000 The Device Shop 51.50632493 -0.12714475 IC1020 Cronus Cardoxy Sales 56.10199973 9.55599993 IC1030 Cronus Cardoxy Procurement 53.55334528 9.99244496 Note that some of the customers have the same latitude, longitude – this is due to the demo data.


Next steps
Ok admitted – that was a long post – but hopefully it was helpful (and hopefully you can make it work)

As usual – you can download the objects and the Visual Studio solution – and please do remember that this is NOT a solution that is installable and will work all over – you might have issues or problems and I would think the best place to post questions is mibuso (where my answer will get read by more people).

In the next post I will create a website, which connects to NAV Web Services to get customer location information – and in the third post I will show how to create an action in the customer List and Card to open up an area map centered around a specific customer.

So… – after step 2 you will see this



Stay tuned!

The forth post in this series is a surprise – and will require NAV 2009 SP1 in order to work – so stay tuned for this one…

 

Enjoy and good luck

 

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV



Подробнее