excel and GIS

You have been supplied with some field survey data for street trees in the town of Cobden (within Corangamite LGA, Victoria). For each tree, the field survey team have recorded:

Unique ID

Latitude (using GCS_GDA_1994/GDA94)

Longitude (using GCS_GDA_1994/GDA94)

Diameter at Breast Height

Species

Name

Origin

Tree type

Tree age

Observation distance (m)

Observation angle (degrees)

The original data was downloaded from here, which also contains metadata and licensing information.

Your task is to work with this data and use the functions of MS Excel to do the following:

Given the observation angle and distance, calculate the height of the trees. Example 1 on this tutorial page will help.

Validate your spreadsheet to be sure the inputs are all correct – including the latitude and longitude values. Hints and help

Remove any invalid records into a separate sheet in your worksheet.

Create a pivot table to show the minimum, maximum and average height of trees of each species.

Protect all cells not used for data input so the worksheet can be reused as a template. Save in template format (xltx or xltm).

Create a graph from your pivot table. The graph should be simple and give some insight into the data..

Create a sheet to document your spreadsheet. The following is a good template to start. For more information look at this AuditExcel webpage.

Filename and path

=CELL(“FILENAME”)

Date

=NOW()

Spreadsheet Owner

Overview

How to use the spreadsheet:

Output

Calculations

Input

Initial Testing Log

Change Log

Date, Person, Changes made

Privacy restrictions

As well as your completed spreadsheet, you need to submit the following:

Consult with your teacher to clarify two questions that you have about this project. Document this consultation and how you incorporated any feedback.

Use an online search or a help search to solve a problem. Take a screen capture of this search.

Your organisation requires you to save files in the old xls format for users who haven’t the new version of excel.

Save the spreadsheet as a template so it can be used again for future surveys.

Locations in your tree spreadsheet have been recorded to 9 decimal places. If I inadvertently truncate this location to 2 decimal places in file conversion between GPS receiver, Excel and GIS, what will happen to accuracy of the location?

Make a presentation showing the table and graph and export to JPG and PDF formats.

Include it in a short report explaining what data you received and what you have done. Include any assumptions you have made, limitations, features of the spreadsheet.

Import the CSV/TXT into your GIS software (ArcGIS, QGIS or MapInfo). "Zoom to layer" to check that the coordinates are all correct (ie. All points are in or near Cobden) and save the point data in Geodatabase/Shapefile/Tab format.

Put all your files into a single Zip file and submit.

-looking for when marking Presentation

Correct use of Trig functions

Ability to export to a range of formats

Import to GIS software and saving in spatial data format

All required calculations are made with appropriate formula

Calculations are correct

Data validation is used

Spreadsheet is documented

Consultation with your supervisor (teacher) to clarify task

Graphing and pivot tables are used

i got excel file which iwill send to you

Cartographie & Plans Excel Geographical Information System (GIS) Géospatial

Nº du projet : #19403038

À propos du projet