Pipeline Database Design problem

[Sands]

Hi Guys,
I am new to Access. Currently I am building a database of oil pipelines. So each record is a particular oil pipeline with related information such as toll, origin point(City), destination point, type of crude so on.

My problem is one of main task is that User can just pick a origin point A and a destination point B, the database can return a route or multiple routes to get to B from A.
Usually, there is not a single pipeline can get you there, it has to connect to several other pipelines. In some cases, you may have different routes that all can get you from A to B but at different costs (sum of total tolls).

I have no clue how to make that functionality happen. I am really appreciated if you guys can provide some suggestions. Thanks

 

[spikepl]

So what have you googled? This is some type of network path optimization ... so you just need to come up with/google an algorithm. Whether or not it can run in Access will surely emerge

 

[spikepl]

I smell matrix algebra coming up ...linear equations .. functional analysis. This is why I am not too sure about using Access by itself... But then again, my sense of smell could be misguided

If you have some math-oriented engineers nearby then enlist them.

 

[Sands]

I smell matrix algebra coming up ...linear equations .. functional analysis. This is why I am not too sure about using Access by itself... But then again, my sense of smell could be misguided

If you have some math-oriented engineers nearby then enlist them.

I wasn't expect it to be that complex. Has mentioned I am new to Access and no programming experience. Is learning VBA the only option? damn I got one month to accomplish that!!!!!

 

[spikepl]

I have no verified idea about how complex your issue is. That's why I'd recommend googling, because your problem does not sound very unique, so solutions must exist out there. Whether Access is suitable depends on what tools/methods are required, but that is not given so long as an algorithm has not been identified. Forget for the moment Access and VBA ... the beginning of the solution is to identify/create the algorithm, so that is a job for you and google and your engineering/math friends. If you have a month then you better get cracking with google then

 

[WayneRyan]

Sands,

Here's a link to an old thread here; its as good a place to start as any:

http://www.access-programmers.co.uk/forums/showthread.php?t=51083&highlight=recursive

hth,
Wayne

 

[gemma-the-husky]

network analysis

i am pretty sure that for any non-trivial network there is no way of finding the optimal network design. all linear programming examples are based on finding a "better" solution rather than an absolute best.


i think any solutiuons that do exist are likely to be highly proprietary, and expensive. there is a lot of money to be made in optimising network topolgy.

maybe there might be some examples in terms of designing a computer / telecoms network.

 

[Lightwave]

Congratulations Sands you've asked one of those simple questions that have a very complicated answer! (I'm sure you are happy to hear that)

Its not that Visual Basic isn't an adequate programming language its that the mathematics involved is complicated and most in here would struggle to write it.

Whatever language you were to choose the basic optimal mathematics or algorithm is likely to be the same.

Just done a quick search and came up with this paper.

Visualizing solutions in network analysis with VBA

It actually says.

"It is well known that large models cannot be solved to exact optimality"

Which backs up the huskies point. I would normally solve this in a digital mapping package after importing the desired network.

I think you seriously might of bitten off more than you can chew.

 

[Lightwave]

Just had a look at Wayne's link

Looks the most promising of all our responses.

 

[gemma-the-husky]

the other issue with this, is that network designers have to actively build in redundancy. in most cases there needs to be more than 1 way of getting from A to B - so that a single "connector" outage does not cause network failure.

so generally, the designers are looking for the most efficient way to connect a network, under certain connection conditions.


the problem you iniitally posed - finding all paths from A to B, (without traversing the same node more than once) is a modest exercise in recursion, although finding a way to avoid solutions that take you further away from B is possibly tricky.

for very large networks, though it becomes like finding a solution for a game of chess!