I mocked up some dummy data and put together an example showing the two approaches.
For the circles approach I used the approximation method I mentioned, so the trig for the circle locations is done in the Excel workbook. For the one that calculates the distance and flags conflicts, the formula is in Tableau. I'm pretty sure that that is only working because I'm using Tableau 8.2 which has a new driver for Excel. I'm pretty sure you would still hit the JET restriction about missing trig functions if you tried that against Access - so you might have to use a data extract if you want to go that way.
I just grabbed a lat/lon out of one of those old example workbooks as the starting point. I think you might get a bit of opposition if you actually try drilling in downtown Seattle.
I'd like to combine these approaches a somehow highlight the conflict where circles intersect (as you mentioned):
- 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
To answer your questions (to clarify):
1. How many locations (wells & rigs) are you dealing with at any one time?
As per my output table, for example, I have 768 records. These are not necessarily unique wells, but unique work being done across all dates. As per how much work is being done on a given date - another example - I have 64 jobs on unique wells currently scheduled for 10-4-2014. Bound to be some conflict, but maybe not.
2. How large a physical area do these cover over the course of time?
All in one state, but spans several miles.
These look pretty much right on for what I'm after. Tried to load your workbook, but it errored. In case you are using a later version and it's causing a conflict, I'm on Tableau 8.1.
Love that this method works the buffer right into the table. I see your example also used Excel. I'd like to just have the one source in Access, but it seems like Excel may be generally accepted as the preferred method. Not quite sure how the circle offsets work. Do I need to create a 0-360 reference for each well? If so, I'm concerned that I'll exceed the row limit for Excel. Also, will I be able to automate this? For example, I plan to have Access update via scheduled task and if I dump wells and locations into Excel as part of the process, will Excel do it's calculations and save when not actually "open"? If not, I can add vb to for it to open and save and close. Just get's hairy.
Also, is it possible to:
-Show the well as a point in the center of the circle?
-Shade the circles and/or highlight the intersect red?
Thanks a million for the responses. You definitely get what I'm after.
Brief response now, more detail later (sometime...).
- Workbook is in 8.2 which won't load with 8.1. No chance that you are upgrading soon? If not I can possibly put an 8.1 version together for you some time, not sure when.
- I just did it in Excel because that's the easiest way to put a sample together to post on the forums, but I would expect that you would want to connect directly to Access - but read on...
- Some of the calculations won't work in Access (though I saw earlier in the thread that Christian had pointed out a workaround for that). It might be better to do it by connecting to Access and then creating data extracts which you can refresh whenever the data in Access changes. With that much data the refresh would only take a couple of minutes and you could refresh all datasources in one go.
- I knew I needed to explain the circle offsets - not time to explain properly - but in short, no you don't need a separate set per well. Thinking about it, if you do end up doing the whole thing with data extracts there is no need for the offsets and you can go back to calculating all the points exactly. The offsets thing was just an idea I had to avoid the missing trig functions in Access. The approximation basically works on the assumption that over a small area the distance on the ground corresponding to a given angular offset is the same. Clearly that isn't true over the whole globe (one degree of longitude around the equator is a lot further on the ground than one degree around the Arctic Circle), but if your data is sufficently localised the error would be minuscule.
- Point at the centre of the circle. This comes up a lot, It's doable but a bit tricky and I can't remember the best approach - Shawn Wallwork probably remembers, he did a lot of this stuff with me a couple of years ago and the point in the middle was one of Shawn's hot topics.
- Agree that some way of highlighting the intersections would be cool. I'm sure it's doable - I was just trying to throw something together quickly to show you the way forward.
- I wonder whether a dashboard with a table of conflicts and then filter actions to show the circles on a map (or lines between the conflicting sites) might work.
for Point at the centre of the circle
I think the trick is that you should use dual axis with one map, aka have two lat and two long on columns/rows pills plus double axis enabled; then you can add the well points on the same map with circles.
Just asked and it looks like I'll be added to 8.2 shortly. Sweet.
I didn't quite follow how to make the calculations work in Access, unfortunately (how to call the functions, the formula to achieve the result I need, etc). Still pursuing how to make that happen.
No worries on the circle offsets in Excel. Calculating in Access would be nice, unless I can do a similar calculation within Tableau itself without performance loss.
For the point at the center of the circle, I researched shawnwallwork's method and wrote to him in this older forum post a few days ago: http://community.tableau.com/message/297442, but haven't heard back yet.
I appreciate your help on this.
Looking at Shawnwallwork's workbook, it looks like he only has a single lat lon and somehow still got the point in the center of the circle. This has pretty much the look I'm after in terms of the look of the circles. When I click "View Data" to look at the xls behind it (since it wasn't included separately in his set), looks like he's got 7000+ records for 2 addresses on the map (equivalent of 2 wells for me). Not sure if this is a good approach for as many wells as I have. Richards method may be more adequate for a larger volume of data points. Not sure.
Cristian Vasile - so, in Access, given that I have the well/lat/lon in a table, how do I reference the formulas and what might the formula look like to reference the standard lat/lon and dump out the calculated circle lat/lon? Thinking I'll need a static table of 0-360
Here's an attempt at allowing you to highlight the conflicts. Lots of scope for tidying it up, but it gives you an idea about one way to do it.
I couldn't find a way to highlight both wells involved in a conflict from the single conflict line, so I've showed two lines of detail for each conflict - it just means you have to select the pair of marks for the two circles to be highlighted on the map.
I'll have a think about how best to do this from Access next - but no promises.
I remade it in version 8.1 using an Access database as the datasource.
I had a bit of a look at how to use VBA functions and do all the calculations in Access, but it looked really messy and it obscures what is going on a lot, so I decided to see if I could cajole Tableau into doing all the required math.
Cajole was the operative word - it took a bit of trickery to force a lot of the calculations to happen on the client side (i.e. in Tableau rather than in Access). There were two reasons for needing to do that: the ATAN2() trigonometric function isn't supported by the JET driver used for Access and also, having managed to get that to happen client-side I was still getting "query is too complex" errors.
I managed to find a combination of wrapping values in the ATTR() function and also using the LOOKUP() table calculation function to force part of a calculation to happen in Tableau, which got past the query is too complex message. (The expression LOOKUP([X], 0) is evaluated by Tableau using the results returned from the D/B, so any further expressions using that result are also evaluated in Tableau rather than the D/B).
Anyway - the resulting workbook is attached. You should be able to unpackage the Tableau workbook (right click on it in explorer and select Unpackage) which will let you get at the access D/B to see how I structured that.
One last thing. The trick for showing the centre of the circle is to include an extra row with a different type in the table used to drive the circles. If you look in the D/B you'll see a Circle table with 361 rows with values 0 to 360 for the boundary points plus one for the centre. The expressions for the boundary points just return the coordinates of the well for the centre point instead of the calculation for the points on the boundary..
WellsAndRigs_v8_1_Access.twbx 106.1 KB
This is outstanding. Any chance you can provide the DB so I can see how you set up the tables? For example, I see you have two ID columns. I want to see how you assigned ID's, etc. I'm thinking in my case, well name may serve as one of the ID's.
Awesome that the calcs are done in Tableau. Thank you...
By the way - looks like we will be rolling out 8.2 shortly. Will I be able to easily save this in 8.2 from 8.1? Or will it require rework?
Should just open in 8.2 and save and it will all be fine.
Sent from my phone - excuse the weird typos.
Thank you. Are you in a position to post the db?