Gamma Log Clean-Up for Report

Hello,

I am looking for a way to either clean a large set of gamma ray data for input into a gint log or for a way to apply some sort of best fit line into a log. I understand this can be done in the graph report design but I'm looking to do it in the actual log report design in a plot vs. depth graph.

 Trying to get this (left) to look like this (right)

  • Yes, Phil is correct in that doing lots of calculations on the fly during report generation can significantly increase the generation time.  I decided to put together a little example to determine just how much. I created some random data in a table called GEOLOG.  It had a key set of PointID, depth.  I created a record at every 0.01 ft for 30 feet = 3000 records. I added a random value to each record in a field called Gamma using a random number generator. To my existing fence template I added 4 graph columns, one that plotted the raw data and 3 that used different methods to filter the data.  The result is shown in the following picture.

    This took about 7 minutes to generate a preview (yes 7 minutes).  I then deleted all but one of the graphs in turn and timed how long it took to generate.  Each graph is identical except for the output data expression used.  I will discuss each below.

    Raw data

    The expression used was simply

    <<GEOLOG.Gamma>>

    This took 5 seconds to generate a preview.

    As expected it shows all 3000 points with no filtering and gets pretty conjested

    3 Point Running Average

    The expression used was

    <<Calc(_
      (<<Lookup(<<GEOLOG.Gamma>>,<<GEOLOG.Depth>>=<<PrevDepth(<<GEOLOG.Depth>>)>>)>>+_
      <<GEOLOG.Gamma>>+_
      <<Lookup(<<GEOLOG.Gamma>>,<<GEOLOG.Depth>>=<<NextDepth(<<GEOLOG.Depth>>)>>)>>)_
      /3_
    )>>

    This creates a running average of 3 data points using the prevdepth and nextdepth functions.  It still plots all 3000 points but the data is filtered to eliminate some of ups and downs.

    This took 3 minutes 40 seconds to generate a preview.

    Plot every 10th value only

    The expresion used was

    <<IIf(_
     <<Calc(_
      <<Int(<<RoundTo(<<Calc(<<GEOLOG.Depth>>/(10*(<<NextDepth(<<GEOLOG.Depth>>)>>-<<GEOLOG.Depth>>)))>>,0.01)>>)>>_
        =<<RoundTo(<<Calc(<<GEOLOG.Depth>>/(10*(<<NextDepth(<<GEOLOG.Depth>>)>>-<<GEOLOG.Depth>>)))>>,0.01)>> )>>,_
      <<GEOLOG.Gamma>>_
    )>>

    This uses an if function to only plot every 10th value. Note that it is dependent on the the depths being at even intervals. the 10 value is hardwired into the code but you could add a report time variable to select this at report time.  Note that the result is "cleaner" but can be misleading because the selection of every10th point is arbitrary and you could easily select all high or all low values.

    This took about 3 minutes and 10 seconds to generate a preview.

    SQL average

    The expression used was

    <<Sql(_
      Select Avg([GEOLOG].[Gamma]) _
      FROM [GEOLOG] _
      WHERE [GEOLOG].[PointID] = <<CurrSetKey>> _
      AND [GEOLOG].[Depth] > (<<GEOLOG.Depth>> - 0.05) _
      AND [GEOLOG].[Depth] < (<<GEOLOG.Depth>> + 0.05)_
    )>>

    This uses an SQL function to extract a running average based on a depth range before and after the current depth.  In this case the depth range was set to +/- 0.05 feet which essentially averages about 10 values for this data. In this example the depth range was hardwired into the expression but it could easily be set up as a print time variable or a fixed variable in the point table. the range over which you average determines the smoothness of the line.  If I set the range to +/- 1 foot, I got a nearly straight line which confirms the randomness of my random number generator. It still plots all 3000 points but peaks and valleys are greatly smoothed out depending on the range selected for the average.

    Surprisingly, this took only 15 seconds to generate which isn't bad.

    Summary

    If you want to do this on the fly using an SQL function is the way to go. You can set it up with a variable to control the amount of smoothing. Note it is possible to over smooth the data.

    There are many other ways to filter the data.  You can pre-filter before even importing into gINT using excel or other dedicated data processing applications.  You can use gINT rules as Phil has suggested. However remember that filtered data may present misleading results. Also remember that once data is filtered, it can not be unfiltered or refiltered differently. This is why I prefer to only store raw data and process it at output time.

  • Doing calcs like this at output time for thousands of rows will make the report very slow to produce.

    Phil Wade
    Datgel
    Bentley Channel Partner and Developer Partner
    E: phil.wade@datgel.com | T: +61 2 8202 8600 & +65 6631 9780

    Get the most out of gINT with Datgel Tools.

  • If you are looking to smooth the data, you could do a running average on the fly using something like the following.

    I am assuming your data is in a table called GammaLog with fields of Depth and Reading.

    The plotted value expression in the graph would be something like

    <<Calc((<<LookUp(<<GammaLog.Reading>>,<<GammaLog.Depth>>=<<PrevDepth(<<GammaLog.Depth>>)>>)>>+<<GammaLog.Reading>>+<<LookUp(<<GammaLog.Reading>>,<<GammaLog.Depth>>=<<NextDepth(<<GammaLog.Depth>>)>>)>>)/3)>>

    This will calculate the running average of the current value, the previous value and the next vale which will smooth out the data.  You could add weighting values to each reading to center weight the average or produce other effects. If you want to include more values in the running average, you will have to use an Sql function to extract the average of a desired depth range (based on the current depth + and - a range value).

    If you are looking to just eliminate some of the data points you would have to write a condition into the output expression based on the sampling interval (assuming that the interval is a constant value). This would look something like

    <<IIf(_

        <<Int(<<GammaLog.Depth>>/(5 * (<<NextDepth(<<GammaLog.Depth>>)>> - <<GammaLog.Depth>>)))>>_

        =_

        <<GammaLog.Depth>>/(5 * (<<NextDepth(<<GammaLog.Depth>>)>> - <<GammaLog.Depth>>)),_

        <<GammaLog.Reading>>_

    )>>

    This will only output every 5th value. You may have to include some rounding functions to account for small variations in sampling intervals but you can see the idea. Note that outputting every 5th value may produce misleading results so you may want to combine this with a running average.

    Please note, I have not tested these expressions and it is highly likely that I have a syntax error or have forgotten something.  You will have to adapt to your particular data structure and test but it will give you a start.

  • You'd need to make a new column in your database and calculate the moving average using gINT Rules.

    Phil Wade
    Datgel
    Bentley Channel Partner and Developer Partner
    E: phil.wade@datgel.com | T: +61 2 8202 8600 & +65 6631 9780

    Get the most out of gINT with Datgel Tools.