How to make a fancy elevation profile including XY points

I did not expect that making a decent graph of an elevation profile could be a hard task. I tried to make it in ArcMap, but it did not meet my expectations for a formal presentation, the output graph was very simple!. Then, I used Microsoft Excel to refine the graph design and have a better presentation.

Anyway, here I share this tricky and very detailed methodology. However, it is necessary some patient and time because there is not a direct method to accomplish this task. (This graph was done in ArcMap 10.3 and Microsoft Excel 2016).

Main steps:

• Create a Profile Graph and a Point Profile in ArcMap
• Export the data table from the created profiles to Excel
• Create a unique Scatter (X,Y) chart in Excel with the two data series together (from the Profile Graph and the Point Profile)
• Add labels and refine graph details with the Format and Design tools of Excel.

Detailed steps:

1. In ArcMap add your DEM image and data points in a Blank template

2. Convert data points to 3D features. It is only in case that you have 2D points, then you have to convert these points to 3D features as follow:

2.1. Get the Z values of the 2D points from the DEM image

ArcToolbox > 3D Analyst Tools > Functional Surface > Add Surface Information

In this new windows, set the options as follow:
– Input Feature Class: your points layer (Profile_points)
– Input Surface: your DEM image (Loja17S.img)
– Output Property: check Z

– OK. Now you can see how a new column with the Z values appears in the table of your Points.

2.2. Convert 2D to 3D points

ArcToolbox > 3D Analyst Tools > 3D Features > Feature To 3D By Attribute

– Input Features: your points layer (Profile_points)
– Output Feature Class: set your name (Profile_points_3D)
– Height Field: Z
– Add this new layer (Profile_points_3D) to the map

3. Create a Profile Graph
– Be sure to activate the 3D Analyst toolbar. Then in this toolbar click on the button “Interpolate line”.
– With this tool draw your profile line. To create this line is very important taking into account the direction, left to right or right to left, it is because the Profile graph will be plotted according the direction of your draw. In this example we use the direction left to right.
– Once you have created your profile line go to the 3D Analyst toolbar and click on the Profile Graph button. Then your profile graph will appear.

4. Create a Point Profile. Now you have to create a new graph of your points
– In the 3D Analyst toolbar click on the Point Profile button.
– Draw a new line following the same length and direction of the first line, the red one (try to be precise, but do not worry if you cannot draw an identical line like the first one). To do that, give the first click at the start of the first line (the red one), the second click at the end of this line, then move the mouse pointer up or down drawing an area that include all your desired points, and finally give your third click to create the Point Profile.

5. Export Profile data to Excel. Now is time to work with the raw data of these profiles.
– Right click on the first Profile Graph > Advanced properties
– Once in this new windows, click on Data, then click on the Copy button
– Paste this data in a new Excel document.
– Do the same procedure with the second Profile Graph with the points. Then you have almost all the raw data in Excel

However, it is still lacking the names or labels of the points, in our example these are the name of cities. There are some options to perform that:
a) You can write these names manually in a new column in Excel,
b) You can get these labels directly from the its *.dbf file, it is importing the table into Excel and copying these names in a new column.
c) In this example, we create a report from the Attribute Table of your Point’s layer only with the column containing the point names (column “POBLADO”). Then, we copied this information to Excel as follow:

Right click on the layer of points > Open Attribute Table > Click on Table Options > Reports > Create Report … > Select the variable name where are your labels from Available Fields to Report Fields > Click on the button Finish > Once in the Report Viewer, click on the Copy button
– Then, Paste it in Excel and put these labels in a new column close to their respective data points. Now we have all the needed data from ArcMap into Excel.
6. Data transformation. To have a better look of your final Profile Graph is necessary first to work a little with the data. Two steps are enough, it is transforming the X values which are the length scale from m to Km, and smoothing the data of the elevation line:
6.1 Transforming length values from m to Km. It is really easy; you can do it in a new column dividing the X values per thousand. Eg. In cell D2 write: = B2/1000, then drag the fill handle down to all cells to apply this formula. The same for the length of the values of the Point Profile, in a new column it is =H2/1000
6.2 Data smoothing. It is basically to show the most important patterns of the profile line removing the fine-scale structures. In other words, it is to have a smoothed line of the elevation profile. There are many methods to perform this task, but in our example we use a simple running-average method in Excel. However, you can use any technique for your data. Here, we calculate a running-average with data sets of 100 rows. You can change this number depending of the length of your elevation profile, or according your desired smoothing strength. E.g. In an empty column, in the first row (E2) you can use this formula: =AVERAGE(C2:C101), then drag the fill handle down to all cells.

7. Graph plotting in Excel. Now is starting the best part to see your effort reflected into a Profile Elevation graph following these steps:

7.1 Plot a new Scatter (X,Y) chart.

– Select the two columns: Length (as X axis) and Elevation (as Y axis), in our example the columns D:E
– Go to Insert > Charts > Scatter with smooth lines. Here your have already the profile elevation line.

7.2 Add a new series. It is a second series with the points of your locations in the same graph.
– Right click on the graph > Select Data… >
– Click on the Add button
– Put a name in Series name: (In this case we put “Sites”)
– In “Series X values:” select the X values of the second graph (In this case the column “Length2 (Km)” =Hoja1!$I$2:$I$11 )
– In “Series Y values:” select the Y values of the second graph (In this case the column “Point Profile” =Hoja1!$J$2:$J$11)
– Now you can see a second line in your graph, but it is still not ready.
7.3 Change this second line to points.
– Select the recently added line (the orange one in our example)
– Right click > Change Series Chart Type…
– Click on the name of the second series (in this case “Sites”)
– Click on the drop down menu
– Click on the type of graph called “Scatter”
8. Add labels to the data points. Your elevation profile is almost ready but the names of your points are still missing, then follow these steps:

Select the series of points (“Sites”)
1 Go to the Design main tab > Add Chart Element > Data Labels > More Data Label Options…
2 In label Options Check “Value From Cells”
3 Select the column with the labels (=Hoja1!$K$2:$K$11)
– Uncheck “Y Value”

9. Graph retouching. Finally, we have our Elevation Profile together with the Points of your locations. However, we can improve this graph customizing the following features as we want:
– Title, legend,
– Text colour and size,
– Adding axis titles,
– Customizing the profile line with a colour gradient (in this case we changed the colour of the profile line from orange to green), removing the grid, etc.
And finally we have our fancy Elevation Profile ready to publication:

Also you can add it on your map to show a good presentation.

A collaboration of Darwin Pucha-Cofrep, 2016. Here you can see a real example of this Elevation Profile.