xonsolidated.pl

#!/usr/bin/perl -w

# xonsolidated.pl - Copyright 2011 by Jan Fredrik Leversund <kluzz@radical.org> (Twitter: @KluZz)

# This script reads the CellLocation data from a consolidated.db file (extracted from your
# iPhone 4 backup or whatever) and spits out a KML file suitable for loading into
# Google Earth or similar.

# NB! This script is provided AS-IS, and I make no claims that it is accurate, safe or
# otherwise useful to anyone but myself.

# This script requires the DBI and DBD::SQLite modules.

use DBI;

die "Usage: $0 <db file>\n" unless scalar @ARGV == 1 and -f $ARGV[0];
my $dbfile = $ARGV[0];
my $dbh = DBI->connect("dbi:SQLite:dbname=" . $dbfile, "", "");
die "Unable to open database file $dbfile: $!\n" unless $dbh;

my $data  = $dbh->selectall_arrayref("select rowid, Timestamp, Longitude, Latitude from CellLocation where Confidence > 0 order by rowid");

die "No data found! Possibly not an SQLite file?\n" unless $data;

# Parse stuff into time segments
my %segments = ();
foreach $p (@{$data})
{
    push @{$segments{$p->[1]}}, {lon => $p->[2], lat => $p->[3]};
}

# Convert the segment hash into an array (it'll make sense later, honestly)
my @segments = ();
foreach $s (sort keys %segments)
{
    push @segments, [$s, $segments{$s}];
}

# Start outputting the XML file
open KML, ">$dbfile.kml" or die "Unable to create $dbfile.kml file: $!\n";

print KML "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<kml xmlns=\"http://www.opengis.net/kml/2.2\">\n<Document>\n";
print KML "<name>$dbfile.kml</name>\n";

# Define line and icon styles for each segment
foreach $s (@segments)
{
    my ($ts, $points) = @{$s};
    my $col = sprintf("ff%06x", int(rand(0x1000000)));
    print KML "<Style id=\"_$ts\">\n<LineStyle>\n<color>$col</color>\n<width>2</width>\n</LineStyle>\n";
    print KML "<IconStyle>\n<color>$col</color>\n</IconStyle>\n</Style>\n";
}

for ($i = 0; $i < scalar @segments; $i++)
{
    my ($ts, $points) = @{$segments[$i]};

    # Include the first point of the next segment, for continuity.
    if (defined $segments[$i + 1])
    {
        push @{$points}, $segments[$i + 1][1][0];
    }

    my $newdate = scalar localtime ($ts + 978307200);

    # Define the first coordinate of the segment as a point placemark
    print KML "<Placemark>\n<name>$newdate</name>\n<description></description>\n<styleUrl>_$ts</styleUrl>\n";
    printf KML "<Point>\n<coordinates>%s,%s</coordinates>\n</Point>\n</Placemark>\n", $points->[0]{lon}, $points->[0]{lat};

    # Define the entire segment as a line string
    print KML "<Placemark>\n<name>$newdate</name>\n<description></description>\n<styleUrl>_$ts</styleUrl>\n";
    print KML "<LineString>\n<extrude>1</extrude>\n<tessellate>1</tessellate>\n<altitudeMode>clampedToGround</altitudeMode>\n<coordinates>\n";

    foreach $c (@{$points})
    {
        print KML $c->{lon} . "," . $c->{lat} . "\n";
    }

    print KML "</coordinates>\n</LineString>\n</Placemark>\n";
}

print KML "</Document>\n</kml>\n";

# Finito.