Databases
StackOverflow2013
Postsdbo
POMYSQL/PHP - Return average of rows while value doesn't change?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMYSQL/PHP - Return average of rows while value doesn't change?
    text
    copied!<p>The title of this doesn't quite make sense, so I'll do my best to explain.</p> <p>I have a very large dataset (1000's of rows) in a single table. The data in this table relates to GPS tracking of vehicles. When the vehicle is stationary (Speed=0), the latitude and longitude can vary quite dramatically over a period of 12 hours.</p> <p>My current SELECT query is this:</p> <pre><code>$query = "SELECT UUID, UNITID, Truncate(LONGITUDE,6) AS LONGITUDE, Truncate(LATITUDE,6) AS LATITUDE, SPEED, TRACKINGTIME FROM trackpoint_au WHERE SPEED &gt; -1 Order By UnitID, TRACKINGTIME"; </code></pre> <p>The query returns an XML page, via PHP. Built like so:</p> <pre><code>header("Content-type: text/xml"); // Start XML file, echo parent node echo '&lt;markers&gt;'; // Iterate through the rows, printing XML nodes for each while ($row = @mysql_fetch_assoc($result)){ // ADD TO XML DOCUMENT NODE echo '&lt;marker '; echo 'unitid="' . $row['UNITID'] . '" '; echo 'lat="' . $row['LATITUDE'] . '" '; echo 'lng="' . $row['LONGITUDE'] . '" '; echo 'spd="' . $row['SPEED'] . '" '; echo 'time="' . $row['TRACKINGTIME'] . '" '; echo '/&gt;'; } // End XML file echo '&lt;/markers&gt;'; </code></pre> <p>The output looks like:</p> <pre><code>&lt;marker unitid="7711010426" lat="-32.080402" lng="115.854890" spd="0" time="2011-11-30 06:15:00" /&gt; &lt;marker unitid="7711010426" lat="-32.080376" lng="115.854880" spd="0" time="2011-11-30 06:16:00" /&gt; &lt;marker unitid="7711010426" lat="-32.080364" lng="115.854880" spd="0" time="2011-11-30 06:17:00" /&gt; &lt;marker unitid="7711010426" lat="-32.080330" lng="115.854836" spd="0" time="2011-11-30 06:18:00" /&gt; &lt;marker unitid="7711010426" lat="-32.080326" lng="115.854860" spd="1.85" time="2011-11-30 06:20:00" /&gt; &lt;marker unitid="7711010426" lat="-32.080265" lng="115.854890" spd="0" time="2011-11-30 06:21:00" /&gt; &lt;marker unitid="7711010426" lat="-32.080276" lng="115.854920" spd="0" time="2011-11-30 06:22:00" /&gt; &lt;marker unitid="7711010426" lat="-32.080315" lng="115.854900" spd="0" time="2011-11-30 06:23:00" /&gt; &lt;marker unitid="7711010426" lat="-32.080296" lng="115.854866" spd="0" time="2011-11-30 06:24:00" /&gt; </code></pre> <p><strong>My question is this: How can I use PHP OR MYSQL to return the average latitude/longitude of the rows with spd=0?</strong></p> <p>My resulting data should be like this:</p> <pre><code>&lt;marker unitid="7711010426" lat="-32.080367" lng="115.8548715" spd="0" time="2011-11-30 06:18:00" /&gt; &lt;marker unitid="7711010426" lat="-32.080326" lng="115.854860" spd="1.85" time="2011-11-30 06:20:00" /&gt; &lt;marker unitid="7711010426" lat="-32.080288" lng="115.854894" spd="0" time="2011-11-30 06:24:00" /&gt; </code></pre> <p>Note, the 'average' row has the LAST timestamp of the rows that have been averaged out.</p> <p>I have tried to use a 'Group By Speed'. However, this fails to do what I need as it groups ALL the records with an identical speed, not just the ones with a value of 0.</p> <p><strong>EDIT</strong></p> <p>Grouping by the UUID as suggested by macek does not help, as the UUID is unique for each row.</p> <pre><code>&lt;marker time="2011-11-30 06:15:00" spd="0" lng="115.854890" lat="-32.080402" unitid="7711010426" uuid="c6d50454-aa5b-4069-8756-72c787923173"/&gt; &lt;marker time="2011-11-30 06:16:00" spd="0" lng="115.854880" lat="-32.080376" unitid="7711010426" uuid="be6f9052-ab00-430a-8cec-6abf5051cad1"/&gt; </code></pre> <p><strong>ANSWER</strong></p> <p>After posting the question and reading some of the answers below, I managed to put this PHP code together. It loops through all the rows, checks the speed, if the speed is 0, check the next row (until speed&lt;>0) and average out the lat/lng of those points.</p> <pre><code>for($i=0;$i&lt;$num;$i++){ mysql_data_seek($result,$i); $row = mysql_fetch_assoc($result); if ($row['SPEED']==0){ //echo $i . ' spd: '.$row['SPEED'] . '&lt;br /&gt;'; $spd0 = true; $counter = 1; $lat = $row['LATITUDE']; $lng = $row['LONGITUDE']; $i++; while (($spd0==true) &amp;&amp; ($i&lt;$num)){ //echo ' + ' . $i; mysql_data_seek($result,$i); $row2 = mysql_fetch_assoc($result); if (($row2['UNITID']==$row['UNITID']) &amp;&amp; ($row2['SPEED']==0)){ $counter++; $lat = $lat + $row2['LATITUDE']; $lng = $lng + $row2['LONGITUDE']; //echo $i . ' spd: '.$row2['SPEED'] . '&lt;br /&gt;'; $i++; } else{ $spd0=false; $i--; } } $lat = $lat/$counter; $lng = $lng/$counter; // ADD TO XML DOCUMENT NODE echo '&lt;marker '; echo 'uuid ="' . $row['UUID'] . '" '; echo 'unitid="' . $row['UNITID'] . '" '; echo 'lat="' . $lat . '" '; echo 'lng="' . $lng . '" '; echo 'spd="' . $row['SPEED'] . '" '; echo 'time="' . $row['TRACKINGTIME'] . '" '; echo '/&gt;'; } else { //echo $i; // ADD TO XML DOCUMENT NODE echo '&lt;marker '; echo 'uuid ="' . $row['UUID'] . '" '; echo 'unitid="' . $row['UNITID'] . '" '; echo 'lat="' . $row['LATITUDE'] . '" '; echo 'lng="' . $row['LONGITUDE'] . '" '; echo 'spd="' . $row['SPEED'] . '" '; echo 'time="' . $row['TRACKINGTIME'] . '" '; echo '/&gt;'; } } </code></pre> <p>If someone has a more elegant way of checking the next rows, please post it, as always looking for ways to improve my code.</p> <p>Thanks all!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload