How do you connect SHL and BHL through an Excel import?

CarrieAlison · ‎04-12-2016

I'm using Arc Map 10.4. I have the SHL (surface hole location) and BHL (Bottom hole location) for a series of wells I need to post on a map. I need to draw a connecting line between the SHL and BHL. What tool do I use to do this? Instructions? I can't seem to find a video or instruction on how to do this.

curtvprice · ‎04-12-2016

There is no out of the box tool to do this.

Seems to me the best approach would be to

1) Plot the two sets of wells from Excel on the map using the table to XY event (the tool or in ArcMap by right clicking the table view)

2) Copy the two point feature classes to GIS data sets (recommend: file GDB). If they are in lat long, make sure the output is in a projected coordinate system where distances make sense, like UTM or Albers. Also make sure the two feature classes have a common ID, like WELL_ID, so you can match each SHL to its corresponding SHL point.

3) Run the AddXY tool to add X_COORD and Y_COORD to both point feature classes.

4) Run Copy Rows to make two tables one from each point feature class.

5) Merge these two tables together (Merge tool).

6) Run the Points To Polylines tool. Specify well ID as the group field. This should make your lines.

CarrieAlison · ‎04-13-2016

Arc kept crashing over and over again, so I lost all of my work from last night and this morning trying to fix this problem. There are many problems I am having, and going to do my best to detail them here. What I am doing is taking permit data from the site Drilling Info.com. There are many different entries for each API (well), as sometimes the SHL and BHL are altered at different times. I want to make a "as the crow flies" SHL and BHL connector map/shape file, and also be able to build some queries later from this data set.

What I am doing is taking permit data from the site Drilling Info.com. There are many different entries for each API (well) in the list I am trying to load. I tried an array and pivot table to sort by last date of data but that won't work because the last date doesn't always line up with the last SHL or BHL per row. In Arc I tried to do an Advanced sort by API and permit date to try to get the most recent SHL or BHL to overwrite and that seemingly worked as I got all the way to the step of merging the data. However, once plotted all the LINES (FROM points) seemed to want to center around this strange value and I'm thinking that it was confused and also reading the "blank" rows with 0 values as real values. Each row in the table is not unique. The fitting together of data is also not unique. Therefore, I think my issue is around the fact that somehow I need to be able to filter out this data before I import into Arc. I still have not resolved this issue and hope somebody can help me with this.

My method in the past was to use a program called Petra to load this data. I used the advanced sorting method and then Petra would just accept the last value loaded for each column either SHL or BHL. The final result is one API and one value for SHL and BHL for each well/API. This is a cleaner dataset, and for now in order to proceed I'm going to try an export from Petra to proceed. However, I'm hoping to eliminate this program and just be able to load this data directly from public sources like DrilllingInfo without all this drama. HELP

curtvprice · ‎04-15-2016

Until your data file is "clean" you are not getting anywhere. I think you are correct to get a clean file and start from there.

I thought of an easier approach. If you can create a table of the form:

ptype, id, x, y
SHL, <id>, <x>, <y>
BHL, <id>, <x>, <y>

where <id> is the same ID for each SHL/BHL pair, you could load the table into arcmap, create an xy layer, and then run the Points To Line tool, specifying id as the group field, and ptype as the sort field.

CarrieAlison · ‎04-15-2016

I had a "clean" data set which was run through Petra first. After I excluded some bad data with definition query and also coding with Python within the data tables I was able to get a test plot which worked- YAY!

However, the goal is to take the data straight from Drilling Info and get it to work which I have not yet done. The current problem I'm trying to tackle is to figure out how to load all the

The current problem I'm trying to tackle is to figure out how to load all the data, and to take the last value loaded for SHL and BHL and then convert that to some point where there is only one API for each X,Y data point. This should be able to be accomplished by first loading in the data that is already sorted, but for whatever reason I cannot get it to work correctly. Right now in my data table I have more than 5 entries for some wells due to different permit dates. I only want to end up with one line per API so that I can then use those points to plot. I've decided to "clean" the data as best I can in Excel first by the following methods:

SHL=BHL for vertical wells. No "0" data points in the data table.

If SHL Lon = 0, then make Lat =0 so the coordinates would be 0,0 (SHL) and 0,0 BHL

After doing this in Excel, I still have multiple entries per API #. I tried to load it anyway. If I use the XY to line feature and have more than one BHL or SHL for each API then Arc freaks out and can't process it correctly.

This is my current stopping point and I'm not sure how to proceed from here. I tried to plot by point feature class the SHL and BHL, and then thought I could proceed forward and use the Add XY Coords tool to move forward but that is not working because I still have multiple lines per API.

If you come up with any ideas that would be most helpful. If there was any sort of rhyme or reason to the input data I think I could filter it out. The problem is that what happens a lot is the BHL was given at some date let's say June 1, 2000. Later there were several movements of the permitted surface hole SHL in January 2003 and March 2004. So the result is not one line of data (sorted by last date of permit activity) which would result in the SHL and BHL I need. Perhaps I need to do two different pivot tables, and then do some sort of V Lookup to get them back into a one-line format. I briefly found a sorting tool in pivot tables where you can return the value of the latest sample, but it only worked for one value for each row. I have not yet tested this out but this may be the place I need to go next. Off to the weekend now.