# Calculating volume of archaeological excavation levels from points

Question asked by tdennehy on Dec 11, 2019
Latest reply on Dec 14, 2019 by knoop_umich

Hi all,

I've been trying to perform what seems like it should be a fairly simple task, but I've been unable to get it to work using the simple "dummy" versions of the data I have, so I figured I'd ask here.

Essentially, I need to know the volume of sediment excavated in each level of several archaeological units. The units themselves are either square or rectangular pits, but the tricky part is that each level has irregular starting and ending depths. I have XYZ coordinates for the four corners and center point of each level. While the XY coordinates of those points remain essentially unchanged throughout the unit, the Z coordinates are different even for the top or bottom of the same level (because unfortunately we're less-than-perfect excavators, as it turns out). I'm trying to quickly turn these points into 3D representations in ArcGIS so I can calculate their volumes. I've found several posts about this but can't seem to get the procedures that were suggested to work. For example, I tried two workflows from this post:

... but can't seem to get a simple "dummy" level (i.e. a cube 100 m on a side) to look right in ArcGlobe or to have the correct volume. Here's a stripped down version of workflow 1:

Save coordinates in XYZ format as a csv

ASCII 3D to Feature Class (3D Analyst)

Create TIN (3D Analyst)

Surface Volume (3D Analyst)

A different version of a similar workflow suggested in that post is below:

Add Data (import csv file to map)

Display XY Data

Export Data as Feature Class

Create TIN from that Feature Class (3D Analyst)

Surface Volume (3D Analyst)

In both of the above workflows, the point features look correct in ArcGlobe (i.e. I get 8 points floating in space at the corners of a cube), but the TIN's produced always look non-cube-like, and the Surface Volume tool doesn't yield the correct calculations when applied to them.

I also found a potential solution involving 3D polygons in another post:

I was able to create a cube-shaped 3D polygon, but couldn't figure out how to calculate volume measurements  because both Surface Volume and Polygon Volume tools require a TIN for the whole 3D shape.

Creating a TIN seems to be where I run into problems with every workflow I've tried. I'm not sure if this is because the point data aren't ordered correctly - I read somewhere that polygons needs to be "oriented clockwise", but I don't know what that means for a 3D shape. Or it may be because the xyz data isn't "closed" in the sense that the first and last vertices are different. I've tried making them the same in my dummy data by adding a ninth point that is the same as the first, but that yields an odd looking TIN with an edge stretching from diagonally from top to bottom.

Any help would be greatly appreciated! Xander Bakker, I saw you had written a blog post back in 2015 dealing with some of the data from the 2nd post above, but couldn't figure out how to get volume calculations from it (and I'm not proficient in python - yet! - so wasn't able to follow some of what you write anyway).

EDIT: I'm attaching both the dummy data (TextXYZz1) in .csv format and a sample of the actual data that I'm using in .xlsx format.