runmeter export display

Runmeter is an iPhone app useful for GPS tracking outdoor exercise and as such does a fine job eclipsing some of the other variants. Runmeter features the ability to export and share your (KML,GPX,CSV) data via email or link. Although Runmeter has excellent data analysis menu choices on the iPhone itself and can recall and layout event history; the Runmeter does not have a co-branded display and summary website. However, it is possible to mirror your events and statistics on a personal remote web page. Using the Runmeter export feature you might try uploading a GPX file to Everytrail or Trailguru. I prefer to roll my own and follows is a tutorial on how it can be done.

Add a table, which mimics Runmeters CVS file column headings, to your mysql database.

CREATE TABLE raw_runmeter_data (
  Route varchar(35) NOT NULL,
  Activity varchar(10) NOT NULL,
  Start_Time datetime NOT NULL,
  Time_normal time NOT NULL,
  Time_normal_seconds smallint(6) NOT NULL,
  Time_stopped time NOT NULL,
  Time_stopped_seconds int(11) NOT NULL,
  Distance decimal(5,2) NOT NULL,
  Average_speed decimal(5,2) NOT NULL,
  Average_pace time NOT NULL,
  Average_pace_seconds int(11) NOT NULL,
  Climb int(11) NOT NULL,
  Calories int(11) NOT NULL,
  Fastest_speed decimal(5,2) NOT NULL,
  Fastest_pace time NOT NULL,
  Fastest_pace_seconds int(11) NOT NULL,
  Notes text
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

With this table constructed use PHP to retrieve your event data from the Runmeter server and store it in the mysql table. This code downloads your Runmeter CSV file and after some housekeeping, imports the data events to your mysql.

import_cvs.php
[php]
<?php

/**********************************/
/* This file is named import_cvs.php
/* As the file name implies, this code will import your data from Runmeter. You may run this file manually or better still, by setting up a cron job.
/* Edit the entry below to reflect the appropriate value i.e. your unique Runmeter export URL which should look something like this: “http://share.abvio.com/438h2 53u978cd/Runmeter-Route-All.csv”
/*********************************/
$inp = file(‘http://share.abvio.com/***********/Runmeter-Route-All.csv’);
/*********************************/
/* If you use this code, find a flaw, enhance or otherwise improve it then please let me know
/* via comment or link.
/* End Edit (nothing to do below this line)
/*********************************/

include ‘dbinfo.inc.php’; // your variable constants for database access

if (!$inp) {
echo “Unable to open remote file.\n”;
exit;
}
$out = fopen(‘runmeter_clean.csv’,’w’); // a flat file which will temporaily hold your imported data
for ($i=1;$i<count($inp)-1;$i++) // step through the cvs file to leave out the first line ($i=1) and the last line of the file (count($inp)-1) because the first line is a header row and the last line has extraneous data
{
fwrite($out,$inp[$i]);
}
fclose($out);

echo “{$i} Runmeter events ”;

$file_handle = fopen(‘runmeter_clean.csv’,”r”);

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

// first task: let’s empty the mysql table (remove all existing recors)
mysql_query(“TRUNCATE $databasetable”);

// now insert the new recent data. Process Each Line
while (!feof($file_handle) ) {

$data = fgetcsv($file_handle);

$Route = mysql_real_escape_string($data[0]); // this function escapes out the pesky apostrophe character (and double quuotes etc.

$sql_insert = “insert into $databasetable ( Route, Activity, Start_Time, Time_normal, Time_normal_seconds, Time_stopped, Time_stopped_seconds, Distance, Average_speed, Average_pace, Average_pace_seconds, Climb, Calories, Fastest_speed, Fastest_pace, Fastest_pace_seconds, Notes)
values ( ‘$Route’, ‘$data[1]’, ‘$data[2]’, ‘$data[3]’, ‘$data[4]’, ‘$data[5]’ ,’$data[6]’ ,’$data[7]’, ‘$data[8]’, ‘$data[9]’, ‘$data[10]’ , ‘$data[11]’, ‘$data[12]’ , ‘$data[13]’, ‘$data[14]’, ‘$data[15]’, ‘$data[16]’ )”;

echo $data[0].” ”;

mysql_query($sql_insert);
}
fclose($file_handle);
@mysql_close($con);
?>
[/php]

Runmeter furnishes a unique and user specific download URL. Replace the URL (line #8) in import_csv.php file code above with yours. If not already familiar with the Runmeter app, review the screenshots from the app below. Start with the Routes Tab then touch Email/Export All. Next screen choose CSV File URL. (Runmeter sends your event data from your device to update your file CSV File on their server) In the third screen you are provided the discreet link which you will need for access. This URL does not normally change so you will not necessarily need to alter your php configuration above but note that current Runmeter app behavior does require you to refresh their data by doing this little procedure each time.

You can run import_csv.php as needed manually from a web browser. I created a cron job that will do this automatically. (once daily so as not to hammer the Runmeter server)

* * * * * wget -q http://www.strombotne.com/import_cvs.php > /dev/null 2>&1

With the event data in place. It is a simple task of writing some queries that will display the information in meaningful way. For example, this snippet will count the number of days run within the last 30.
[php]
<?php
include (“../dbinfo.inc.php”);
/********************************/
/* Code from http://strombotne.com/
/* The query in this file is for PHP5 only
/* If you use this code, find a flaw, enhance or otherwise improve it then please let me know
/* via comment or link.
/********************************/
$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(‘Could not connect: ‘ . mysql_error());

$dataArray=array();

//get data from database
$sql=”SELECT COUNT(*) AS Last30Days FROM $databasetable WHERE Activity = ‘run’ AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= Start_Time”;
$result = mysql_query($sql) or die(‘Query failed: ‘ . mysql_error());
$event_count=mysql_fetch_object($result);
$event_count=$event_count->Last30Days;

// days run will be $count_events_data from 30 == days off
$days_off = 30 – $event_count;

@mysql_close($con);
?>
[/php]

Google has an API for charting that neatly displays the query output.
[php]

[/php]

And the output looks like this:

There are additional queries and charts in the zip file, along with the complete code for this project for download here. Please share a link to YOUR Runmeter Statistics display in the comments section.

blog.Strombotne

Strombotne is an old Norwegian Family Name

Leave a Reply