Forums

matthisco

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['northings'],$row['eastings']);  

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

}
?>

traq

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.

matthisco

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.

traq

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?

matthisco

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['Eastings'];
$north = $row['Northings'];

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

}


?>

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?

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 UPDATEs 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['pKey'];

          # do your conversions
          $newValue1 = conversion_function( $row['value1'] );
          $newValue2 = conversion_function( $row['value2'] );

          # 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/mysqli or PDO are recommended instead.

More info about choosing.