gis/openstreetmap/pcode-location-update

   1 #!/usr/bin/perl
   2 #
   3 # Create postgis database from the postal code
   4 #
   5 # Author:  Petter Reinholdtsen <pere@hungry.com>
   6 # Date:    2005-05-14
   7 # Lisence: GNU Public Lisence
   8
   9 my $dbtable = "postal_codes";
  10
  11 sub create_table {
  12
  13     $sql = <<EOF;
  14 CREATE TABLE $dbtable (
  15         postal_code     text NOT NULL PRIMARY KEY,
  16         postal_location text
  17 );
  18 SELECT AddGeometryColumn('','$dbtable','center_point', '-1','POINT',   2);
  19 SELECT AddGeometryColumn('','$dbtable','area',         '-1','POLYGON', 2);
  20 EOF
  21     return $sql;
  22 }
  23
  24 sub insert_location {
  25     my ($postal_code, $lat, $lon) = @_;
  26     $sql = <<EOF;
  27 INSERT INTO $dbtable (postal_code, center_point)
  28   VALUES ('$postal_code', GeometryFromText('POINT($lon $lat)', -1));
  29 EOF
  30     return $sql;
  31 }
  32
  33 sub drop_table {
  34     $sql = <<EOF;
  35 SELECT DropGeometryColumn('','$dbtable','center_point');
  36 SELECT DropGeometryColumn('','$dbtable','area');
  37 DROP TABLE $dbtable;
  38 EOF
  39     return $sql;
  40 }
  41
  42 sub load_sql_table {
  43     print create_table();
  44     open(FILE, "<pcode-location.txt") or
  45         die "Unable to read pcode-location.txt";
  46     while (<FILE>) {
  47         chomp;
  48         s/#.*//;
  49         next if /^\s*$/;
  50         my ($postal_code, $lat, $lon) = split;
  51         print insert_location($postal_code, $lat, $lon);
  52     }
  53     close(FILE);
  54 }
  55
  56 sub save_sql_table {
  57     print <<EOF;
  58 #
  59 # Location of postal area codes in Norway.  The values are aproximate.
  60 #
  61 # Author: Petter Reinholdsten <pere@hungry.com>
  62 # Date:   2005-02-13
  63 #
  64 # Updates and corrections are most welcome.  The accurasy is unknown,
  65 # as the positions are based on a random set of addresses with the
  66 # given zip code.
  67 #
  68 # zip latitude longitude
  69 EOF
  70     d
  71
  72 }
  73
  74
  75 print drop_table();
  76 load_sql_table();