我想计算多个位置(环路)之间行驶的总距离，包括距离(起点(车库)-第一个位置的起点)和(最后一个位置的终点-终点(车库))。例子:(车库+ D1) + (D1和D2) + (D2 + E1) + (E1 + E2) + E2 +车库)
我有一个正确的循环问题。下面是我的简化代码:
<?
$driver = 5;
$result2 = mysql_query("SELECT * FROM test WHERE id='$driver' LIMIT 1") or die(mysql_error());
while($row2 = mysql_fetch_array( $result2 )) {
$lon=$row2['lon'];
$lat=$row2['lat'];
echo "$lon, $lat";
}
$result = mysql_query("SELECT * FROM test1 WHERE driver='$driver'") or die(mysql_error());
while($row = mysql_fetch_array( $result )) {
$lon1=$row['lon1'];
$lat1=$row['lat1'];
$lon2=$row['lon2'];
$lat2=$row['lat2'];
////////// distance between driver address and starting address
$distancecalc = (3958*3.1415926*sqrt(($lat-$lat1)*($lat-$lat1) + cos($lat/57.29578)*cos($lat1/57.29578)*($lon-$lon1)*($lon-$lon1))/180);
////////// distance between statring address and finishing address - multiple adsresses
$distancecalc1 = $distancecalc1 + (3958*3.1415926*sqrt(($lat2-$lat1)*($lat2-$lat1) + cos($lat2/57.29578)*cos($lat1/57.29578)*($lon2-$lon1)*($lon2-$lon1))/180);
////////// distance between finishing address and driver address
$distancecalc2 = (3958*3.1415926*sqrt(($lat2-$lat)*($lat2-$lat) + cos($lat2/57.29578)*cos($lat/57.29578)*($lon2-$lon)*($lon2-$lon))/180);
$distancetotal = $distancecalc + $distancecalc1 +$distancecalc2;
echo "$distancecalc<br>
$distancecalc1<br>
$distancecalc2<br>";
}
echo "$distancetotal";
?>
我尝试了一些事情(主要是如果…)也有更多的数据库请求，但我仍然有避免多次计算的问题，我也坚信有一种方法来编码它，使它更容易和更清晰。
我希望你能帮我解决这个问题。
下面是您需要的函数:
function distance ($lat1, $lon1, $lat2, $lon2) {
return (3958*3.1415926*sqrt(($lat2-$lat1)*($lat2-$lat1) + cos($lat2/57.29578)*cos($lat1/57.29578)*($lon2-$lon1)*($lon2-$lon1))/180);
}
函数体与您使用的内联公式完全相同，所以我不明白为什么您需要帮助。
数字6367 -是以千米为单位的地球半径
DELIMITER $$
DROP FUNCTION IF EXISTS geodist $$
CREATE FUNCTION geodist (
src_lat DECIMAL(9,6), src_lon DECIMAL(9,6),
dst_lat DECIMAL(9,6), dst_lon DECIMAL(9,6)
) RETURNS DECIMAL(6,2) DETERMINISTIC
BEGIN
SET @dist := 6367 * 2 * ASIN(SQRT(
POWER(SIN((src_lat - ABS(dst_lat)) * PI()/180 / 2), 2) +
COS(src_lat * PI()/180) *
COS(ABS(dst_lat) * PI()/180) *
POWER(SIN((src_lon - dst_lon) * PI()/180 / 2), 2)
));
RETURN @dist;
END $$
DELIMITER ;
好的。我在mac_gyver (php怪胎)的帮助下解决了这个问题。所有的计算都如我所愿。我的代码如下:
<?
include "connectdb.php";
$driver = 5;
$datestamp = '2013/05/07';
$result2 = mysql_query("SELECT * FROM drivers WHERE id='$driver' LIMIT 1") or die(mysql_error());
while($row2 = mysql_fetch_array( $result2 )) {
$lon=$row2['lon'];
$lat=$row2['lat'];
}
$result = mysql_query("SELECT * FROM quotedb WHERE moveday='$datestamp' AND driver='$driver' AND cleared='Not Cleared' AND status='Done' ORDER BY moveday, timeday") or die(mysql_error());
$distance = 0; // accumulate the distance
$first_pass = true; // flag to detect the first row inside the loop
while($row = mysql_fetch_assoc( $result )) {
$lon2a=$lon2;
$lat2a=$lat2;
$lon1=$row['lon1'];
$lat1=$row['lat1'];
$lon2=$row['lon2'];
$lat2=$row['lat2'];
// calculate the distance from the Garage to the first point of the first row
if($first_pass){
$distance += (3958*3.1415926*sqrt(($lat-$lat1)*($lat-$lat1) + cos($lat/57.29578)*cos($lat1/57.29578)*($lon-$lon1)*($lon-$lon1))/180);
$first_pass = false;
}
// calculate the distance for each row (segment) in the route
$distance += (3958*3.1415926*sqrt(($lat2-$lat1)*($lat2-$lat1) + cos($lat2/57.29578)*cos($lat1/57.29578)*($lon2-$lon1)*($lon2-$lon1))/180);
if ( $lon2a == "" or $lat2a =="" ) {
} else {
// calculate the distance from the second point of the first row to the first point of the next row
$distance += (3958*3.1415926*sqrt(($lat2a-$lat1)*($lat2a-$lat1) + cos($lat2a/57.29578)*cos($lat1/57.29578)*($lon2a-$lon1)*($lon2a-$lon1))/180);
}
}
// calculate the distance from the second point of the last row to the Garage
$distance += (3958*3.1415926*sqrt(($lat2-$lat)*($lat2-$lat) + cos($lat2/57.29578)*cos($lat/57.29578)*($lon2-$lon)*($lon2-$lon))/180);
echo "$distance<br>
";
?>
仍然认为代码中有改进的地方。将采用哈弗森方法进行计算。你们有什么建议来改进这段代码吗?thx
改进版本:
<?
include "connectdb.php";
$driver = 5;
$datestamp = '2013/05/07';
$result2 = mysql_query("SELECT * FROM drivers WHERE id='$driver' LIMIT 1") or die(mysql_error());
while($row2 = mysql_fetch_array( $result2 )) {
$garage_lon=$row2['lon'];
$garage_lat=$row2['lat'];
}
$result = mysql_query("SELECT * FROM quotedb WHERE moveday='$datestamp' AND driver='$driver' AND cleared='Not Cleared' AND status='Done' ORDER BY moveday, timeday") or die(mysql_error());
function calculate_distance($lon1, $lat1, $lon2, $lat2) {
return (3958 * 3.1415926 * sqrt(($lat2 - $lat1) * ($lat2 - $lat1) + cos($lat2 / 57.29578) * cos($lat1 / 57.29578) * ($lon2 - $lon1) * ($lon2 - $lon1)) / 180);}
$previous_lon = $garage_lon;
$previous_lat = $garage_lat;
$distance = 0; // accumulate the distance
while($row = mysql_fetch_assoc( $result ))
{
$lon1 = $row['lon1'];
$lat1 = $row['lat1'];
$lon2 = $row['lon2'];
$lat2 = $row['lat2'];
if ( $previous_lon && $previous_lat )
{
// calculate the distance from the second point of the first row to the first point of the next row
$distance += calculate_distance($lon1, $lat1, $previous_lon, $previous_lat);
}
// calculate the distance for each row (segment) in the route
$distance += calculate_distance($lon1, $lat1, $lon2, $lat2);
$previous_lon = $lon2;
$previous_lat = $lat2;
}
// calculate the distance from the second point of the last row to the Garage
$distance += calculate_distance($garage_lon, $garage_lat, $lon2, $lat2);
$distance = round($distance,0);
echo "$distance<br>
";
?>