Import Pile Plans Command

D_ImportPilePlan_600ppi   Import Pile Plans

Command Licensing and Default Menu Location

  • The Import Pile Plan command is part of the RPS Tool Shed Toolbox
  • The command is located on the Tool Shed Toolbox menu ribbon
  • The command is located in the Piling menu group

Command Description

The Import Pile Plan command provides the ability to import Design Pile / pile plan data, As built pile data and QA survey point data into a TBC project from Microsoft Excel spreadsheets using a custom import definition template for each type of information.

The imported data can then be analyzed using the Piling QA Report command.

The piles / pile plans created by the command can be directly used in Trimble Groundworks for Solar Farm projects.

Video Demonstration

The following video shows how to utilize the Import Pile Plan command

Use Case Videos

The following videos show the use of the Import Pile Plan command in a work process context

Command Interface Description

The Import Pile Plans command dialog looks as follows

The command supports three stages of data import

  1. Importing Design Pile information from a Microsoft Excel spreadsheet (*.xlsx)
  2. Importing Machine As Built Pile information from a Microsoft Excel spreadsheet (*.xlsx)
  3. Importing Survey QA data from a Microsoft Excel spreadsheet (*.xlsx)

Step 1

Creates the Pile Plan(s) and the Design Piles for each pile plan.

Step 2

Creates the As Built Piles for each of the Design Piles based on the machine data captured at the time of piling.

Step 3

Imports a pair of points measured using a Total Station to capture the final elevation and twist of each of the piles. These points are correlated to the As Built Piles and are used to adjust the As Built piles to the final QA position, elevation and orientation. It is assumed that the Survey QA data is more accurate than the Machine As Built data.

Step 4

The imported and adjusted can then be used with the Pile Quality Report command to generate a piling quality report of the As Built piles vs the Design piles for the project.

Select the data process stage that you are executing by checking the check box associated with the process step that you are executing.

Once selected, select the filename / path to the source file for the process step (Design, AsBuilt or QA Survey Points.

Select the Template definition that you want to use to process the spreadsheet of data.

If you want to check the template or define a new template, click the Manage button associated with the process step. Clicking the Manage button will open the RPS Settings dialog for the Piling Settings for that process step e.g. Design piles, As-built piles, QA Data, Report. The RPS Settings dialog for Piling settings has 4 tabs, one for each process step.

RPS Settings - Piling Settings

Design Piles

The Template defines the import structure for the source data excel spreadsheet (.xlsx file) through which data columns in the spreadsheet are mapped to pile plan and pile property fields that are then used to import and create the pile data in the TBC project.

Use the icons to the right of the template selector to

  • Create a new template
  • Copy a template
  • Edit a template
  • Save a template
  • Delete a template

Use the template selector to select a previously defined design pile import template. Once selected you can edit the template by selecting the pencil icon.

Template name:
Enter or change the name for the template definition here

Column map:
The column map provides a predefined list of pile and pile plan properties that are currently supported for import of Design Piles.

For each property you can select the column of the spreadsheet in which that property data item will be found.

The Unit column shows the currently selected unit for the data being imported. You can change the unit by clicking the field once to activate the field and then use the pull down selector to change the unit to a different unit e.g. Foot to Meter etc.

If the Property has a Default value that you want to assign in the absence of the field being available in the source data file. you can enter it into the Default value column e.g. for a Default Rotation value of 0 degrees, 0 minutes, 0.00 seconds you can enter 0°00’00.0".

Supported Properties include the following

  • Rotation - this is the rotation of the pile from North (normally 0 degrees for Solar Farm work)
  • Orientation - this is the Orientation of inclination angle for raked / sloping piles (normally 0 degrees)
  • Inclination - this is the inclination of the pile from vertical (normally 0)
  • Notes - this is a field that can be used to e.g. identify the pile use (Motor Pile, Desd End Pile etc.)
  • Pile plan name - Piles are grouped into Pile Plans (Solar Blocks)
  • Pile name - Piles have a unique identifier
  • Pile definition name - Piles have a pile definition (usually a color) that defines the material, length, width and height and minimum embedment depth values. The data item here needs to match exactly a Pile Definition defined within the Pile Definition Manager. (See Pile Definition Manager Command for more details).
  • Design location - Northing
  • Design location - Easting
  • Design location - Elevation

Adjustment rules:
If some of the piles need adjustment from the provided design values based on information provided in the source data file e.g. Dead End Piles require a rotation of 90 degrees from design orientation and may need a height adjustment of e.g. -0.5’ so that they do not catch on the solar panels as they are rotated by the motor, these adjustments can be defined here. Use the icons to the right of the adjustment area to

  • Add an adjustment
  • Copy an adjustment
  • Delete an adjustment

For each adjustment rule, you will

  • Name the adjustment e.g. Pile Rotation 90 Degrees
  • Select the pile property to be adjusted e.g. Rotation, Design location - Elevation
  • Select the column in which the “Value” that will be used to trigger the adjustment can be found e.g. If the words “Dead End” can be found in a text string located in Column W will trigger a pile rotation and pile elevation adjustment, select column W here.
  • Select the comparison function that you want to use to identify the “Value” used for the adjustment e.g. Equals or Contains etc.
  • Enter the “Value” to use e.g. Dead End
  • Enter the Formula to use to compute the adjustment e.g. {value}+90.00 for a rotation of 90 degrees

For example to apply a rotation of 90 degrees to piles that have the words “Dead End” in a text string located in column W use the following entries

Name = Rotate Pile by 90 Degrees
Property = Rotation
Column = W
Comparison = Contains
Value = Dead End
Formula = {Value}+90.0

For example to apply an Elevation adjustment to the Design Elevation of -1’ to Dead End piles identified by the words “Dead End” in a text string in column W use the following entries

Name = Change Pile Elevation by -1.0’
Property = Design location - Elevation
Column = W
Comparison = Contains
Value = Dead End
Formula = {Value}-1.0

Sample data file:
To develop and test a template you will need to select an example data file. The data file will be an Excel spreadsheet file (*.xlsx). You can select a file and edit the source file (in Excel) using the icons to the right of the file name and path selection. If you right click in the file name field you can Browse your PC, start browsing in the Project Data folder or in the RPS Settings folder. You can also copy the selected filename and path to the clipboard for use in Windows File Explorer. Once a file has been selected, it will be displayed in the preview pane below the file selector.

If the file was last used in Excel, and the work sheet currently in use was other than the source data tab of the spreadsheet, you will need to edit the spreadsheet, select the worksheet in which the source data can be found and then save the spreadsheet with that work sheet selected - the import template only works on the currently selected work sheet.

You can now review the columns in which the data properties of the piles and pile plans are found, and create the mapping for the template.

You can resize the Import Pile Plan Template Editor dialog to suit the work that you are doing here.

Click OK to complete the edits and save the Template definition.

Click Cancel to close the template editor without saving the changes.

Add points
In the RPS Settings dialog for Design Piles, you can elect to create 3D points at the Top of Pile and at the Bottom of Pile. These selections will create a 3D point at the Design Top and Design Bottom of the piles that are created. These points can be placed on a layer of your choice, made via the layer selector. You can create new layers and apply layer list filters using the New Layer controls at the right side of the layer selector.

Duplicate options:
The Replace and Skip options define what happens if you import a second or subsequent file of data that contains duplicate data to that already imported.

The Replace option indicates that the second or subsequent data will replace the previously imported data.

the Skip option indicates that the second or subsequent data will be ignored and skipped over leaving the previously imported data in place.

Once all of the Design Pile settings have been defined you can select the As-built Piles tab, the QA data tab or the Report tab of the dialog to define those settings.

You can create as many Template definitions as needed to handle the variations of source data that you encounter.

As-built Piles

The As-built Piles Template definition follows the same pattern as the Design Piles Template definition. The RPS Settings dialog looks as follows

The Template Editor for As-built Piles is almost identical to that of the Design Piles. The only differences are as follows

Properties for As-built Piles include the following additional properties

  • Date - This is the date on which the pile was placed
  • Machine ID - This is the identifier of the machine that placed the pile
  • Start and End Northing, Easting and Elevation values - these are used to determine the pile inclination and orientation and the pile top elevation after piling has been completed.

The Machine ID and Date are used in all Reported QA results. These values can help diagnose systematic problems relating to a specific machine on specific dates and times if necessary.

The As-built Pile import does not have any adjustment rules.

Duplicate options:
The Replace and Skip options define what happens if you import a second or subsequent file of data that contains duplicate data to that already imported.

The Replace option indicates that the second or subsequent data will replace the previously imported data.

the Skip option indicates that the second or subsequent data will be ignored and skipped over leaving the previously imported data in place.

Distance tolerance:
The Distance tolerance is used to determine which As-built Pile belongs to which Design Pile. If you enter a value of 1.0’ then if an As-built Pile is within 1.0’ of a Design Pile it is assumed to be the same pile. While piles have a unique Pile ID that should match, the distance tolerance check will allow TBC to match up the Design and As-built Piles. In TBC the Design and As-built Piles are stored on the same object. The QA Report relies on the Design and As-built Piles being stored in this manner.

If your Design Piles have a unique ID but the Machine As Built Pile IDs do not match, set the Pile ID field in the import template for the Machine data to None, this forces the system to use proximity of the As-built pile to the Design pile location to create matched pairs.

Plumb tolerance:
The Plumb tolerance is used to check the As-built Piles during import. Any pile that is “Out of Plumb” based on the imported data will be flagged on the import report of the Import Pile Plan command dialog.

QA Data

The QA Data for a pile will include a pair of points that have been measured using e.g. a Total Station to a prism placed directly above the two ends of the pile web. Because the QA points are being measured using a Total Station, the accuracy of the measurements should be greater than that captured using the GPS on the machine control system. The QA points will therefore be used to replace the machine captured values for position, elevation and twist / rotation of the As-built Piles.

The QA points will be used to determine the As-built Northing and Easting value for the pile. These values will be used to adjust the As-built piles position. The average Northing and Easting value of the two QA points will be computed to define the As-built Pile location.

The As-built Elevation captured by the machine will be replaced by the average elevation of the two Survey QA values and the As-built Pile will be adjusted accordingly.

The Pile Rotation (Twist) computed from the two survey points will rotate the As Built pile created from the machine As-built data.

The As-built Piles will therefore be a combined result of the machine captured data and the values derived from the Survey QA points.

The Survey QA point data will be imported from an Excel Spreadsheet. The format of the spreadsheet will include the Pile ID (Optional) and the two points Northing, Easting and Elevation values. Each pair of points can be on a single row of data in the spreadsheet or on separate rows of the spreadsheet. Where the points are on separate rows of the spreadsheet, define the import template to have just one point (Point 1) per line. Where there are two points per data line in the spreadsheet, define both Point 1 and Point 2 in the import template for the QA data.

The QA Survey Point Import template is defined in the same way that you defined the templates for the Design and As-built Pile source data imports. The Survey QA points will also be verified as belonging to the Design / As-built Pile locations using the nearest pile to the survey QA points.

Data Layer:
Select the layer onto which the Survey QA points will be placed during import e.g. PILE - Survey QA Points layer. You can create New layers and use the layer list filter controls tio the right of the layer selector as required.

Height adjustment:
If you measured the top of the pile using a target height of 0 (total station measurements) but were using e.g. a mini prism with a small target height of say 0.25’ then you can enter -0.25’ as a height adjustment in the settings for the QA data. This will reduce the computed elevation of all QA points by 0.25’ to correct the elevations to the top of pile.

Report Settings

Once the Design, As-built and Survey QA data has been imported, you will be able to run a QA report on selections of the imported data. The combination of the Design, As-built and Survey QA data will be used to report on 6 different Quality metrics for each pile.

The metrics are as follows

  • Delta North - This is the delta between the Design and As-built pile in the N-S direction (along a solar panel table axis)
  • Delta East - This is the delta between the Design and As-built pile in the W-E direction (perpendicular to the axis of a solar panel table)
  • Delta Elevation - This is the delta between the Design and As-built Pile elevation values
  • Delta Vertical - This is the delta between the As-built piles elevation and a slope line between the top of the first to the top of the last As-built pile in each solar panel table. All the pile tops in a single solar panel table should be aligned to a plane that runs from the first to last pile in a solar panel table.
  • Twist - This is the delta between the Design and As-built Pile’s rotation value
  • Dip - This is the delta between the Design and As-built Pile’s verticality / inclination check
  • Embedment - This is the check that ensures that the As-built Pile meets the Minimum Embedment Value for that piles Pile Type definition. Each Pile definition (Black, Blue, Red, Yellow etc.) has a Minimum Embedment expectation

The tolerances for each quality metric are defined in a template. You can create as many templates as needed. Use the icons to the right of the Template selector to Create, Copy, Edit, Save and Delete template definitions.

All tolerance values are defined in the same way. Tolerances have an In tolerance (Above and Below 0), Near tolerance (High and Low) and Out of tolerance (High and Low).

In Tolerance is the band between the Lower Near and Upper Near tolerance values. i.e. if you define the Lower Near value as -0.1’ and the Upper Near value as +0.1’ the In Tolerance values will be those that fall between +/- 0.1’

Near Tolerance is the band between the Lower Out and Lower Near or the Upper Near and Upper Out values.

Out of Tolerance is the band above the Upper Out or below the Lower Out values.

Embedment depth - Compute depth from surface checkbox:
The pile embedment depth is computed by default from the As-built Pile source data Start N,E,Z and End N,E,Z values. The difference between the two coordinates defines the embedment depth value for each pile. You can override the default approach using this checkbox to use a surface model in the calculations. When checked, when you run the QA Report you will be asked to select the Surface Model to use in the computations e.g. n Existing Ground surface model. In that case, the Top of Pile Elevation, the Pile Length (as defined by the Pile Definition Manager for the Pile Type (Black, Brown, Red etc.) and the surface elevation are used to determine the embedment depth as follows

Top of Pile Elevation - Pile Length = Bottom of Pile Elevation
Surface Elevation - Bottom of Pile Elevation = Embedment Depth
Top of Pile Elevation - Surface Elevation = Reveal Height of Pile
Reveal Height + Embedment Depth = Length of Pile

Color Settings:
You can define colors that will be used in the report pages for the In Tolerance, Near Tolerance, Out of Tolerance and Incomplete Pile Definition. Each pile quality metric will be placed in a colored cell in Excel where the color represents the tolerance status of the metric being analyzed.

An incomplete pile is one where the Design Pile, As-built Pile or Survey QA data is missing so that the Quality Metrics cannot be determined.

Build Sample QA Data button:
This is a test tool only and will be removed from the final dialog once testing and QA has been completed. The button triggers the generation of some example QA points in a file for all of the Design Piles that have been imported. The point data is “dithered” to create errors in the points that introduce artificial twists and elevation deltas to the As-built piles. The file that is created is stored in the RPS folder in the Project Data folder for the current project. You can import the file using the Import QA data function of the Import Pile Plan command.

Command Tips
The Command Tips area of the dialog provides helpful tips on command use that supplement the help system accessed using the F1 key.

Header Commands

In the header bar of the command you will find command icons that link you to other commands that you may need access to while using this command. In this command the following commands are linked

  • Help - this document access
  • RPS Settings
  • Takeoff Lines
  • Smart Edit
  • Adjust Linestring Elevation
  • Pile Definition Manager
  • Pile QA Report
  • Create Surface
  • Add / Remove Surface Members
  • Create Surface Edge Breakline
  • Add / Remove Surface Boundaries

Executes the command with the current settings and readies itself for a second or subsequent import process.

Closes the command without further execution.

Feedback and Enhancement Requests

If you would like to provide feedback on the use of the Import Pile Plan command or to request enhancements or improvements to the command please click Reply below.