Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

VBB: Shapes zu weit von den Haltestellen der Trips entfernt #126

Open
derhuerst opened this issue Jan 19, 2023 · 1 comment
Open

VBB: Shapes zu weit von den Haltestellen der Trips entfernt #126

derhuerst opened this issue Jan 19, 2023 · 1 comment
Labels
Fehler Hier liegt ein Fehler vor VBB Verkehrsverbund Berlin-Brandenburg

Comments

@derhuerst
Copy link
Collaborator

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.
@derhuerst derhuerst added Fehler Hier liegt ein Fehler vor VBB Verkehrsverbund Berlin-Brandenburg labels Jan 19, 2023
@derhuerst
Copy link
Collaborator Author

cc @OpenDataVBB

@hbruch hbruch mentioned this issue Apr 24, 2023
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Fehler Hier liegt ein Fehler vor VBB Verkehrsverbund Berlin-Brandenburg
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@derhuerst