[Openstreetmap] london free maps...

[Petter Reinholdtsen]

[Petter Reinholdtsen]
> I do not have that much yet, because I do not want to collect lots
> of points if the information collected is lacking accuracy info.

Hm, I just discovered that my database was incorrect.  All points are
timestamped using the time when the table was created. :(

I didn't want "'now'::timestamp", I wanted "now()". :(

Oh, well.  I'll collect more points. :)

> Converting my database to GPX should be fairly easy, so I'll try to
> hack something together in the near future.  Is there a good
> reference explaining GPX?  The doc available from
> <URL:http://www.topografix.com/GPX/1/1/> isn't too good.

I did a first try, ending up with this script.  Anyone know if it is
correct?  I'm not too happy about the Date::Manip dependency.  It
might be possible to get PostgreSQL to give me the date format I want.
Not sure about that.


#!/usr/bin/perl
#
# Author:  Petter Reinholdtsen
# Date:    2004-11-12
# License: GPL
#
# Fetch coordinate info from SQL database, and output as GPX

use strict;
use warnings;
use DBI;
use Getopt::Std;
use Date::Manip;

use vars qw($debug %opts);

getopt("d:", \%opts);

$debug = $opts{'d'} || 0;

# 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";

# Turn off buffering on stdout
$| = 1;

print <<EOF;
<gpx>
 <metadata>
 <name>name</name>
 <author>author</author>
 <copyright>copyright</copyright>
 </metadata>
 <trk>
  <name>$dbtable database dump</name>
  <number>1</number>
  <trkseg>
EOF

my $sql = "SELECT g, altitude, stamp from $dbtable order by stamp, oid";
my $sth = $dbh->prepare($sql) || die "Bad SQL '$sql'";
$sth->execute();
my $lastsecs = 0;
while (my ($g, $ele, $stamp) = $sth->fetchrow_array()) {
    # Extract long/lat from $g
    my ($lat, $lon) = $g =~ m/\((.+),(.+)\)/;

    my $date = &ParseDateString($stamp);
    my $secs = &UnixDate($date,"%s");
    if ($lastsecs > 0 && $secs - $lastsecs > 2) { # Lost signal, make new track
	print <<EOF;
  </trkseg>
  <trkseg>
EOF
    }
    $lastsecs = $secs;

    # Convert $stamp to ISO date format.  Example: '2004-11-11T00:20:06Z'
    $date =~ s/^(....)(..)(..)(.+)/$1-$2-$3T$4Z/;
    print <<EOF;
    <trkpt lat="$lat" lon="$lon">
      <ele>$ele</ele>
      <time>$date</time>
    </trkpt>
EOF
}
print <<EOF;
  </trkseg>
 </trk>
</gpx>
EOF

$dbh->disconnect;