#!/usr/bin/perl
#
# Create postgis database from the postal code 
#
# Author:  Petter Reinholdtsen <pere@hungry.com>
# Date:    2005-05-14
# Lisence: GNU Public Lisence

use DBI;

my $dbtable = "postal_codes";

sub create_table {

    $sql = <<EOF;
CREATE TABLE $dbtable (
	postal_code     text NOT NULL PRIMARY KEY,
	postal_location text
);
SELECT AddGeometryColumn('','$dbtable','center_point', '-1','POINT',   2);
SELECT AddGeometryColumn('','$dbtable','area',         '-1','POLYGON', 2);
EOF
    return $sql;
}

sub insert_location {
    my ($postal_code, $lat, $lon, $postal_location) = @_;
    $sql = <<EOF;
INSERT INTO $dbtable (postal_code, center_point)
  VALUES ('$postal_code', GeometryFromText('POINT($lon $lat)', -1));
EOF
    return $sql;
}

sub drop_table {
    $sql = <<EOF;
SELECT DropGeometryColumn('','$dbtable','center_point');
SELECT DropGeometryColumn('','$dbtable','area');
DROP TABLE $dbtable;
EOF
    return $sql;
}

sub load_sql_table {
    print create_table();
    open(FILE, "<pcode-location.txt") or
	die "Unable to read pcode-location.txt";
    while (<FILE>) {
	chomp;
	s/#.*//;
	next if /^\s*$/;
	my ($postal_code, $lat, $lon, $postal_location) = split;
	print insert_location($postal_code, $lat, $lon, $postal_location);
    }
    close(FILE);
}

sub save_sql_table {
    print <<EOF;
#
# Location of postal area codes in Norway.  The values are aproximate.
#
# Author: Petter Reinholdsten <pere\@hungry.com>
# Date:   2005-02-13
#
# Updates and corrections are most welcome.  The accurasy is unknown,
# as the positions are based on a random set of addresses with the
# given zip code.
#
# zip latitude longitude postoffice area
EOF
    $dbname = "pere";
    $dbhost = "localhost";
    $dbuser = "pere";

    $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $dbuser) # ;host=$dbhost
	|| die "Unable to connect to DB: $DBI::errstr";
    $dbh->do("SET CLIENT_ENCODING TO 'ISO_8859_1'");

    my $sql = <<EOF;
SELECT postal_code, postal_location, center_point, area from $dbtable 
  ORDER BY postal_code
EOF

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while ( my @row = $sth->fetchrow_array() ) {
	my ($postal_code, $postal_location, $center_point, $area) = @row;
	my ($lon, $lat) =
	    $center_point =~ m/SRID=[-+\d]+;POINT\(([\d.]+) ([\d.]+)\)/;
	my $line = "$postal_code\t$lat\t$lon\t$postal_location\t$area\n";
	$line =~ s/\t+/\t/;
	$line =~ s/\t*$//;
	print $line;
    }
    $sth->finish();
    $dbh->disconnect();
}

if (1) {
    save_sql_table();
} else {
    print drop_table();
    load_sql_table();
}