RCaller: Orphan Well Scenario

Scenario

You have been asked by a land reclamation company to determine which orphan oil wells should be decommissioned and removed. An orphan well is a well that is abandoned by the oil company or has been inactive for more than 12 months. You will need to create a scatterplot to show the overall production status of all the orphan wells in Alberta. To create the scatterplot, we will be using a R script and the RCaller transformer to incorporate this plotting functionality into our FME Workspace. Then you’ll need to determine which of the sites have the lowest overall production (oil, gas, and water) using the RCaller to calculate the statistics. We are using a R script to consolidate the process over using multiple transformers. The R Script can additionally be used by other clients who do not have access to FME. Then using the Surface Location, determine where each site is located and create a Shapefile for the land crew to use.

Step-by-step Instructions

Part 1: Create a scatter plot using R

1. Add Well Licenses Excel File as a Reader

In a blank workspace, add an Excel Reader to read the AER-licence-details-report.xlsx file. Open up the parameters and confirm the Attribute Types are set correctly. (ie. Company=char, Final Total Depth, m = number, Last Volumetric Activity Date = date).

2. Clean up the attribute names using the AttributeRenamer

The attribute names that came with the Well Licences file are a little bit longer with special characters, to call them easier in R later on, change them to the following using the AttributeRenamer:

3. Add an RCaller transformer

To create a scatterplot to overview our data in FME, we will use an R Script. Add an RCaller transformer to the workspace, connecting it to the AttributeRenamer Output port. Connecting the RCaller will add a port named Output.

In the parameters for the RCaller, under inputs, rename Output to wells to make it easier and more memorable to call. Under inputs, click on the columns box then the […]. In the Edit Attributes window, remove all of the attributes except for:

In the R Script box below the green comments, add the following:

For Windows:

png(filename="C:\\Users\\YourUserName\\Documents\\OrphanPlot.png")
plot(wells)
dev.off()
fmeOutput <- data.frame(1)

For Mac/Linux:

png(filename="/Users/YourUserName/Documents/OrphanPlot.png")
plot(wells)
dev.off()
fmeOutput <- data.frame(1)

Then for Attributes to Expose, type pathname

4. Add a FeatureReader transformer

Add the FeatureReader transformer to the output port of the RCaller. In the parameters, set the Format to PNG (Portable Network Graphics) and then set the Dataset to the file name you used in the RCaller, just without the quotations.

For Windows:

C:\Users\YourUserName\Documents\OrphanPlot.png

For Mac/Linux:

/Users/YourUserName/Documents/OrphanPlot.png

Add an Inspector to the <Generic> Output port and run the workspace.

Scatterplot output in PNG format

Part 2: Filter Data and Create a File for GPS

1. Test for Null or Missing values and create a spreadsheet

Connect a Tester transformer to the AttributeRenamer to test for Null or Missing Values. Looking at the spreadsheet in Excel, if Last Annual Oil Prod, m3 (OilProduction) is missing a value, then most of the data will be missing as well. Since this will skew our results, let's remove the rows with missing values and create a spreadsheet to research later why the values are missing. So in the Tester set Left Value to the OilProduction attribute, and Operator to Attribute has a value. Then connect an Excel Writer to the Failed Output port, name it Inspect Oil Wells.

2. Add a second RCaller

To reduce the number of transformers required in this translation, we will use an R Script to consolidate our workflow. Additionally, using an R Script also allows us to pass this script on to other clients who might not have access to FME.

Add another RCaller to the Passed Output port on the Tester. Change the table name to wells and remove the LicenceStatus, DepositName, UWI and LastActiveDate columns, set the correct Type if it has defaulted to varchar.

Please enter the following R snippets into the RCaller.

There is a section of copyright information at the bottom of the spreadsheet, we will need to remove it so we can do a proper analysis.

#Removes the copyright information from the bottom of the spreadsheet to allow for proper analysis
wells <- wells[-c(2127:2133, drop=FALSE),]

To determine which well has the least amount of production overall (oil, gas and water) we will need to add the OilProduction, GasProduction and WaterProduction columns together and put the total into a new variable called TotalProduction.

#Create a new variable called TotalProduction, which adds oil, gas and water production for each well
TotalProduction <- wells$OilProduction +  wells$GasProduction + wells$WaterProduction

Now that we have created a new variable we will need to add it as a column to our wells dataset.

#Adds the new variable to the wells dataset and create a new column called TotalProduction
wells$TotalProduction <- TotalProduction

We are interested to know the TotalProduction for each of the areas so we will have to create an aggregated list. If you are interested in viewing the aggregated list results, you will have to open up the in RStudio.

#Combines(sums) the TotalProduction for each AreaName to create an aggregated list
listFieldName <- aggregate(wells$TotalProduction, by=list(FieldName=wells$AreaName), FUN=sum)

After looking at the aggregated list (in RStudio) we have decided that any wells with a TotalProduction less than 10 should be removed.

#Creates a new variable called Remove, where all wells with a TotalProduction less than 10 will be removed. 
Remove <- wells[wells$TotalProduction < 10,]

This line is specific to the RCaller transformer. Viewing the Remove data frame we will only see the wells that we had filtered out.

#Sets the fmeOutput to the Remove variable. This variable will only show the wells that we filtered out in the previous line. 
fmeOutput<-data.frame(Remove)

Output Attributes
For Attributes to Expose enter AreaName, Company, SurfaceLocation, Total Depth, GasProduction, OilProduction, GasProduction, WaterProduction and TotalProduction

Connect an Inspector to the Output port on the RCaller and run the translation, to ensure your R code is correct. If you get a red error message in the log, double check your code syntax. If you are still getting error messages, comment out all of the lines using the #, then run each line one at a time, double checking that your attributes were renamed correctly.

3. Sort the output

This transformer is completely optional. Add the Sorter transformer after the RCaller and sort by AreaName Alphabetic Ascending and then TotalProduction Numeric Descending. This will allow you to see the results of the RCaller in FME Data Inspector in an ordered fashion.

4. Add the reference grid

We have acquired a reference grid to merge with our spreadsheet. This one in particular references the Alberta Township Survey System (ATS). Merging our data to this spreadsheet we will be able to extract the locations of each of our orphan wells and display them on a map for the land crew to reference. Add the V4-1_LSD.shp file to the workspace using a Shapefile Reader. This file is very large as it contains all the Legal Subdivisions in province of Alberta. View it in Data Inspector to take note of the attribute names: LS (Legal Subdivision or LSD), SEC (Section), TWP (Township), RGE (Range), M (Meridian). We will come back to this grid in a moment.

5. Prepare data to merge with reference grid

We now need to prepeare our orphan well data to be able to merge it to the reference grid. Looking at the aer-licence-details-report.xlsx file there is an attribute called Surface Location. This string of numbers and letters refers to a location on a township and range reference grid of the ATS. In order to merge this with the grid shapefile, we will need to split Surface Location into its sections.

Connect an AttributeSplitter to the Sorter. Then using the AttributeSplitter transformer we will split SurfaceLocation with - being the delimiter. This will create a list containing each part. We will need to expose the lists. On the AttributeSplitter, under Output, a new attribute called _list{} was created. Right click on _list{} and click Expose Elements.

Then in Select List Elements window next to _list type 0-3 . This will expose the lists containing LSD, SEC, TWP, RGE and M. RGE and M didn’t have a - between them, so we will have to split them differently.

6. Extract RGE and M

Add a SubstringExtractor transformer and choose the Source String as _list{3}. This value contains RGE and M. Since we want to extract RGE set the start index to 0 and the end index to 1. Then for the Result Attribute call it RGE.

7. Extract direction from M

If you look at the grid shapefile again, for the M attribute, there is no directional coordinate associated. We will need to separate this out of our SurfaceLocation.

Add an additional SubstringExtractor. Using _list{3} as the Source String again, the Start and End index is 3, and the Result Attribute is M. This will only take the number and not the letter.

8. Rename the remaining _list{} attributes

Add an AttributeManager, rename _list{0} to LSD, _list{1} to SEC, _list{2} to TWP. Then remove _list{3} because we have already extracted everything we need from it. Don’t remove _list{} because _list{0-3} are still associated with it, but it won’t appear in the final output.

9. Add an AttributeRemover

Moving back to the reference grid, the final results will contain an attribute called RA, which contains data about right of way and land use types. For our final output this information isn't necessary and will duplicate our output. We will just remove it. Add an AttributeRemover transformer to the V4-1_LSD.shp and in the parameters for Attributes to Remove select RA.

10. Merge the Wells Excel File with the Reference Grid Shapefile

Add the FeatureMerger transformer, connecting the AttributeManager from the Well Licences to the Supplier Input port, and the AttributeRemover from the Reference Grid to the Requestor Input port. Then match the Requestor and Suppliers to each other. M to M, TWP to TWP, etc. Just be aware that the LSD matches to LS from the reference grid.

11. Add a Shapefile writer

The final step is to write the orphan wells to a shapefile for the field technicians to use on their GPS devices. Once the Shapefile Writer has been added, go into the parameters and set the Geometry to shape_polygon. Save and run the workspace. The workspace takes a while to run, so go grab a coffee and come back to see the results.

Overview of entire workspace

Data Attribution

ATS V4.1 Polygons (V4-1_LSD.shp) from AltaLIS:

The digital data is protected under copyright to the Government of Alberta with all rights reserved and is licensed under the Province of Alberta’s ‘Open Government Licence’ https://open.alberta.ca/licence

AER_Licence_Details_Report.xslx from the Orphan Well Association:

The Orphan Well Association or OWA makes no representation, warranties, or guarantees, expressed or implied, for the fitness of the data with respect to its use. The OWA is not responsible for any costs incurred from use of the information in this list.

The AER Licence Details Report includes multiple events for individual wells in order to report production. Therefore there will be more entries than number of wells. Users of this list are advised that it does not necessarily reflect the current inventory of orphan wells held by the OWA and users are responsible for confirming any information used.