>Prepared BC Coastal Waterbirds Survey dataset:Imported excel worksheets "BCCWS raw data 1, raw data 2, raw data 3" from Coastal Waterbird Survey data '99-'08.xls into a geodatabase. To join tables to spatial, created new field, SITECODE, in all 3 tables. Using field calculator, populated SITECODE field with following formula: [RegionCode]& [SiteNumber]. Create one field storing date (ymd) rather than 3 fields. Populated field using field calculator with the following formula: [Year_]&"/"& [Month_]&"/"& [Day_]. To combine all 3 tables into one table, used the Append tool and appended raw data 2 and raw data 3 to raw data 1 table. Deleted "Year_" Month_" and "Day_" fields using Delete Field tool. Added 3 new fields using Add Field tool: Feature, Data_Source, Dataset_ID. Populated fields using Calculate Field tool. Feature field calculated to equal the SpeciesCode field. Standardized the Feature field naming convention to equal the common name of the marine species rather than the abbreviated species code. Data_Source field calculated to equal the name of the dataset from which data was derived. Dataset_ID field calculated to equal the common dataset identifier as categorized by BCMCA.
>Prepared BIEAP dataset:Using select by attributes, selected records within Max_month_Subsector_Taxo table where [SPECIES_NM] = 'Generic Scaup' and Months = 12, 1, 2, 3. Extracted selected records to a separate table. To calculate the total number of surveys, two queries were created using Microsoft Access. The first query was created to record all instances of survey months meeting the features' winter specifications within each survey polygon and grouped and ordered by polygon. The second query was created to count and sum all month instances within each grouped polygon.
>Created Survey File:Added 2 new fields: Area_km2 and Density_km2. Area_km2 calculated by multiplying the shape area (m2) by .000001. Density_km2 Density field calculated using the following formula: [nbird_Total / [Area_km2]. Deleted records from the Coastal Waterbird Inventory file where nbirds_Total was '-999'.
>Created Final Processed File:Overlaid merged feature polygons with BCMCA planning units using Spatial Join tool. Tool parameter example: Target Feature = dfo_bc_2k_4k_combined.shp, Join Features = feature layer, Join Operation = Join_One_to_Many, Match Option = Intersects. To create the final processed file, summarized data by planning unit ID and calculated the sum of the total surveys, the sum of the surveys species sighted, and the sum of the density. Added a new field: AVG_DENSITY_km2. Calculated field using the following formula: [sum_DENSITY_km2] / [sum_SURVEYS_TOTAL].
>Created Marxan File:Reviewed the distribution of the data in the final processed file using 5 quantiles as defined by BCMCA. Deleted the planning units with average densities that fell within the bottom quantile.