如何使用日历异常使用GTFS生成准确的时间表? [英] How do I use calendar exceptions to generate accurate schedules using GTFS?

问题描述

我在弄清楚GTFS查询以获取给定停止ID和给定方向的下20个时间表时遇到了麻烦.

I'm having trouble figuring out the GTFS query to obtain the next 20 schedules for a given stop ID and a given direction.

我知道站点ID，行进方向ID，时间(现在)和日期(今天)

I know the stop ID, the trip direction ID, the time (now) and the date (today)

我写了

SELECT DISTINCT ST.departure_time FROM stop_times ST
JOIN trips T ON T._id = ST.trip_id
JOIN calendar C ON C._id = T.service_id
JOIN calendar_dates CD on CD.service_id = T.service_id
WHERE ST.stop_id =  3377699724118483 
AND T.direction_id = 0
AND ST.departure_time >= "16:00:00"
AND 
( 
( C.start_date <= 20140607 AND C.end_date >= 20140607 AND C.saturday= 1 ) // regular service today
AND (     ( CD.date != 20140607 ) // no exception today
OR  ( CD.date = 20140607 AND CD.exception_type = 1 ) // or ADDED exception today
)
)
ORDER BY stopTimes.departure_time LIMIT 20

这将导致找不到记录. 如果删除最后一部分，处理CD表(即删除或添加的例外)，则效果很好.

This results in no record being found. If a remove the last part, dealgin with the CD tables (i.e. the removed or added exceptions), it works perfectly fine.

所以我认为我误写了有关异常的检查. 正如上面用//注释写的，我想检查一下

So I think I'm miswriting the check on the exceptions. As written above with // comments, I want to check that

• 今天是常规服务(通过检查日历表)
• 今天没有移除例外(或在这种情况下，与该服务ID相对应的行程未包含在计算中)
• 如果今天还有例外，则相应的行程应包含在计算中

你能帮我吗?

推荐答案

由于calendar和calendar_dates表.

我要做的是使用第二个内部查询在请求的日期构建活动服务ID的集合，然后将外部查询与此集合结合以仅包含与该日期相关的结果.试试这个:

What I do is use a second, inner query to build the set of active service IDs on the requested date, then join the outer query against this set to include only results relevant for that date. Try this:

SELECT DISTINCT ST.departure_time FROM stop_times ST
  JOIN trips T ON T._id = ST.trip_id
  JOIN (SELECT _id FROM calendar
          WHERE start_date <= 20140607
            AND end_date >= 20140607
            AND saturday = 1
          UNION
            SELECT service_id FROM calendar_dates
              WHERE date = 20140607
                AND exception_type = 1
          EXCEPT
            SELECT service_id FROM calendar_dates
              WHERE date = 20140607
                AND exception_type = 2
       ) ASI ON ASI._id = T.service_id
  WHERE ST.stop_id = 3377699724118483 
    AND T.direction_id = 0
    AND ST.departure_time >= "16:00:00"
  ORDER BY ST.departure_time
  LIMIT 20

这篇关于如何使用日历异常使用GTFS生成准确的时间表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！