Corangamite Tree Survey Assignment

Project

Part 1 - Corangamite Tree Survey

To complete this part of the assessment, you must complete the following task/s to industry standards:

1. Identify task requirements (all output files must be submitted with this document)
2. Enter and check spatial data
3. Calculate tree heights
4. Identify data errors
5. Insert a pivot table and chart
6. Document the workbook
7. Export the data
8. Format the workbook
9. Import data to GIS
10. Describe the process used to identify data errors
11. Use software documentation or online help

Task 1: Identify task specifications

Complete the following and then fill out the table below to identify task requirements:

• Read through the assessment
• Refer to the Resource Document for organisational guidelines for tasks
• And review the email from your supervisor.

Identify task requirements

1. Data source file
2. Workbook file name and format
3. Worksheet names and content
4. Required Excel features and functions
5. Other output files
6. Output file storage
7. Documentation
8. Reporting

Task 2: Enter and check spatial data

2.1.

• Store the CPPSSI5060_Pro1_CorangamiteTreeSurvey.csv in your MyProject > Data > SourceData directory.
• Import the Pro2_CorangamiteTreeSurvey.csv file into Excel.
• Describe the steps you took to display the data in columns.
• Save this file as a workbook in your Project > Document folder, as per the Project folder structure outlined in the Resource Document (CPPSSI5060_AE_Pro_ResourceDocument.pdf)

2.2.

• Look at the DBH field. What does DBH stand for? What units do you think this field uses and why?
• Click or tap here to enter text.

2.3.

• Look at the Origin field. What values occur in this attribute? Are these values all clear and different? Are there are any potential duplicate values?
• Click or tap here to enter text.

2.4. Latitude and longitude are provided to 9 decimal places. What spatial accuracy does this represent? Does this seem reasonable for a tree that might be a metre wide? What happens if latitude and longitude values are truncated to 2 decimal places?

Task 3: Calculate tree heights

• Calculate the height of the trees in the workbook using the supplied data.
• Insert a column in your workbook and name the column.
• In the first row of tree data, write a formula using Excel functions to calculate the height of the trees
• Check that the function is correct and then copy/fill the formula down the column to calculate the tree heights for all the tree records in the workbook.

Example 1 of this Excel Tutorial on Trigonometry will help.

Task 4: Identify data errors

• Manually check the data in the spreadsheet to be sure the inputs are all correct. Guidance for this process is provided in the Resource Document.(CPPSSI5060_AE_Pro_ResourceDocument.pdf)
• Move any invalid records to a separate sheet in your workbook, highlighting or colouring the value that you have decided is invalid.
• Document the rules, steps and/or procedure you have used to decide which records are invalid in the Reporting Task (Task 10).

Task 5: Insert a pivot table and chart

• In a new worksheet, create a pivot table to show the count of records (number of trees), minimum height, maximum height, and average height of any five (5) species of trees (you select which species).
• Make sure the pivot table does all the calculation of statistics for you.
• Review the document below, Using pivot tables to calculate statistics, if you need some help.
• In the Pivot Table tab, create a chart from your pivot table. The chart should be simple and give some insight into the data.

Remember to edit the Pivot Table fields to show useful names and labels in your chart.

Task 6: Document the workbook

It is important to document your workbook for other users.

• Insert a worksheet in your workbook and enter workbook documentation.
• Guidance for this task is provided in the Student Workbook Topic 4.
• Links to data sources (and metadata) are included in the Assessment Resource document (CPPSSI5060_AE_Pro_ResourceDocument.pdf)

Task 7: Export data

• Some of the Corangamite council survey staff are yet to upgrade their Office software and are using Excel 2003.
• Save a copy of the final workbook in XLS format for these council staff in your MyProject > FinalProduct > Documents folder.

Task 8: Format workbook

• Go back to your XLSX workbook and format the entire workbook, including the pivot table and graph according to organisational guidelines as outlined in the Assessment Resource Document.CPPSSI5060_AE_Pro_ResourceDocument.pdf)
• Save this final version of your XLSX workbook to your MyProject > FinalProduct > Documents folder.

Task 9: Import data to GIS

• Save the validated TreeData worksheet as a CSV file and save it to your MyProject > FinalProduct > Data folder.
• Import into this CSV file into your GIS software.

The coordinate system is Geocentric Datum of Australia 1994 (EPSG code 4283).

Use the EPSG code to check that you have selected the correct CRS. Remember that your points are in degrees, so the CRS must be geographic (not projected).

In your GIS software, overlay the points with another layer to check that they are in the correct part of the world and are showing on the streets and parks of Cobden, Victoria. Use the Zoom to layer function to see if there are points outside of Cobden which might be invalid.

• Save as/ Export the point layer to the appropriate format for your software (Shapefile for QGIS, File Geodatabase for ArcMap). Refer to the Resource Document for guidelines.
• Save these files to your MyProject > FinalProduct > Data folder

Task 10: Describe process to used identify data errors

Briefly outline the steps (not the errors) you carried out to find errors and validate the tree data in the space below:

Click or tap here to enter text.

Task 11: Use software documentation or online help.

Use an online search or a help search in Excel to solve a problem or check some aspect of this project. Take a screen capture of this search (use Snipping Tool or similar, just capture part of the screen).