network_flow_restriction query filters out clusters

planarnetwork / dtd2mysql

MySQL / MariaDB import for DTD feeds (fares, timetable and routeing)

30 stars 10 forks source link

network_flow_restriction query filters out clusters #47

Closed pies closed 5 years ago

pies commented 5 years ago

https://github.com/planarnetwork/dtd2mysql/blob/72b72606c25353ea92c5e3dbb5396c0bb374db32/src/cli/CleanFaresCommand.ts#L146

oc.cluster_id IN (SELECT SUBSTR(uic_code, 3, 4) AS nlc FROM location_railcard WHERE railcard_code = 'NEW')

This removes (all?) flows to clusters because location_railcard does not contain (some?) clusters.

linusnorton commented 5 years ago

I don't think so, I think it extracts the NLC from the cluster ID and uses that as the NLC... but I must admit I'm not 100% on that.

pies commented 5 years ago

Well, we were missing some restrictions, like DFD to RAM, because RAM is present in flows as its clusters and those clusters were not included in location_railcard.

Our implementation removes this restriction and adds SVR and SVS to the list of tickets, which bumps the number of rows significantly (to 400k). It fixes some issues (admittedly not huge ones) and makes it a more general tool for checking whether there's an off-peak on the particular flow. I think it's worth the extra resources, but YMMV obviously.

linusnorton commented 5 years ago

Yeah, I would like that patch if you have it available

pies commented 5 years ago

https://github.com/planarnetwork/dtd2mysql/pull/48