Who wants a colorful water puzzle challenge??

[Chikaleen]

Hi guys, Chikaleen here, have I got a puzzle for you!

I find and map water in the oilfields for a living. We then secure, permit, and transport the water via temporary rubber hose to the location it needs to go to. This keeps thousands of tanker truck trips off the county roads and keep them from kicking up dust every time they pass your house. I'm attempting to make my methods more automated: The old-school guys like to get in a pickup, drive 2+ hours to a rig area, and proceed to drive in circles to find useful water for the frac jobs that take place after the rig moves out. What used to take weeks to do driving around the countryside now takes minutes thanks to this fancy operation I've got going on to make my job easier. I'm asking for your help in making it simpler yet - find a way to mesh these two spreadsheets together to pinpoint exact distances from my source water to the job I need to get it at. Bear with me and I'll explain and maybe teach you some of the more fun aspects of this job.

Legal land descriptions operate on a grid called the Public Land Survey System (PLSS) and are divided into "Township" and "Range" grids (horizontal and vertical lines, respectively) and then further subdivided into 36 "Sections" of that grid. The 6x6 grid is commonly referred to as just a "Township." With the previous help of some Mr. Excel members (because you guys are the greatest) I have gotten the sheet to recognize a data point (NWNES34T150NR97W, or whatever) and plot it to a map I have drawn.

I have two sheets I'm proud as all heck of that I operate out of for this task. ALL RIGS 'Data' tab can be updated daily by a simple copy/paste from the state website. Make sure the VLOOKUP is matching all of the colors, make sure formulas are selecting the correct cells, and then run the ClearCounty and Main macros on the 'Map' tab and voila! All of the active drilling rigs in the area are updated to within a <1 mile radius of their actual location (which is close enough for what I need.)

View ALL RIGS 8.20.14 here https://www.dropbox.com/s/gjbzj4m7c1m68ug/ALL RIGS 8.20.14.xlsm?dl=0

The second sheet is made up of fixed data points of ponds, streams, rivers, water wells, etc. that I have permitted or access to for utilization in the fracking process. I have removed a lot of personal data and ownership information from this sheet for the purpose of this exercise, so normally there is a lot more in there to sort by and play with.

View Sources 8.20.14 here https://www.dropbox.com/s/hot8qgxyx56vibu/Sources 8.20.14.xlsm?dl=0

NOW TO THE FUN PART

I want to be able to link or overlay these sheets somehow to be able to see which Operator I need to go talk to about providing the closest and easiest accessible water for. Here's the steps in my head of how to get there:

Step 0: (sort of backwards step) If you can suggest a better way of using VBE on the current sheets, I'm all for it.

Step 1: Make the Sources recognize the Rig locations in relation to their static points.

Step 2: Make the Rigs tell you the distance in cells (one square mile) it is away from a source using the Pythagorean Theorem (A^2+B^2=C^2)

Step 3: Make the Sources only return Rig information in a X cell radius of the source.

Step 4: Return a printable or viewable Rig sheet of X Source within X radius.

Step 5: Whatever else could come next, I have no idea.


Thanks in advance to anyone that tries it out. If I don't get many bites to this article I'll attempt to use interesting land or water tidbits for my thread bumps to keep you all entertained. I'm sitting right here if you have any questions as to how or why something was set up in a particular way, and thanks again



Chikaleen

 

[Chikaleen]

The land system is an important part of American history and culture. Among other things, the stock phrases "lower 40", "front 40", "back 40", and "40 acres and a mule", which are sometimes heard in American movies, reference the quarter-quarter section.

 

[shg]

It seems to me it would have been way easier to plot the locations on a chart, using the county map (in equirectangular projection) as a background image. The map coordinates of rigs and water sources could be converted to x-y postion in straightforward fashion.

 

[Chikaleen]

It seems to me it would have been way easier to plot the locations on a chart, using the county map (in equirectangular projection) as a background image. The map coordinates of rigs and water sources could be converted to x-y postion in straightforward fashion.

I tried that out to start with, but saw that I could pull information out better this way.

Judging by the Texas flag that you have as an avatar, you may be more familiar with the Texas Land Survey system, which differs from the PLSS greatly. In that system, there is no set X,Y axis and everything seems bonkers in relation to the next cell. Some strange variation of an X,Y system or a lat,long in Texas is definitely the way to go but this ended up being easier for what I needed it to do. A tidbit from the Internet to explain why you are different down there:

"In 1784, the Jeffersonian Public Land Survey System (PLSS) was presented to the Continental Congress by a committee headed by Thomas Jefferson. By 1785, the Jeffersonian PLSS became the standard mode, by law, for surveying lands in the rapidly expanding nation. During this time, the region that makes up present-day Texas was under Spanish rule."

 

[Chikaleen]

"Permanent man-made markers are placed at each surveyed section corner. These markers, or monuments, are also positioned at section sub-divisions. The current markers today are generally inscribed plaques or iron rods. The original markers consisted of wooden posts, trees, bodies of water, piles of stone, or other natural landmark features."

Wonder how they put a body of water in the way...

 

[Chikaleen]

The PLSS actually consists of a series of separate surveys. Most PLSS surveys begin at an initial point, and townships are surveyed north, south, east, and west from that point. The north-south line that runs through the initial point is a true meridian and is called the Principal Meridian. There are 37 Principal Meridians, each is named, and these names are used to distinguish the various surveys. The east-west line that runs through the initial point is called a base line. This line is perpendicular to the Principal Meridian.

 

[Chikaleen]

The first Satellite positioning system was the U.S. Navy TRANSIT system. The first successful launch took place in 1960. The system's primary purpose was to provide position information to Polaris missile submarines, but it could also be used by surveyors with field receivers to determine the location of a point. The small number of satellites and bulky equipment made observations slow, difficult and inaccurate, so usage of this system was limited to establishing benchmarks in remote locations.

I can only imagine attempting to map out a mountain range in the late 1800s... How did anyone ever do it without satellites??

 

[Chikaleen]

Not quoted exactly, but I heard this from an employee of the North Dakota State Water Commission in regards to fracking and freshwater usage - that all fracking for a year in North Dakota uses the equivalent of one inch worth of water off of Lake Sakakawea... Evaporation takes 36 inches of it a year.

Neat fact from the guy that puts the numbers together. Anyone tried out the spreadsheet yet?

 

[Chikaleen]

I'm sort of out of interesting things to say. Bump.

 

[shg]

There's a workbook at https://app.box.com/s/6365058cfff4b405a371 that was done for a completely different purpose. It lists a lot of locations down the left, a few location across the top, and calculates the distance between them using lat, lon, and a user-defined function. (Surface disance, not driving distance, which might be more useful.) You could enter water sources down the left, well locations at the top, and sort by distance to a particular well.