In-Class Exercise 14 [Point Mapping]
Point Location Mapping at UC Berkeley
Download and unzip to your Documents folder:
Practice Point dataset Download Practice Point dataset
Note: These data are NOT real archaeological data. The artifact tables are based on analysis of collections from South America, and the spatial locations are artificial locations for teaching purposes located on the south-east corner of the UC Berkeley campus.
The Exercise:
1. Use Pivot Tables in Excel to collapse the 1:Many relationship into individual locations and then map them to the Locs shapefile using the unique key field. Summarize quantitative variables in the Pivot table process.
2. Join these collapsed layers to the Loc layer in Arcmap and symbolize by these summarized values.
3. Create a Polygon shapefile in the same coordinate system and delineate several sites including Site 1 and Site 2 indicated in the Locs attribute table.
4. Create polygons for Site 3, 4 using data Lithics_lab data and Sites 5 and 6 from attributes from Ceramics_lab.csv.
5. Put the Site # as an Attribute in the Loc points shapefile using a Spatial Join and Field Calculator.
Turn in for course: Print to PDF showing sites and attribute tables
I. CSV (Comma Separated Values) to Pivot Tables.
Examine the Lithic_Lab table
Download Point Mapping.zip file and unzip (double-click) and copy out to your work directory. Working from the Desktop is fine for this exercise.
Look in the folder. There are a number of .CSV (comma-separated values) files which are basically tables in a non-proprietary text format.
There is a Shapefile called “locs”. When you view a Shapefile in Windows you actually see 4 to 7 constituent files. In ArcCatalog or Arcmap this would appear as one shapefile.
Let’s begin by double-clicking lithics_lab.csv. On most Windows setups this will open a text editor and display the CSV format. Note the first line is the “Header” line and these become the fieldnames if you bring it into a database so always keep the names in Row1 simple.
Now open Excel with a Blank Worksheet and drag and drop the Lithics_Lab.CSV file in there.
Let’s look at the columns: LocID is the unique spatial location ID number while Cat_ID is the unique catalog number within a given LocID. Lit_Mat is the lithic material type and other artifact descriptions follow.
Note that LocID 1004 repeats 5 times, 1011 repeats twice. There were multiple lithic artifacts collected from LocID 1004 but they are so close together it’s not worthwhile mapping each one individually.
However there are various material types, and there’s weight for every artifact. When we map these items we want to incorporate the richness of these lab results onto the map.
Now open the work directory in Windows and drag the Loc.csv table into Excel. Scroll down looking at the data. Does LocID repeat in this table?
Our goal is to collapse the Lithic_Lab table so that there is only one line per LocID and then we can join these data to Locs file and map the points.
Recall that we have a nominal category “Material Type” and numerical value Weight for every artifact. We should be able to show the proportion of each material type in a given LocID by it’s weight total. As an added complication, let’s only use tools including preforms and exclude other categories.
Create Pivot Table
In Excel with Lithic_Lab in the foreground and select one cell of data (it doesn’t matter which) so Excel knows which data you’re focused on. Choose Insert menu > PivotTable. Open in a New Worksheet.
Drag “LocID” field into the Rows, drag the Lit_Mat into Columns, and then drag the Wt_g (Weight in Grams) field into Values. Note the Pivot Table is basically already done. The “Values” field now says Sum of Wt_g, the default, but clicking this you could have it use Average or other calculated values. Sum works for this task.
To exclude certain artifact forms drag Form field into Filters and click the (All) cell at the top of the table. Check “Select Multiple Items” and uncheck all Flake categories except Retouch, Heat Shatter, and Other.
Choose Save As… and use the CSV (Comma-delimited) *.csv format. Call it “lithics_lab_nonflake_locXwt” and save it to the work directory. Click OK on the warning messages.
Back in Windows double-click the resulting .CSV document to inspect the results. The results should look like this. You'll need to strip the first 3 lines so that ArcGIS can read the table. The fourth line becomes the top line, so Fields in Arcmap table will take these names.
Fields can only be 12 characters long and have no spaces or other special characters like slashes so rename the Andesite/Rhyolite/Basalt field to Volcanics (or MatType as it says here). Also spaces are not valid. Remove spaces from "Row Labels" to read "RowLabel" and "Grand Total" to read "Total"
Bringing data into Arcmap
We've already learned how to use "Display X,Y Data..." to convert a table of coordinates like "Loc.csv" into a Shapefile. The Shapefile "Loc.shp" is available in the work directory.
Open Arcmap
Drag the Loc.shp into Arcmap
Under File > Add Data > Basemap... add the Imagery layer.
Add the CSV files into Arcmap and your screen should look something like this.
Starting with the spatial layer, the Loc table, right-click Loc and choose "Joins and Relates... > Join..."
Next choose your Pivot Table output as the Table to Join to. Use LocID in section 1 and RowLabels in section 3.
Now you've got a joined table. If you open the Attribute Table for Loc you should see many more fields off to the right that came from the pivot table CSV.
You can symbolize on variables in this added table. Right-click Loc > Properties.
Choose the Symbology tab and then under Quantities and Charts you'll find ways to represent quantitative values. For example with Graduated Symbols you could have circle sizes scaled to the total weight in each Loc.
Using symbology Charts > Pies you could have each circle be a pie chart with slice size for each Material Type showing to the weight proportion in that location.
Site Polygons
Next we will create a Site polygon shapefile and assign Locs to sites.