Forum OpenACS Development: ec_location_based_on_zip_code

Thanks for all your contributions !!

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

Jon (or anyone else),

Is this an optimally formed query, considering it is a join of the 3 reference tables?

 select s.abbrev, z.name, c.name
 from us_zipcodes z, us_states s, us_counties c
 where  z.fips_state_code = s.fips_state_code and
        z.fips_county_code = c.fips_county_code and
        z.fips_state_code = c.fips_state_code and
        z.zipcode=:zip_code;

It is to replace this query for the non-existant table:

    	select state_code, city_name, county_name
      	from zip_codes
     	where zip_code=:zip_code;

Torben

I'll add these to the bug report.

https://openacs.org/bugtracker/openacs/bug?bug%5fnumber=1619