In this blog post I’ll show you how to use a data function to quickly and easily draw the wellbore lateral lines (AKA well sticks, horizontals, laterals, etc) on a map chart in Spotfire. We’ll end up with a line connecting the surface and bottomhole locations to look like this:
There are a few different ways that you can accomplish this with transformations and calculated columns, and my colleague Lucas Wood recently published an article on this recently at DataShopTalk.com. Check it out here to see his approach: https://datashoptalk.com/creating-well-sticks-using-the-spotfire-map-visualization
After looking at his solution, I said to Lucas, “Hey: I think I automate this with a data function” and he said “Cool, give it a shot and write a blog post on it.” It turns out this is possible with TERR, and best of all fast and easy to implement!
The end goal is to use the Map Chart feature called Line Connection to draw a straight line between the surface and bottomhole locations for each well. It sounds easy, but to do this we must modify the data a bit.
The dataset that I’m starting with is a well header table with one line for each well. There are other columns in the table, but these are the key ones:
- Well ID (API, UWI, etc)
- Surfacehole Longitude
- Surfacehole Latitude
- Botthomhole Longitude
- Surface Hole Latitude
If you’ve ever tried the Line Connection feature before, you probably ran into the problem about how to position both the surface and bottomhole locations on the same chart layer, and then connect the dots. The challenge is that you can only choose one column for the Lat and Long coordinates.
The trick is to build in some logic that sometimes plots both the surface and bottomohle locations on the same layer, based on the value in a “Coordinate Type” column. The problem is that we don’t have this column in our dataset, so we have to create it. We also have to add an additional row for each coordinate type, which is a bit trickier. So how do we do that? Well it’s pretty easy to add columns to Spotfire via Calculated Columns, but adding rows is a little different.
There are different ways that we could add rows to the table, but a data function is a great tool to manipulate and mashup data. The data function will basically just copy all values in the header table and append (paste) the values to itself. What we end up with is instead of 1 row for each well is 2 rows for each well – an exact copy like this:
Next, we’ll add a column to classify one set of rows as “Surface” and the other as “Bottom” locations.
Then we’ll take that new data table and use it in a Map Chart and draw a line between the surface and bottomhole locations.
The TERR script
Here’s the TERR script to generate the new table:
s <- data.frame("coordType"=c("Surface"))
surface <- cbind(sourceTable, s)
b <- data.frame("coordType"=c("Bottom"))
bottom <- cbind(sourceTable, b)
wellSticks <- rbind(surface, bottom)
Let me break it down. I mentioned we're duplicating the table rows and adding a new column. In the script, I've done it in 3 steps:
- Take the header table (sourceTable) and add a column called "coordType" with values of "Surface" for each row
- Take the header table (sourceTable) and add a column called "coordType" with values of "Bottom" for each row
- Combine these two tables into one new table
Here's a description of the key R code used:
- data.frame: A data frame is essentially a table, and in our example a table with just one column.
- <- : An assignment operator. In this case we're assigning various letters and words to different tables.
- c(..): Used to combine values into a vector
- cbind: Column Bind, used to join columns from one table to another
- rbind: Row Bind, used to append rows from one table to another
Register a Data Function
To execute this script, create a new Data Function by clicking Edit > Data Function Properties. Then click "Register New" and give it a name - I've called mine "Well Sticks". Next, paste in the TERR script from above.
Now that we have the script written out, the next step is to configure the input and output parameters. The easiest way to think about this is that Inputs are things coming from Spotfire (values, columns or tables) that you're passing into the data function, and Outputs are generated by the script (values, columns or tables) that you're sending back into Spotfire. Make sense?
This data function is pretty simple, there's just one input and one output. The input is the Well Header table, and the output is the new table generated by the data function.
Select the Input Parameters tab and click "Add". Give the parameter a name, making sure you type the name exactly as it is in the script. In our script it's called "sourceTable". The type is Table. Click the "All" button to accept all data types. Click OK.
Now select the Output Parameters tab and click "Add". Give the parameter a name, again making sure you use the exact same name as in the script. In our script it's called "wellSticks". The type is a table, so select that from the dropdown, and then click OK.
Connecting Parameters to Spotfire
Okay, now we can save and close the script editor. This will take you the Edit Parameters screen, which is where we connect the input and output parameters that we just created to something in Spotfire.
For the input, select the "sourceTable" parameter, set the Input Handler to Columns, and then choose your well header table from the dropdown. You can choose which columns to bring in. Instead of the bringing in the entire table, you just need the relevant coordinate columns & Well ID.
Now click the Output tab. Select the wellSticks parameter and set the Output Handler to a Data Table. This table doesn't exist in Spotfire yet since we haven't run the data function, so we'll need to "Create a new data table."
Then click Okay, and Spotfire will run the data function and add the new table. Sort by Well ID and notice how there's now 2 rows for each well, and their differentiated by the "coordType" column.
Configuring the Map
Now that the data is structured correctly, setting up the map is easy.
Insert a map and add a marker layer. Select the "wellSticks" table as the data source.
Marker by your Well ID column
Now for the grand finale – drawing the lateral lines! It turns out this is the easiest step – just select your Well ID column.
I've also done some additional customization by changing the marker shape and color.
I'm sure that if you've made it to the end of this post, at some point you've wanted to draw the lateral lines and tried the Line Connection feature, played with it a few different ways but just couldn't get it to work exactly right. And as you've discovered by now, the key was just changing the data structure a bit, and then it was super easy. I've learned that most things in Spotfire are much easier and work the way you want when the data is structured well. I believe it's always a good practice to learn more about your data and how to manipulate / transform it so it fits in nicely with the analytics you're trying to do.