Generated.

[homepage.git] / gis / openstreetmap / pcode-location-update

diff --git a/gis/openstreetmap/pcode-location-update b/gis/openstreetmap/pcode-location-update
index d260301f4674fdcc284b12225d12c4213f8f5dd3..952b0caacd038632180bd85c2e9c32da4547739c 100755 (executable)
--- 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();
+}