SQL 公交数据
2018-09-22 15:35:07 204

create or replace function is_number(i_str varchar2) return number is
  v_number number;
begin
  select to_number(i_str) into v_number from dual;
  if v_number is not null then
    return 1;
  else
    return 0;
  end if;
EXCEPTION
  WHEN OTHERS THEN
    return 0;
end;
/

--查找重复站点名称
select *
  from (select count(t.station_name) station_name_count, t.station_name
          from SDE.BUSSITE t
         group by t.station_name) m
 where m.station_name_count > 1

--插入线路
insert into SD_MAP_BUSLINE
(id,CITY_ID,LINE_NAME,LINE_TYPE,KM,START_TIME,END_TIME)
select rownum rnum, n.*
  from (select 1,
               m.line_name,
               'bus',
               (select round(sum(b.shape_leng), 5)
                  from SDE.BUSLINE b
                 where b.line_name = m.line_name) km,
               (select b.start_time
                  from SDE.BUSLINE b
                 where b.line_name = m.line_name
                   and b.line_seq = 1),
               (select b.end_time
                  from SDE.BUSLINE b
                 where b.line_name = m.line_name
                   and b.line_seq = 1)
          from (select a.line_name from SDE.Busline a group by a.LINE_NAME) m
         order by m.line_name) n --多加一层为了取rownum

--插入站点
insert into SD_MAP_BUSTATION
(line_id,
 station_name,
 station_seq,
 station_pointx,
 station_pointy)
select (select b.id from SD_MAP_BUSLINE b where b.line_name = a.line_name) line_id,
       decode(is_number(a.line_station_start), 0,
              a.line_station_start,
              (select station_name
                 from SDE.Bussite
                where station_id = a.line_station_start)) station_name,
       a.line_seq,
       decode(is_number(a.line_station_start), 0,
              (select point_x
                 from SDE.Bussite
                where station_name = a.line_station_start),
              (select point_x
                 from SDE.Bussite
                where station_id = a.line_station_start)) station_pointx,                
        decode(is_number(a.line_station_start), 0,
              (select point_y
                 from SDE.Bussite
                where station_name = a.line_station_start),
              (select point_y
                 from SDE.Bussite
                where station_id = a.line_station_start)) station_pointy       
  from SDE.Busline a, SD_MAP_BUSLINE c
 where a.line_name = c.line_name
 order by c.id,a.line_seq

--检查空站名
select * from SD_MAP_BUSTATION t where t.station_name is null

--直达线路
select 
t1.station_name,
t2.station_name,
(select b.line_name from SD_MAP_BUSLINE b where b.id = t1.line_id),
t2.station_seq - t1.station_seq
from 
SD_MAP_BUSTATION t1,SD_MAP_BUSTATION t2
where 1=1
and t1.line_id = t2.line_id
and t1.station_seq < t2.station_seq
and t1.station_name = '高屿'
and t2.station_name = '保税区'

--所有直达视图
create or replace view routet0 as
select
t1.station_name start_station,
t2.station_name end_station,
(select b.line_name from SD_MAP_BUSLINE b where b.id = t1.line_id) line_name,
(t2.station_seq - t1.station_seq) station_count
from
SD_MAP_BUSTATION t1,SD_MAP_BUSTATION t2
where 1=1
and t1.line_id = t2.line_id
and t1.station_seq < t2.station_seq;

--一次换乘
select r1.start_station,
       r1.line_name,
       r1.stop_station,
       r2.line_name,
       r2.stop_station,
       r1.station_count + r2.station_count
  from RouteT0 r1, RouteT0 r2
 where 1 = 1
   and r1.start_station = '高屿'
   and r1.stop_station = r2.start_station
   and r2.stop_station = '中心小学'