-
Jul 17th, 2017, 12:59 AM
#1
Thread Starter
PowerPoster
Query / Table creation advice
I have a table which stores the Station names and their incremental distance, i can able to calculate the inter distance between the two stations some thing like the ( Distance Of station xxx - Distance Of station yyy) .
But Some stations are junction stations at where another route diverges / Begins from the main route, i am having issue in such cases how to calculate the inter distance and also query the details of between stations using VIA (i mean junction station)
my table structure so far i planned is like this
Code:
CREATE TABLE `RouteTest` (`StationName` varchar(10) NOT NULL ,
`StationGeographicalOrderIndex` smallint(3) NOT NULL ,
`IncrementalDistance` smallint(2) NOT NULL ,
`Is_Junction` enum('Y','N') NOT NULL DEFAULT 'N' ,
`Is_NewDeviatingRouteBegin` enum('Y','N') NOT NULL DEFAULT 'N' ,
`DeviatingJunctionStation` varchar(255) NULL
COMMENT 'If the station is the beginning of a new route from a junction then from which station it is deviating'
)
;
Now i will insert some imaginary data for demo
Code:
INSERT INTO routetest VALUES('A',0,0,'Y','N',NULL) ,
('B',1,7,'N','N',NULL) ,
('C',2,17,'Y','N',NULL) ,
('D',3,28,'N','N',NULL) ,
('E',4,45,'N','N',NULL)
Now here P is a station deviating from junction station C
Code:
INSERT INTO routetest VALUES('P',0,0,'N','Y','C') ,
('Q',1,9,'N','N',NULL) ,
('R',2,17,'N','N',NULL) ,
('S',3,22,'N','N',NULL) ,
('T',4,37,'Y','N',NULL)
So i must be able to
(1) Get the details of the stations Starting from Station A and till T Via (that is DeviatingJunctionStation) Station C
(2) and the Inter distance between A and C
(3) Thus the user can able to query the stations between any two stations using the Via junction or without
How to materialize this please
-
Jul 18th, 2017, 07:27 AM
#2
Re: Query / Table creation advice
I'd set up a table of routes to use the table of stations and connections you have already.
Alternatively you'd need to bring back every single route possible by connecting the stations then filter those results to the stations you are interested in...
Have a look at recursive loops.
If you are coding, either on the sql server or other package you may be able to use source/start station as a starting point, loop all connected and recursivce loop until either they have no other connections or you are at the destination station
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|