Original Article Date: Oct 10, 2001
Take a look at the latest contribution by Mr. Juan Auces who has developed a nifty way of using data extracted with Edg and plunking it into Excel for further processing! This article is based on MicroStation V7.
I'd like to introduce Mr. Juan Auces from Parsons Brinckerhoff for this excellent article! Juan has worked as a Designer for several Houston based Civil Engineering firms, Texas DOT Roadway Design Section, and as a CADD Support Specialist for Texas DOT in the Houston District.
He recently joined Parsons Brinckerhoff, a worldwide leader in infrastructure services, as the CADD Manager for the Houston office. Having worked with MicroStation for over 16 years, he has become well versed with Edg and has developed a interesting workflow to increase production. To say the least it's an innovative way of combining his Edg expertise with Excel!
How would you like to save hundreds of hours of manually checking quantities in your civil design files? If so, then read along as I show you how to do this by combining the functionality of Edg and a spreadsheet!
The key feature of Edg is that it offers the opportunity to quantify linear elements without much user intervention. It can be considered a untapped resource, one which can streamline your design effort and allow you to extract lineal quantities from MicroStation for a variety of items such as RCP, waterlines, and gaslines.
Every design effort has design assumptions, as does this method. My first assumption is that you've used one linear element with a custom line style to place your utilities such as a 36 inch storm sewer pipe. This eliminates drawing two separate lines representing each edge of the pipe which will result in quantifying the pipe twice. Another assumption is that any cells used for items such as manholes are not dropped. This will allow you to create scripts to search for the cells based on their names. This may sound like too many requirements but even the best design software packages rely on these assumptions to accurately quantify.
To help you keep all this in check I recommend using either a menu bar or the Settings Manager. Which of these is better? Well that's a debate best left for another day, but my personal preference is the Settings Manager.
Extracting such quantities from a MicroStation design file can be done fairly easily by using Edg and a spreadsheet program such as Excel. Although most civil firms use design software such as GEOPAK Roadway, there may be cases where you're required by the client, or are using an in-house Quality Control effort, to use an alternative method to check quantities.
When faced with checking quantities manually, it often means assigning people to grab a scale, and a large cup of coffee and in complex jobs several people may be required to average out the inevitable human errors...such as the spilled coffee over the drawing.
My method sets a new standard and since it requires no keyins – typos aren't an issue. You can run your scripts in batch mode while you are out to lunch or overnight. The next day simply cut and paste and let Excel take over. To quantify lines, I use standard Edg commands to extract the required element information and then simply apply a trigonometry formula:
C=(sq rt of B sq + A sq)
To obtain the necessary results for arcs you'll need the sweep angle and arc radius which can also be extracted with Edg. The formula for calculating arc lengths is:
L= Sweep angle x Pi / 180 x radius
Let's take a look at putting this all together!...(By the way, the following information assumes that you have a working knowledge of Edg, the Select by Attributes dialog and Excel.)
Quantifying Linear Elements:Edg provides the high and low ranges of the elements which in turn are the required values for calculating quantities. For example, in Auto_Qty_Line.dgn you'll find ten randomly drawn lines with a total length of 55 feet.
Open the file with Edg and use the following script to extract the ranges to a journal file:
The resulting journal file is an ASCII text file containing the results of the above Edg script. A snippet Edg's results are shown below:
To use the contents of Auto_Qty_Line.JNL, open an empty MicroStation design file and select File > Import > Text to import the JNL file as text into the design file. Of course, you could also just copy and paste from the open journal file into MicroStation via the system clipboard. (If you're using the supplied file, you can use it "as is" or simply rename it to have the extension of JNL) In any event, the resulting element will be one large piece of multi-line text which will need to be dropped before proceeding further. Use the Drop Element tool and adjust the settings so that only Complex is turned on and identify the text. You will now be able to use the Select by Attributes to create a Selection Set of those pieces of text that contain the element ranges.
From the Select by Attributes dialog, ensure that you're only search for Text elements that contain a comma in the string and click Execute. Only those lines of text that contain a comma will be part of your Selection Set and can now be copied into the system clipboard via Edit > Copy.
Open Excel and paste the data into a fresh worksheet. Since the data ends up in one column, you will need to segment the ranges by running Data > Convert Text to Columns. Place the ranges in column A and B and add the following formulas to columns C, D, and E.
Note that you may need to adjust the value of 1000 since it's based on working units. Using copy and paste, add these formulas to every other line, since each elements ranges require two rows in the spreadsheet. You may want to refer to Auto_Qty_Line.XLS which has been prepared for you. Once downloaded, just change the extension to XLS and away you go.
Now you can use the standard autosum to total the lines in column E. The result is 55 feet which matches the total linear length of the elements in the design file.
Quantifying Arc Lengths:
To quantify the length of arcs use these steps, and remember that we only need the arcs sweep angle and radius to complete this task.
For practice, download and take a peek at Auto_Qty_Arc.dgn which contains ten arcs with various sweep angles and radiuses.
Run Edg to extract the arc data using the following script:
A snippet of the resulting journal file is shown below:
Import the journal file into a design file and using the Select by Attributes utility, extract the text that contains the Primary Axis and the Sweep Angle:
This information can then be pasted into columns A and B respectively of an open Excel spreadsheet. Again, you will need to run the Data > Text to Columns utility to spit the data up. Once columns A and B are populated, add the following formulas to columns B, D and E:
Note that the formula in column B is simply converting the extracted value to the proper working units. You may need to adjust this value to something slightly different. Please refer to the prepared Excel file...just be certain to change the extension to XLS prior to using it.
To further expand on Edg's versatility, you can use it to quantify items that are not in a linear or arc format. Lets say you want to quantify the amount of traffic loop detector cells placed in a ten mile roadway project. Using Edg you would search for all cells with a particular name and Edg will find them even if the user placed them by error on different levels. The journal file will show how many cells were found, and will enable you to then modify the symbology of the cell so that it's highly visible. When you re-open the design file, a quick visual scan of the graphics will let you know if you missed any cells or if a cell was dropped and subsequently not quantified.
That's it for me…hope you've found this article of interest that that it's inspired you to incorporate Edg into your workflows.
AskInga Article #85