# Thread: How did they do this? Rail/Plane Travel distance calculations

1. ## How did they do this? Rail/Plane Travel distance calculations

Hi,

I'm interested in knowing the basics of how this form works:
http://www.co2balance.uk.com/co2calc...s/rail-travel/ figuring out the miles.

Do they use actual Google longitude/latitude values then assign them for each train station? I'm assuming it is developed to measure the miles 'as the crow flies' and not the actual path of the train.

Would it be the same for their Airport calculator:
http://www.co2balance.uk.com/co2calculators/air-travel/ where they use long/lat values to get the miles/km for travel.

Surely this must take yeeeeeeeears to build and assign every possible airport in the world a value?

2. A long time? Possibly - unless of course they have access to a reliable data source already.

I would imagine the data for distances for Rail journeys would be out there too, somewhere.

There are few post's on here detailing how to calculate distances from 2 longs / lats, most will use the Great Circle Formula.

3. Oh man, just typed a biffta of a reply and it disappeared.

Thanks for the link and help. I will look for data on rail journeys soon.

Thanks for the help.

4. Hi SB.

I've just been going through the Global Airport db you so kindly pointed me onto

Having had a look at the db structure and contents themselves, it seems to be:

ICAO Code:IATA Code:Airport Name:City,Town or Suburb:Country:Latitude Degrees:Latitude Minutes:Latitude Seconds:Latitude Direction:Longitude Degrees:Longitude Minutes:Longitude Seconds:Longitude:Altitude:

AYGA:GKA:GOROKA:GOROKA:PAPUA NEW GUINEA:06:04:54:S:145:23:30:E:5282

I think my best plan now is to run a PHP script which will then insert all this data into a 'countries' SQL table.
Then plan a script which will determine miles from the longitude/latitude degrees, minutes, seconds that the user selects.

Does this make sense or am I way off?

5. Sounds good to me.

I did post a MySQL function on here somewhere which lets you calculate the distance in MySQL, it saved you having pull all the data back to PHP to perform the calculations.

I'll try to find it.

Cups is a self confessed expert at this stuff, maybe can offer some pointers if he comes by.

6. He's thinking of this one.

7. Brilliant, thanks for the replies guys.

I guess I'd be using the form to find the miles between 2 areas, then use that value in the multiplication to get the c02 value.
The plan would be to store all the airports in a sql table using the database SB pointed me to.

There's quite a lot of code examples in that thread, which one would be most useful to me do you think?
Sorry if it sounds lazy, I'm just a little confused by it all

8. It look's like post numbers 21 and 22 should set you away.

9. Thanks again guys for your help and patience.

OK, just so I understand....

I'll have a MySQL table, something like:
PHP Code:
``` id | ICAOCode | IATACode | AirportName | City | Country | LatitudeDegrees | LatitudeMinutes | LatitudeSeconds | LatitudeDirection | LongitudeDegrees | LongitudeMinutes | LongitudeSeconds | Longitude | Altitude  ```
which could store the following:

PHP Code:
``` 1 | EGPD | ABZ | DYCE | ABERDEEN | U.K | 57 | 12 | 15 | N | 02 | 12 | 01 | U | 0215 2 | EGPF | GLA | GLASGOW | GLASGOW | U.K | 55 | 52 | 19 | N | 04:25 | 59 | U | 0026  ```
Then when the user submits say Aberdeen --> Glasgow...

MySQL does...

PHP Code:
``` \$sSQL = sprintf("SELECT prop_name, prop_long, prop_lat FROM properties WHERE DISTANCE(%s,%s,prop_lat,prop_long) < %s;",         \$userLong, //--> The users longtitude         \$userLat, //--> The users latitude         ); ?>  ```
Code MySQL:
`CREATE  FUNCTION DISTANCE( userLat FLOAT, userLong FLOAT, targetLat FLOAT, targetLong FLOAT )  RETURNS FLOAT( 11  )  RETURN ( 3958.75 * ACOS( SIN( userLat / 57.29577951  )  * SIN( targetLat / 57.29577951  )  + COS( userLat / 57.29577951  )  * COS( targetLat / 57.29577951  )  * COS( targetLong / 57.29577951 - userLong / 57.29577951  )  )  )`

Now how does it compare the longitude and lattitude from this?

10. Those examples are just that I'm afraid, examples.

You're going to have to apply your own specific logic to get them to work for your particular situation.

First, you're going to have to import that function into your database. Then using SQL, obtain the longs and lats of the two selected airports passing these to the function which will produce the distance for you.

I'm not sure how to do this in one SQL query, but it would go along the lines of...

PHP Code:
``` <?php\$sSQL = sprintf("SELECT longitude, latitude FROM table WHERE name = '%s'",    mysql_real_escape_string(\$_POST['startingAirport']));\$rResult = mysql_query(\$sSQL);\$aStartingAiport = mysql_fetch_assoc(\$rResult);\$sSQL = sprintf("SELECT longitude, latitude FROM table WHERE name = '%s'",    mysql_real_escape_string(\$_POST['destinationAirport']));\$rResult = mysql_query(\$sSQL);\$aDestinationAiport = mysql_fetch_assoc(\$rResult);\$sSQL = sprintf("SELECT DISTANCE(%s, %s, %s, %s) AS theDistance;",    \$aStartingAiport['latitude'],    \$aStartingAiport['longitude'],    \$aDestinationAiport['latitude'],    \$aDestinationAiport['longitude']);\$rResult = mysql_query(\$sSQL);\$aDistance = mysql_fetch_assoc(\$rResult);printf('The distance between %s Airport and %s Airport is %s',    \$_POST['startingAirport'],    \$_POST['destinationAirport'],    \$aDistance['theDistance']);?> ```
Off Topic:

Awaits the wrath of the SQL bods!

11. Many thanks for your help once more SilverBullet.

I will dissect this tonight but it all seems to make sense.

I'm still not sure whether I'd take in the airport POST value or perhaps as a unique id to speak to the db.

12. Back again

PHP Code:
``` SQL-query:CREATE FUNCTION distance(userLat FLOAT,userLong FLOAT,targetLat FLOAT,targetLong FLOAT) RETURNS FLOAT( 11 ) RETURN ( 3958.75 * ACOS( SIN( userLat / 57.29577951 ) * SIN( targetLat / 57.29577951 ) + COS( userLat / 57.29577951 ) * COS( targetLat / 57.29577951 ) * COS( targetLong / 57.29577951 - userLong / 57.29577951 ) ) )MySQL said:#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(userLat FLOAT, userLong FLOAT, targetLat FLOAT, targetLong FLOAT )  RETURNS FLO' at line 1  ```
I tried executing the function but get a error message. Not sure why this is the case.

Here's my current DB structure:

PHP Code:
``` CREATE TABLE `airports` (  `id` smallint(6) NOT NULL auto_increment,  `name` varchar(255) NOT NULL default '',  `long` varchar(20) NOT NULL default '',  `altitude` varchar(25) NOT NULL default '',  `lat` varchar(20) NOT NULL default '',  PRIMARY KEY  (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;-- -- Dumping data for table `airports`-- INSERT INTO `airports` VALUES (1, 'Glasgow', '042559U', '0026', '555219N');INSERT INTO `airports` VALUES (2, 'Aberdeen', '021201U', '0215', '571215N');  ```

13. Good morning trouble!

Try this SQL query to create the function...

PHP Code:
``` DELIMITER \$\$DROP FUNCTION IF EXISTS `distance`\$\$CREATE FUNCTION `distance`(userLat float, userLong float, targetLat float, targetLong float)    RETURNS float(11)BEGIN    RETURN (3958.75 * acos(sin(userLat/57.2958) * sin(targetLat/57.2958) + cos(userLat/57.2958) * cos(targetLat/57.2958) * cos(targetLong/57.2958 - userLong/57.2958)));END\$\$DELIMITER ;  ```
Just paste it directly into PMA if you have it.

14. Hahaha, yep, that's the middle name. For the 'big' projects anyway

Back in a sec.

Oh man, when I put it in...

PHP Code:
``` #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER \$\$DROP FUNCTION IF EXISTS `distance`\$\$CREATE FUNCTION `distanc' at line 1  ```
How do I know if the function has been created successfully?

Here's my code for the form itself:

PHP Code:
``` <?phpif (isset(\$_POST['submitted'])) {    \$sSQL = sprintf("SELECT long, lat FROM airports WHERE name = '%s'",        mysql_real_escape_string(\$_POST['startingAirport'])    );        \$rResult = mysql_query(\$sSQL);    \$aStartingAiport = mysql_fetch_assoc(\$rResult);        \$sSQL = sprintf("SELECT long, lat FROM airports WHERE name = '%s'",        mysql_real_escape_string(\$_POST['destinationAirport'])    );        \$rResult = mysql_query(\$sSQL);    \$aDestinationAiport = mysql_fetch_assoc(\$rResult);        \$sSQL = sprintf("SELECT DISTANCE(%s, %s, %s, %s) AS theDistance;",        \$aStartingAiport['latitude'],        \$aStartingAiport['longitude'],        \$aDestinationAiport['latitude'],        \$aDestinationAiport['longitude']    );        \$rResult = mysql_query(\$sSQL);    \$aDistance = mysql_fetch_assoc(\$rResult);            printf('The distance between %s Airport and %s Airport is %s',        \$_POST['startingAirport'],        \$_POST['destinationAirport'],        \$aDistance['theDistance']    );    }?><form action="airport.php" method="POST">    <label for="startingAirport">        <select id="startingAirport" name="startingAirport">            <option value="2">Aberdeen</option>        </select>    </label>        <label for="destinationAirport">        <select id="destinationAirport" name="destinationAirport">            <option value="1">Glasgow</option>        </select>    </label>        <input type="submit" value="Submit!" />    <input type="hidden" name="submitted" value="TRUE" /></form> ```

15. I'm afraid my SQL skills are going to let us down here, but If I paste it directly from post 13, it works just fine this end. :s

16. That is an odd issue. Seems to be just having problems with the web host's MyPANEL.

I've tried using the code in my local PHPMYADMIN it's crashed a lot, but now gives me:

PHP Code:
``` SQL query:CREATE FUNCTION `distance` (userLat float,userLong float,targetLat float,targetLong float) RETURNS float( 11 ) BEGIN RETURN ( 3958.75 * acos( sin( userLat / 57.2958 ) * sin( targetLat / 57.2958 ) + cos( userLat / 57.2958 ) * cos( targetLat / 57.2958 ) * cos( targetLong / 57.2958 - userLong / 57.2958 ) ) ) ;END\$\$MySQL said: Documentation#1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)  ```
Thanks for your help with this

17. Can you explain exactly how you run that function SB? I'm afraid I'm having no luck at this end

18. Have you managed to successfully create it yet?

19. No, I don't think so. Sorry, that's what I meant, I'm having trouble creating the function in the first place.

I've tried it on my localhost phpmyadmin and still getting nowhere It's phpmyadmin3.1.1 if that's any use.

It just seems to take forver to do anything, then times out

20. I'm using 2.11.6 and MySQL 5.0.51a.

Lets just make sure we're doing the exact same thing here.

1. Open PMA
2. Click on target database on left of screen.
3. Select the SQL tab from the right of screen.
4. Paste inside the white query box the following SQL...
Code:
```DELIMITER \$\$

DROP FUNCTION IF EXISTS `distance`\$\$

CREATE FUNCTION `distance`(userLat float, userLong float, targetLat float, targetLong float)
RETURNS float(11)
BEGIN
RETURN (3958.75 * acos(sin(userLat/57.2958) * sin(targetLat/57.2958) + cos(userLat/57.2958) * cos(targetLat/57.2958) * cos(targetLong/57.2958 - userLong/57.2958)));
END\$\$

DELIMITER ;```
5. Click 'Go'.

21. I'm with phpmyadmin3.1.1, mysql 5.1.30, php 5.2.8.

Have done the exact same as above, but no success i.e. the green loading bar takes aaaaages to load, then it gives me a 'maximum time exceeded' error

When I click 'Go' it just seems to die

Do I need to have sql tables in the database?
Is it something to do with Vista/Firefox?

Is there anyway to skip PMA and go direct through the MySQL console.

22. OK,

I've now got the same error I received in Post 16.

Is this saying I need to have a SQL table in there?

23. Sorry fella, I'm all out.

You could try creating the function directly in the console, there is nothing that should hold you up....

He says!

Edit: Just pasted the query into the console and it worked just fine.

24. I'll pay for a train ticket to my house, return ticket of course

OK, will try directly in console.
Painstakingly put it into my mysql console, to get the #1064 error once more

Just to confirm, I'm putting in:

PHP Code:
``` DELIMITER \$\$DROP FUNCTION IF EXISTS `distance`\$\$CREATE FUNCTION `distance`(userLat float, userLong float, targetLat float, targetLong float)    RETURNS float(11)BEGIN    RETURN (3958.75 * acos(sin(userLat/57.2958) * sin(targetLat/57.2958) + cos(userLat/57.2958) * cos(targetLat/57.2958) * cos(targetLong/57.2958 - userLong/57.2958)));END\$\$DELIMITER ;  ```
and getting out:

PHP Code:
```  MySQL said:#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER \$\$DROP FUNCTION IF EXISTS `distance`\$\$CREATE FUNCTION `distanc' at line 1  ```

EDIT::

Just tried to follow your console screengrab, and have attached my output

25. Silly question, but do I need to have a SQL table with userLat etc. as column headings?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•