Grow your CSS skills. Land your dream job.

loop through a database and covert a northings and eastings value for each row

  • # March 14, 2013 at 12:54 pm

    Hi folks,

    I’m trying to loop through a database and covert a northings and eastings value for each row.

    This is what I have so far but it doesnt work its a very rough example, hopefully you can get the idea, can anyone help?

    Many thanks

    < ?php

    // function to convert values

    function OSGB36ToWGS84(lat,lang) {
    $airy1830 = new RefEll(6377563.396, 6356256.909);
    $a = $airy1830->maj;
    $b = $airy1830->min;
    $eSquared = $airy1830->ecc;
    $phi = deg2rad($this->lat);
    $lambda = deg2rad($this->lng);
    $v = $a / (sqrt(1 – $eSquared * sinSquared($phi)));
    $H = 0; // height
    $x = ($v + $H) * cos($phi) * cos($lambda);
    $y = ($v + $H) * cos($phi) * sin($lambda);
    $z = ((1 – $eSquared) * $v + $H) * sin($phi);

    $tx = 446.448;
    $ty = -124.157;
    $tz = 542.060;
    $s = -0.0000204894;
    $rx = deg2rad( 0.00004172222);
    $ry = deg2rad( 0.00006861111);
    $rz = deg2rad( 0.00023391666);

    $xB = $tx + ($x * (1 + $s)) + (-$rx * $y) + ($ry * $z);
    $yB = $ty + ($rz * $x) + ($y * (1 + $s)) + (-$rx * $z);
    $zB = $tz + (-$ry * $x) + ($rx * $y) + ($z * (1 + $s));

    $wgs84 = new RefEll(6378137.000, 6356752.3141);
    $a = $wgs84->maj;
    $b = $wgs84->min;
    $eSquared = $wgs84->ecc;

    $lambdaB = rad2deg(atan($yB / $xB));
    $p = sqrt(($xB * $xB) + ($yB * $yB));
    $phiN = atan($zB / ($p * (1 – $eSquared)));
    for ($i = 1; $i < 10; $i++) {
    $v = $a / (sqrt(1 – $eSquared * sinSquared($phiN)));
    $phiN1 = atan(($zB + ($eSquared * $v * sin($phiN))) / $p);
    $phiN = $phiN1;
    }

    $phiB = rad2deg($phiN);

    $this->lat = $phiB;
    $this->lng = $lambdaB;
    }

    // Make a MySQL Connection
    $query = “SELECT * FROM example”;

    $result = mysql_query($query) or die(mysql_error());

    // loop through each row

    while($row = mysql_fetch_array($result)){

    // convert values using function

    OSGB36ToWGS84($row,$row);

    // update row with new values
    mysql_query(“UPDATE table SET latitude = newlatitude,longitude = newlongitude “);
    $result = mysql_query($query) or die(mysql_error());

    }
    ?>

    __
    # March 14, 2013 at 2:04 pm

    > but it doesnt work

    This is very, very useless information.

    If you don’t describe what your problem is, it is difficult for anyone to help you solve it. In general, the problems that jump out at me are:

    …your function definition uses constants instead of variables for its args (you should be getting a syntax error because of this)

    …your function returns no values

    …you never actually connect to the database

    …the SQL for your UPDATE query is (*probably*) invalid

    ****

    In general, when looking for help, you need to:

    …** What do you want to accomplish?** Be specific. Never say things like, “hopefully you can get the idea.”

    …** What problems did you encounter?** What did you expect to happen, and what *actually* happened instead? Again, be specific: “I got an error message that says `undefined variable …`” is helpful; “it doesn’t work” is not.

    …** What have you already tried?** How did you try to solve your problem? If you couldn’t figure anything out, describe what you think might be causing the problem: e.g., “if I change `$something` to `true` instead of `false`, the error changes…”.

    Above all, be specific and describe everything completely, clearly, and simply. Asking good questions helps you get good answers (and often, helps you answer the question yourself). *[Learn more about Asking The Duck](http://hwrnmnbsol.livejournal.com/148664.html)*.

    # March 15, 2013 at 11:59 am

    Thanks for the reply.

    I’m sorry I was so vaigue – I was just trying to post some code as a start point rather than nothing at all.

    I need to covert some northing and easting values into longitude and latitude. I’d like a PHP script to do this – could anyone advise on the best way to go about this?

    I have found a function that converts these values, at the top of code.

    I am then trying to use this function in a while loop to convert these values and insert them into a field in the database table.

    Any advise much appreciated.

    __
    # March 16, 2013 at 3:53 pm

    I can’t attest to how well the code in that function works (it may work perfectly, I don’t know). Problem is, it won’t accept any input, and doesn’t return anything. In a practical sense, this makes it useless.

    Do you understand the problems I mentioned above? for example, this:

    function OSGB36ToWGS84(lat,lang)

    should be causing a parse error. You can’t use constants in a function definition like that – you need to use variables (you probably meant to do something like this):

    function OSGB36ToWGS84( $lat,$lang )

    However, you don’t seem to try to use either of those later on anyway. Further, your function doesn’t `return` any values: all of the work is simply discarded when the function is done.

    There are other parse errors as well. Did you try to copy this function out of a class definition?

    # March 21, 2013 at 12:08 pm

    Hi traq,

    Thanks for the reply really appreciate it.

    I’m getting there slowly.

    I’ve manged to loop through my DB and convert the values into latitude and longitude. Do you know how I can update each row in the loop with the new values?

    < ?php

    require(“dbinfo.php”);

    include_once(“phpcoord-2.3.php”);

    // Opens a connection to a mySQL server
    $connection=mysql_connect ($hostname, $username, $password);

    if (!$connection) {
    die(“Not connected : ” . mysql_error());
    }

    // Set the active mySQL database
    $db_selected = mysql_select_db($database, $connection);
    if (!$db_selected) {
    die (“Can’t use db : ” . mysql_error());
    }

    // Make a MySQL Connection
    $query = ” SELECT *
    FROM codebase LIMIT 10″;

    $result = mysql_query($query);

    if (!$result) {
    die(“Invalid query: ” . mysql_error());
    }

    // loop through each row

    while($row = mysql_fetch_array($result)){

    // convert values using function
    $east = $row;
    $north = $row;

    $os1 = new OSRef($east, $north);
    echo “OS Grid Reference: ” . $os1->toString() . ” – ” . $os1->toSixFigureString() . “
    “;
    $ll1 = $os1->toLatLng();
    echo “Converted to Lat/Long: ” . $ll1->toString();

    }

    ?>

    __
    # March 22, 2013 at 12:44 am

    > Thanks for the reply really appreciate it. I’m getting there slowly. I’ve manged to loop through my DB and convert the values into latitude and longitude. Do you know how I can update each row in the loop with the new values?

    I’m glad to hear that you’re making progress. However, it is difficult to offer advise when you ignore my questions.

    > Do you understand the problems I mentioned above?

    > Did you try to copy this function out of a class definition?

    It is also unclear if anything I suggested actually led to a solution. You don’t show (or mention) which problems you’ve solved, how you’ve solved them, or what your code looked like afterwards. (It’s okay if my suggestion didn’t work; but I need to know – otherwise, I’m “in the dark” and it’s hard to know what to suggest next.) In fact, it’s difficult to tell if the code snippets you’ve posted are directly related, or if you’ve simply moved on to the next section you’re having problems with.

    *****

    > I’ve manged to loop through my DB and convert the values into latitude and longitude. Do you know how I can update each row in the loop with the new values?

    Now, if I understand your current question correctly, you want to take the values from the DB, convert them, and then update the DB with the new values. Correct?

    The best way would be to store the converted values in an array as you process them, then use them to write a new query when the loop is done. You can do multiple `UPDATE`s with a mutli-query, but I’d suggest using the `INSERT …ON DUPLICATE KEY UPDATE` syntax instead.

    The basic structure would be like this:

    < ?php
    # get values from DB
    # make sure you get the col’s primary key also
    $SQL_select = “SELECT pKey,value1,value2 FROM my_table WHERE whatever”;
    if( ($result = mysql_query( $SQL_select )) !== false ){

    # loop through result set
    while( $row = mysql_fetch_assoc( $result ) ){

    $pKey = $row;

    # do your conversions
    $newValue1 = conversion_function( $row );
    $newValue2 = conversion_function( $row );

    # write the pKey and new values as a set
    # (for use in the INSERT query)
    $newValues[] = “( ‘$pKey’,$newValue1,$newValue2 )”;
    }
    # next, write an INSERT query.
    $SQL_insert = “INSERT INTO my_table( pKey,value1,value2 ) VALUES”;

    # use implode() to concatenate each of the sets in $newValues
    # with a comma
    $SQL_insert .= implode( ‘,’,$newValues );

    # finish up with the ON DUPLICATE command
    $SQL_insert .= ” ON DUPLICATE KEY UPDATE
    value1 = VALUES( value1 )
    value2 = VALUES( value2 )”;

    # run the query
    if( mysql_query( $SQL_insert ) ){

    print “success!”;

    }else{

    print “failure!”;

    }

    }else{

    print “if you’re seeing this, the SELECT query failed.”;

    }

    *****
    On yet another note, **you should not use the `mysql_*` functions**, *especially* if you’re learning. **ext/mysql is deprecated** and is **not recommended** for new code. It will be removed from PHP in the future. [ext/mysql**i**](http://php.net/mysqli) or [PDO](http://php.net/PDO) are recommended instead.

    [More info about choosing](http://www.php.net/manual/en/mysqlinfo.api.choosing.php).

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".