Tuesday, July 9, 2013

Logging GPS Data to a MySQL Database Using the Adafruit Ultimate GPS Breakout

The Adafruit Ultimate GPS Breakout (ver. 3) works nicely with the Raspberry Pi.  This quick example shows how to log GPS data from this GPS module to a MySQL database table.

Connections


Adafruit GPS Raspberry Pi
VIN          3V3 (P1-01)
GND          Ground (P1-06)
RX           GPIO 14 (TXD) (P1-08)
TX           GPIO 15 (RXD) (P1-10)

The RXD and TXD pins on the Pi connect to the Linux serial device ttyAMA0. By default, though, this device is used for console output. Follow the 3 steps on the Adafruit Learning System page, "Freeing UART on the Pi."


Software


In addition to installing MySQL on the Raspberry Pi, you will need to have the python-serial and python-mysqldb packages installed (using apt-get).

The structure of the gps database table is very simple. Here is the output from MySQL's describe gps -

+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| n_lat     | float    | NO   |     | NULL    |       |
| w_long    | float    | NO   |     | NULL    |       |
| date_time | datetime | NO   | PRI | NULL    |       |
+-----------+----------+------+-----+---------+-------+


Python Code


import serial
import time
import datetime
import os
import sys
import MySQLdb as mdb

try:
    serial = serial.Serial("/dev/ttyAMA0", baudrate=9600)
    time.sleep(1)
    con = mdb.connect('localhost', 'YourDBUser', 'YourDBPasswd', 'YourDB');
    cur = con.cursor()

except:
    print "Error opening serial port."
    sys.exit(1)

resp = ""

try:
    while True:
        while (serial.inWaiting() > 0):
            resp += serial.read()
            if "\r\n" in resp:
                if "$GPRMC" in resp:
                    data = resp.split(',')
                        if data[2] == 'A':
                            dom = data[9][0:2]
                            month = data[9][2:4]
                            year = int(data[9][4:6]) + 2000
                            date = "%d-%s-%s" % (year, month, dom)
                            hour = data[1][0:2]
                            min = data[1][2:4]
                            sec = data[1][4:6]
                            t = "%s:%s:%s" % (hour, min, sec)
                            dateTime = "%s %s" % (date, t)
                            north = data[3]
                            west = data[5]
                            sql = "insert into gps(n_lat, w_long, date_time) values(%s, %s, %s)" % (north, west, t)
                            print sql
                            cur.execute(sql)
                            print "Rows inserted: %s" % cur.rowcount
                            con.commit()
                            time.sleep(0.5)
                            resp = ""
except:
    print sys.exc_info()[0]

finally:
    if con:
        con.close()
    serial.close()

9 comments:

  1. I do have this error !!!

    File "sql1",line 27
    if data[2] == 'A':
    ^
    IndentationError: unexpected indent

    ReplyDelete
    Replies
    1. as the error indicates, you need to add an indent (tab space) before that statement

      Delete
  2. Replies
    1. With Python the indentation is important. Copying code from the Web may require some reformatting (changing spaces to tabs, perhaps). The error message tells you what the problem is.

      Delete
  3. i have error opening serial port
    i tried with this code and i use raspi 3:
    import serial
    import time
    import datetime
    import os
    import sys

    try:
    serial = serial.Serial("/dev/ttyS0", baudrate=9600)
    time.sleep(1)
    con = mdb.connect('localhost', 'root', 'root', 'gps');
    cur = con.cursor()

    except:
    print "Error opening serial port."
    sys.exit(1)

    ReplyDelete
    Replies
    1. have a look at this thread https://www.raspberrypi.org/forums/viewtopic.php?f=32&t=80857. maybe the serial is in use by another process and you need to flush it before using it

      Delete
    2. hi brad,
      now im trying use the java code..
      and i have a new problem here
      what's wrong with this?

      pi@raspberrypi:/usr/share/java $ sudo javac -cp /usr/share/java/RXTXcomm.jar gps.java
      pi@raspberrypi:/usr/share/java $ java -Djava.library.path=/usr/lib/jni/ -cp /usr/share/java/RXTXcomm.jar:. gps
      Exception in thread "main" java.lang.UnsupportedClassVersionError: gps : Unsupported major.minor version 52.0
      at java.lang.ClassLoader.defineClass1(Native Method)
      at java.lang.ClassLoader.defineClass(ClassLoader.java:803)
      at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
      at java.net.URLClassLoader.defineClass(URLClassLoader.java:449)
      at java.net.URLClassLoader.access$100(URLClassLoader.java:71)
      at java.net.URLClassLoader$1.run(URLClassLoader.java:361)
      at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
      at java.security.AccessController.doPrivileged(Native Method)
      at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
      at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
      at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
      at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
      at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:482)

      thanks for helping

      Delete
  4. I notice you use GPRMC while my understanding was the good data was GPGGA???

    ReplyDelete
    Replies
    1. Do you think MySQLdb can connect to a remote db also?

      Delete