Round off error in the database? - Page 2 - GreenHybrid

Jason · #11 (**permalink**) 09-08-2004, 01:52 PM

Old numbers:

Quote:

Car Name Image Mi Mi/Tank MPG Gal Gas Gas/Tank °F Min/Ride Highway Traffic
.
JHX 04 CVT Prius 2,928 586 58.8 49.8 10.0 70 37 43% 92%

New numbers:

Quote:

Car Name Image Mi Mi/Tank MPG Gal Gas Gas/Tank °F Min/Ride Highway Traffic
.
JHX 04 CVT Prius 2,928 586 58.4 50.1 10.0 70 36 67% 89%

If this looks right to you, please let me know ASAP and I'll update the rest of the database.

Jason · #12 (**permalink**) 09-08-2004, 02:07 PM

Ohhh, no. Please tell me I did this right. Good thing I backed up the database.

I ran the script through all the cars to reconfigure. The mileages have changed dramatically... so much so that I question which is more reliable! The Prius went from 49 to 53. The Insight went from 67 to 58. Ah!

Jason · #13 (**permalink**) 09-08-2004, 02:11 PM

Accwai, your equation cannot be right. Look at EMAXX:

Quote:

Car Name Image Mi Mi/Tank MPG Gal Gas Gas/Tank °F Min/Ride Highway Traffic
.
EMAXX 04 CVT Prius 18,498 561 54.7 337.9 10.2 59 46 79% 166%

Jason · #14 (**permalink**) 09-08-2004, 02:18 PM

Okay. I've reverted everything back.

Here is the graph for EMAXX based on YOUR equation. Look at that average. It simply cannot be right.

accwai · #15 (**permalink**) 09-08-2004, 02:34 PM

Well, I downloaded all of Dan's tank data and crunch them through the Excel equivalent of that formula and I got 18498 total miles and 315.08 total gallons. Works out to 58.7mpg. So something is wrong with the PHP version. Can you put in a debug statement and print out the numerator and denominator?

In any case, I'm busy tonight but I probably crunch through all cars tomorrow night and see what the different car types would be like with the new formula, and how the individual cars will change. Or we can just forget the whole thing. Are you interested in pursuing this further?

Andy

Jason · #16 (**permalink**) 09-08-2004, 02:38 PM

Andy,
Yes, it's possible the equation didn't translate properly into PHP. The odd part, however, is that YOUR numbers came out correct. And mine barely changed.

Don't bother going through each car individually. I'll try pulling up some of the code to see what went wrong.

Jason · #17 (**permalink**) 09-08-2004, 02:42 PM

Here's what I ran through to update all the cars:

Code:

$cid = 0;
while ($cid <= 359) {

$weightSQL = "SELECT mileage,distance,temperature,time,highway,traffic FROM data WHERE cid = '$cid'";
$weightresult = mysql_query($weightSQL) or die(mysql_error());
while ( $weight = mysql_fetch_array($weightresult) ) {

	if ($weight['mileage'] != 0) { $mileagedenominator = $mileagedenominator + $weight['distance']/$weight['mileage']; }
	if ($weight['temperature'] != 0) { $temperaturedenominator = $temperaturedenominator + $weight['distance']/$weight['temperature']; }
	if ($weight['time'] != 0) { $timedenominator = $timedenominator + $weight['distance']/$weight['time']; }
	if ($weight['highway'] != 0) { $highwaydenominator = $highwaydenominator + $weight['distance']/$weight['highway']; }
	if ($weight['traffic'] != 0) { $trafficdenominator = $trafficdenominator + $weight['distance']/$weight['traffic']; }
	
	$numerator= $numerator + $weight['distance'];
	
	if ($mileagedenominator != 0) { $mileage = $numerator / $mileagedenominator; }
	if ($temperaturedenominator != 0) { $temperature = round($numerator / $temperaturedenominator,0); }
	if ($timedenominator != 0) { $time = round($numerator / $timedenominator,0); }
	if ($highwaydenominator != 0) { $highway = $numerator / $highwaydenominator; }
	if ($trafficdenominator != 0) { $traffic = $numerator / $trafficdenominator; }
	
	if ($override['lmpgoverride'] != 0) { 
 $mileage = $override['lmpgoverride'];	
	}

}

	$updateSQL = "UPDATE car SET avgmileage = '$mileage', avgtemperature = '$temperature', avgtime = '$time', avghighway = '$highway', avgtraffic = '$traffic' WHERE cid = '$cid'";
	mysql_query($updateSQL) or die(mysql_error());

echo "$cid<br>";
$cid++;

}

Jason · #18 (**permalink**) 09-08-2004, 02:45 PM

Okay, I noticed a potential problem with that code. I don't know that it is what caused the large problem, though.

Because you told me to divide by values that could possibly be 0, I put an IF statement in so it wouldn't throw up a DIVIDE BY 0 error. However, if it skips this step then perhaps the variable's value will carry over from the previous car.

accwai · #19 (**permalink**) 09-08-2004, 02:49 PM

Ok, I have to run. Will take a look at the code tomorrow.

Andy

Jason · #20 (**permalink**) 09-09-2004, 05:58 PM

Ah. Looks like that was the problem. I reset the variables when the script looped (recalculating all of the overall mileages) and it appears to have worked. Please double-check me on that.

Andy, thanks for being such a careful observer! On an individual basis, this may make the database a bit more accurate. Overall, it did not influence the mean mileages for any car. Oh, well!