PostgreSQL perl script and GPS protocol question (Was: [Openstreetmap] howto)

[Petter Reinholdtsen]

To avoid having to investigate how to get the Java program to store
points into a database to work on my woody installation (kaffe didn't
seem up for the job), I rewrote the collecting task in Perl.  This is
just a quick and dirty hack, but it seem to work.  It is a duplication
of traceSQL.java, but with less error handling at the moment.  I had
to change the database definition as timestamp is a type in
PostgreSQL, and the mysql geometry type seem to be called point in
PostgreSQL.  Not sure if there are better and more standardized type
names available.

I believe it is important that the collecting phrase have as few
dependencies as possible, to make it very easy to get started.  Perl
is installed on all Debian machines, and the DBI interfaces are
available for all suites using apt-get.  As opposed to a working Java
and database connectors.

Regarding the protocol.  What about using the RMC command to get a
stream of data instead of the APS command every second?  At least my
GPS give more info when using RMC:

  RMC
  GPSD,R=1,M=1
  $GPGGA,074953.000,5957.0311,N,01047.0321,E,0,00,,,M,,M,,*40
  $GPGSA,A,1,,,,,,,,,,,,,,,*1E
  $GPGSV,1,1,04,26,09,274,,21,07,344,,17,31,253,,20,36,134,*77
  $GPRMC,074953.000,V,5957.0311,N,01047.0321,E,,,071004,,*15
  $GPGGA,074954.000,5957.0311,N,01047.0321,E,0,00,,,M,,M,,*47
  $GPGSA,A,1,,,,,,,,,,,,,,,*1E
  $GPGSV,1,1,04,26,09,274,,21,07,344,,17,31,253,,20,36,134,*77
  $GPRMC,074954.000,V,5957.0311,N,01047.0321,E,,,071004,,*12
  $GPGGA,074955.000,5957.0311,N,01047.0321,E,0,00,,,M,,M,,*46
  $GPGSA,A,1,,,,,,,,,,,,,,,*1E
  $GPGSV,1,1,04,26,09,274,,21,07,344,,17,31,253,,20,36,134,*77
  $GPRMC,074955.000,V,5957.0311,N,01047.0321,E,,,071004,,*13

(This is from a session where it lacked lock on the position.  Was
testing it inside the house.)

#!/usr/bin/perl
#
# Author: Petter Reinholdtsen
# Date:   2004-10-07
#
# Connect to gpsd, and store coordinate info into an SQL database.

use strict;
use warnings;
use DBI;
use Socket;

my $debug = 0;

my $gpsdhost = "localhost";
my $gpsdport = "2947";

my $period = 1; # How many seconds between each position check?

# Connect to database

my $dbname  = "pere";
my $dbuser  = "" ; # "pere";
my $dbpass  = "" ; # "pere";
my $dbtable = "tempPoints";

my $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $dbuser, $dbpass,
		       {'AutoCommit' => 1}) ||
    die "Unable to connect to database";

# Connect to gpsd

my $socket = tcp_connect($gpsdhost, $gpsdport);
# Turn off buffering on $socket
my $old_fh = select($socket);
$| = 1;
select($old_fh);

# Turn off buffering on stdout
$| = 1;

while (1) {
    print $socket "aps\n";
    # flush socket
    my $line = <$socket>;
    $line =~ s/\r\n//;
    print "\nL: '$line'\n" if $debug;
    my ($x, $y, $altitude, $status);
    my @token = split(/,/, $line);
    for (@token) {
	if (m/^P=(\S+) (\S+)/) {
	    $x = $1;
	    $y = $2;
	}
	$altitude = $1 if (m/^A=(\S+)/);
	$status = $1 if (m/^S=(\d+)/);
    }
    if (1 == $status) { # Satellites in view

	# This SQL is PostgreSQL specific
	my $sql = "INSERT INTO $dbtable (g, altitude) VALUES ".
	    "('($x, $y)'::point, '$altitude');";

	$dbh->do($sql) || print "S: '$sql'\n";
	print ".";
    } else {
	print "_";
    }
    sleep $period;
}

sub tcp_connect {
    my ($iaddr, $paddr, $proto);

    if ($gpsdport =~ /\D/) { $gpsdport = getservbyname($gpsdport, 'tcp') }
    die "No port" unless $gpsdport;
    $iaddr   = inet_aton($gpsdhost)             || die "no host: $gpsdhost";
    $paddr   = sockaddr_in($gpsdport, $iaddr);
    $proto   = getprotobyname('tcp');
    socket(SOCK, PF_INET, SOCK_STREAM, $proto)  || die "socket: $!";
    connect(SOCK, $paddr)                       || die "connect: $!";
    return *SOCK;
}

sub createTable {
    my $dbh = shift;
    # Postgresql table
    $dbh->do("CREATE TABLE $dbtable (g point, altitude float4,".
	     "stamp datetime  default 'now'::datetime);");
}