New WeatherFX Geo Reference Data Guide

Database and Table Names

# Database Table Name Total Records Type Access Primary Keys Archived S3
1 wfx_ref_data climate_region 51 Table Public Country, State, Climate_Region YES
1 wfx_ref_data country_names 268 Table Public Country_Name YES
2 wfx_ref_data dma 210 Table Public DMA_Code YES
3 wfx_ref_data dma_mapping 40891 View Public None NO
4 wfx_ref_data holidays 104 Table Public Country, Holiday_Date YES
5 wfx_ref_data location 50891 View Public None YES
6 wfx_ref_data noaa_coopid_mapping 7735 View Public None NO
7 wfx_ref_data obs_stn_mapping 41275 View Public None NO
8 wfx_ref_data season 10 Table Public Season_ID YES
9 wfx_ref_data season_dates 1122 Table Public Country, Climate_Region, Season_Dates YES
10 wfx_ref_data state_names 65 Table Public None YES
11 wfx_ref_data zip_type 40886 Table Public LocationID YES
12 wfx_ref_data loc_type 21 Table Public LocID YES
13 wfx_ref_data_private location_master_wte 2968035 Table Private twc_loc_id, loc_type, country_cd YES
14 wfx_ref_data_private location_master_copy 2917693 Table Private twc_loc_id, loc_type, country_cd YES
15 wfx_ref_data_private noaa_coop_obs_stn_atl 2112 Table Private None YES
16 wfx_ref_data_private noaa_coopid_mapping_union 78841 View Private None NO

Table 1: LOCATION (View: Public)

In [43]: 
#Create view:
stmt = """select
twc_loc_id as location_id,
loc_type as loc_id_type,
city_nm as city,
st_cd as state,
country_cd as country
from wfx_ref_data_private.location_master_wte
where country_cd in("US", "GB") and loc_type = '4'"""
In [46]: 
#View DDL
queryqc('describe wfx_ref_data.location')
Out[46]:
         Field         Type Null Key Default Extra
0  location_id  varchar(50)   NO        None      
1  loc_id_type  smallint(6)   NO        None      
2         city  varchar(45)   NO        None      
3        state   varchar(4)   NO        None      
4      country   varchar(2)   NO        None
In [49]: 
#sample 3 records query from created view
queryqc("""select * from wfx_ref_data.location limit 3""")
Out[49]:
  location_id  loc_id_type        city state country
0       00501            4  HOLTSVILLE    NY      US
1       00544            4  HOLTSVILLE    NY      US
2       00601            4    ADJUNTAS    PR      US

2. DMA (Table: Public)

In [59]: 
#Table DDL
queryqc('describe wfx_ref_data.dma')
Out[59]:
              Field         Type Null  Key Default Extra
0          dma_code      char(3)   NO  PRI              
1      dma_name_wfx  varchar(50)  YES         None      
2  dma_name_nielsen  varchar(50)  YES         None
In [60]: 
#Sample 3 Records from Tbl
queryqc('select * from wfx_ref_data.dma limit 3')
Out[60]:
  dma_code     dma_name_wfx dma_name_nielsen
0      500  Portland Auburn  PORTLAND-AUBURN
1      501         New York         NEW YORK
2      502       Binghamton       BINGHAMTON

3. DMA_MAPPING (View: Public)

In [5]: 
stmt = """select 
twc_loc_id as location_id,
dma_cd as dma_code
from wfx_ref_data_private.location_master_wte
where (country_cd in('US','GB') and loc_type = '4' and dma_cd is not null)"""
In [64]: 
#View DDL
queryqc('describe wfx_ref_data.dma_mapping')
Out[64]:
         Field         Type Null Key Default Extra
0  location_id  varchar(50)   NO        None      
1     dma_code   varchar(3)  YES        None
In [65]: 
#Sample 3 Records from View
queryqc('select * from wfx_ref_data.dma_mapping limit 3')
Out[65]:
  location_id dma_code
0       00501      501
1       00544      501
2       01001      543

4. OBS_STN_MAPPING (View: Public)

In [67]: 
stmt = """select 
twc_loc_id as location_id, 
country_cd as country,
obs_stn as obs_stn, 
sec_obs_stn as obs_stn_sec, 
tert_obs_stn as obs_stn_ter
from wfx_ref_data_private.location_master_wte
where (loc_type='4' and country_cd in('US', 'GB'))"""
In [69]: 
#View DDL
queryqc('describe wfx_ref_data.obs_stn_mapping')
Out[69]:
         Field         Type Null Key Default Extra
0  location_id  varchar(50)   NO        None      
1      country   varchar(2)   NO        None      
2      obs_stn  varchar(10)   NO        None      
3  obs_stn_sec  varchar(10)  YES        None      
4  obs_stn_ter  varchar(10)  YES        None
In [70]: 
#Sample 3 records from view
queryqc('select * from wfx_ref_data.obs_stn_mapping limit 3')
Out[70]:
  location_id country obs_stn obs_stn_sec obs_stn_ter
0       00501      US    KISP        KHWV        KFRG
1       00544      US    KISP        KHWV        KFRG
2       00601      US    TJPS        None        None

5. NOAA_COOPID_MAPPING (View: Public)

Step 1: Upload external Excel File from ATL to fill missing obs_stn that are not present in locmaster
Step 2: Create helper view in wfx_ref_data_private called noaa_coopid_mapping_union
Step 3: Create final view in wfx_ref_data called noaa_coopid_mapping for union

In []: 
#Step 1: Load external file from ATL
In [75]: 
#Step 2: Create noaa_coopid_mapping_union helper in private using excel from above and locmasterwte
stmt = """select distinct obs_stn as obs_station_id, twc_loc_id as coop_id
from wfx_ref_data_private.location_master_wte
where loc_type = '6' and country_cd = 'US'
union (
    select * from wfx_ref_data_private.noaa_coop_obs_stn_atl
    )"""
In []: 
#Step 3: Create final view from distinct query
stmt = """select distinct * from wfx_ref_data_private.noaa_coopid_mapping_union where obs_station_id is not null"""
In [78]: 
#View DDL
queryqc('describe wfx_ref_data.noaa_coopid_mapping')
Out[78]:
            Field         Type Null Key Default Extra
0  obs_station_id  varchar(20)  YES        None      
1         coop_id  varchar(50)  YES        None
In [79]: 
#Sample 3 records from final view
queryqc('select * from wfx_ref_data.noaa_coopid_mapping limit 3')
Out[79]:
  obs_station_id coop_id
0          71704  206680
1           CPBH  240392
2           CPBH  242629

6. CLIMATE_REGION (Table: Public)

Note: Need to Add International Data

In [87]: 
#Table DDL
queryqc('describe wfx_ref_data.climate_region')
Out[87]:
            Field         Type Null  Key Default Extra
0         country   varchar(2)   NO  PRI              
1           state   varchar(4)   NO  PRI              
2  climate_region  varchar(20)   NO  PRI
In [1]: 
#Sample 3 Records from Table
queryqc('select * from wfx_ref_data.climate_region limit 3')
Out[1]:
  country state climate_region
0      US    AK         Alaska
1      US    AL      Southeast
2      US    AR          South

7. SEASON (Table: Public)

Note: Need to Add International Data

In [158]: 
#Table DDL:
queryqc('describe wfx_ref_data.season')
Out[158]:
         Field         Type Null  Key Default Extra
0    season_id   varchar(5)   NO  PRI              
1  season_name  varchar(30)  YES         None
In [106]: 
#Sample * Records from Table
queryqc('select * from wfx_ref_data.season')
Out[106]:
  season_id     season_name
0        S1          spring
1       S10  generic-winter
2        S2   spring-summer
3        S3          summer
4        S4     summer-fall
5        S5            fall
6        S6          winter
7        S7  generic-spring
8        S8  generic-summer
9        S9    generic-fall

8. SEASON_DATES (Table: Public)

Note: Need to Add International Data

In [115]: 
#Table DDL
queryqc('describe wfx_ref_data.season_dates')
Out[115]:
               Field         Type Null  Key     Default Extra
0            country   varchar(4)   NO  PRI                  
1     climate_region  varchar(20)   NO  PRI                  
2          season_id   varchar(4)   NO  PRI                  
3  season_start_date         date   NO  PRI  0000-00-00      
4    season_end_date         date   NO  PRI  0000-00-00
In [120]: 
#sample records
season_dates_df[:3]
Out[120]:
  country climate_region season_id season_start_date season_end_date
0      US         Alaska        S1        2004-11-07      2005-03-13
1      US         Alaska        S1        2005-11-07      2006-03-13
2      US         Alaska        S1        2006-11-07      2007-03-13

9. HOLIDAYS (Table: Public)

Note: Need to Add International Data

In [148]: 
#Table DDL
queryqc('describe wfx_ref_data.holidays')
Out[148]:
          Field         Type Null  Key     Default Extra
0       country      char(2)   NO  PRI                  
1  holiday_date         date   NO  PRI  0000-00-00      
2  holiday_name  varchar(50)  YES             None
In [2]: 
#Sample 3 Records from Table
queryqc('select * from wfx_ref_data.holidays limit 3')
Out[2]:
  country holiday_date   holiday_name
0      US   2010-01-01  New Years Day
1      US   2010-01-18        MLK Day
2      US   2010-02-07      Superbowl

10. STATE_NAMES (Table: Public)

In [226]: 
#Table DDL
queryqc('describe wfx_ref_data.state_names')
Out[226]:
          Field         Type Null  Key Default Extra
0       country      char(2)   NO  PRI              
1    state_name  varchar(50)  YES         None      
2  state_abbrev      char(4)   NO  PRI
In [227]: 
#Sample 3 Records from Table
queryqc('select * from wfx_ref_data.state_names limit 3')
Out[227]:
  country             state_name state_abbrev
0      US  Armed Forces Americas           AA
1      US    Armed Forces Africa           AE
2      US                 Alaska           AK

11. ZIP_TYPE (Table: Public)

In [165]: 
#Table DDL
queryqc('describe wfx_ref_data.zip_type')
Out[165]:
         Field         Type Null  Key Default Extra
0  location_id  varchar(10)   NO  PRI              
1     zip_type  varchar(20)  YES         None
In [3]: 
#Sample 3 Records
queryqc('select * from wfx_ref_data.zip_type limit 3')
Out[3]:
  location_id  zip_type
0       00501    UNIQUE
1       00544    UNIQUE
2       01001  STANDARD

12. LOCATION_TYPE (Table: Public)

In [201]: 
#Tbl DDL
queryqc('describe wfx_ref_data.loc_type')
Out[201]:
           Field           Type Null  Key Default Extra
0    loc_id_type    varchar(10)   NO  PRI              
1  location_desc  varchar(1000)  YES         None
In [205]: 
#Records from Table
queryqc("""select CAST(loc_id_type as signed integer) as loc_id_type, location_desc as loc_desc
from wfx_ref_data.loc_type order by loc_id_type asc""")
Out[205]:
    loc_id_type                                 loc_desc
0             1                                     City
1             2                             Observations
2             3                                 Forecast
3             4                              Postal Code
4             5                                     Golf
5             6                                  Climate
6             7                                   Pollen
7             9                                  Airport
8            10                                      EPA
9            11                                      Ski
10           12                                     Tide
11           13                               Recreation
12           14                              FAA Airport
13           15                          Pollen Forecast
14           16  Interstate and Major Road Intersections
15           17                                  Schools
16           19                                  Outdoor
17           21                              Hydrography
18           22                         NWS Marine Zones
19           23                                   WXLabs
20           25                                   Sports