Non-file based database logging?

**cr08** · 02-26-2012, 10:34 AM

Hi there. Hoping I can gain a little bit of help with something I have been trying to wrap my head around. Long story made short I have a likely ~2500 mile (one way) road trip coming up in the next couple months and my nerd side is going nuts and wanting to set up a live tracking page on my web server with GPS data, OBD data, and pretty graphs and mapping however I can manage to get it going. Hardware setup currently planned is going to be my primary notebook, a bluetooth OBD adapter which I already have and extensively tested with approval from me, an old Nextel phone which will function as the GPS source (outputs standard NMEA over the USB port and stays charged at the same time, not to mention fairly accurate and quick locks), either GPSGate or Xport or both to split the signal for multiple applications as I need them, and most importantly a Mifi for 3G data.

My original thought was quite simple: Log to a MySQL db up on the server and parse it appropriately server side in PHP. All this I know well on my own short of actually getting the data up there. OBDII GPS Logger is the closest thing I have found to what I am wanting to accomplish but is missing that last key part of getting the data the right way so it isn't overly convoluted or difficult to get live data sent over the net and parsed elsewhere. Considering the laptop in question is running Windows, the plan would be to use Cygwin obviously to run it given it doesn't pose further problems. Otherwise could probably run a small temporary VM just for OBD GPS Logger. But this is preferably last resort. As it is running through my head now, software overload is inevitable. lol.

Any further insight and help on this is greatly appreciated.

**chunkyks** · 03-04-2012, 10:11 PM

Well... *live* might be harder than you envisage. That being said...

I'd crack open obdgpslogger's code, and set journal_mode to WAL [so that concurrent writing and reading doesn't cause heartache]:
http://www.sqlite.org/pragma.html
http://www.sqlite.org/draft/wal.html
[just use sqlite3_exec shortly after the db successfully opens].
I'm not sure, but you may need to drop in a version of sqlite3 that's 3.7 or later [can't remember what's included in the source, offhand]. If your system has sqlite3 3.7 or later, there's a cmake compilation flag to use the system sqlite3 instaead of the one included

Then I'd use obd2csv occasionally [or some other custom-written tool for the job] to dump the database to a file that I could upload to the server. One simple script could do that.

Or, I might insert code into obdgpslogger that uses the backup api to make an entire copy of the current database occasionally while running, and have a script copying that file up to a server where it could be postprocessed.
http://www.sqlite.org/backup.html

Basically, if it were me, I'd use obdgpslogger as-is [or almost as-is], and "occasionally" [10 seconds/30 seconds/5 minutes/whatever] take a copy of the existing database via either a script or a combination script + modification to obdgpslogger, and do my own processing on the server.

Gary (-;

**cr08** · 03-14-2012, 04:48 AM

Sorry for the delayed response. Work has not allowed me much computer time lately. o.x

With that said, your post gave me a little bit of thought prodding and 'live' live is definitely overkill for what I'd actually need. Not to mention in my scenarios, OBD update timing for numerous sensors at once is once every 5 seconds or thereabouts, possibly worse. So a 5 or even 10 second update interval would be doable or go even longer. 30 seconds to 1m wouldn't be completely out of the question as far as an upload interval. And as you noted: Best plan of attack may be to simply run obd2csv at these update intervals, modify it slightly to include some curl to upload the csv data at conversion time and a PHP script server-side will place it in the db. The only thing running through my head now is figuring out how, at the time obd2csv runs, to get the initial OBD log file wiped to a clean slate (or somehow be able to keep two copies, one a full log from the start of the trip and a second that will be refreshed at every update interval) since I don't want to be converting and uploading an ever growing log file, just what is new since the last upload.

Are there any useful OBDSim data files around for toying with this idea? Unfortunately until this trip I don't have many suitable occasions to do a live test.

**chunkyks** · 03-15-2012, 11:01 AM

I have two datafiles I use on regular occasions with obdsim. ces2010 is me driving to vegas, and striple is me shortly after I got my new bike puttering through the hills above Santa Monica
http://icculus.org/obdgpslogger/examples/striple/
http://icculus.org/obdgpslogger/examples/ces2010/

Hope that helps,
Gary (-;