mfdz/GTFS-Issues

VBB: Shapes zu weit von den Haltestellen der Trips entfernt

Opened this issue · 1 comments

derhuerst commented

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.