GeoNames Forum

Greetings -

For AR (Argentina) the main places files contains FIPS ADMIN1 codes and the postal records contain ISO ADMIN1 codes. The following contains the SQL to convert the ISO to FIPS codes in the postal table and the result of the conversion is included in the attached file.

Could you please delete the AR postal records and replace them with this set. It would make the distance to match place to postal code a little less.

Code:

 SELECT *
 FROM (
 ----
 SELECT
   trim(gp.country_code)             AS country_code,
   trim(gp.postal_code)              AS postal_code,
   trim(gp.place_name)               AS place_name,
   trim(gp.admin1_name)              AS admin1_name,
   -- RA.2009.11.21 - Convert ISO code to FIPS code 
   (CASE WHEN gp.admin1_code = 'B' THEN '01'
         WHEN gp.admin1_code = 'K' THEN '02'
         WHEN gp.admin1_code = 'H' THEN '03'
         WHEN gp.admin1_code = 'U' THEN '04'
         WHEN gp.admin1_code = 'C' THEN '05'
         WHEN gp.admin1_code = 'X' THEN '06'
         WHEN gp.admin1_code = 'W' THEN '07'
         WHEN gp.admin1_code = 'E' THEN '08'
         WHEN gp.admin1_code = 'P' THEN '09'
         WHEN gp.admin1_code = 'Y' THEN '10'
         WHEN gp.admin1_code = 'L' THEN '11'
         WHEN gp.admin1_code = 'F' THEN '12'
         WHEN gp.admin1_code = 'M' THEN '13'
         WHEN gp.admin1_code = 'N' THEN '13'
         WHEN gp.admin1_code = 'Q' THEN '15'
         WHEN gp.admin1_code = 'R' THEN '16'
         WHEN gp.admin1_code = 'A' THEN '17'
         WHEN gp.admin1_code = 'J' THEN '18'
         WHEN gp.admin1_code = 'D' THEN '19'
         WHEN gp.admin1_code = 'Z' THEN '20'
         WHEN gp.admin1_code = 'S' THEN '21'
         WHEN gp.admin1_code = 'G' THEN '22'
         WHEN gp.admin1_code = 'V' THEN '23'
         WHEN gp.admin1_code = 'T' THEN '24'
    ELSE '----------'
   END)                              AS admin1_code,
   trim(gp.admin2_name)              AS admin2_name,
   trim(gp.admin2_code)              AS admin2_code,
   trim(gp.admin3_name)              AS admin3_name,
   gp.latitude                       AS latitude,
   gp.latitude                       AS longitude,
   trim(gp.accuracy)                 AS accuracy
   --
 FROM  xwor.geoname_places_postal AS gp
 WHERE gp.country_code = 'AR'
 ) AS x
 ----
 ORDER BY postal_code, admin1_code, place_name
 ;

Regards,