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 use DBI;
  10
  11 my $dbtable = "postal_codes";
  12
  13 sub create_table {
  14
  15     $sql = <<EOF;
  16 CREATE TABLE $dbtable (
  17         postal_code     text NOT NULL PRIMARY KEY,
  18         postal_location text
  19 );
  20 SELECT AddGeometryColumn('','$dbtable','center_point', '-1','POINT',   2);
  21 SELECT AddGeometryColumn('','$dbtable','area',         '-1','POLYGON', 2);
  22 EOF
  23     return $sql;
  24 }
  25
  26 sub insert_location {
  27     my ($postal_code, $lat, $lon, $postal_location) = @_;
  28     $sql = <<EOF;
  29 INSERT INTO $dbtable (postal_code, center_point)
  30   VALUES ('$postal_code', GeometryFromText('POINT($lon $lat)', -1));
  31 EOF
  32     return $sql;
  33 }
  34
  35 sub drop_table {
  36     $sql = <<EOF;
  37 SELECT DropGeometryColumn('','$dbtable','center_point');
  38 SELECT DropGeometryColumn('','$dbtable','area');
  39 DROP TABLE $dbtable;
  40 EOF
  41     return $sql;
  42 }
  43
  44 sub load_sql_table {
  45     print create_table();
  46     open(FILE, "<pcode-location.txt") or
  47         die "Unable to read pcode-location.txt";
  48     while (<FILE>) {
  49         chomp;
  50         s/#.*//;
  51         next if /^\s*$/;
  52         my ($postal_code, $lat, $lon, $postal_location) = split;
  53         print insert_location($postal_code, $lat, $lon, $postal_location);
  54     }
  55     close(FILE);
  56 }
  57
  58 sub save_sql_table {
  59     print <<EOF;
  60 #
  61 # Location of postal area codes in Norway.  The values are aproximate.
  62 #
  63 # Author: Petter Reinholdsten <pere\@hungry.com>
  64 # Date:   2005-02-13
  65 #
  66 # Updates and corrections are most welcome.  The accurasy is unknown,
  67 # as the positions are based on a random set of addresses with the
  68 # given zip code.
  69 #
  70 # zip latitude longitude postoffice area
  71 EOF
  72     $dbname = "pere";
  73     $dbhost = "localhost";
  74     $dbuser = "pere";
  75
  76     $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $dbuser) # ;host=$dbhost
  77         || die "Unable to connect to DB: $DBI::errstr";
  78     $dbh->do("SET CLIENT_ENCODING TO 'ISO_8859_1'");
  79
  80     my $sql = <<EOF;
  81 SELECT postal_code, postal_location, center_point, area from $dbtable
  82   ORDER BY postal_code
  83 EOF
  84
  85     my $sth = $dbh->prepare($sql);
  86     $sth->execute();
  87     while ( my @row = $sth->fetchrow_array() ) {
  88         my ($postal_code, $postal_location, $center_point, $area) = @row;
  89         my ($lon, $lat) =
  90             $center_point =~ m/SRID=[-+\d]+;POINT\(([\d.]+) ([\d.]+)\)/;
  91         my $line = "$postal_code\t$lat\t$lon\t$postal_location\t$area\n";
  92         $line =~ s/\t+/\t/;
  93         $line =~ s/\t*$//;
  94         print $line;
  95     }
  96     $sth->finish();
  97     $dbh->disconnect();
  98 }
  99
 100 if (1) {
 101     save_sql_table();
 102 } else {
 103     print drop_table();
 104     load_sql_table();
 105 }