I (think) what you are referring to in GIS terms is an Origin/Destination Matrix.
If you think of the wells as a matrix or network all you are trying to do is to establish the distance between each node, correct? If this is the case it's very simple, if not, I'm unsure of the requirements. Also, can the wells be considered "static" and the rigs are "dynamic"?
Yes, the wells are static and that's really all I'm working with, with one exception...when certain rigs set up, they will be on a "pad", so that'll have it's own LAT/LON.
I envision each well/pad having a bubble around it which will be the buffer zone, if any of these buffers intersect on a particular date, it will indicate a scheduling issue. We have 3 teams that schedule work, we'll call them crew 1, crew 2, and crew 3. Crew 1 requires a 150ft around each well they are working on, crew 2 requires 150ft around each well they are working on, and crew 3 requires 300 ft around the pad as well as 100ft around each well they are hooked up to. I'll have a date slider where I can see their scheduling conflicts. Assuming this picture below was scheduled work for 9/20/2014, we'd see an issue between crew 1 and crew 3 working on wells 7 & 8. I'd have to cancel crew 1's work. Is this doable?
Here's how I would tackle this: there are probably better ways:
The wells could be points or polygons: if your GIS department has the shapefiles for the wells, then this becomes easier.
If not, the centroids (points) will have to do.
Taking the points, you would create buffers:
This generates polygons, which I'd use ET GeoWizards for ArcGIS Polygon to Point. The same principle for "Pad".
For each entity, give it an attribute in the DBF file (Well1-7,Pad) so you know what each row pertains to, with all the attributes you need.
Then you need to set up the from -> to for each relationship in turn. So, in your example, Well 5 x,y/Pad x,y:
Distance is calculated by =ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *3958.756, you can pre-process this and add it to your DBF.
The key for Tableau ETL is to reduce everything to points and where there are polygons or lines you need a path order.
Ok. GIS is not my world at all, so I'm starting from ground 0. So, per your suggestion, the only way to accomplish this is to involve ArcGIS? Also, does this solution take into account a buffer between crew 1 and 2 work (well to well and not just well to pad). I'll research the methods you mentioned.
EDIT - Hmmm...looks like ET GeoWizards for ArcGIS isn't a free package, so it wouldn't be an option. I also am not authorized to install apps (if it requires installation).
Thank you. So far, I have no traction on this. No one in my org is knowledgeable on this, unfortunately.
I'll try to take a bit of a look at the weekend. I've certainly done vaguely similar stuff in the past, though I'd be surprised if Alan hasn't already covered the options.
Thank you. Right now I just have points on a map, a date slider, and well names. I can dummy up a twbx if that helps.
No time to look in detail now - but I'll just point you to a few old threads I can think of which touched on related topics. Not exactly what you are doing, but I think if you have a read you'll see why I think they might be useful background.
I haven't re-read them myself, but I'm pretty sure at least one of these touches on the use of buffer zones - which I did in SQL using PostGIS directly (I don't have access to the ESRI tools). I'll try to dig out some sample SQL at the weekend.
The title of this first thread looks very unlikely - but the thread actually morphed into something much more relevant. The population density map of Germany later on in the thread is doing exactly the analysis of distance from every point to every other point that you are talking about.
This might just be helpful - it talks about combining shapes in PostGIS.
This one deals with shape overlaps and how to visualise the overlaps:
Take a look at the formulas embedded on excel file, here Re: Re: shaded areas of 30 miles on a map
What i had done was to compute next lat/long based on current lat/long and a radius (distance) expressed in KM
Hope this helps,
Going down the rabbit hole... Looks promising. I love the simplicity of it the approach (not too many moving parts). Few questions:
1. Didn't look like the data in the radius tab was used in the excel sheet (1-5). I even tried deleting it and it didn't affect the data in the tableau report. Is this necessary?
2. The data in the example is using one fixed distance to buffer (50km). In my case, there are 3 different distances that vary by team. Does that change how I approach it?
3. Is it possible to calculate circle lat/lon within Access? Access is the source of this data and would like to limit it to just the one source if possible. Or even within Tableau itself? Need a method I can automate.
I will try to answer now the simple question aka #3
For MS Access you need acos() and if i remember right is not available. Richard did mention this issue in an old post.
However please read this external resource Microsoft Access Tips & Tricks: Great Circle Distance | The Blogannath Rolls On . . . and use the workaround formulas for acos() / asin()
For #1 & #2 i need to re-check what i did on that worksheet, and i shall revert back.
I saw that the question related to feet vs km was deleted so to convert from km to miles you need to change the Earth Mean Radius from 6,371 (Km) to 3,958.8 (Mi); Allan had used the more precise value ie 3,958.756 (Mi)
Hope this helps.
Thank you. I appreciate the responses. I deleted that question about km to feet, because I just Googled it and found the distance around the Earth in feet.
So, I plugged in those Access modules into formulas from the link you provided, so that part is done, but there was no explanation of how to use them to achieve my goal. ie. referencing lat/lon, etc.
Just catching up and reading the thread properly (rainy Saturday morning here, time for a bit of forum browsing!).
A few quick thoughts.
1) I agree - no need for ARCGIS or PostGIS or any other spatial tools - that would be overkill for what you are trying to do.
2) It seems to me that you have a couple of completely different approaches that you could take to achieve what you want:
- draw a map with appropriate sized circles around each well or rig and just spot any conflicts by eye
- calculate the distances between each pair of locations (wells or rigs) and highlight any where the distance is less than the sum of the buffer distances needed for each of the points
You could also combine the two approaches, so that you show the circles but also highlight the conflicts in some way. Doing both might make it trickier, though.
3) A couple of clarifying questions that might influence the approach.
- How many locations (wells & rigs) are you dealing with at any one time?
- How large a physical area do these cover over the course of time? If all locations are within a relatively small geographic area you can probably use pre-calculated offsets for calculating circle coordinates, whereas if you need to be covering points hundreds or thousands of miles apart (in the North-South direction) the distance calculations need to be done at the correct latitude to avoid large distortions. Off the top of my head I don't know how big an area is too big - but it would be pretty easy to put the formula in Excel and experiment to find out.
The second point is just about trying to avoid the extra complexity of the calculations in Access. It would only help if you were just going to draw the circles and spot the conflicts by eye - I think.
4) You will need a table of rig/well locations, and if you want to draw the circles, a table to drive that from. The way I would approach it in the two cases is:
- for the "draw the circles and spot the conflicts by eye" approach I'd have a cross product between the locations table and the circle points table as the datasource
- for the "calculate the separation between each pair of points and highlight conflicts" approach I'd just have a cross product of the locations table with itself.
If you wanted circles and highlighting of conflicts you end up needing a three-way cross product, I think (that's what II meant by it being trickier if you want too do both).
Feel free to bounce questions as you go - or post a packaged workbook showing how far you've got.- I'm sure plenty of people will chip in with suggestions.