#!/usr/bin/env perl # # Tim's script to populate a table in PostgreSQL with GPS co-ordinates # Copyright (C) Tim Haynes # Licensed under the terms of the GPL: # http://www.gnu.org/copyleft/gpl.html # # Run this to create the table: # create table whereabouts (tstmp timestamp, la float, lo float, alt float); # require DBI; # Supply a regexp to filter lines as the first parameter: $filter=shift || ".*"; # Supply a default altitude as the second parameter: $alt=shift || 1.5; $dbh=DBI->connect("dbi:Pg:dbname=mydb;host=myhostname", "me", "mypassword") or die "Error connecting to postgresql"; $str=$dbh->prepare("insert into whereabouts values (?, ?, ?, ?);") or die "Can't prepare statement"; while (<>) { chomp; next unless /^T/oig; next unless /$filter/; my @parts=split /[^\d.-]/; my $when="$parts[4]-$parts[2]-$parts[3] $parts[5]:$parts[6]:$parts[7]"; my $lat=(abs($parts[8])+$parts[9]/60+$parts[10]/3600)*sgn($parts[8]); my $long=(abs($parts[12])+$parts[13]/60+$parts[14]/3600)*sgn($parts[12]); print "$when, $long,$lat,$alt\n"; $str->execute($when, $lat, $long, $alt) or warn "Failed to insert.."; } $dbh->disconnect; ################ sub sgn { my $n=shift; return ($n==0)?0 : $n/abs($n); }