Using multiple rows and attributes in field calculator

1625
5
08-04-2017 01:14 PM
JohnDialesandro
New Contributor II

Hi, 

I am currently trying to calculate the slope of roads using certain plugin hybrid vehicle trips. 

My data are currently a point shape file that is recorded every 1 second. The points have several attributes but the important ones are elevation at each point, and vehicle speed. I would like to calculate the slope by doing something along the lines of:  calculating slope between point at time T and T-1 as (elevation at time T – elevation at time T-1) / distance * 100 and the slope between point at T and T+1, and use the average of these two slopes as the slope of point at time T. If the vehicle speed is 45 mph (72 km/h), then the distance between each two points is 72/60/60*1000=20 meters. Unfortunately I would need to reference  the attributes in rows above and below in field calculator and I am having a lot of trouble figuring out how to do that. Is it possible in the field calculator using either python or vb script?

Thank you!

0 Kudos
5 Replies
RichardFairhurst
MVP Honored Contributor

The calculation you want to do can be done if you apply the following steps:

1. If your ObjectID sort does not match the sequential order of the trip, use the Sort tool to create a replacement feature class that matches the sequence of the trip point order.  If you must maintain the original feature class, first add a Long field to the original feature class and calculate the original non-ordered ObjectIDs into that field so they will be preserved in a new feature class, and then create a new feature class using the Sort tool.

2. Now that your ObjectIDs are sorted, add a Long field and calculate the ObjectID values plus 1 into the field ([OBJECTID] + 1).

3.  Create a copy of the feature class with feature class to feature class tool.

4. Join the original ObjectID of the first sorted feature class to the ObjectID +1 field in the copy.

5. Calculate the fields you want to using formulas that use the differences between the record you are calculating and the record joined to it, which will be the previous point in the trip sequence.

6. To make the joined feature class point to the point after the records you are calculating, recalculate the ObjectID+1 field in the copy to be OBjectID - 1.  Now your join links the records you are calculating to the data of the point that follows it.

7.  Perform the calculations you need that need data from the point after the point records you are calculating.

8. Break the join.  If you can replace the original feature class or your original feature class was already sorted correctly, you are done.  If you need to transfer the data back to the original non-ordered point feature class, do a Join on the original ObjectID to the field in the sorted feature class that holds the static copy of that ObjectID and do calculations to transfer the data from the joined records into the original and then you will be done.

Note:  Using cursors and dictionaries is much faster than using the field calculator if you have a large number of points in your trips and many fields that need to be calculated, since a cursor can operate on all of the fields in a single pass, while the field calculator will repeatedly have to do a complete pass on all of the points for each and every field you need to calculate, and join calculations are slower than loading and reading a dictionary to do the point matching and data transfer.

JohnDialesandro
New Contributor II

Richard, 

Thank you for the thorough resposne. I will give this a try!!

0 Kudos
JohnDialesandro
New Contributor II

Hi Richard, 

Thank you once again for your great insight on this issue. Would you have any pointers on using a cursor for this? I have essentially the entire road network of California, and need to calculate the slope for very road segment so a cursor method, or dictionary may be more efficient. while I have used ArcGIS desktop for many years, I unfortunately have very little experience using arcpy. 

0 Kudos
RichardFairhurst
MVP Honored Contributor

The starting point for learning about using arcpy cursors and python dictionaries is my Turbo Charging Data Manipulation with Python Cursors and Dictionaries Blog.  You also will need to search on the Python forum for examples of iterating through directory and layer lists. Getting a grasp on the Python syntax of For loops will enable you to make Python cycle through all of your data for you..  Making an honest attempt at trying to write code for your particular problem and then posting to the Python forum when you get stuck is a good way to learn Python.  There are several people here who are willing to help and point out coding errors and solutions, as long as you are making the effort to solve the problem for yourself.

JohnDialesandro
New Contributor II

Thank you Richard!!

0 Kudos