To response the recent emails and questions that I’ve been asked of What and How Mappoint together with VB/VB.NET/VBA programming languages can help improve your business by targeting your customers based on the UK geographic location, I have published the following code/tips and alone with a real world scenario that my program is trying to solve. Hope this would help!
A well-known restaurant chain has 70 odd branches across the UK. The restaurant Management promote its specific seasonal offers in a regular basis for each their branches only to the customer who lives within 2 miles radius distance. For central London however, 0.5 miles of distance is thought to be appropriate for their direct mailing campaign. Overlapping is strictly prohibited, as it would confuse customer if they receive two letters from two different branches.
More than 50,000 of their customer list was given in a format of MS Excel with full address and the UK postcode.
A solution was written by using VB.NET that calls an instance of Mappoint to measure the two address one at a time and pick up the qualified record from customer list and then output this record with the store code to a new file.
The most important measurement is handled by the following syntax;
Radius = locMaster.DistanceTo(oMap.FindResults(dr("PostCode "))(1))
Of course, you need to declare all your variables, including create instance of Mappoint at the beginning of your program;
Dim locMaster As MapPoint.Location
…
objApp = New MapPoint.Application
objApp.Units = MapPoint.GeoUnits.geoMiles
…
I used a loop that loops thru all our customer list by using a SqlDataReader dr().
While dr.Read
Radius = locMaster.DistanceTo(oMap.FindResults(dr("PostCode "))(1))
If Radius <= CType(txtRadius.Text, Double) Then
txtFileText.Text += strAddress(dr("CustomerID"), dr("Title"), dr("FirstName"), dr("LastName"), dr("Address1"), dr("PostTown"), dr("SubTown"), dr("SubLoca"), dr("County"), dr("PostCode")) & Trim(StoreCode.Text) & vbCrLf
iFounder = iFounder + 1
End If
If CInt(RecodRequired.Text) = i2Founder Then
Exit While
End If
End While
dr.Close()
I have also used a loop counter; iFounder to counts how many founds since sometime restaurant requires certain number of records from their list. For example, 200 for central London while 500 for Birmingham’s branch…etc