Calculating distances

Some examples to be used directly
Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Calculating distances

Post by Villeroy »

Interesting question by the other forum's member "Antonic":
Calculating distances
Antonic wrote:This is my issue.

I have a table with for instance 5 cities:
City1
City2
City3
City4
City5.

I know the distances between all of them. How can I sum up the distances by only naming the cities i am going through.

City1-City2=20km
City2-City3=30km
City3-City4=15km
City4-City5=25km.

And now i say i am going from city1 through city2 to city3. I need the road length.

Thanks
With the help of DACM I suggest the following solution, which includes a README form.
As always, it may a little bit ugly for I was focussed on getting the queries right and it is free of any macro poison.
The queries try to resemble a simple step-by-step approach where each query depends on a preceeding one.

Fill in your own data:
All you've got to do now is to enter your list of location names in table "Cities", run the following INSERT (Tools>SQL...) statement and fill out the distances between the entered cities (form "Enter Distances").

Code: Select all

INSERT INTO "DIST"
(
SELECT C1.ID AS CID1, C2.ID AS CID2, NULL
FROM "Cities" AS C1, "Cities" AS C2
WHERE CID1<CID2
AND NOT EXISTS
(SELECT * FROM "DIST" WHERE C1.ID || C2.ID = CID1 || CID2)
);
Then you enter new tours into the "Tours" form entering some date and remark and selecting the destinations. The order of destinations is determined by an ordinal number of type TINYINT (0 to 255).
The subforms on the right show the saved form's collected data in right order and the aggregated distances (sum, count, average, ...).

This is how to delete all test data in the right order:
menu:Tools>SQL...

Code: Select all

DELETE FROM "T_C";
DELETE FROM "DIST";
DELETE FROM "Cities";
DELETE FROM "Tours";
Attachments
tour_distances.odb
Calculate distances
(39.4 KiB) Downloaded 1269 times
Last edited by Villeroy on Sat Aug 13, 2011 11:20 pm, edited 1 time in total.
Reason: replaced "Distance" with "DIST" in the README form and posting
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply