Forum OpenACS Development: Re: ec_location_based_on_zip_code

Collapse
4: Re: ec_location_based_on_zip_code (response to 1)
Posted by Torben Brosten on 03/10/04 07:36 AM

Okay,this[2] database contains zip code, city and county names, which is all that ec_location_based_on_zip_code uses: 

    	select state_code, city_name, county_name
      	from zip_codes

Here's the zip_codes definition: 

--  ZIP_CODE                               NOT NULL VARCHAR2(10)
--  STATE_CODE                                      CHAR(2)
--  CITY_NAME                                       VARCHAR2(32)
--  COUNTY_NAME                                     VARCHAR2(32)
--  LONGITUDE                                       NUMBER(9,6)
--  LATITUDE                                        NUMBER(9,6)

The longitude and latitude seem redundant anyway, since that data is already in the us_zip_codes reference table.

Now to call 1-800-ask-usps (1-800-275-8777) for pricing, availability, and conditions for distribution.

Oh! Wait.. I get it now. The census bureau provides the same info for free via us_zip_codes, us_state_codes, and us_county_codes reference tables, which are already available via the references packages on Openacs!

Now I believe the question is, how to join these in a query so that there is not a significant delay (or load on the system).

2. http://www.usps.com/ncsc/addressinfo/citystate.htm