Nowadays we can track GPS position with several devices as smartphones, wearables and handheld devices, but how can we store massive GPS positions on a effective way for later postprocessing, analysis and comparison with another device information. We have created a whole procedure enable the a GPS module as a serial device on a Raspberry Pi, then read the GPS information and sent it to a Postgresql database. The tutorial have several steps from the software installation, scripting to monitoring stored data.

Video

GPS connection

This the wiring for the UBlox Neo 6M:

VCC 3.3V (Pin 1)
RX TXD/GPIO 14 (Pin 8)
TX RXD/GPIO 15 (Pin 10)
GND Ground (Pin 6)

Check the Raspberry Pi pinout here:

Steps

On a session with the Raspberry:

Connect to wifi

Enable ssh on Preferences/Rasbberry Pi Configuration/Interfaces/SSH

Check the internal IP on terminal:

ifconfig

Conect through Putty (in Windows):

Host Name: pi@192.168.1.XX Port:22

Enable UART:

On prompt: sudo raspi-config

On 5. Interfacing Options / P6 Serial:

Would you like a login ... ? = No

Would you like the serial ... ? = Yes

Finish and Reboot

Check if gps send info to the serial:

cat /dev/serial0

Install postgres:

sudo apt-get update

sudo apt install postgresql

Check if database is running:

sudo service postgresql status

Create default user and table:

sudo -u postgres createuser --superuser pi

Create user:

postgres@raspberrypi:~$ createuser --interactive -P

Enter name of role to add: hatari

Enter password for new role: labs

Enter it again: labs

Shall the new role be a superuser? (y/n) y

Create table:

createdb -O hatari gpsposition

Check databases and create table:

psql gpsposition -U hatari -h localhost

\c gpsposition

CREATE TABLE datecoords (

id serial primary key,

easting numeric,

northing numeric,

elevation numeric,

date timestamp

);

\d datecoords

Install sqlalchemy, psycopg2 and pynmea:

pip3 install sqlalchemy

pip3 install psycopg2

pip3 install pynmea2

Create a directory for scripts:

mkdir gpsScripts

cd gpsScripts

Create a script to read and store gps data:

nano gpsPosition.py

Run script (for 30 secs):

python3 gpsPosition.py

Crtl+Z

Check if records are located on the database:

psql gpsposition -U hatari -h localhost

SELECT * FROM datecoords;

Run script on the background:

chmod +x gpsPosition.py

nohup /home/pi/gpsScripts/gpsPosition.py > output.log &

Check if the script is running:

ps ax | grep gpsPosition.py

You can check the database as well:

date

psql gpsposition -U hatari -h localhost

select * from datecoords order by date desc limit 10;

Scripts

This is the Python script that reads the serial information, parse the NMEA codes and store the data in the database.

#!/usr/bin/env python3
import io, time, os, serial
import pynmea2
from datetime import datetime,date, timedelta
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, Float, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

db_string = "postgres://hatari:labs@localhost:5432/gpsposition"
db = create_engine(db_string)
base = declarative_base()
Session = sessionmaker(db)
session = Session()

class Coords(base):
    __tablename__ = 'datecoords'
    id = Column(Integer,
    primary_key=True)
    easting = Column(Float)
    northing = Column(Float)
    elevation = Column(Float)
    date = Column(Date)

ser = serial.Serial('/dev/serial0', 9600, timeout=5.0)
sio = io.TextIOWrapper(io.BufferedRWPair(ser, ser))

while True:
    try:
        line = sio.readline()
        if line[:6]=="$GPGGA":
            msg = pynmea2.parse(line)
            print(msg.latitude,msg.longitude,msg.altitude)
            gpsTime = datetime.combine(date.today(), msg.timestamp)
            gpsRead = Coords(easting=msg.latitude, northing=msg.longitude, elevation=msg.altitude,date= gpsTime + timedelta(hours=1))
            session.add(gpsRead)
            session.commit()
    except serial.SerialException as e:
        print('Device error: {}'.format(e))
        break
    except pynmea2.ParseError as e:
        print('Parse error: {}'.format(e))
        continue