4. Aggregating the stops and route data - ScandinavianSection-UCLA/hGIS_ETK GitHub Wiki

The way the data is organized is two shapefiles for each fieldtrip, one for the route and one for the stops connected by the route. In addition to the data for the separate fieldtrips, aggregating the data in different ways allow for a wider range of analysis and ways to see to see trends in the data (see #Data Analysis). In the aggregate, we want to keep a sense of the order of the data by creating a new field in the fieldtrips stop shapefile.

4.1 absolute stop order

  1. Each fieldtrip gets a new field called “absolute” added to its attribute table. It is a numeric field. Using the “field calculator” in the attribute table (right-click on field heading), the same number is added for each record in the fieldtrip: the first fieldtrip he was on will be assigned the number “1”, the second “2” etc. It should be noted that the absolute field assignment for each fieldtrip will often differ from the fieldtrip name (“FT_XXX” – see 1. Place name extraction), since the fieldtrip names are not always in a chronological order).

  2. Use the tool “Merge” to combine all the stops segments in into an aggregated shapefile. Add a field called “abs_seq” that should be set to numeric and contain the absolute order of stops. Leave it without data for now.

  3. To get the absolute order of stops in the field abs_seq, the simplest way was to export the attribute table Excel using the tool “Table to Excel”. Excel, as well as ArcMap, has an option to do “Advanced sorting”. The option is reached by clicking on “Data” > “Sort” and in the window this interface should appear where “Add Level” allows for a second order of sorting (Excel 2013):

  1. In accordance with the above window, the data will first be sorted by the absolute fieldtrip value, then by the sequence of those stops in each individual fieldtrip. Once the data is sorted, the first few integers are added to the newly created field (1, 2, 3 etc). These digits are recognized by the Excel program to be in sequence. This means that highlighting the digits and “pulling down” until the last stop will make the program fill in the remaining digits. This is the reason Excel is convenient to use, as opposed to have to use programming in the “Field Calculator” in ArcMap.

  1. The “abs_seq” populated Excel attribute table is imported back into ArcMap using joining. Right-click on the population layer in ArcMap “Joins and Relates” > “Join” > choose “Join Attribute from Table”. Chose the join to be based on the “FID” field. All the fields will be joined, not just the new “abs_seq”, and unnecessary fields are deleted.

4.2 inter-route segments for travel between stops Each route shapefile that goes with every stop shapefile consists of one line. Splitting this line into inter-route segments to be individual lines between two stops opens for several possibilities such as animations (see 5. Visualizing the aggregated data using tracking analyst) and getting travel statistics on the different modes of transportation (see #). The splitting of the individual fieldtrip routes need to happen before an aggregate is made. With abs_seq data already being added to the merged stops shapefile, a corresponding abs_seq field can be added to the corresponding route segment.

  1. One route at the time are added to ArcMap, and have the fields “Absolute” and “abs_seq” added.

  2. An edit session is started, and the single line route is ready to be split. To start an edit session, get the editor toolbar activated by clicking on “Customize” > “Toolbars” > “Editor”.

  3. Adding the stops for the route, and label them in with the sequence field, makes it easy to identify the route segments. Right-click on the stops shapefile > “Label Features”. Right-click on the stops shapefile > “Properties” > “Labels” > choose to label the stops with the sequence field.

  1. The route is simply selected, and after clicking the split line button in the editor toolbar.

In the picture, all the records above the selected row are segments that have been broken away from the original route. The remaining part of the route, yet to be split, is the selected row. In the editor toolbar, the split line button is pushed and another line part is ready to be separated.

  1. The last step is to fill in the “Absolute” and “Abs_seq” (see Place Name Extraction) fields with data. This should match the stop which the route segment is leading up to. For instance, the first fieldtrip’s route segment will start with Abs_seq 2 (3, 4, 5 etc.). The absolute field will be assigned 1 for all the records, and the easiest way to do that is using the field calculator and simply put Absolute = 1.

  2. Once the individual routes have been split and assigned abs_seq values they can all be merged into an aggregate route shapefile.