SitePoint Sponsor

User Tag List

Page 1 of 3 123 LastLast
Results 1 to 25 of 64
  1. #1
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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?


    Thanks for any advice you can give, my head's friend(!)

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by AnthonySterling; Feb 6, 2009 at 06:26. Reason: Poor, very poor, and quite possibly a complete lack of proof reading...
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by invision2; Feb 6, 2009 at 04:25.

  4. #4
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    He's thinking of this one.

  7. #7
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


    Thanks again for any advice.

  8. #8
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    It look's like post numbers 21 and 22 should set you away.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  9. #9
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    EGPD ABZ DYCE ABERDEEN U.57 12 15 02 12 01 0215
    EGPF GLA GLASGOW GLASGOW U.55 52 19 04:25 59 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?

    My head's scrambled I'm afraid.

  10. #10
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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!
    Last edited by AnthonySterling; Feb 6, 2009 at 11:11.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  11. #11
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #12
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Back again

    PHP Code:
    SQL-query:

    CREATE FUNCTION distance(
    userLat FLOAT,
    userLong FLOAT,
    targetLat FLOAT,
    targetLong FLOAT
    RETURNS FLOAT11 ) RETURN ( 3958.75 ACOSSINuserLat 57.29577951 ) * SINtargetLat 57.29577951 ) + COSuserLat 57.29577951 ) * COStargetLat 57.29577951 ) * COStargetLong 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` (
      `
    idsmallint(6NOT NULL auto_increment,
      `
    namevarchar(255NOT NULL default '',
      `
    longvarchar(20NOT NULL default '',
      `
    altitudevarchar(25NOT NULL default '',
      `
    latvarchar(20NOT NULL default '',
      
    PRIMARY KEY  (`id`)
    ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=;

    -- 
    -- 
    Dumping data for table `airports`
    -- 

    INSERT INTO `airportsVALUES (1'Glasgow''042559U''0026''555219N');
    INSERT INTO `airportsVALUES (2'Aberdeen''021201U''0215''571215N'); 

  13. #13
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Good morning trouble!

    Try this SQL query to create the function...

    PHP Code:
    DELIMITER $$

    DROP FUNCTION IF EXISTS `distance`$$

    CREATE FUNCTION `distance`(userLat floatuserLong floattargetLat floattargetLong 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.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  14. #14
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    <?php

    if (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. #15
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  16. #16
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 float11 BEGIN RETURN ( 3958.75 acossinuserLat 57.2958 ) * sintargetLat 57.2958 ) + cosuserLat 57.2958 ) * costargetLat 57.2958 ) * costargetLong 57.2958 userLong 57.2958 ) ) ) ;

    END$$

    MySQL saidDocumentation
    #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. #17
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you explain exactly how you run that function SB? I'm afraid I'm having no luck at this end

  18. #18
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Have you managed to successfully create it yet?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  19. #19
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #20
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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'.

    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  21. #21
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #22
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #23
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.

    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  24. #24
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 floatuserLong floattargetLat floattargetLong 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. #25
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Silly question, but do I need to have a SQL table with userLat etc. as column headings?


Bookmarks

Posting Permissions

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