My results from this past summer

**longfeltwant** · 09-04-2009, 11:35 PM

This summer my partner and I went on a road trip all around North America. We started in Juneau, Alaska, went as far south as Carlsbad, New Mexico, drove as far east as Maine and Nova Scotia, then looped back around to the destination in Wisconsin. It was an 11,000-mile trip during which I kept a blog (see rinarledge.com if you like).

I used obdgpslogger on this trip. I had a very high rate of success, mapping most of my trip. In the end I collected a little over 60 megabytes of raw data (obdlogger.db). On my little Acer Aspire, calculating the KML files on a couple dozen "trips" (a thousand miles or so) would take overnite. I just kicked off the process on the whole 60 megabytes, which represents a little over 500 "trips", so I expect it to take about a week to calculate.

The data I collected was interesting. It was cool to see what the data looked like after my engine overheated. It was cool to see that most of the "trips" I took were very short (seconds or minutes long) and very few were long (the longest single trip was short of four hours).

I would offer to post the data but it's too much, I imagine.

**chunkyks** · 09-05-2009, 01:23 PM

Wow! I'd be interested in seeing the raw data, absolutely! It'd be very useful to me for testing stuff in future :-)

How big are the google earth files?

In future, I'd hope to figure out how to make a sixty meg file not take so long to process; seems like 60m should be well within the realm of doable-in-reasonable-time :-)

Gary (-;

**longfeltwant** · 09-05-2009, 05:17 PM

How could I get the file to you?

When I would process about 1000 miles of data I would get two- or three-meg KML files, so I'm guessing the final product will be thirty megs or so. The process has been running for about 18 hours now, and is on trip #39. Of course, the trips aren't all the same length. Also keep in mind that the process is running on an Acer Aspire, which is a low-powered netbook.

You can see some of the smaller KML files from the trip at http://sites.google.com/a/rinard.us/family/Home/

**chunkyks** · 09-05-2009, 06:58 PM

Hrm. Good question.

I've used this in the past: http://www.yousendit.com/ If that works, great! If not, erm, I'll figure something else out. Probably put up an ftp server at home or something.

Gary (-;

**chunkyks** · 09-05-2009, 06:58 PM

Oh! To try that, you need my email: chunky@icculus.org

Thanks!
Gary (-;

**chunkyks** · 09-06-2009, 06:30 PM

Good grief, obd2kml is sloooowwwwwwww. Must fix.

Gary (-;

**chunkyks** · 09-06-2009, 10:37 PM

Hahahahahaha. Go me, amateur day on databases at my house!

To make performance on your 60M database not suck [and convert it to an 80M database at the time]:

Code:

CREATE INDEX IDX_GPSTIME ON gps (time);
CREATE INDEX IDX_OBDTIME ON obd (time);

Go me. :-(

Gary (-;

**chunkyks** · 09-06-2009, 10:40 PM

Code:

kibbles-2:build chunky$ ls -l longfeltwant.db 
-rw-------  1 chunky  501  86970368 Sep  6 19:22 longfeltwant.db

kibbles-2:build chunky$ sqlite3 ./longfeltwant.db '.schema'
CREATE TABLE gps (lat REAL, lon REAL, alt REAL, time REAL);
CREATE TABLE obd (fuelstatus REAL,load REAL,temp REAL,trim1short REAL,trim1long REAL,trim2short REAL,trim2long REAL,map REAL,rpm REAL,vss REAL,iat REAL,throttlepos REAL,time REAL);
CREATE TABLE trip (tripid INTEGER PRIMARY KEY, start REAL, end REAL DEFAULT -1);
CREATE INDEX GPS_TIME ON gps(time);
CREATE INDEX OBD_TIME ON obd(time);

kibbles-2:build chunky$ time ../bin/obd2kml -d ./longfeltwant.db

<snippage>

real	0m11.565s
user	0m10.509s
sys	0m0.634s
kibbles-2:build chunky$

11 seconds down from the previous hours-and-hours-and-hours. Sometimes I feel so dumb.

Gary (-;

**chunkyks** · 09-10-2009, 10:24 PM

Longfeltwant graciously allowed me to post his data and hock it as a good example of obdgpslogger.

Thus: http://icculus.org/obdgpslogger/examples/longfeltwant/

A few minor statistics:

Code:

$ du -sh longfeltwant.*
84M     longfeltwant.db
33M     longfeltwant.kml
52M     longfeltwant.csv

$ wc -l longfeltwant.csv
269811 longfeltwant.csv

$ sqlite3 ./longfeltwant.db 'SELECT COUNT(*) FROM trip'
505

Gary (-;

**chunkyks** · 02-20-2010, 02:59 PM

I've been making some small db schema changes recently. Those improve the runtime of obd2kml a fair bit against this database. obdlogrepair also now run ANALYZE against the database, which makes little difference in most cases, but against this particular database it changes the runtime of obd2kml from ~40 minutes to ~10 seconds.

Longfeltwant, if you're reading this; grab the latest SVN, and run obdlogrepair against your database. All performance woes just kinda evaporate.

Gary (-;