[AccessD] Re: Zip Code questions

Arthur,

I have read each of Celko's books.  Not only is he an expert, he is on the 
standards board for SQL.

I do not have the Zip+4 database.  Since the +4 portion narrows it down to 
individual houses, it would be huge and expensive.  What I do have is a 
table I generated from census tract data that has about 3/4 of a million 
records for street address ranges for the state of Texas.  It includes the 
zip code and an autonumber type primary key.  Just the one table in an 
Access mdb is about 250 meg.  I have not run any statistics on it, but 1) 
it is running on SQL Server and 2) the zip and the street alpha columns are 
indexed.  A search for a specific zip with a portion of the street name
(WHERE (((tbl_Address_Street.ZIPCode)="77418") AND 
((tbl_Address_Street.StreetSort) Like "*hol*")))
takes less than 1 second to return 10 records.

None of the zip codes in this db start with zero because that is an east 
coast thing since the numbering starts from east to west.  In my db of 5 
place zip codes there are just over 42,200 entries and the first ones start 
with "005."  There are no duplicates when looking at Zip, City, County, and 
State.

Does this help?

Robert

P.S.  Weren't you the developer of ArtfulLib for Clipper?

At 08:06 PM 10/22/2003 -0500, you wrote:
>Date: Wed, 22 Oct 2003 18:49:14 -0700
>From: "Arthur Fuller" <artful at rogers.com>
>Subject: [AccessD] Zip Code questions (was:  Number vs text data type)
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID: <011401c39907$e4e17ef0$6501a8c0 at rock>
>Content-Type: text/plain;       charset="Windows-1252"
>
>Just out of curiosity, has anyone followed this up with actual
>benchmarks? (I ask this because a man with whom I communicate
>frequently, Joe Celko, a well-known SQL expert, has said that the lookup
>speed difference between a 10-char value and a long int on modern
>systems is the least significant of our problems.)
>
>Assuming you code 5+4 zips as 9-digit zips (eliminating the "-"), does
>anyone on the list have a sufficiently large sample to actually
>benchmark this meaningfully? By that I mean that about 1M rows, two
>columns, one of each data type, indexes to suit, and a timer to verify
>the results?
>
>And while I'm on the subject, does anyone have any ideas on how to
>negate the cache in repeated searches? I'm thinking that the way is to
>search for non-existent values, but that's just an armchair guess
>unsupported by facts or knowledge about cache-algorithms.
>
>Anybody got such a db that you can send me without violating copyrights
>etc.?
>
>Assuming factorial(9) is the maximum number of 5+4 zip codes, what is
>the actual number currently?
>
>Is there any zip code that begins with "0"? And if so, what about "00"?
>
>Is there any logic to the expansion from 5 to 5+4? I.e., they put in a
>housing development in zip 97600, each of ten buildings sufficiently
>large to warrant its own zip code, how are they assigned? Incrementally?
>(Not necessarily step 1).
>
>One final question: is there any extant zip code that spans more than
>one city or town or other regional designation?
>
>Curiosity killed the programmer.
>
>TIA,
>Arthur