#!/usr/bin/perl
#
# Author:  Petter Reinholdtsen
# Date:    2007-04-01
# License: GNU General Public License
#
# Laster inn postnummer-info til SQL.

use DBI;
use mydatabase;
use Getopt::Std;

my %opts;
getopts("ef:l:", \%opts);

#from http://epab.posten.no/Norsk/Nedlasting/_Files/tilbud5.txt
my $pcodefile = $opts{'f'} || "pcode-oversikt.txt";

#from http://developer.skolelinux.no/~pere/gis/openstreetmap/pcode-location.txt
my $pcodelocfile = $opts{'l'} || "pcode-location.txt";

my $dbh = dbi_connect();
if ($opts{e}) {
    print_post_code_locations($dbh);
} else {
    load_post_codes($dbh, $pcodefile);
    load_post_code_locations($dbh, $pcodelocfile);
}
dbi_disconnect($dbh);

sub load_post_codes {
    my ($dbh, $pcodefile) = @_;
    open(LIST, "<", "$pcodefile") || die "Unable to read $pcodefile";
    while (<LIST>) {
        chomp;
        s/\r//; # File might have DOS line endings
        s/\#.*//g;
        next if (/^\s*$/);
        # 0001    OSLO    0301    OSLO    K
        my ($postcode, $postname, $loccode, $county, $category) = split(/\t/);
        my ($areanum, $countynum) = $loccode =~ m/(\d\d)(\d\d)/;
        $dbh->do("INSERT INTO postnummer VALUES (?, ?, ?, ?, ?, ?)", undef,
                 $postcode, $postname, $areanum, $countynum,
                 $county, $category) || warn "Unable to insert postcode $postcode";
    }
    close(LIST);
}

sub load_post_code_locations {
    my ($dbh, $pcodelocfile) = @_;
    open(LIST, "<", "$pcodelocfile") || die "Unable to read $pcodelocfile";
    while (<LIST>) {
        chomp;
        s/\#.*//g;
        next if (/^\s*$/);

        # 9990    70.63507        29.720943
        my ($postcode, $lat, $long) = split(/\t+/);
        my $wgs84 = 4326;
        my $sql = "UPDATE postnummer ".
            "SET geom_center = GeomFromText('POINT($long $lat)', $wgs84) ".
            "WHERE postnr = ?";
        $dbh->do($sql, undef, $postcode)
            || die "Failed SQL $sql";
    }
    close(LIST);
}

sub print_post_code_locations {
    my ($dbh) = @_;

    my $sql = "SELECT postnr, geom_center FROM postnummer WHERE  geom_center IS NOT NULL";

}