Location to Location distance matrix

[David Grant]

I'd like to design something very similar to a location-to-location distance
matrix that you see on many road maps. I'm not concerend with the GUI at
this point, just the table relationships.

Supposing I have a table called tblCity that stores cities. I want a table
that contains every permutation of fromCityID and toCityID city which can
then have a distance value assigned to it. What's the ideal relationship for
this?

 

[Pat Hartman \(MVP\)]

You might be able to make use of the web services that MapQuest and similar
sites make available. You can pass in two addresses and get a return
mileage. If you are OK with "as the crow flies" distances, store the
latitude and longitude of each city and calculate the distance.

I don't remember the website where I got the information but it came
directly from MapQutest. Here's the code I use to bring up the map for an
address. It is a function. You call it and pass in a city, state, address,
and/or zip. The more complete the information, the more precise the map.

Public Function Mapper_Click(strCity As Variant, strState As Variant,
strAddress As Variant, strZip As Variant) As Variant
On Error GoTo Err_Mapper_Click

Dim strFullAddress As Variant
Dim strMapquest As Variant

strFullAddress = strAddress & "&city=" & strCity & "&state=" & strState
& "&zipcode=" & strZip
strMapquest =
"http://www.mapquest.com/maps/map.ad...ountryid=US&addtohistory=&country=US&address="
& strFullAddress & "&historyid=&submit=Get Map"

'Remove trailing spaces
strMapquest = Trim(strMapquest)

'Change imbedded spaces with plus signs
strMapquest = Replace(strMapquest, " ", "+", 1, , vbTextCompare)

Application.FollowHyperlink strMapquest, , True

strMapquest = vbNullString

Exit_Mapper_Click:
Exit Function

Err_Mapper_Click:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_Mapper_Click

End Function

 

[David Grant]

Thanks for the prompt reply. What I actually want to do is provide a grid
interface which allows the user to view and/or edit cable distance values
between electrical device locations in a large building. I provided the
example of the road map grid because I thought it would be easier to
explain.

So what I have is a table: tblLocation. I created a second table
tblDistanceMatrix which contains a concatonated primary key (fromLocationID
& toLocationID) and a numberic field called "distance". tblLocation and an
aliased version of itself link one-to-many to tblDistanceMatrix. The table
behaves as expected, the problem now is how to design a query that does what
I want.

Given a blank tblDistanceMatrix, and 4 locations defined in tblLocation, I
want a query to return the following (as an example):

fromLocationID | toLocationID | Distance
-----------------------------------------
1|1|
1|2|
1|3|
1|4|
2|1|
2|2|
2|3|
2|4|
3|1|
....etc


Is this possible?

Thanks.

You might be able to make use of the web services that MapQuest and
similar sites make available. You can pass in two addresses and get a
return mileage. If you are OK with "as the crow flies" distances, store
the latitude and longitude of each city and calculate the distance.

I don't remember the website where I got the information but it came
directly from MapQutest. Here's the code I use to bring up the map for an
address. It is a function. You call it and pass in a city, state,
address, and/or zip. The more complete the information, the more precise
the map.

Public Function Mapper_Click(strCity As Variant, strState As Variant,
strAddress As Variant, strZip As Variant) As Variant
On Error GoTo Err_Mapper_Click

Dim strFullAddress As Variant
Dim strMapquest As Variant

strFullAddress = strAddress & "&city=" & strCity & "&state=" & strState
& "&zipcode=" & strZip
strMapquest =
"http://www.mapquest.com/maps/map.ad...ountryid=US&addtohistory=&country=US&address="
& strFullAddress & "&historyid=&submit=Get Map"

'Remove trailing spaces
strMapquest = Trim(strMapquest)

'Change imbedded spaces with plus signs
strMapquest = Replace(strMapquest, " ", "+", 1, , vbTextCompare)

Application.FollowHyperlink strMapquest, , True

strMapquest = vbNullString

Exit_Mapper_Click:
Exit Function

Err_Mapper_Click:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_Mapper_Click

End Function

 

[Guest]

The tblDistanceMatrix table is exactly right to model this. To populate it
a query like this should fill it with all possible permutations, but
excluding each location linked to itself:

INSERT INTO tblDistanceMatrix (fromLocationID, toLocationID)
SELECT L1.LocationID, L2.LocationID
FROM tblLocation AS L1, tblLocation AS L2
WHERE L1.LocationID <> L2.LocationID
ORDER BY L1.LocationID, L2.LocationID;

For editing the data how about using an unbound form with a subform bound to
the tblDistanceMatrix table in it? The unbound parent form could have a
combo or list box listing all locations like so:

As its RowSource property:

SELECT LocationID, Location FROM tblLocation ORDER BY Location;

and other properties as so:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, LocationID, column, so only the
location shows)

The subform would be based on a query joining the tblLocation table to the
tblDistanceMatrix table as its Recordsource:

SELECT fromLocationID, Location, Distance
FROM tblLocation INNER JOIN tblDistanceMatrix
ON tblDistanceMatrix.toLocationID = tblLocation.LocationID
ORDER BY Location;

The subform, in continuous form, would have two bound controls, text boxes
bound to the Location and Distance columns, but you'll probably want the
former's Enabled property to be set to False and its Locked property to True
to make it read-only.

The subform will be linked to the main form by using the name of the combo
or list box as the LinkMasterFields property of the subform control and
fromLocationID as the LinkChildFields property. When a location is selected
in the combo or list box on the main parent from the subform will show all
locations linked to that location and the distances in each case, which the
user can then edit if necessary.

Ken Sheridan
Stafford, England

Thanks for the prompt reply. What I actually want to do is provide a grid
interface which allows the user to view and/or edit cable distance values
between electrical device locations in a large building. I provided the
example of the road map grid because I thought it would be easier to
explain.

So what I have is a table: tblLocation. I created a second table
tblDistanceMatrix which contains a concatonated primary key (fromLocationID
& toLocationID) and a numberic field called "distance". tblLocation and an
aliased version of itself link one-to-many to tblDistanceMatrix. The table
behaves as expected, the problem now is how to design a query that does what
I want.

Given a blank tblDistanceMatrix, and 4 locations defined in tblLocation, I
want a query to return the following (as an example):

fromLocationID | toLocationID | Distance
-----------------------------------------
1|1|
1|2|
1|3|
1|4|
2|1|
2|2|
2|3|
2|4|
3|1|
....etc


Is this possible?

Thanks.

 

[John W. Vinson]

INSERT INTO tblDistanceMatrix (fromLocationID, toLocationID)
SELECT L1.LocationID, L2.LocationID
FROM tblLocation AS L1, tblLocation AS L2
WHERE L1.LocationID <> L2.LocationID
ORDER BY L1.LocationID, L2.LocationID;

I'd suggest using > rather than <> - otherwise you'll get each pair twice, 1
paired with 2, and 2 paired with 1.


John W. Vinson [MVP]

 

[Guest]

John:

I think one would probably want the duplication. Certainly David's sample
data shows it. Otherwise you'd end up with a progressively decreasing
imbalance of 'locations from', and a correspondingly progressively increasing
imbalance of 'locations to from location 2 onwards and location 1 as a
'location from' would be represented by zero rows. The practical
implications of this would be that to edit the distances from location 1 it
would be necessary to go to each other row in the table and edit it there;
with location 2 you could edit its distance to location 1, but then you'd
have to go to each other row individually; and so on. There does arise the
possibility of inconsistencies of course, as it would be possible for
location 42 to have a distance of 10 metres from location 99, but location 99
to have a distance of 15 metres from location 42 in another row. This could
be covered at form level by executing some code in the subform's AfterUpdate
event procedure:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL

strSQL = "UPDATE tblDistanceMatrix" & _
" SET Distance = " & Me.Distance" & _
" WHERE toLocationID = " & Me.fromLocationID & _
" AND fromLocationID = " & Me.toLocationID

cmd.CommandText = strSQL
cmd.Execute

Ken Sheridan
Stafford, England

 

[Bob Quintal]

John:

I think one would probably want the duplication.

Or create a union query to swap the from and to columns, while
keeping the data intact.
SELECT locationfrom, locationto From x
UNION ALL SELECT locationto, LocationFrom from A



Certainly

David's sample data shows it. Otherwise you'd end up with a
progressively decreasing imbalance of 'locations from', and a
correspondingly progressively increasing imbalance of 'locations
to from location 2 onwards and location 1 as a 'location from'
would be represented by zero rows. The practical implications of
this would be that to edit the distances from location 1 it would
be necessary to go to each other row in the table and edit it
there; with location 2 you could edit its distance to location 1,
but then you'd have to go to each other row individually; and so
on. There does arise the possibility of inconsistencies of
course, as it would be possible for location 42 to have a distance
of 10 metres from location 99, but location 99 to have a distance
of 15 metres from location 42 in another row. This could be
covered at form level by executing some code in the subform's
AfterUpdate event procedure:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL

strSQL = "UPDATE tblDistanceMatrix" & _
" SET Distance = " & Me.Distance" & _
" WHERE toLocationID = " & Me.fromLocationID & _
" AND fromLocationID = " & Me.toLocationID

cmd.CommandText = strSQL
cmd.Execute

Ken Sheridan
Stafford, England

 

[Bob Quintal]

That would not be updatable, however, so would not make it
suitable as the RecordSource of a subform or any other interface
element for editing the distance values, which is the object of
the exercise.

True, but what the original poster wants is a Cartesian join between
two copies of his Locations table upon itself. with an additional
field. That is really most inefficient in that the numbre of rows in
that table increases exponentially to the number of locations. and
also given locations a, b and c, running a cable from a to c that
passes through b results in a value that's the summation of the two
individual runs, and that's another source of error if the distance
doesn't add up to a+b.

To build a dataentry/edit interface for this, I'd go with the
listbox and subform concept you proposed. It may not be how the
client envisioned it, but it's a sight more practical than any other
way.

Q

An updatable alternative with a single set of pairs

would be to base the subform solely on the tblDistanceMatrix table
and include both the toLocationID and fromLocationID columns,
either binding combo boxes to them or (less efficiently) using
unbound controls calling the DLookup function to show the location
value from tblLocation, and restricting the query by parameters on
both locationID columns:

SELECT toLocationID, fromLocationID, Distance
FROM tblDistanceMatrix.toLocationID
WHERE toLocationID = Forms!frmLocation!cboLocation
OR fromLocationID = Forms!frmLocation!cboLocation
ORDER BY toLocationID, fromLocationID;

The subform would be requeried in cblLocation's AfterUpdate event
procedure.
So for location 1 the subform would show, assuming 100 locations
in total:

From To Distance
2 1 10
3 1 15
4 1 20
and so on to
100 1 50

For location 2:

From To Distance
2 1 10
3 2 25
4 2 30
and so on to
100 2 55


Finally for location 100:

From To Distance
100 1 50
100 2 55
100 3 60
and so on to
100 99 75


i.e. the selected location would increasingly become the 'from'
location in the pairings.

This does make the assumption, however, that the relationship type
modelled by tblDistanceMatrix has no other non-key attributes
other than Distance, which might differ for each 'direction' of a
pairing. In the absence of further information from the OP I would
hesitate to assume categorically that this is the case.

Ken Sheridan
Stafford, England

=?Utf-8?B?S2VuIFNoZXJpZGFu?=


Or create a union query to swap the from and to columns, while
keeping the data intact.
SELECT locationfrom, locationto From x
UNION ALL SELECT locationto, LocationFrom from A



Certainly

 

[Guest]

That would not be updatable, however, so would not make it suitable as the
RecordSource of a subform or any other interface element for editing the
distance values, which is the object of the exercise. An updatable
alternative with a single set of pairs would be to base the subform solely on
the tblDistanceMatrix table and include both the toLocationID and
fromLocationID columns, either binding combo boxes to them or (less
efficiently) using unbound controls calling the DLookup function to show the
location value from tblLocation, and restricting the query by parameters on
both locationID columns:

SELECT toLocationID, fromLocationID, Distance
FROM tblDistanceMatrix.toLocationID
WHERE toLocationID = Forms!frmLocation!cboLocation
OR fromLocationID = Forms!frmLocation!cboLocation
ORDER BY toLocationID, fromLocationID;

The subform would be requeried in cblLocation's AfterUpdate event procedure.
So for location 1 the subform would show, assuming 100 locations in total:

From To Distance
2 1 10
3 1 15
4 1 20
and so on to
100 1 50

For location 2:

From To Distance
2 1 10
3 2 25
4 2 30
and so on to
100 2 55


Finally for location 100:

From To Distance
100 1 50
100 2 55
100 3 60
and so on to
100 99 75


i.e. the selected location would increasingly become the 'from' location in
the pairings.

This does make the assumption, however, that the relationship type modelled
by tblDistanceMatrix has no other non-key attributes other than Distance,
which might differ for each 'direction' of a pairing. In the absence of
further information from the OP I would hesitate to assume categorically that
this is the case.

Ken Sheridan
Stafford, England