VBB: Shapes zu weit von den Haltestellen der Trips entfernt
Opened this issue · 1 comments
Beschreibe den Fehler
Bspw. mittels gtfs-via-postgres
lässt sich ermitteln, welche Routes in >0 Trips die größte Entfernung zwischen einem der Stops und dem zugehörigen Shape haben. 213 Routes haben in >0 Trips >0 Stops mint >=150m Entfernung:
SELECT *
FROM (
SELECT DISTINCT ON (route_id) *
FROM (
SELECT *
FROM (
SELECT
trips.route_id, route_short_name,
stop_times.trip_id, trips.shape_id,
stop_sequence = last_value(stop_sequence) OVER (PARTITION BY stop_times.trip_id) AS is_last_in_trip,
stop_sequence, stop_times.stop_id,
ST_Distance(stop_loc::geography, shape::geography) AS dist_to_shape
FROM stop_times
JOIN stops ON stops.stop_id = stop_times.stop_id
JOIN trips ON trips.trip_id = stop_times.trip_id
JOIN routes ON routes.route_id = trips.route_id
JOIN shapes_aggregated shapes ON shapes.shape_id = trips.shape_id
) t
WHERE dist_to_shape > 150
ORDER BY route_id, dist_to_shape DESC
) t
) t
ORDER BY dist_to_shape DESC
route_id | route_short_name | trip_id | shape_id | is_last_in_trip | stop_sequence | stop_id | dist_to_shape |
---|---|---|---|---|---|---|---|
23946_100 | RE8 | 200257011 | 3600 | 0 | 0 | de:12072:900245036:2:51 | 4518.5268908 |
4226_700 | 875 | 198394072 | 4538 | 0 | 18 | de:12064:900321193::1 | 2157.71604428 |
5254_3 | 836 | 200672660 | 1771 | 0 | 13 | de:12065:900203744::1 | 1079.40792258 |
5254_700 | 836 | 200672647 | 1774 | 0 | 14 | de:12065:900203744::1 | 1079.40792258 |
19106_700 | 453 | 200003609 | 11863 | 0 | 11 | de:12073:900341056::1 | 908.0991428900001 |
4268_700 | 920 | 198406737 | 5102 | 0 | 14 | de:12073:900341056::1 | 908.0991428900001 |
16867_100 | RB13 | 201723567 | 3475 | 1 | 7 | 710008013354 | 906.34922627 |
17326_700 | 161 | 198439498 | 6349 | 1 | 6 | de:11000:900183001::1 | 869.9524792 |
5369_700 | 727 | 200628166 | 10294 | 0 | 8 | de:12061:900261004::1 | 838.51256219 |
5365_700 | 724 | 200626927 | 10245 | 0 | 0 | de:12061:900261004::1 | 838.51256219 |
4236_700 | 885 | 198395127 | 4675 | 0 | 5 | de:12064:900320837::1 | 734.37660748 |
14755_3 | 834 | 200672319 | 1899 | 0 | 7 | de:12065:900203744::1 | 725.84790126 |
14755_700 | 834 | 200672323 | 1898 | 0 | 5 | de:12065:900203744::1 | 725.84790126 |
5263_700 | 846 | 200673847 | 1862 | 0 | 2 | de:12065:900200117::1 | 699.14868024 |
5268_700 | 854 | 200674187 | 1879 | 0 | 4 | de:12065:900204019::1 | 667.7788533299999 |
5256_700 | 838 | 200673077 | 1794 | 0 | 14 | de:12065:900204019::1 | 667.7788533299999 |
5256_3 | 838 | 200673059 | 1798 | 0 | 14 | de:12065:900204019::1 | 667.7788533299999 |
19141_700 | 509 | 200011231 | 12311 | 0 | 7 | de:12065:900203938::2 | 653.7113428599999 |
22706_700 | 752 | 200163609 | 346 | 1 | 21 | de:12068:900205669::2 | 631.11639738 |
22713_700 | 766 | 200165282 | 466 | 0 | 0 | de:12068:900205669::1 | 606.74739454 |
17047_700 | EV65 | 201314696 | 3504 | 0 | 0 | 710009550022 | 588.22179347 |
18734_700 | 626 | 196286972 | 11306 | 0 | 0 | de:12062:900415300::1 | 580.98907396 |
17822_700 | 925 | 200521184 | 8968 | 0 | 6 | de:12070:900216065::2 | 572.30730865 |
17535_700 | X54 | 195939767 | 8587 | 0 | 4 | de:11000:900175554::2 | 545.35301625 |
5340_700 | 473 | 200622066 | 9870 | 0 | 10 | de:12061:900261431::1 | 539.5289556 |
23883_700 | 452 | 201325315 | 1514 | 0 | 9 | de:12067:900311363::1 | 534.7624441299999 |
17387_700 | 316 | 195899039 | 7193 | 0 | 0 | de:12054:900230025::3 | 532.96857823 |
20373_100 | RB64 | 196423102 | 3520 | 0 | 0 | 710009550013 | 528.47737695 |
20369_100 | RB65 | 196423142 | 3507 | 1 | 11 | 710009550013 | 528.47737695 |
5244_3 | 813 | 200669550 | 1706 | 1 | 3 | de:12065:900203615::1 | 504.68842343 |
5234_700 | 802 | 200665293 | 1614 | 0 | 0 | de:12065:900203962::2 | 498.3817168 |
5359_700 | 517 | 200625561 | 10183 | 0 | 6 | de:12061:900261300::3 | 476.4519817 |
5366_700 | 725 | 200627355 | 10256 | 1 | 5 | de:12061:900260765::1 | 474.49591906 |
23764_700 | 790 | 200635410 | 10549 | 0 | 0 | de:12061:900260690::2 | 450.57563698 |
22670_100 | RB19 | 196422221 | 3521 | 0 | 4 | 710009550193 | 442.93466188 |
22695_700 | 715 | 200161894 | 172 | 1 | 7 | de:12068:900205278::1 | 438.71239838 |
19132_700 | 492 | 200009085 | 12152 | 0 | 0 | 500000550901 | 437.46636444 |
22690_700 | 707 | 200160250 | 89 | 0 | 0 | de:12068:900205545::1 | 427.0242664 |
13113_700 | 21 | 200481066 | 13298 | 1 | 9 | de:12071:900445337::2 | 421.12682799 |
17812_3 | 904 | 200515857 | 8859 | 1 | 27 | de:12070:900215133::1 | 412.58310857 |
17812_700 | 904 | 200515866 | 8858 | 1 | 31 | de:12070:900215133::1 | 412.58310857 |
17839_700 | 947 | 200529651 | 9088 | 0 | 28 | de:12070:900215133::1 | 410.27349959 |
17839_3 | 947 | 200529645 | 9081 | 0 | 18 | de:12070:900215133::1 | 409.47173044 |
4232_700 | 881 | 198394736 | 4625 | 1 | 20 | de:12064:900321109::1 | 407.29420102 |
4229_700 | 878 | 198394552 | 4585 | 0 | 0 | de:12064:900321109::1 | 407.29420102 |
22701_700 | 743 | 200162578 | 250 | 0 | 18 | de:12068:900205100::1 | 398.43492634 |
4240_700 | 891 | 198396822 | 4729 | 0 | 16 | de:12060:900350165::1 | 384.41303124 |
340_900 | 1 | 200047169 | 3097 | 1 | 10 | de:12051:900275301::5 | 384.06162172 |
23389_100 | RB15 | 196422190 | 3525 | 0 | 2 | 710009550188 | 373.35019418 |
13120_700 | 29 | 198677779 | 13380 | 1 | 23 | de:12071:900445927::1 | 344.51339697 |
22707_700 | 754 | 200163751 | 383 | 0 | 1 | de:12068:900205604::2 | 341.11475627 |
17293_700 | 107 | 195865224 | 5807 | 1 | 14 | de:12065:900200029::2 | 339.54311093 |
19103_700 | 450 | 200002909 | 11783 | 1 | 23 | de:12073:900341340::1 | 323.05808343 |
16868_100 | RB14 | 196422169 | 3488 | 1 | 9 | 710009550203 | 314.12454102 |
5363_700 | 722 | 200626416 | 10197 | 1 | 5 | de:12061:900260620::1 | 309.58387533 |
5364_700 | 723 | 200626704 | 10207 | 0 | 0 | de:12061:900260620::1 | 309.44999109 |
17509_700 | N95 | 195928801 | 8268 | 0 | 5 | de:11000:900176510::2 | 307.67391018 |
17412_700 | 395 | 195901983 | 7313 | 0 | 23 | de:11000:900176510::2 | 307.67391018 |
17361_700 | 245 | 201640034 | 6904 | 0 | 4 | de:11000:900003257::3 | 307.25680663 |
17487_700 | N40 | 201690354 | 8112 | 0 | 34 | de:11000:900003257::3 | 307.25680663 |
13111_700 | 20 | 198674179 | 13291 | 0 | 0 | de:12052:900470202::1 | 299.17561764 |
18362_700 | 955 | 200509892 | 10757 | 0 | 13 | de:12064:900321848::1 | 298.62943341 |
9696_700 | 711 | 200788666 | 2875 | 0 | 0 | de:11000:900072101::2 | 298.621053 |
4503_700 | 710 | 200788170 | 2513 | 0 | 0 | de:11000:900072101::2 | 298.621053 |
18715_700 | 607 | 196286494 | 11060 | 1 | 3 | de:12066:900435861::1 | 294.05182369 |
22692_700 | 712 | 200161154 | 104 | 0 | 8 | de:12068:900205682::1 | 293.13621024 |
22688_700 | 705 | 200159971 | 53 | 1 | 9 | de:12068:900205682::1 | 293.13621024 |
4260_700 | 911 | 198404931 | 5022 | 1 | 3 | de:12060:900350509::1 | 287.59201965 |
4269_700 | 921 | 198406786 | 5127 | 0 | 0 | de:12060:900350509::1 | 287.59201965 |
19146_700 | 515 | 200012009 | 12392 | 0 | 1 | de:12060:900350509::1 | 287.58728304 |
22717_700 | 777 | 200167570 | 515 | 0 | 11 | de:12068:900205513::1 | 283.26896005 |
18718_700 | 610 | 196286543 | 11101 | 0 | 0 | de:12066:900435212::2 | 281.81419868 |
17857_700 | 977 | 200539328 | 9288 | 0 | 5 | de:12070:900215240::1 | 281.06423136 |
17847_700 | 955 | 200534159 | 9175 | 0 | 19 | de:12070:900215240::1 | 281.06423136 |
18721_700 | 613 | 196286584 | 11144 | 1 | 16 | de:12066:900435212::1 | 280.60564541 |
18723_700 | 615 | 196286632 | 11157 | 1 | 9 | de:12066:900435212::1 | 280.60564541 |
18719_700 | 611 | 196286555 | 11114 | 0 | 5 | de:12066:900435212::1 | 280.60564541 |
23874_700 | 436 | 201325140 | 1347 | 0 | 27 | de:12067:900311035::2 | 279.27568158 |
5368_700 | 726 | 200627771 | 10278 | 0 | 34 | de:12061:900260762::2 | 278.26031304 |
6625_700 | 768 | 200793130 | 2815 | 0 | 17 | de:12072:900245980::1 | 276.1114535 |
18717_700 | 609 | 196286521 | 11088 | 0 | 2 | de:12066:900435212::2 | 275.30983272 |
17292_700 | 106 | 201608361 | 5800 | 0 | 0 | de:11000:900009103::7 | 271.48820754 |
16374_700 | 882 | 198394806 | 5177 | 0 | 26 | de:12060:900350726::1 | 268.31773441 |
17846_700 | 954 | 200533761 | 9154 | 0 | 3 | de:12070:900215304::1 | 266.30095641 |
22693_700 | 713 | 200161493 | 137 | 0 | 4 | de:12068:900205346::1 | 264.77293806 |
23872_700 | 434 | 201324995 | 1307 | 0 | 19 | de:12067:900310822::2 | 257.40033097 |
18368_700 | 967 | 200511172 | 10880 | 0 | 5 | de:12064:900321731::1 | 252.08665882 |
18368_3 | 967 | 200511174 | 10880 | 0 | 5 | de:12064:900321731::1 | 252.08665882 |
18369_700 | 968 | 200511490 | 10929 | 0 | 13 | de:12064:900321731::1 | 252.08665882 |
4234_700 | 883 | 198394871 | 4630 | 1 | 22 | de:12064:900321029::1 | 251.45850108 |
4228_700 | 877 | 201192041 | 4561 | 0 | 0 | de:12064:900321029::1 | 251.45850108 |
19087_700 | 413 | 199998372 | 11475 | 1 | 12 | de:12073:900344554::2 | 251.20942577 |
13130_700 | 41 | 198681842 | 13452 | 0 | 19 | de:12071:900445575::1 | 248.0814357 |
13132_700 | 43 | 198682052 | 13472 | 0 | 12 | de:12071:900445575::1 | 248.0814357 |
13114_700 | 22 | 200481126 | 13322 | 0 | 22 | de:12071:900445575::1 | 248.0814357 |
17039_700 | EV33 | 201314205 | 3495 | 0 | 0 | de:12069:900220938:1:50 | 246.5226551 |
4256_700 | 907 | 198403414 | 4946 | 0 | 14 | de:12060:900350203::1 | 246.15446193 |
4495_700 | 702 | 200786925 | 2445 | 1 | 18 | de:12072:900245842::1 | 245.99574251 |
19093_700 | 425 | 199999869 | 11601 | 0 | 0 | de:12073:900340613::1 | 244.09640598 |
19110_700 | 457 | 200003911 | 11893 | 1 | 16 | de:12073:900341009::1 | 243.29787358 |
19124_700 | 475 | 200006855 | 12089 | 1 | 14 | de:12073:900341009::1 | 243.29787358 |
22704_700 | 746 | 200163289 | 318 | 0 | 3 | de:12068:900205552::1 | 243.16422336 |
19156_109 | S1 | 201724093 | 12728 | 0 | 0 | de:12070:900216026:3:53 | 241.33863977 |
5343_700 | 476 | 200622732 | 9935 | 0 | 11 | de:12061:900261919::1 | 241.09259702 |
5344_700 | 477 | 200623159 | 9972 | 1 | 3 | de:12061:900261919::1 | 241.09259702 |
22705_700 | 748 | 200163373 | 334 | 0 | 0 | de:12068:900205533::2 | 240.84753048 |
17378_700 | 282 | 201646618 | 7105 | 0 | 7 | de:11000:900070152::2 | 236.37529437 |
19104_700 | 451 | 200003250 | 11836 | 0 | 0 | de:12073:900340872::2 | 234.12974552 |
13105_700 | 12 | 200475518 | 13240 | 1 | 30 | de:12071:900445899::1 | 232.16988488 |
17065_700 | 596 | 200221449 | 3709 | 0 | 11 | de:12069:900220230::1 | 232.1532477 |
22724_700 | 791 | 200168610 | 584 | 1 | 10 | de:12068:900205331::1 | 231.50071503 |
18728_700 | 620 | 196286782 | 11219 | 0 | 0 | de:12066:900435849::1 | 230.62477245 |
18727_700 | 619 | 196286760 | 11211 | 0 | 0 | de:12066:900435849::1 | 230.62477245 |
17819_700 | 920 | 200519322 | 8943 | 0 | 0 | de:12070:900215351::1 | 225.72960181 |
19116_700 | 465 | 200005119 | 12008 | 0 | 0 | de:12073:900341009::2 | 225.48319807 |
15714_700 | 742 | 200633174 | 10502 | 1 | 11 | de:12061:900260937::1 | 223.19924033 |
16009_700 | 741 | 200632807 | 10521 | 0 | 0 | de:12061:900260937::1 | 223.19924033 |
15374_3 | 519 | 200625898 | 10487 | 0 | 17 | de:12061:900261611::1 | 220.39270757 |
19100_700 | 445 | 200001370 | 11754 | 0 | 0 | de:12073:900340548::1 | 220.12044531 |
5269_700 | 857 | 200674237 | 1883 | 0 | 6 | de:12065:900203503::1 | 219.66309026 |
5269_3 | 857 | 200674226 | 1884 | 0 | 9 | de:12065:900203503::1 | 219.66309026 |
22709_700 | 757 | 200164233 | 409 | 0 | 5 | de:12068:900205497::1 | 219.3122271 |
19135_700 | 502 | 200009809 | 12180 | 1 | 4 | de:12073:900340421::1 | 218.46527355 |
19137_700 | 504 | 200010288 | 12235 | 0 | 7 | de:12073:900340421::1 | 218.46527355 |
19115_700 | 464 | 200004885 | 11992 | 0 | 18 | de:12073:900340985::1 | 218.09591963 |
5237_700 | 805 | 200666269 | 1649 | 0 | 21 | de:12065:900204002::1 | 217.56974003 |
17817_3 | 917 | 200518138 | 8913 | 0 | 0 | de:12070:900215342::1 | 213.88388749 |
6629_700 | 771 | 200793646 | 2856 | 0 | 0 | de:12072:900245346::2 | 213.78685473 |
17063_700 | 559 | 200220824 | 3666 | 0 | 8 | de:12069:900222090::2 | 213.74016295 |
22711_700 | 762 | 200164852 | 444 | 1 | 19 | de:12068:900205266::1 | 213.55913031 |
4248_700 | 899 | 201198070 | 4839 | 0 | 3 | de:12060:900350953::2 | 212.16276656 |
19715_700 | RB43 | 201729757 | 12854 | 1 | 4 | de:12071:900445593:2:52 | 209.14649549 |
5341_700 | 474 | 200622234 | 9903 | 0 | 20 | de:12061:900260434::1 | 206.36724741 |
10676_700 | 676 | 200498781 | 2253 | 0 | 13 | de:12063:900210803::2 | 203.90564526 |
19092_700 | 424 | 199999602 | 11587 | 0 | 7 | de:12073:900340214::1 | 203.77079341 |
6617_700 | 751 | 200790741 | 2727 | 0 | 0 | de:12072:900245287::1 | 202.60834397 |
4515_700 | 757 | 200791737 | 2628 | 0 | 0 | de:12072:900245287::1 | 202.60834397 |
24194_700 | EV8 | 201721073 | 3651 | 0 | 0 | 710008010173 | 201.85612584 |
6621_700 | 756 | 200791655 | 2791 | 0 | 0 | de:12072:900245330::2 | 201.67207558 |
6620_700 | 754 | 200791319 | 2769 | 0 | 0 | de:12072:900245330::2 | 201.67207558 |
18364_700 | 957 | 200510392 | 10807 | 0 | 0 | de:12064:900321547::1 | 198.60716232 |
18363_700 | 956 | 200510165 | 10789 | 0 | 0 | de:12064:900321547::1 | 198.60716232 |
18737_700 | 643 | 196287019 | 11323 | 0 | 12 | de:12066:900435849::1 | 197.71217814 |
22687_700 | 704 | 200159934 | 35 | 0 | 0 | de:12068:900205664::1 | 196.99153571 |
17311_700 | 134 | 201617387 | 6110 | 0 | 37 | de:11000:900027354::2 | 196.97338301 |
17325_700 | 160 | 195878057 | 6331 | 0 | 29 | de:11000:900195521::2 | 195.64376263 |
17821_3 | 924 | 200520645 | 8957 | 0 | 7 | de:12070:900215510::1 | 194.5119221 |
17351_700 | 204 | 201636504 | 6787 | 0 | 15 | de:11000:900057154::2 | 193.41803848 |
23886_700 | A400 | 201325532 | 1546 | 0 | 0 | de:12067:900310007::2 | 193.24532518 |
19149_700 | 519 | 200604193 | 12470 | 0 | 0 | de:12073:900340242::2 | 192.82737066 |
22697_700 | 719 | 200162025 | 204 | 0 | 7 | de:12068:900205682::1 | 192.52094719 |
17826_700 | 934 | 200522659 | 9013 | 0 | 14 | de:12070:900216010::1 | 189.76239969 |
10914_700 | 773 | 200793989 | 2916 | 0 | 12 | de:12072:900245114::1 | 189.54294147 |
22726_700 | 794 | 200168860 | 616 | 0 | 3 | de:12068:900205434::1 | 189.45057811 |
4225_700 | 874 | 201191505 | 4514 | 0 | 7 | de:12064:900321058::1 | 187.03228031 |
19096_700 | 431 | 200000331 | 11648 | 0 | 0 | de:12073:900340330::1 | 185.08351068 |
17432_1000 | F21 | 195905469 | 7461 | 0 | 0 | de:11000:900185501::7 | 185.03853973 |
17846_3 | 954 | 200533432 | 9160 | 0 | 2 | de:12070:900216059::1 | 183.57254117 |
17848_700 | 957 | 200535041 | 9195 | 0 | 5 | de:12070:900216059::1 | 183.57254117 |
13126_700 | 37 | 198680412 | 13419 | 0 | 32 | de:12071:900446038::1 | 182.38781185 |
5377_700 | 734 | 200630584 | 10415 | 1 | 4 | de:12061:900260020::1 | 181.65858361 |
17819_3 | 920 | 200519541 | 8928 | 0 | 0 | de:12070:900216052::1 | 181.0536604 |
19129_700 | 486 | 200008452 | 12126 | 1 | 3 | de:12073:900341111::1 | 180.28647483 |
18915_700 | 611 | 201015432 | 4119 | 0 | 0 | de:12069:900220008::3 | 179.3263782 |
10912_700 | 752 | 200790815 | 2884 | 0 | 7 | de:12072:900245295::1 | 179.07008292 |
15713_700 | 796 | 200795335 | 2965 | 0 | 2 | de:12072:900245295::1 | 179.07008292 |
4514_700 | 750 | 200790612 | 2609 | 1 | 20 | de:12072:900245295::1 | 179.07008292 |
4521_700 | 772 | 200793961 | 2691 | 1 | 14 | de:12072:900245295::1 | 179.07008292 |
6618_700 | 753 | 200791076 | 2745 | 1 | 14 | de:12072:900245295::1 | 179.07008292 |
10913_700 | 755 | 200791477 | 2902 | 0 | 4 | de:12072:900245295::1 | 179.07008292 |
5354_700 | 510 | 200625269 | 10139 | 0 | 4 | de:12061:900261908::1 | 179.00636839 |
1935_700 | 666 | 200495994 | 2046 | 1 | 17 | de:12063:900210214::3 | 175.05540272 |
22714_700 | 770 | 200166770 | 496 | 0 | 23 | de:12068:900205196::1 | 174.3345054 |
17850_3 | 960 | 200537396 | 9230 | 0 | 0 | de:12070:900215801::1 | 174.06316606 |
5335_700 | 468 | 200620803 | 9768 | 0 | 6 | de:12061:900261597::1 | 169.73642873 |
5337_700 | 470 | 200621149 | 9825 | 0 | 7 | de:12061:900261597::1 | 169.73642873 |
18887_3 | 555 | 200999411 | 3839 | 0 | 0 | de:12069:900220152:3:53 | 169.50160382 |
5241_700 | 809 | 200668934 | 1684 | 0 | 22 | de:12065:900203380::1 | 168.70247341 |
17827_700 | 935 | 200522840 | 9023 | 0 | 0 | de:12070:900216054::1 | 168.05964372 |
19098_700 | 435 | 200000832 | 11682 | 1 | 10 | de:12073:900340308::1 | 167.29182285 |
13109_700 | 18 | 198672145 | 13274 | 1 | 3 | de:12052:900470151::1 | 166.93676384 |
18714_700 | 606 | 196286471 | 11044 | 0 | 0 | de:12066:900435557::1 | 165.76682129 |
17854_700 | 974 | 200538291 | 9253 | 0 | 15 | de:12070:900215762::1 | 165.34470675 |
17844_700 | 952 | 200531936 | 9139 | 0 | 0 | de:12070:900215094::1 | 163.92618736 |
5336_700 | 469 | 200620965 | 9809 | 1 | 4 | de:12061:900260409::1 | 163.7939204 |
23871_700 | 433 | 201324970 | 1267 | 0 | 0 | de:12067:900310001::6 | 163.39067158 |
23870_700 | 432 | 201324934 | 1224 | 0 | 0 | de:12067:900310001::6 | 163.39067158 |
19118_700 | 468 | 200005509 | 12025 | 0 | 0 | de:12073:900340069::2 | 163.07919437 |
17833_700 | 941 | 200524039 | 9050 | 0 | 3 | de:12070:900215685::1 | 163.07575519 |
17858_700 | 978 | 200539433 | 9299 | 0 | 7 | de:12070:900215685::1 | 163.07575519 |
22698_700 | 740 | 200162249 | 211 | 0 | 0 | de:12068:900205202::2 | 162.4153904 |
9806_700 | 585 | 195599972 | 5625 | 0 | 6 | de:12062:900416059::1 | 162.18777045 |
9805_700 | 584 | 195599946 | 5599 | 0 | 9 | de:12062:900416059::1 | 162.18777045 |
24049_100 | RB21 | 201725652 | 13055 | 0 | 0 | de:11000:900007102:5:59 | 161.90287801 |
15026_700 | 30 | 198678490 | 13565 | 0 | 3 | de:12071:900445734::1 | 160.85480796 |
5266_700 | 851 | 200674152 | 1875 | 1 | 16 | de:12065:900203735::1 | 160.76772009 |
5264_3 | 847 | 200673922 | 1869 | 0 | 0 | de:12065:900203735::1 | 160.76772009 |
5264_700 | 847 | 200673928 | 1868 | 0 | 0 | de:12065:900203735::1 | 160.76772009 |
5266_3 | 851 | 200674148 | 1878 | 0 | 0 | de:12065:900203735::1 | 160.76772009 |
23847_700 | 892 | 200222782 | 888 | 0 | 0 | de:12071:900445177::1 | 158.95792479 |
17856_3 | 976 | 200539047 | 9281 | 0 | 4 | de:12070:900215344::2 | 157.97952788 |
24054_100 | RB10 | 201725418 | 13160 | 0 | 0 | de:11000:900007102:5:58 | 157.10361571 |
1871_700 | 981 | 200783871 | 11336 | 1 | 16 | de:12053:900360055::1 | 156.47434133 |
24044_100 | RB23 | 201725920 | 12959 | 0 | 0 | de:11000:900024101:1:50 | 153.81476417 |
24045_100 | RE2 | 201726295 | 13023 | 0 | 0 | de:11000:900024101:1:50 | 153.81476417 |
23945_100 | RE1 | 198162459 | 3554 | 0 | 0 | de:11000:900024101:1:50 | 153.81476417 |
4507_700 | 716 | 200789778 | 2543 | 0 | 10 | de:12072:900245462::1 | 153.50647935 |
4493_700 | 701 | 200786096 | 2443 | 0 | 13 | de:12072:900245462::1 | 153.50647935 |
24051_2 | FEX | 201725402 | 12959 | 0 | 0 | de:11000:900024101:1:51 | 152.8430536 |
4227_700 | 876 | 198394185 | 4543 | 0 | 0 | de:12064:900321167::1 | 152.02282757 |
4235_700 | 884 | 198395013 | 4642 | 0 | 0 | de:12064:900321167::1 | 152.02282757 |
4237_700 | 886 | 198395365 | 4702 | 0 | 0 | de:12064:900321167::1 | 152.02282757 |
24053_100 | RE7 | 201726597 | 13120 | 1 | 5 | de:11000:900024101:2:52 | 151.71710994 |
Insgesamt haben 28162 von 232k Trips mind. 1 Stop mit einer Entfernung von mind. 50m zum Shape:
SELECT count(*)
FROM (
SELECT DISTINCT ON (trip_id) *
FROM (
SELECT *
FROM (
SELECT
stop_times.trip_id,
ST_Distance(stop_loc::geography, shape::geography) AS dist_to_shape
FROM stop_times
JOIN stops ON stops.stop_id = stop_times.stop_id
JOIN trips ON trips.trip_id = stop_times.trip_id
JOIN shapes_aggregated shapes ON shapes.shape_id = trips.shape_id
) t
WHERE dist_to_shape > 50
ORDER BY trip_id, dist_to_shape DESC
) t
) t
-- 28162
Referenz
[...] Shapes do not need to intercept the location of Stops exactly, but all Stops on a trip should lie within a small distance of the shape for that trip [...].
shapes.txt
-Referenz auf gtfs.org
Aktualisierungszeitpunkt der GTFS-Daten:
2023-01-06
Downloadlink der GTFS-Daten:
Aktuell unter https://www.vbb.de/vbbgtfs bzw. als dauerhaft unter https://vbb-gtfs.jannisr.de/2023-01-06/ abrufbar.
cc @OpenDataVBB