Distance table - Dev Shed

Dev Shed Forums Sponsor:

April 22nd, 2002, 06:23 PM

SiS

Contributing User

Join Date: Nov 2000

Location: Denmark

Posts: 69

Time spent in forums: 7 h 3 m 19 sec
Reputation Power: 13

Distance table

Hi!!

You know theese tables you have in the back or front of your car-maps. The ones where you have the cities listes in both the first row and the first collon, and by looking at 2 names you can read out the distance between the two of them.

I'm trying to create such a function, but I really can't crack the nut!! I wanna store the data in my database. And also have it as dynamical as it gets.

My first thoughts was to create a table and create cells foreach city, and then insert the city again and the distance, but it's a pretty good solution i think!!

The table I have been thinking about is similar to this on

Code:

+-------------+-------------+----------+-------------+
| city        | New York    | Chicago  | Los Angeles |
+-------------+-------------+----------+-------------+
| New York    | -           | 343445   | 35423323    |
+-------------+-------------+----------+-------------+
| Chicago     | 234234      | -        | 3453454353  |
+-------------+-------------+----------+-------------+
| Los Angeles | 546457567   | 34534543 | -           |
+-------------+-------------+----------+-------------+

You get the point!! (The distance is not real, I just made those numbers up

hehe)

Anyway, you guys have any better ideas??

best regards
Simon

__________________
I always wanted to be somebody, guess I should have been more specific

April 23rd, 2002, 08:12 AM

WoR

Registered User

Join Date: Mar 2002

Posts: 23

Time spent in forums: < 1 sec
Reputation Power: 0

I'd make a table of the xy-coordinates and calculate the distance as needed. This saves space, lots of input time, and errors.
And if the calculations take too long for you, please tell me how you got c running on an abacus in the first place ;>

April 24th, 2002, 01:19 AM

Scorpions4ever

Banned ;)

Join Date: Nov 2001

Location: Glendale, Los Angeles County, California, USA

Posts: 7,717

Time spent in forums: 1 Month 3 Days 12 h 42 m 30 sec
Reputation Power: 1179

How about designing two tables something like this (Note that the layout is in Pseudo-SQL):

Code:

Table #1 (Cities)
+++++++++++++++++++
ID integer, Autoincrement
Name varchar(255)
----------------------------------
Primary Key (ID)
Unique Index (Name)

===========================

Table #2 (Distance)
+++++++++++++++++++++
FromID integer
ToID integer
Distance integer
-----------------------------------
Primary Key (FromID, ToID)
Foreign Keys (FromID --> Cities.ID, ToID --> Cities.ID)

You don't necessarily need a database engine to do this and the keys and indexes I specified are for data integrity reasons (and speed, if you do use a DB). You can always create arrays in memory with similar structure and read the data from text files, if you don't want to use a database engine.

Then the contents of your first table could be like this:
1, New York
2, Los Angeles
3, San Francisco
etc.

and the contents of your second table could be like this:
1, 2, 10000
1, 3, 10500
2, 3, 450
etc.
(assuming NY to LA = 10000 miles, NY to SF = 10500 miles, LA to SF = 450 miles)

It's easier to maintain than an x-y grid and has the advantage of not needing to add an extra column if you add a new city. Also, in a grid layout, there are 2 entries (for e.g. NY to LA and LA to NY) for each pair of cities. With this layout, you can keep one entry and thereby save some resources.

Last edited by Scorpions4ever : April 24th, 2002 at 01:37 AM.

April 26th, 2002, 04:34 AM

Thrasher

Canta como rafaé

Join Date: Feb 2001

Location: Barcelona

Posts: 74

Time spent in forums: < 1 sec
Reputation Power: 8

Each solution of the two presented is suitable for a different situation. If you know what is the distance between cities (road distance, ie), the from-to solution is the good one.

If you know the position of the cities and want to calculate its distance, the x-y is the proper one.

In case it is the second solution (x-y). Wanna save compute time? Easy. If this is for web, return to the client the x-y data in a javascript array and make its web browser to do the calculus for you.

Other method for the second solution is to update the distance tables of the first solution every time a new record is inserted. This combination can be very helpful.

__________________
Thrasher

'Y se ahogaron los dooos
No eran duros pa pagar, cuñaaoo !!'
El vagamundo - El risitas y su cuñao

April 26th, 2002, 08:46 PM

The Ostrich

Contributing User

Join Date: Mar 2002

Location: Oxford, UK

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 7

The only tables I have ever seen of the form shown by SiS were tables of the shortest distances between points that are geographically separated by quite large distances. Thus, the thing to do is to store the latitude and longitude of each city in the database, and calculate the distance between them on a great circle.

May 12th, 2004, 10:27 AM

mmbertels

Registered User

Join Date: May 2004

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

Quote:

Originally Posted by Scorpions4ever

How about designing two tables something like this (Note that the layout is in Pseudo-SQL):

Code:

Table #1 (Cities)
+++++++++++++++++++
ID integer, Autoincrement
Name varchar(255)
----------------------------------
Primary Key (ID)
Unique Index (Name)

===========================

Table #2 (Distance)
+++++++++++++++++++++
FromID integer
ToID integer
Distance integer
-----------------------------------
Primary Key (FromID, ToID)
Foreign Keys (FromID --> Cities.ID, ToID --> Cities.ID)

Wow, this is exactly what I need!
Can you explain me a bit more ( i just got started in access), how a query would look like to look up the distances, if someone enters the two cities? My distances a to b and b to a are equal.

May 12th, 2004, 12:13 PM

dog135

Doggie

Join Date: Jul 2003

Location: Seattle, WA

Posts: 751

Time spent in forums: 10 h 38 m 25 sec
Reputation Power: 7

If your database is big enough, you'll probably get from one city to another by going through some other cities.

I'll use Washington cities, since those are the ones I know:

You want to get from Everett to Vancouver: (all distances are bs)

Everett->Seattle:20m
Seattle->Tacoma:30m
Tacoma->Vancouver:130m

Total distance: 180m

This would save you more room then linking all possible city distances.

__________________
"Science is constructed of facts as a house is of stones. But a collection of facts is no more a science than a heap of stones is a house." - Henri Poincare

May 13th, 2004, 04:06 AM

mmbertels

Registered User

Join Date: May 2004

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks and question

Ha,ha! Thanks alot.
I need all distances as i planning cruises with ships, and at this moment i need to look and measure all distances each time (allthought the ports we sail to are limited). That is why i want to make a "ports" table, in which i list ports and properties of the ports.
The second table needs to be a distance table. Than finnally I want to make a form i which I can select the first port(if it is not there i will declare the port via a form to the ports table)
. Than I will select the second city in the sameway, afterwhich the distance must be shown by the program between these ports.

Questions:
1) do I need to make authonumbering for the ports table?
2) What will the distance table look like
3) what will my wuery look like

(later on I will introduce the timezones)

Thank you very much,

Michiel

May 13th, 2004, 01:27 PM

dog135

Doggie

Join Date: Jul 2003

Location: Seattle, WA

Posts: 751

Time spent in forums: 10 h 38 m 25 sec
Reputation Power: 7

Quote:

Originally Posted by mmbertels

Doing this with a single SQL quiry would be near impossible. If the number of ports you travel to are limited, and your routes are limited, (ie: one long circular route) then you would want to put more data into the database, rather then figuring it out on the fly.

Access does use a programming language. Similar to VB. I'd use that, and starting from your first position, make individual quiries to your database, following your route until you reach the destination.

Your database, in that case, would look something like:

from,to,dist
a,b,10
a,d,20
b,a,10
b,c,25
c,b,25
c,d,100
d,a,20
d,c,100

notice each distance is recorded twice, once one way, once the other way. If you always travel one direction on one route, you can eliminate the second one:

from,to,dist
a,b,20
b,c,25
c,d,100
d,a,20

Then, if you're traveling from b->d, you'd:

runningTotal=0
loc=b
loop:
select dist,to from mytable where from = loc
add dist runningTotal
if 'to'=d, exit
loc='to'
end loop
print runningTotal

#10

May 14th, 2004, 04:27 AM

Scorpions4ever

Banned ;)

Join Date: Nov 2001

Location: Glendale, Los Angeles County, California, USA

Posts: 7,717

Time spent in forums: 1 Month 3 Days 12 h 42 m 30 sec
Reputation Power: 1179

Code:

SELECT distance
FROM    distancetable
WHERE  (from_id = xxx AND to_id = yyy)
OR        (from_id = yyy AND to_id = xxx)

With a query like this, you don't need to have two different combinations of routes. For example, if you have the distance from NY to LA, you don't need another row in the database from LA to NY.

__________________
Up the Irons
What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
"Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
Down with Sharon Osbourne
Puzzle of the Month solved by Keath and KevinADC, superior perl programmers of the month

#11

May 14th, 2004, 04:58 AM

mmbertels

Registered User

Join Date: May 2004

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

WOW! you really know how to do these things.
Do I really need to use the SQL language? Please check if this is ok?
Port Table:
1 Sydny
2 NY
3 LA

Distance Table:
1 2 500
1 3 400

In my situation I never have to calculate the distance from 1 via 2 to 3, I just need to get the distance from La to NY, and also from NY to LA, which should search for the same record cel).

Thanks alot, again!, I am sure that I am getting better each time.

#12

May 14th, 2004, 05:08 AM

Scorpions4ever

Banned ;)

Join Date: Nov 2001

Location: Glendale, Los Angeles County, California, USA

Posts: 7,717

Time spent in forums: 1 Month 3 Days 12 h 42 m 30 sec
Reputation Power: 1179

>> Do I really need to use the SQL language?
Yes, makes it a lot easier than writing an in-memory hash table actually.

As for your table, it might be easier to do this:
Port Table:
1 Sydny
2 NY
3 LA

Distance Table:
1 2 500
1 3 400
2 3 80

Note that the third row in the Distance table has the distance between NY and LA. Because of the way the earth is shaped, the distance between (Sydney to NY - Sydney to LA) does not necessarily equal NY to LA. Also, finding out the distance based on intermediate cities is a pain in the gluteus maximus (and is actually a very interesting topic that is continually debated by computer science professors -- google for "travelling salesman problem"). If you have all three distances, then my query ought to work just fine, whether you're looking for NY to LA or LA to NY.

#13

May 14th, 2004, 05:12 AM

mmbertels

Registered User

Join Date: May 2004

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

add

Sorry, i did not ask you completely correct.
I want to make a form where i can declare ports with their properties. (ports have unique names, so do i need to add a id number?).

Than I want to make Itineraries. So I want to be able to select port 1, and port 2. If they do not exist i want to open a form where i can declare these ports.
Than I want to be able to add the distance in a form, and if the computer already knows this distance, that it will authomaticcaly appear.

I will give a departure time, and the arrival time and date in the next port. The computer will show me the average sailing speed.

The next step I want to add timezones, but that is maybe too difficult.

All the best and thanks alot.
Michiel

Thread Tools	Search this Thread
Email this Page	Search this Thread: