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

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) = @_;
    $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) = split;
	print insert_location($postal_code, $lat, $lon);
    }
    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
EOF
    d
    
}


print drop_table();
load_sql_table();