ENV103 Chap.7 Wrangling Geospatial Data in Excel
Wrangling Geospatial Data in Excel
Lab 1 trains the step that comes before any map: getting data into shape. Working with wastewater, census and housing data for Māngere and Oneroa, you filter a national table to two sites, clean census codes, build line, 100% stacked and box-and-whisker charts, and calculate each house's distance from its treatment plant from NZTM2000 coordinates.
Each step is tied to the Lab 1 proforma questions, so you can see which skill earns which points and why the graphs are the single largest item.
What this chapter covers
- 01
Text, CSV and Excel file formats
- 02
Filtering to two treatment plants
- 03
AVERAGE formulas and the fill handle
- 04
Cleaning census codes such as −999
- 05
Line, stacked and box-and-whisker charts
- 06
Euclidean distance from eastings and northings
- 07
Lab 1 rubric and proforma
Worked example · free
Read the five-number summary and mean from a distance box plot
- +1With eleven sorted values, the median is the 6th value: 1,040 m.
- +1The lower quartile is the median of the five values below it, 750 m; the upper quartile is the median of the five above it, 1,490 m.
- +1Mean = 12,710 ÷ 11 ≈ 1,155 m, the cross on the chart.
- +1The mean sits above the median because the farthest house, at 2,380 m, pulls it to the right.
Key terms
- CSV file
- A CSV file is plain text with data in rows and columns separated by commas.
- Centroid
- A centroid is the geographic centre point of a polygon, such as a building outline.
- Box and whisker plot
- A box and whisker plot summarises a distribution with its quartiles, median, extremes and usually its mean.
- Fill handle
- The fill handle is the small square at a cell's corner that copies a formula into neighbouring cells.
- No-data value
- A no-data value is a code, such as −999, that marks a missing measurement rather than a real number.
- Absolute reference
- An absolute reference, written with dollar signs such as $G$2, keeps a formula pointing at the same cell when it is filled down.
Wrangling Geospatial Data in Excel FAQ
How do I calculate distance from easting and northing in Excel?
Take the difference in eastings and the difference in northings between the house and the plant, square both, add them and take the square root. Because NZTM2000 coordinates are in metres, the result is a straight-line distance in metres. Fix the plant cells with dollar signs before filling the formula down.
Why replace −999 values before charting?
The code marks missing data, and left in place it would plot as a large negative count. The lab replaces it with zero for charting, but remember that zero now also means missing, so exclude those cells before averaging or the mean will be pulled down.
What makes a lab chart earn full marks?
The Lab 1 rubric gives 2.5 points per graph for titles, axis labels and clear legibility. Name each series, title both axes, use colours that print distinctly and check the exported PDF at normal zoom. The practice test added ordering bars by size and a short interpretation.
Why does the lab use Go To Special and Visible Cells Only?
When rows are filtered, Excel still holds the hidden rows in the selected range. Choosing Visible Cells Only before inserting the 100% stacked chart makes sure only the filtered location, such as Māngere with location code 12, feeds the chart, instead of every hidden census row being included.
Assessment move
Lab skills fade quickly unless you rehearse them, so redo Lab 1 from a blank workbook rather than rereading the instructions. Time yourself on the core moves: turning on filters, selecting two sites, copying to a new sheet, inserting an Average column and filling it down.
Then practise the census clean-up with Find and Replace and the Go To Special, Visible Cells Only step, which is the one most people forget and which quietly corrupts charts built from filtered data. Write the distance formula from memory, with absolute references to the plant coordinates, and check one result by hand with a calculator so you trust the column.
Build each of the three chart types and, for every chart, write the one-sentence interpretation the proforma would ask for, quoting a number from the chart itself. Learn what a box plot shows, quartiles, median, extremes and the mean cross, because the mean-distance question depends on it. Finally, practise exporting a document to PDF and checking it, since the course accepts only the proforma as a PDF.
The same habits carried straight into the bar-chart question on the practice test, so they are worth having for any spreadsheet work that follows.