Calendar.txt, am I using it right?

Real-time Public Transport
1

Hi all,

I’m trying to get a list of ‘next trains’ at a random Railway station, but I’m seeing triple results.

I thought perhaps I was seeing multiple days of data, but the dates and times are all the same. Perhaps I am not implementing the ‘Calendar.txt’ data properly?

Is it sufficient to select from ALL routes where calendar.date = DATE(NOW()) ?

See picture for query and sample of results.

2678×2196 948 KB

2

You’ll need to use calendar for the main entries and then calendar_dates for items to include and exclude. A sample query would be:

-- Active services SELECT service_id FROM calendar WHERE start_date <= '20160921' AND end_date >= '20160921' AND wednesday = 1 UNION -- Services to be excluded SELECT service_id FROM calendar_dates WHERE date = '20160921' AND exception_type = 1 EXCEPT -- Services to be added SELECT service_id FROM calendar_dates WHERE date = '20160921' AND exception_type = 2

If you incorporate that does it clean up the results at all?

1 Like
3

Hi Phiali, thanks for your fast response!

That’s an excellent suggestion regarding the excluded dates… I did not understand what the intention of those fields were until you had explained! The documentation is very vague on the subject…

Here’s my attempt at replicating your SQL statement (“Except” doesn’t work on MySQL)

SELECT `service_id`,monday,tuesday,wednesday,thursday,friday,saturday,sunday, `remove`, `add` FROM (
  SELECT `service_id`,monday,tuesday,wednesday,thursday,friday,saturday,sunday, SUM(`add`) "add", SUM(`remove`) "remove" FROM (

    SELECT `calendar`.`service_id`, 1 "add", 0 "remove", monday,tuesday,wednesday,thursday,friday,saturday,sunday FROM calendar
    LEFT JOIN `calendar_dates` ON `calendar`.`service_id` = `calendar_dates`.`service_id`
    WHERE start_date <= NOW() AND end_date >= NOW() AND exception_type = 2

    UNION

    SELECT `calendar`.`service_id`, 0 "add", 1 "remove", monday,tuesday,wednesday,thursday,friday,saturday,sunday FROM calendar
    LEFT JOIN `calendar_dates` ON `calendar`.`service_id` = `calendar_dates`.`service_id`
    WHERE start_date <= NOW() AND end_date >= NOW() AND exception_type = 1

  ) serviceDescriptors GROUP BY `service_id`

) filteredServices /*WHERE `remove` = 0 AND `add` = 1*/;

I’ve implemented this SQL into my query, but it doesn’t seem to have augmented the results much.

I’ve had to suddenly drop this project - we’re handing development over to a third party agency. Since my priorities are shifted I can’t spend time to verify if your solution will work. Hopefully it’ll help the next person! I’ll try come back to this to make a more fuller answer but won’t guaruntee. Thanks again Phiali!

4

Just quickly - actually, maybe your solution will work. I think I spotted an issue with my SQL. Like I mentioned, I’ll have to look at it another day if it all. Thanks again

5

hey guys, any recommended updates to documentation is welcome. If @TheWaste you want to let us know what to update it to we can do that.