gtfs - How can I rearrange the table in MySQL? -


there table stop_times.txt format (gtfs) like: 

+------------------+---------------+ |     trip_id      | stop_sequence | +------------------+---------------+ | 4503599630773892 |      0        | | 4503599630773892 |      1        | |       ...        |      ...      | | 4503599630773892 |      27       | | 4503599630810392 |      0        | | 4503599630810392 |      1        | |       ...        |      ...      | | 4503599630810392 |      17       | | 4503599631507892 |      0        | | 4503599631507892 |      1        | |       ...        |      ...      | | 4503599631507892 |      29       | |       ...        |      ...      | +------------------+---------------+

my expecting result is:

+------------------+------------+-----------+ |     trip_id      | first_stop | last_stop | +------------------+------------+-----------+ | 4503599630773892 |     0      |    27     | | 4503599630810392 |     0      |    17     | | 4503599631507892 |     0      |    19     | |       ...        |    ...     |    ...    | +------------------+------------+-----------+

ps: title might not precise. please refine it.

one further question: how can add stop_name corresponds stop_sequence table?

enter image description here

here incorrect code reason stop name of first_stop , last_stop should different corresponding different stop_id:

(select routes.route_short_name, min(stop_times.stop_sequence) first_stop, stops.stop_name, max(stop_times.stop_sequence) last_stop, stops.stop_name stop_times join stops on stops.stop_id=stop_times.stop_id join trips on stop_times.trip_id=trips.trip_id  join routes on routes.route_id=trips.route_id  group stop_times.trip_id);

edit: make after several hours' work. here key source code:

select t1.trip_id, t1.stop_sequence, t1.stop_id, t2.stop_sequence, t2.stop_id     -- create new table t1: trip_id, stop_sequence=0, stop_id (first stop)     (select st_first1.trip_id, st_first1.stop_sequence, st_first1.stop_id     stop_times st_first1     inner join          -- filter out first stop: trip_id, stop_sequence=0         (select stop_times.trip_id, min(cast(stop_times.stop_sequence unsigned)) first_stop         stop_times         group stop_times.trip_id         ) st_first2     on st_first1.trip_id=st_first2.trip_id , st_first1.stop_sequence=st_first2.first_stop     ) t1  left join -- combine t1 , t2      -- create new table t2: trip_id, stop_sequence=max, stop_id (last stop)     (select st_last1.trip_id, st_last1.stop_sequence, st_last1.stop_id     stop_times st_last1     inner join         -- filter out last stop: trip_id, stop_sequence=max         (select stop_times.trip_id, max(cast(stop_times.stop_sequence unsigned)) last_stop         stop_times         group stop_times.trip_id         ) st_last2     on st_last1.trip_id=st_last2.trip_id , st_last1.stop_sequence=st_last2.last_stop     ) t2  on t1.trip_id=t2.trip_id

you can group by trip_id , take min , max stop_sequence values obtain first , last stops, respectively.

select distinct st.trip_id, s.stop_name, t.first_stop, t.last_stop stop_times st inner join stops s on st.stop_id = s.stop_id right join (     select trip_id, min(stop_sequence) first_stop, max(stop_sequence) last_stop     stop_times     group trip_id ) t on t.trip_id = st.trip_id

Comments

Post a Comment

Popular posts from this blog

html - Outlook 2010 Anchor (url/address/link) -

i'm trying link specific pdf page msoutlook'10 or msword'10, #page=... anchor particularly useful , works fine elsewhere. unfortunately when click [ctr-k] in outlook or winword, links re-formatted assuming unc path. pdf file located on shared drive. using "file:" prefix links open pdf in adobe reader (outside of browser), , "http:" open inside browser. issue seem need open in browser "file:" prefix in order anchor work. know anchors work inside browser. outlook uses windows/explorer default program (based on file extension) seems open in adobe reader neglecting anchor. is aware of solution, link specific page or bookmark of shared-pdf ms outlook and/or winword link? common references: mailermailer.com/...anchor-tags-html-emails campaignmonitor.com/...anchor-links-in-email-newsletters/ stackoverflow.com/...anchor-in-pdf-document
Read more

javascript - Why does running this loop 9 times take 100x longer than running it 8 times? -

consider code: test = function() { } t = new test(); (var = 0; < 8; i++) { result = t instanceof test; } if change number of iterations 8 9 , loop take 100 times longer complete in latest version of firefox (41.0.1). tested on 2 different pcs , magic limit 8. here jsperf test used: http://jsperf.com/instanceof-8-times-vs-9-times does have idea why might happen? seems specific instanceof . not happen if else object, example check property. note: filed bugzilla bug this. jan de mooij mozilla team has posted details in bugzilla thread . here's simplistic interpretation of highly technical answers: in i < 8 case, firefox smart enough hoist result = t instanceof test; statement out of loop (according tests, doesn't seem omit altogether). in i < 9 case, apparently doesn't optimization . why? reason not clear, related fact 9 iterations threshold above function considered "hot" enough run through jit compiler. i < 8 cas...
Read more

Getting gateway time-out Rails app with Nginx + Puma running on Digital Ocean -

following this guide setup app on digital ocean droplet, not working. site not loading , it's throwing: "gateway time-out". the puma server works, since ps aux | grep puma prints: root 15878 0.0 0.4 376832 2168 ? sl 12:50 0:00 puma 2.13.4 (unix:///var/www/myapp/shared/tmp/sockets/myapp-puma.sock) [20150930164940] root 15881 0.0 0.7 903676 3692 ? sl 12:50 0:06 puma: cluster worker 0: 15878 [20150930164940] root 24654 0.0 0.1 11748 916 pts/2 s+ 18:00 0:00 grep --color=auto puma my nginx config: user root; worker_processes 1; events { worker_connections 1024; } http { upstream puma { server unix:///var/www/myapp/shared/tmp/sockets/myapp-puma.sock; } server { listen 80; server_name myapp.com; root /var/www/myapp/current/public; access_log /var/www/myapp/current/log/access.log; error_log /...
Read more