1、公交车路线查询系统后台数据库设计公交车路线查询系统后台数据库设计-查询算法1.公交车路线信息在数据库中的存储方式显然，如果在数据库中简单的使用表 bus_route（ 路线名,路线经过的站点,费用）来保存公交车路线的线路信息， 则很难使用查询语句实现乘车线路查询， 因此，应该对线路的信息进行处理后再保存到数据库中，笔者使用的方法是用 站点-路线关系表stop_route（ 站点,路线名,站点在路线中的位置）来存储公交车路线，例如，如果有以下 3条路线R1 : S1-S2-S3-S4-S5R2 : S6-S7-S2-S8R3 : S8-S9-S10则对应的 站点-路线关系表stop_route
2、为StopRoutePositi onS1R11S2R12S3R13S4R14S5R15S6R21S7R22S2R23S8R24S8R31S9R32S10R33注：Stop为站点名，Route为路线名，Position为站点在路线中的位置2.直达乘车路线查询算法基于表stop_route 可以很方便实现直达乘车路线的查询， 以下是用于查询直达乘车路线的存储过程InquiryTO :create proc In quiryTO(StartStop varchar(32),E ndStop varchar(32)asbeg inselectsr1.Stop as 启始站点,sr2.Stop as
3、目的站点,sr1.Route as 乘坐线路,sr2.Positio n-sr1.Position as 经过的站点数fromstop_route sr1,stop_route sr2wheresr1.Route=sr2.Routeand sr1.Positionsr2.Positionand sr1.Stop=StartStopand sr2.Stop=E ndStopend3.查询换乘路线算法(1)直达路线视图直达路线视图可以理解为一张存储了所有直达路线的表 （如果两个站点之间存在直达路线， 那么在直达路线视图中就有一行与之相对应 ）。例如R1,R2,R3 对应的RouteTO 如下:起点
4、终占 匕八、乘坐路线站点数S3S4R11S3S5R12S4S5R11S1S2R11S1S3R12S1S4R13S1S5R14S2S3R11S2S4R12S2S5R13S2S8R21S6S2R22S6S7R21S6S8R23S7S2R21S7S8R22S8S10R32S8S9R31S9S10R31RouteTO定义如下:create view RouteTOasselectsrl.Stop as StartStop,- 启始站点sr2.Stop as En dStop,- 目的站点srl.Route as Route,- 乘坐线路sr2.Positio n-sr1.Position as Sto
5、pCou nt - 经过的站点数fromstop_route sr1,stop_route sr2wheresr1.Route=sr2.Routeand sr1.Positionsr2.Position(2)换乘路线算法显然，一条换乘路线由若干段直达路线组成 (每段路线的终点与下一段路线的起点相同 )，因此，基于直达路线视图 RouteT0可以很方便实现换乘查询，以下是实现一次换乘查询的存储过程 InquiryT1 :create proc In quiryT1(StartStop varchar(32),E ndStop varchar(32)asbeg inselectrl.StartSt
6、op as 启始站点,rl.Route as 乘坐路线1,rl.EndStop as 中转站点,r2.Route as 乘坐路线2,r2.EndStop as 目的站点，rl.StopCou nt+r2.StopCou nt as 总站点数fromRouteTO r1,RouteTO r2wherer1.StartStop=StartStopand r1.E ndStop=r2.StartStopand r2.E ndStop=E ndStopend同理可以得到二次换乘的查询语句create proc In quiryT2(StartStop varchar(32),E ndStop varc
7、har(32) asbeg inselectr1.StartStop as 启始站点，r1.Route as 乘坐路线1,r1.E ndStop as 中转站点 1,r2.Route as 乘坐路线2,r2.EndStop as 中转站点 2,r3.Route as 乘坐路线3,r3.EndStop as 目的站点，r1.StopCou nt+r2.StopCou nt+r3.StopCou nt as 总站点数fromRouteT0 r1,RouteT0 r2,RouteT0 r3wherer1.StartStop=StartStopand r1.E ndStop=r2.StartStopa
8、nd r2.E ndStop=r3.StartStopand r3.E ndStop=E ndStopend4.测试exec Inqu iryTIS1 ，S8S1 , S9exec Inqu iryT2运行结果：启始站点目的姑点 乘坐线路 经过的站点数LS1 52 R1 1启姒站点乘尘蹈线1中转站点1乘坐畴2目的站点憶站点数1SI R12R2 SS 2启始站点乘坐圖线I中转站点1乘坐路线2中转站点2乘坐蹈线3自的站点总姑点数LSI R1S2R2 58 R3S9 3公交车路线查询系统后台数据库设计-关联地名和站点在公交车路线查询系统后台数据库设计一一查询算法 一文中，已经实现了查询站点到站点的路
9、线查询算法，但是，现实中用户不一定使用站点进行 查询，而是使用地名。因此，公交车查询系统数据库必需记录地名与站点的 对应关系，在查询时将地名映射为站点。根据实际情况，某一地点附近通常 有几个站点，因此，地名与站点之间是多对多的关系。显然，只需创建一个地名站点关系表stop_spot(Stop,Spot) 用于储存这个关系即可。数据库关系图如下：SpotPKNameRemarkKoutePKNitmeRcmurk注:Route :路线表Stop :站点表Spot :地名表stop_route : 路线-站点关系表stop_spot :地名-站点关系表1.路线和地名信息维护：以下函数用于维护公交车
10、路线和地名的相关信息字符串分割函数(信息处理及路线查询的存储过程都需要使用到该函数 )：/*函数功能：将String以SplitChar为分隔点分割为字符串数组，结果保留在表 变量中例如 SplitString(A/B,/) 返回表：Value vin dexA 1B 2*/CREATE fun ctio n SplitStri ng(Stri ng varchar(2048),SplitChar char)returns res table(Value varchar(128),vin dex int)asbeg indeclare in dex in t, un it varchar(12
11、8),i next in t,le n in t,i intset in dex=1set i=1set le n=len( Stri ng)while in dex in dexbeg insetun it=ltrim(rtrim(substri ng(Stri ng,i ndex,i next-i ndex)if un itbeg inin sert into res (value, vin dex) values (un it,i)set i=i+1endendset in dex=in ext+1endreturnend插入新的公车路线：/*插入新的公交车路线Route:路线名Stop
12、s:公交车经过的所有站点，站点用-隔开*/CREATE proc In sertRoute(Route varchar(32),Stops_Str varchar(1024) asbeg indeclare stops table( name varchar(32),positi on int)insert stops( name,positi on)select Value,vl ndex from dbo.SplitStri ng(Stops_Str,-)beg in tran t1save tran sp1- 插入路线信息in sert into Route (n ame) values
13、 (Route)if(error0)beg inrollback tran sp1commit tran t1raiserror( 插入路线时发生错误,16,1)returnend- 插入不存在的站点in sert Stop( name)select dist inct n ame from stops ss where n ame not in (select n ame from Stop)if(error0)beg inrollback tran sp1commit tran t1raiserror( 插入路线时发生错误,16,1)returnendin sert stop_route(
14、Stop,Route,Positi on)select ss.n ame,Route,ss.positi on from stops ssif(error0)beg inrollback tran sp1commit tran t1raiserror( 插入路线时发生错误,16,1)returnendcommit tran t1end插入新地名函数：/*插入新地名name地名Stops:地名附近的所有站点，多个站点用/隔开Remark与地名相关的说明*/CREATE proc In sertSpot(n ame varchar(64),Stops_Str varchar(1024),Remar
15、k varchar(1024)asbeg indeclare stops table( name varchar(32)in sert stops select disti net Value fromdbo.SplitStri ng(Stops_Str,/)declare n varchar(32)set n=”select top 1 n=namfpom stops s where namenot in (select namefromstop)if(n)beg inraiserror ( 站点 s 不存在,16,1, n)returnendin sert into Spot (n ame
16、,remark) values (n ame,remark)in sert stop_spot(Stop,Spot)select s.n ame, n ame from stops sif(error0)beg inraiserror ( 插入地点时发生错误,16,1)returnendend2.路线查询在公交车路线查询系统后台数据库设计 查询算法 一文中，使用储存过程InquiryTO ，InquiryT1 和InquiryT2 实现了站点到站点的查询，但是地名可能对应多个站点，因此，当进行地点到地点的查询相当于 站点集到站点集的查询。因此，为了支持使用地名进行查询， 将InquiryTO
17、，InquiryT1和InquiryT2 修改为站点集到站点集的查询：直达路线查询：/*查询站点StartStops到站点EndStopS之间的直达乘车路线，多个站点用/ 分开，如：exec InquiryTO 站点1/站点2,站点3/站点4*/CREATE proc InquiryT0(StartStops varchar(32),EndStops varchar(32)as beg indeclare ss_tab table( name varchar(32)declare es_tab table( name varchar(32)in sert ss_tab select Value
18、 from dbo.SplitStri ng(StartStops,/) in sert es_tab select Value from dbo.SplitStri ng(E ndStops,/) if(exists(select * from ss_tab sst,es_tab est wherefromss_tab sst, es_tab est, RouteTO rwheresst. name=r.StartStopand r.En dStop=est. name end次换乘查询:/*查询站点StartStops到站点EndStops之间的一次换乘乘车路线，多个站点用/分开，如：ex
19、ec InquiryTI 站点1/站点2,站点3/站点4*/CREATE proc InquiryT1(StartStops varchar(32),EndStops varchar(32) asbeg indeclare ss_tab table( name varchar(32)declare es_tab table( name varchar(32)in sert ss_tab select Value from dbo.SplitStri ng(StartStops,/)in sert es_tab select Value from dbo.SplitStri ng(E ndSto
20、ps,/) if(exists(select * from ss_tab sst,es_tab est where sst. name=est .n ame)beg inraiserror ( 起点集和终点集中含有相同的站点,16,1)returnenddeclare stops table( name varchar(32)in sert stops select n ame from ss_tabin sert stops select n ame from es_tab selectsst.name as 起始站点，rl.Route as 乘坐路线1,rl.E ndStop as 中转站
21、点 1,r2.Route as 乘坐路线2,est.name as 目 的站点，r1.StopCou nt+r2.StopCou nt as 总站点数fromss_tab sst,es_tab est,(select * from RouteTO where En dStop not in (select n ame fromstops) r1,RouteTO r2wheresst. name=r1.StartStopand r1.E ndStop=r2.StartStopand r2.E ndStop=est. nameand r1.Router2 .R outeend二次换乘查询：/*查询
22、站点StartStops到站点EndStops之间的二次换乘乘车路线，多个站点用 /分开，如：exec InquiryT2 站点1/站点2,站点3/站点4*/CREATE proc In quiryT2(StartStops varchar(32),E ndStops varchar(32) as beg indeclare ss_tab table( name varchar(32)declare es_tab table( name varchar(32)in sert ss_tab select Value from dbo.SplitStri ng(StartStops,/)in se
23、rt es_tab select Value from dbo.SplitStri ng(E ndStops,/) if(exists(select * from ss_tab sst,es_tab est wheresst. name=est .n ame)beg inraiserror ( 起点集和终点集中含有相同的站点,16,1)returnenddeclare stops table( name varchar(32)in sert stops select n ame from ss_tabin sert stops select n ame from es_tab selectrl
24、.StartStop as 启始站点，rl.Route as 乘坐路线1,rl.E ndStop as 中转站点 1,r2.Route as 乘坐路线2,r2.EndStop as 中转站点 2,r3.Route as 乘坐路线3,r3.EndStop as 目的站点，r1.StopCou nt+r2.StopCou nt+r3.StopCou nt as 总站点数fromss_tab sst,es_tab est,(select * from RouteTO where En dStop not in (select n ame fromstops) r1,(select * from Ro
25、uteTO where En dStop not in (select n ame fromstops) r2,RouteTO r3wheresst. name=r1.StartStopand r1.E ndStop=r2.StartStopand r2.E ndStop=r3.StartStopand r3.E ndStop=est. nameand r1.Router2 .R outeand r2.Router3 .R outeand r3.Router1 .R outeend综合查询：/*查询站点StartStops到站点EndStops之间的乘车路线,先查询直达路线， 如不存在，则查询
26、一次换乘路线，如果直达和一次换乘均不存在，则查询二次换 乘多个站点用/分开，如：exec Inquiry 站点1/站点2,站点3/站点4*/CREATE proc In quiry(StartStops varchar(32),E ndStops varchar(32) asbeg inexec In quiryTO StartStops,E ndStopsif(rowcou nt=0)beg inexec In quiryT1 StartStops,E ndStopsif(rowcou nt=0)beg inexec In quiryT2 StartStops,E ndStopsendend
27、end如要进行地名到地名的路线查询，必需先调用GetStopsOfSpot 获取地名对应的所有站点，在调用In quiry 进行查询。获取地名对应的站点：/*获取地名对应的站点，如有多个站点，用/隔开*/CREATE fun ctio n GetStopsOfSpot(Spot varchar(32)returns varchar(1024)asbeg indeclare stops varchar(1024)set stops=select stops=stops+/+stop from stop_spot where Spot=Spotretur n substri ng(stops,2,
28、le n( stops)-1)end使用地名查询乘车路线示例：declare sps varchar (1024),eps varchar (1024)set sps=dbo.GetStopsOfSpot( 起始地点名称)set eps=dbo.GetStopsOfSpot( 目的地点名称)exec Inquiry sps,eps公交车路线查询系统后台数据库设计-引入步行路线在查询算法和关联地名和站点两篇文章中，已经实现了通过地名或 站点进行路线查询的算法，但是在现实中，从起点到终点不一定全程都是乘 车，例如，有以下3条路线：R1: S1-S2-S3-S4-S5R2: S6-S7-S2-S8R3: S8-S9-S10假如现在要从站点 S1到S7，如果用I
