[AccessD] More MapPoint Help If Possible

Paul,

Are you pretesting the addresses to ensure that they resolve to a single
location?  In my data I get a ton of addresses which end up with many
possible locations (can't be found so a "set" of possibilities is returned.
.FindResults returns a collection of all the possible locations.  If .Count
is 1 then a single address was found and thus the address is "valid".  You
might want to look at how many addresses are returning a count > 0 and mark
those addresses as invalid (using a field in the table), then do not look
them up in the future until they are fixed.  These location lookups take far
longer than if the address resolves correctly and in the end just shouldn't
be calculated every time.

The thing you need to do is time the various parts of the code.  Here is a
look at a set of data points as I look up the address, and then look up the
distance as the crow flies and distance as the car drives:


Time to find Children's Specialized Hospital and 12 MSec
Time to calc Distance ATCF between Family Resource Associates, Inc. and
Children's Specialized Hospital: 1 MSec - 26.89295 miles"
Time to calc Distance ATCD between Family Resource Associates, Inc. and
Children's Specialized Hospital: 448 MSec - 33.64725 miles
Time to find Fort Lee School District and 7 MSec
Time to calc Distance ATCF between Family Resource Associates, Inc. and Fort
Lee School District: 0 MSec - 35.70896 miles"
Time to calc Distance ATCD between Family Resource Associates, Inc. and Fort
Lee School District: 1143 MSec - 53.54356 miles
Time to find Garfield School District and 4 MSec
Time to calc Distance ATCF between Family Resource Associates, Inc. and
Garfield School District: 0 MSec - 37.72034 miles"
Time to calc Distance ATCD between Family Resource Associates, Inc. and
Garfield School District: 758 MSec - 52.45616 miles
Time to find Garwood School District and 8 MSec
Time to calc Distance ATCF between Family Resource Associates, Inc. and
Garwood School District: 0 MSec - 25.45199 miles"
Time to calc Distance ATCD between Family Resource Associates, Inc. and
Garwood School District: 401 MSec - 30.58389 miles

What this tells me is that the time to look up an address that correctly
resolves to a single address is minimal (~10 milliseconds), the time to calc
the distance as the crow floes is almost nothing, but the time to calc the
distance driven is huge and obviously varies with the number of roads that
have to be found and calculated.  I would guess that you just aren't going
to change that time.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
paul.hartland at fsmail.net
Sent: Thursday, June 09, 2005 5:06 AM
To: accessd
Subject: [AccessD] More MapPoint Help If Possible


To all,

I have the following code which sends a Client postcode and around 475
employee postcodes to MapPoint and returns the Mileage, however this takes
around 16 minutes to complete which is far too long for our needs, can
anyone see a way of speeding this up we really need this procedure to be as
fast as possible.

Private Sub FlyMap_Click()
On Error Resume Next
    Dim rsClient As ADODB.Recordset
    Dim rsEmp As ADODB.Recordset
    Dim oMap As MapPoint.Map
    Dim oLoc1 As MapPoint.Location
    Dim oLoc2 As MapPoint.Location
    Dim strCPC As String
    
    Dim dtStart As Date
    Dim dtFinish As Date
    
    Set rsClient = New ADODB.Recordset
    rsClient.Open ("SELECT InvPostCode FROM tblClaires"), OpenDataConn,
adOpenDynamic, adLockReadOnly
    strCPC = rsClient!InvPostCode
    rsClient.Close
    Set rsClient = Nothing
    
    Set rsEmp = New ADODB.Recordset
    rsEmp.Open ("SELECT PayrollNo, Postcode, DistFromClient FROM
tblMidlands"), OpenDataConn, adOpenDynamic, adLockOptimistic
    
    Set oMap = CreateObject("MapPoint.Application").ActiveMap
    oMap.Parent.PaneState = geoPaneRoutePlanner
    
    With oMap.ActiveRoute
        rsEmp.MoveFirst
        dtStart = Format(Time(), "HH:MM:SS")
        
        Screen.MousePointer = vbHourglass
        
        Do Until (rsEmp.EOF)
            If (IsNull(rsEmp.Fields("Postcode")) = False Or
rsEmp.Fields("Postcode") <> "") Then
                Set oLoc1 = oMap.FindResults(strCPC)(1)
                Set oLoc2 = oMap.FindResults(rsEmp.Fields("Postcode"))(1)
                If (oLoc1 Is Nothing Or oLoc2 Is Nothing) Then
                    ' **** No Results Returned ****
                Else
                    ' **** Results Returned So Calculate Distance Etc ****
                    .Waypoints.Add oLoc1, "Start"
                    .Waypoints.Add oLoc2, "End"
                    .Calculate
                    If (.IsCalculated = True) Then
                        rsEmp.Fields("DistFromClient") = Format(.Distance,
"00.00")
                        rsEmp.Update
                    End If
                End If
            End If
            rsEmp.MoveNext
            .Clear
        Loop
        
        Screen.MousePointer = vbDefault
    End With
    rsEmp.Close
    Set rsEmp = Nothing
    oMap.Saved = True
    Set oMap = Nothing
    
    dtFinish = Format(Time(), "HH:MM:SS")
    MsgBox "Finished : " & Format(dtFinish - dtStart, "HH:MM:SS") End Sub


Thanks in advance for any help and/or code samples

Paul Hartland

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at:
http://www.wanadoo.co.uk/help/id/7098.htm
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com