#!/usr/bin/perl
use XML::Simple;
use DBI;
use LWP::Simple;
use File::Temp qw/ :mktemp  /;

my $dbh = DBI->connect('dbi:SQLite:poi.db', '', '', { AutoCommit => 1 } );
# this would create the db structure..
$dbh->do(q{ 
   create table poi (poi_id integer PRIMARY KEY, lat real, lon real, 
   label text, desc text, cat_id integer)});
$dbh->do(q{ 
   create table category (cat_id integer PRIMARY KEY, label text, 
   desc text, enabled integer)});
$poi = $dbh->prepare(q{
   INSERT into poi (lat, lon, label, desc, cat_id) values (?, ?, ?, ?, ?) });
$categ = $dbh->prepare(q{
   INSERT into category (label, desc, enabled) values (?, ?, 1) });
$catp = $dbh->prepare(q{
   SELECT cat_id FROM category WHERE label=? });

$xml = new XML::Simple;
# read XML file
$kml = $xml->XMLin($ARGV[0], 
  GroupTags => { Point => "coordinates", IconStyle => "Icon", Icon => "href", NetworkLink => "Url", Url => "href" },
  KeyAttr => { IconStyle => "href", Placemark => "name", Style => "id" } );

if (defined($kml->{Document}->{NetworkLink}))
{
    print "downloading ". $kml->{Document}->{NetworkLink}->{href} ."\n";
    $kmltmp = mktemp("/tmp/kmlXXXXXXXX");
    mirror($kml->{Document}->{NetworkLink}->{href}, $kmltmp);
    $kml = $xml->XMLin($kmltmp, 
      GroupTags => { Point => "coordinates", IconStyle => "Icon", Icon => "href", NetworkLink => "Url", Url => "href" },
      KeyAttr => { IconStyle => "href", Placemark => "name", Style => "id" } );
}

while (($label, $data) = each %{$kml->{Document}->{Placemark}})
{
    ($lon, $lat, $alt) = split(",", $data->{Point});
    $desc = $data->{description};
    $style = substr($data->{styleUrl},1);
    $icon = $kml->{Document}->{Style}{$style}->{IconStyle};
    $category = substr($icon, rindex($icon, "/")+1, -4);
    $catp->execute($category);
    $cat = $catp->fetchrow_array;
     if (!defined($cat)) 
    {
       $categ->execute($category, undef);
       $cat = $dbh->func('last_insert_rowid');
       mirror($icon, "poi/$category.jpg");
    }
    $catp->finish;
    print "$lat:$lon - $category ($cat) - $label\n";
    $poi->execute($lat,$lon,$label,$desc,$cat);
}

$dbh->disconnect;