>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 Burger data:Exported all excel survey worksheets (those labeled "coded") into a personal geodatabase. Appended all relevant survey tables into one master table. Added 2 new fields to spatial layer using Add Field tool: Mid_XCOORD, Mid_YCOORD and calculated the midpoint geometry. Displayed XY data and exported XY using NAD 1983 BC Environment Albers to a class. Merged all midpoints together for each applicable folder (i.e. shf_files_plotted). Used identify duplicate fields calculation to make sure keycode field is unique. Using Microsoft Access query, selected all records within burger data files (coded_with_behaviour) where [cagut] > 0, [megut] > 0, [hegut] > 0, [thgutWat] > 0 with survey months >9 or <3 and exported to separate feature tables. Joined master survey table to spatial layer using keycode field in both spatial and aspatial. Deleted all species' fields except those relevant to the target feature(s) identified. Data_Source field calculated to equal the name of the dataset from which the data was derived. Dataset_ID field calculated to equal the common dataset identifier as categorized by BCMCA. Surveys_Total and Surveys_Species_Sighted fields calculated to equal one to reflect each keycode value. nbirds_Total field calculated to equal the value recorded in the species' survey table. Buffered line transects to 150m (radius) using Buffer tool.
>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].
>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. Species was used to create the at-sea density feature. To avoid double representation, overlaid feature with at_sea_density.shp and deleted planning units from the marxan file that were identical to those planning units with densities greater than zero in the at sea density feature.