From 5c21f8e36c395e051d4eed215454a925bd93ea9f Mon Sep 17 00:00:00 2001
From: Petter Reinholdtsen <pere@hungry.com>
Date: Sun, 15 May 2005 21:46:28 +0000
Subject: [PATCH] Implement export.

---
 gis/openstreetmap/pcode-location-update | 49 ++++++++++++++++++++-----
 1 file changed, 39 insertions(+), 10 deletions(-)

diff --git a/gis/openstreetmap/pcode-location-update b/gis/openstreetmap/pcode-location-update
index d260301f46..952b0caacd 100755
--- a/gis/openstreetmap/pcode-location-update
+++ b/gis/openstreetmap/pcode-location-update
@@ -6,6 +6,8 @@
 # Date:    2005-05-14
 # Lisence: GNU Public Lisence
 
+use DBI;
+
 my $dbtable = "postal_codes";
 
 sub create_table {
@@ -22,7 +24,7 @@ EOF
 }
 
 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));
@@ -47,8 +49,8 @@ sub load_sql_table {
 	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);
 }
@@ -58,19 +60,46 @@ sub save_sql_table {
 #
 # 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();
+}
-- 
2.47.2