MAPPOINT Q & A

Top | Post New Question | Read All | MP2K Asks Microsoft

627 Walter - March 15, 2002

I am a first time database developer working on an Access database. I have a list of 200,000 customer records that have Latitude and Longitude coordinates (formatted in decimals, like: 40.72323 = Latitude and -73.99569 = longitude). I know the coordinates of 700 points of sale and I know the closest point of sale and the "crow flies distance" to that closest point of sale. I would like to add to my table of customers the drive distance to the closest point of sale. Thus I have a table: [cust_ID], [CLOSEST_POS_ID], [customer_latitude], [customer_longitude], [closest_POS_latitude] and [closest_POS_longitude], [drive_distance]. I would like to be able to do an update query that would populate the [drive_distance] field with the actual drive distance. Thus far, in my fledgling developer career, I have not "connected" with another program (please be specific). Thanks for your help.

Answer:

In a VB form, add the MapPoint ActiveX control, and call it "MapPointCtl".

Then do something like:

Dim objLocS As MapPointCtl.Location
Dim objLocF As MapPointCtl.Location
Dim objMap As MapPointCtl.Map
Dim objRoute as MapPointCtl.Route

Set objMap = ctlMapPoint.ActiveMap
Set objRoute = objMap.ActiveRoute

'...get dblLatS and dblLatF for the starting and ending points
'from your database
Set objLocS = objMap.GetLocation(dblLatS, dblLonS)
Set objLocF = objMap.GetLocation(dblLatF, dblLonF)

'Add Waypoints
objRoute.Waypoints.Add objLocS
objRoute.Waypoints.Add objLocF
objRoute.Calculate

'Get the distance
dblDriveDistance = objRoute.Distance

I haven't tested this so it may need some tweaking, but this should generally work. Be advised it will take a little bit of time to calculate 200,000 routes.

- Walt Cygan

Post New Answer / Follow-up

	MP2Kmag Internet