complicated? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

complicated?

Author

Topic

netcop
Starting Member

12 Posts

Posted - 2002-07-29 : 12:31:51

I'm working on some special routing algortithm. For this one I need a query which tells me some important information. I think it's not complicated, but I'm rotating on the same place.

When I'm starting at 'Heiligengeistplatz' and want to the destionation 'Weihergasse', then I see that on the direction 1 there is no entry. So I have to drive with direction 1 to this entry 'Ganghofergasse' on direction 1, because it's the end point with 4339m. From there you can get to the destination. The query should return me, that i'have to turn, and give me in this case 'Ganghofergasse'.


line        direction   distance    busstop                      
----------- ----------- ----------- -------------------------------- 
90          1           0           Heiligengeistplatz
90          1           322         Benediktinerplatz
90          1           532         10.-Oktober-Straße
90          1           861         Bürgerheim
90          1           1061        Messe
90          1           1471        Rosentaler Straße
90          1           2010        Unfallkrankenhaus
90          1           2290        Schaumgasse
90          1           2886        Hauptm.-Hermann-Platz
90          1           3147        Baumbachplatz Ost
90          1           3509        Zaungasse
90          1           3778        Mozartgasse
90          1           4020        Ru.-Kattnig-Straße
90          1           4339        Ganghofergasse
90          2           0           Ganghofergasse
90          2           286         Weihergasse
90          2           547         Sumpfweg
90          2           1111        Stadion
90          2           1376        Zaungasse
90          2           1732        Neugasse
90          2           2022        Hauptm.-Hermann-Platz
90          2           2567        Schaumgasse
90          2           2845        Unfallkrankenhaus
90          2           3305        Rosentaler Straße
90          2           3884        Messe
90          2           4141        Bürgerheim
90          2           4641        Domplatz
90          2           5086        Heiligengeistplatz

When I want from 'Heiligengeistplatz' to 'Mozartgasse', then it
should return me, there is no turn, because it's later
(distance 1) than the start point.

When I' want to get from 'Heiligengeistplatz' to 'Unfallkrankenhaus'
then I see, there are 2 directions, so the result should be, that there is no turn needed.

Maybe the entries on more lines (like the Mozartgasse is on line 90,91,92), then the query should give me the result for each line.

Is there anybody who can help me!?
Thx

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-07-29 : 12:51:26

quote:

I'm working on some special routing algortithm. For this one I need a query which tells me some important information. I think it's not complicated, but I'm rotating on the same place.

When I'm starting at 'Heiligengeistplatz' and want to the destionation 'Weihergasse', then I see that on the direction 1 there is no entry. So I have to drive with direction 1 to this entry 'Ganghofergasse' on direction 1, because it's the end point with 4339m. From there you can get to the destination. The query should return me, that i'have to turn, and give me in this case 'Ganghofergasse'.

line direction distance busstop
----------- ----------- ----------- --------------------------------
90 1 0 Heiligengeistplatz
90 1 322 Benediktinerplatz
90 1 532 10.-Oktober-Straße
90 1 861 Bürgerheim
90 1 1061 Messe
90 1 1471 Rosentaler Straße
90 1 2010 Unfallkrankenhaus
90 1 2290 Schaumgasse
90 1 2886 Hauptm.-Hermann-Platz
90 1 3147 Baumbachplatz Ost
90 1 3509 Zaungasse
90 1 3778 Mozartgasse
90 1 4020 Ru.-Kattnig-Straße
90 1 4339 Ganghofergasse
90 2 0 Ganghofergasse
90 2 286 Weihergasse
90 2 547 Sumpfweg
90 2 1111 Stadion
90 2 1376 Zaungasse
90 2 1732 Neugasse
90 2 2022 Hauptm.-Hermann-Platz
90 2 2567 Schaumgasse
90 2 2845 Unfallkrankenhaus
90 2 3305 Rosentaler Straße
90 2 3884 Messe
90 2 4141 Bürgerheim
90 2 4641 Domplatz
90 2 5086 Heiligengeistplatz

When I want from 'Heiligengeistplatz' to 'Mozartgasse', then it
should return me, there is no turn, because it's later
(distance 1) than the start point.

When I' want to get from 'Heiligengeistplatz' to 'Unfallkrankenhaus'
then I see, there are 2 directions, so the result should be, that there is no turn needed.

Maybe the entries on more lines (like the Mozartgasse is on line 90,91,92), then the query should give me the result for each line.

Is there anybody who can help me!?
Thx

--CELKO--
Joe Celko, SQL Guru

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-07-29 : 12:58:00

>> I'm working on some special routing algortithm. For this one I need a query which tells me some important information. I think it's not complicated, but I'm rotating on the same place. <<

If you re-designed the table to represent a directed graph, I think life might be easier:

CREATE BusRoutes
(bus_line_nbr INTEGER NOT NULL,
start_busstop VARCHAR(30) NOT NULL,
end_busstop VARCHAR(30) NOT NULL,
distance INTEGER NOT NULL,
CHECK (start_busstop <> end_busstop),
PRIMARY KEY (bus_line_nbr, start_busstop, end_busstop));

We need another CHECK() constraint to see that the bus returns to its first busstop.

Your queries can be done with a cursor or with self-joins.

--CELKO--
Joe Celko, SQL Guru

netcop
Starting Member

12 Posts

Posted - 2002-07-30 : 03:17:59

The algorithm works without graphs. It simulates the tour. The data is genereted in an old DOS program and shouldn't modified.

Can anybody help me with the query?
THX

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources