Notes on documenting the zips.txt feature of evergreen

esi zip scripts.txt

elect_zips will find the most popular city/state combination for each ZIP code in your database.

enrich_zips will add county names (given a copy of the Geonames database, which is at http://download.geonames.org/export/zip/US.zip)
and spit out data in zips.txt format, warning you about mismatches and non-matches.

http://git.esilibrary.com/?p=migration-tools.git;a=blob;f=elect_zips
http://git.esilibrary.com/?p=migration-tools.git;a=blob;f=enrich_zips

Usage:

psql -U evergreen -A -t -F $'\t' -c 'SELECT city, state, post_code FROM actor.usr_address' | elect_zips | enrich_zips --warn --makezips --db US.txt > zips.txt 2> warnings.txt

I'd like to add support for inserting an area code (where there is a known and unambiguous area code for a ZIP), but I haven't found a database to use.

Share and enjoy!
Ben

###
psql egdb26 -A -t -F $'|' \
 -c "SELECT count(substring(post_code from 1 for 5)) as zipcount, state, \
 city, substring(post_code from 1 for 5) as pc, \
 '1', '', county, '', '' FROM actor.usr_address \
 group by pc, city, state, county \
 order by pc, zipcount DESC"

geonames.org.txt

http://pastebin.com/M1eg1zTm

www.geonames.org seems fine with anyone using the data, as long as you link to them.

## How to get a generic Evergreen zips.txt for free
wget http://download.geonames.org/export/zip/US.zip
unzip US.zip
cut -f2,3,5,6 US.txt \
 | perl -ne 'chomp; @f=split(/\t/); print "|" . join("|", (@f[2,1,0], "1", "", $f[3], "")), "|\n";' \
 > zips.txt

gistfile1.txt

#http://pastie.org/3997120
# Another method for using the Google API to get a list of Zip, State, City, County for use in Evegreen zips.txt data file.
#


#!/usr/bin/perl

  use Geo::Coder::Google;

  # Niles Ingalls
  # Define the settings below, to brute force a list of addresses based on zip.
  # note: google api does not return county.  To view what the google API gives you,
  # do a use Data::Dumper; print Dumper($location)

  # Indiana zip range is 46001 - 47997
  # Georgia zip range is 30001 - 31999
  # Michigan zip range is 48001 - 49971
  # Google maps API can deal with Canadian postal
  # codes the same way, but their codes are alphanumeric.
  # someone else can extend that functionality to this script.
use Data::Dumper;
    my $zip_low = '46001';
    my $zip_high = '47997';
    my $country_code = 'US';
    my $apikey = 'ABQIAAAAe93_rLAesWPx0mRQyf3NMxQjH1QZxVy418IgD1y6bCmwuGdcRBQDndEsk9CTwMu_H0Iau32gShPWCw';

          for ($zip = $zip_low;$zip < ($zip_high + 1);$zip++) {

            my $geocoder = Geo::Coder::Google->new(apikey => $apikey);
            my $location = $geocoder->geocode( location => $zip );

            my $state = $location->{AddressDetails}->{Country}->{AdministrativeArea}->{AdministrativeAreaName};
            my $city = $location->{AddressDetails}->{Country}->{AdministrativeArea}->{SubAdministrativeArea}->{Locality}->{LocalityName};
            my $country = $location->{AddressDetails}->{Country}->{CountryNameCode};
#           print "$state $city $country $zip\n";
#print Dumper($location);
                    if ($country eq $country_code && defined($city)) { print "|$state|$city|$zip|1|||\n"; }
            sleep(1);

Notes.txt

I got curious just how far back ZIP lookup goes, and it starts with commit 2ea132cef5bf36106378f8c15943d0acbe443361 from 2006-05-03 ... It's been a thing since since before PINES went live!  Anyone up for retrospectively writing version 0.01 docs?

- only the first five digits in the ZIP code field are considered
- only the city, state, and county fields are used during address prepopulation
- the field after area code can store an alert message.  If that's present, then the patron registration form will display an alert -- this can be useful if (say) a ZIP code is used by more than one town

https://web.archive.org/web/20110417152404/http://dmagick.wordpress.com/2011/01/06/notes-on-installing-zipcode-information/


FWIW, we just used this free listing to pre-populate our code list
("zips.txt"): http://geocoder.ca/?freedata=1 But for our working list, we've only extracted out the more commonly used codes in our patron database (so subset of that full list).

If we had cleaner data, we could have used our own db to generate the listing of zips.txt as starting point.

Nice feature - works well in the Staff Client.

George


Bug filed by a user annoyed that his address got entered incorrectly because the zip code covers more than one city/county
https://bugs.launchpad.net/evergreen/+bug/1006545


Development - 

zips.pm
Open-ILS / src / perlmods / lib / OpenILS / Application / Search / Zips.pm


Where the lookup happens
Open-ILS / web / js / ui / default / actor / user / register.js