gps sql database raspberry pi

Accessing GPS data via SQL database

View your car’s GPS data

The Pi will log GPS data to a local database. All this data is stored locally until it’s synced off the device. You can take the Pi out of your car, bring it home, plug it into an ethernet cable, and SSH into it to view the raw GPS data from the database.

  1. cd /home/pi/OBD2SDK_Python/
  2. You’ll see that the database file is growing slowly over time:
    ls -lh gps.db
  3. sqlite3 gps.db
  4. For help, type “.help” and press Enter.
  5. List the databases:
     .databases
  6. Since SQLite3 doesn’t handle concurrency very well, the db may be locked while the logger is inserting values (default is 10/second) so you might see this:
    sqlite> .databases
    Error: database is locked
  7. If the above problem persists, temporarily disable the Pi’s GPS logging so you can run queries against the database:
    sudo service gpslogger stop

    After you’re done, turn the logging back on:

    sudo service gpslogger start
  8. List  the tables, you should see “gps” listed:
    .tables
  9. View the number of GPS records
    select count(*) from gps;
  10. The output will look like this:
    sqlite> select count(*) from gps;
    31201
  11. View the newest 30 GPS records
    select * from gps order by eventdate desc limit 30;
  12. List the number of records per day
    select count(*) as count, date(eventdate) from gps group by date(eventdate) order by date(eventdate) desc limit 30;
  13. View the [gps] table’s schema
    .schema gps
  14. Exit SQLite3
    .quit

 

Leave a Reply

Your email address will not be published. Required fields are marked *

A Pi powered black box and cloud storage for your car