Oracle oder Standard SQL Auskunftsabfrage für U-Bahn

[Liberty-son]

Hallo Leute,

ich stehe hier vor einem Problem mit meinem Auskunftssystem einer U-/S-Bahn Auskunft.
Ich habe follgende Tabellen:

linien mit l_id und l_name

desweitern ein Tabelle: haltestellen mit h_id und h_namen

dies ist natürlich eine n:m Beziehung.

Jetzt die 4te und letzte Tabelle: haltestellen_der_linie
eine lnr als Primary key
eine l_id und die h_id

Ich weiss, das hier Redundanzen entstehen aber ich sehe keine andere Möglichkeit festzustellen, welche Linie welche Stationen anfährt.

Jetzt zu meiner Frage. Ich möchte die Verbindung von A nach G angezeigt bekommen mit dem Umstieg von Linie 1 auf Linie 2, das heißt, er soll den Schnittpunkt beider Linien finden diese als Umstiegshaltestelle ausweisen.

Hat jemand einen kleinen Tip für mich.
Thx

 

[Liberty-son]

kann mir keiner helfen? Ich drehe noch durch.

 

[Exceptionfault]

So, ich hoffe du bist auf eine lange Antwort gefasst
Es ist noch nicht ganz perfekt, d.h. ich hab nicht getestet was passiert wenns mehrere Möglichkeiten gibt, aber das kann man ja noch ausbauen...

Also zuerst mal meine Tabellen:

CREATE TABLE LI (
   L_ID    NUMBER(6),
   L_NAME  VARCHAR2(30)
);

CREATE TABLE HA (
   H_ID    NUMBER(6),
   H_NAME  VARCHAR2(30)
);

CREATE TABLE LI_HA (
   L_ID    NUMBER(6),
   H_ID    NUMBER(6),
   POS     NUMBER(6)
);

Linien, Haltestellen, und Kombination aus Linie und Haltestelle. POS gibt die Reihenfolge der Haltestellen an, in denen eine Linie daran vorbeifährt.

Hier meine Testdaten:

INSERT INTO LI VALUES (1, 'Linie 1');
INSERT INTO LI VALUES (2, 'Linie 2');
INSERT INTO LI VALUES (3, 'Linie 3');

INSERT INTO HA VALUES (1, 'Haltestelle 1');
INSERT INTO HA VALUES (2, 'Haltestelle 2');
INSERT INTO HA VALUES (3, 'Haltestelle 3');
INSERT INTO HA VALUES (4, 'Haltestelle 4');
INSERT INTO HA VALUES (5, 'Haltestelle 5');
INSERT INTO HA VALUES (6, 'Haltestelle 6');
INSERT INTO HA VALUES (7, 'Haltestelle 7');
INSERT INTO HA VALUES (8, 'Haltestelle 8');

INSERT INTO LI_HA VALUES (1, 1, 1);
INSERT INTO LI_HA VALUES (1, 3, 2);
INSERT INTO LI_HA VALUES (1, 5, 3);
INSERT INTO LI_HA VALUES (1, 7, 4);

INSERT INTO LI_HA VALUES (2, 2, 1);
INSERT INTO LI_HA VALUES (2, 3, 2);
INSERT INTO LI_HA VALUES (2, 4, 3);
INSERT INTO LI_HA VALUES (2, 8, 4);

Wir haben 3 Linien, 8 Haltestellen und folgenden Fahrplan:

Linie 1:
1 => 3 => 5 => 7

Linie 2:
2 => 3 => 4 => 8

Mein Ziel ist von 1 nach 8 also von Linie 1, umsteigen bei H3 in Linie 2 bis H8.

Und nun die Prozedur:

SET SERVEROUTPUT ON

DECLARE
	v_startline	NUMBER(6);
	v_endline	NUMBER(6);
	v_cross		NUMBER(6);
	
	cursor_name INTEGER;
	ignore		INTEGER;
	h_stelle	NUMBER(6);
	
	v_found		VARCHAR2(1) := 'N';
	
BEGIN
	SELECT 	L_ID 
	INTO 	v_startline
	FROM 	LI_HA 
	WHERE 	H_ID = 1;

	SELECT 	L_ID 
	INTO	v_endline
	FROM 	LI_HA 
	WHERE 	H_ID = 8;
	
	SELECT 	T1.H_ID 
	INTO	v_cross
	FROM	LI_HA T1, LI_HA T2
	WHERE   T1.L_ID = v_startline
	AND		T2.L_ID = v_endline
	AND		T1.H_ID = T2.H_ID;
	
	DBMS_OUTPUT.put_line('Startlinie......: '|| v_startline );
	DBMS_OUTPUT.put_line('Endlinie........: '|| v_endline );
	DBMS_OUTPUT.put_line('Schnittlinie....: '|| v_cross );
	
	
	DBMS_OUTPUT.put_line('Einsteigen in...: ' || v_startline );
	
	cursor_name := dbms_sql.open_cursor;
	DBMS_SQL.PARSE(cursor_name, 
         'SELECT H_ID FROM LI_HA WHERE L_ID = ' || v_startline || ' ORDER BY POS ', 
          DBMS_SQL.native); 
	DBMS_SQL.DEFINE_COLUMN(cursor_name, 1, h_stelle); 
	ignore := DBMS_SQL.EXECUTE(cursor_name); 
	
	LOOP

       IF DBMS_SQL.FETCH_ROWS(cursor_name) > 0 THEN          
       
         DBMS_SQL.COLUMN_VALUE(cursor_name, 1, h_stelle);
         DBMS_OUTPUT.put_line( 'Haltestellen....: ' || h_stelle );
         
         IF h_stelle = v_cross THEN          
         	EXIT;
         END IF;
         
       ELSE
       	EXIT;
       END IF; 
	
	END LOOP;
	
	DBMS_SQL.CLOSE_CURSOR(cursor_name);
	
	DBMS_OUTPUT.put_line('Umsteigen in ...: ' || v_endline );
	
	
	
	cursor_name := dbms_sql.open_cursor;
	DBMS_SQL.PARSE(cursor_name, 
         'SELECT H_ID FROM LI_HA WHERE L_ID = ' || v_endline || ' ORDER BY POS ', 
          DBMS_SQL.native); 
	DBMS_SQL.DEFINE_COLUMN(cursor_name, 1, h_stelle); 
	ignore := DBMS_SQL.EXECUTE(cursor_name); 
	
	LOOP

       IF DBMS_SQL.FETCH_ROWS(cursor_name) > 0 THEN          
       	DBMS_SQL.COLUMN_VALUE(cursor_name, 1, h_stelle);
       	
         IF h_stelle = v_cross THEN          
         	v_found := 'Y';
         END IF;
         
         IF v_found = 'Y' THEN
       	  DBMS_OUTPUT.put_line( 'Haltestellen....: ' || h_stelle );
       	 END IF;
         
         
       ELSE
       	EXIT;
       END IF; 
	
	END LOOP;
	
	DBMS_SQL.CLOSE_CURSOR(cursor_name);
	
	DBMS_OUTPUT.put_line('Ende' );	
	
	
END;
/

Das Ergebnis dann wie folgt:

Startlinie......: 1
Endlinie........: 2
Schnittlinie....: 3
Einsteigen in...: 1
Haltestellen....: 1
Haltestellen....: 3
Umsteigen in ...: 2
Haltestellen....: 3
Haltestellen....: 4
Haltestellen....: 8
Ende

Die Funktion ist eigentlich ganz simpel. Ich ermittle zuerst die Linie, welche durch die Haltestelle 1 fährt, und die Linie, welche durch die Haltestelle 8 fährt.
Dann such ich den Schnittpunkt der beiden. Laufe dann von der 1 bis zum Schnittpunkt und dann mit der anderen bis zum Ende.

Ich habe hier nicht geprüft ob es mehrere Möglichkeiten gibt. Auch laufe ich bei der Linie ganz zu Beginn los und nicht erst bei der gewünschten Haltestelle (ist in meinen Daten zufällig das gleiche) und so sind noch viele andere kleine Bugs drin.

Aber ich hoffe es ist mal ein Anfang und hilft dir ein bisschen weiter. In einem einzigen SQL Statement wirste das übrigens nie hinbekommen, daher würde ich eine PL/SQL Prozedur empfehlen, das dürfte die schnellste Methode sein.

Mit den Daten würde ich dann eine temporare Tabelle befüllen, die du nach dem Aufruf der Prozedur auslesen kannst, eben mit den Feldern Reihenfolge, Linie, Haltestelle. Wenn dus ganz gut machst kommt noch ein Feld für Routennr. hinzu falls es mehrere Möglichkeiten gibt.

 

[Liberty-son]

danke! ich habe es so ähnlich realisiert.