From: Petter Reinholdtsen <pere@hungry.com>
Date: Sat, 14 May 2005 18:13:14 +0000 (+0000)
Subject: Start on tool to maintain pcode database.
X-Git-Url: http://pere.pagekite.me/gitweb/homepage.git/commitdiff_plain/2fd72f43f97c8cac4d45fe19276a2a2310b16d7c?hp=889e81aacad5f55cece0c23d3456fb57888b622e

Start on tool to maintain pcode database.
---

diff --git a/gis/openstreetmap/pcode-location-update b/gis/openstreetmap/pcode-location-update
new file mode 100755
index 0000000000..d260301f46
--- /dev/null
+++ b/gis/openstreetmap/pcode-location-update
@@ -0,0 +1,76 @@
+#!/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();