>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, select all records within burger data files (coded_with_behaviour) where [anmuWat] > 0 with survey months >11 or <3 and exported to a feature table. Deleted all species' fields except those relevant to the target feature(s) identified. Joined master survey table to spatial layer using keycode field in both spatial and aspatial. 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 for birds on water. Note: Counts for 'birds on the water' were recommended by the experts and are consistent with the survey methodology employed by the BC Coastal Waterbird Survey. Buffered line transects to 150m (radius) using Buffer tool.
>Prepared and added PSS dataset:Steps taken prior to CRM processing: Reconciliation of data files - Survey data was originally input and stored in various MS Excel. Field names and records were standardized for all spreadsheets. In addition to design standards, date information was reconciled to conform to international date formats. This is important to address year 2000 date issues. All spreadsheets were converted into DBF files. Creation of relational database - The original database design was assessed and redesigned to conform to GIS conventions and standards. A single key identifier uniquely identifies all features. These features link to the pelagic shipboard survey tabular data (one-to-many data relationship), based on the single key identifier in that database. The key identifier is a concatenation of the date, original transect identifier, and the original survey identifier. Transposing of fields and other programming - It was necessary to transpose species information that was stored as many fields (100+) into a single species field. By storing species information into a relational database, query effort is simplified and processing more efficient. A GIS automation script was created to automate the process that would otherwise have had to been conducted manually.
>Created Survey File:To create the survey File, added 2 new fields: Area_km2 and Density_km2. Area_km2 calulated by mulitplying 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].