# Date: 2005-05-14
# Lisence: GNU Public Lisence
+use DBI;
+
my $dbtable = "postal_codes";
sub create_table {
}
sub insert_location {
- my ($postal_code, $lat, $lon) = @_;
+ my ($postal_code, $lat, $lon, $postal_location) = @_;
$sql = <<EOF;
INSERT INTO $dbtable (postal_code, center_point)
VALUES ('$postal_code', GeometryFromText('POINT($lon $lat)', -1));
chomp;
s/#.*//;
next if /^\s*$/;
- my ($postal_code, $lat, $lon) = split;
- print insert_location($postal_code, $lat, $lon);
+ my ($postal_code, $lat, $lon, $postal_location) = split;
+ print insert_location($postal_code, $lat, $lon, $postal_location);
}
close(FILE);
}
#
# Location of postal area codes in Norway. The values are aproximate.
#
-# Author: Petter Reinholdsten <pere@hungry.com>
+# 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
+# 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
- d
-
-}
+ 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();
+}
-print drop_table();
-load_sql_table();
+if (1) {
+ save_sql_table();
+} else {
+ print drop_table();
+ load_sql_table();
+}