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

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&#8221;)).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&#8221;)).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/find-30/common.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:\Windows\Microsoft.NET\Framework\v2.0.50727\regasm 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/Excel/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