forked from ChelseaKR/gtfs-scorecard
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathindex.html
More file actions
242 lines (242 loc) · 13.5 KB
/
Copy pathindex.html
File metadata and controls
242 lines (242 loc) · 13.5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Query the dataset in your browser — GTFS Scorecard</title>
<meta name="description" content="Run SQL against the covered GTFS quality dataset in your browser with DuckDB: grades, category scores, and freshness for every tracked feed record.">
<link rel="canonical" href="https://gtfsscorecard.org/query/">
<meta property="og:type" content="website">
<meta property="og:title" content="Query the dataset in your browser — GTFS Scorecard">
<meta property="og:description" content="Run SQL against the covered GTFS quality dataset in your browser with DuckDB: grades, category scores, and freshness for every tracked feed record.">
<meta property="og:url" content="https://gtfsscorecard.org/query/">
<meta property="og:image" content="https://gtfsscorecard.org/og.png">
<meta property="og:image:width" content="1200">
<meta property="og:image:height" content="630">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:image" content="https://gtfsscorecard.org/og.png">
<link rel="stylesheet" href="/src/styles.css">
<link rel="icon" href="data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 32 32'%3E%3Ccircle cx='16' cy='16' r='13' fill='%23204e3a'/%3E%3Ccircle cx='16' cy='16' r='5' fill='%23f2f3ee'/%3E%3C/svg%3E">
<script>
/* Apply the saved theme before first paint to avoid a flash (WCAG 1.4.8). */
try {
var t = localStorage.getItem("scorecard-theme");
if (t && ["light", "contrast", "dark"].indexOf(t) >= 0)
document.documentElement.setAttribute("data-theme", t);
} catch (e) {}
</script>
<script src="/src/theme.js" defer></script>
<script src="/src/nav.js" defer></script>
<noscript><style>
/* Without JS the menu button cannot expand the collapsed nav, so show the
stacked nav permanently and hide the button (content stays operable
without scripting). nav.js never runs here, so nothing double-toggles. */
@media (max-width: 1040px) {
.nav-menu-btn { display: none !important; }
.nav-cluster { display: flex !important; position: static; }
}
</style></noscript>
</head>
<body>
<a class="skip-link" href="#main">Skip to main content</a>
<header class="site-header"><div class="wrap"><a class="brand" href="/"><span class="brand-mark" aria-hidden="true"></span><span class="brand-name">GTFS Scorecard</span></a><button class="nav-menu-btn" type="button" aria-expanded="false" aria-controls="nav-cluster"><span aria-hidden="true">☰</span> Menu</button><div class="nav-cluster" id="nav-cluster"><nav class="nav-stops" aria-label="Primary"><a class="nav-stop" href="/agencies/"><span class="pip" aria-hidden="true"></span>Find an agency</a><a class="nav-stop" href="/pulse/"><span class="pip" aria-hidden="true"></span>Coverage</a><a class="nav-stop" href="/focus/"><span class="pip" aria-hidden="true"></span>Focus areas</a><a class="nav-stop" href="/tools/" aria-current="page"><span class="pip" aria-hidden="true"></span>Tools</a><a class="nav-stop" href="/how-to-read/"><span class="pip" aria-hidden="true"></span>How it works</a><a class="nav-stop" href="/about/"><span class="pip" aria-hidden="true"></span>About</a></nav><div id="theme-control"></div></div></div></header>
<main id="main" class="wrap" tabindex="-1">
<nav class="breadcrumb" aria-label="Breadcrumb"><ol itemscope itemtype="https://schema.org/BreadcrumbList"><li itemprop="itemListElement" itemscope itemtype="https://schema.org/ListItem"><a itemprop="item" href="/"><span itemprop="name">Home</span></a><meta itemprop="position" content="1"></li><li itemprop="itemListElement" itemscope itemtype="https://schema.org/ListItem"><span itemprop="name" aria-current="page">Query the dataset</span><meta itemprop="position" content="2"></li></ol></nav>
<a class="backlink" href="/">← Home</a>
<h1 class="page-title">Query the dataset.</h1>
<p class="page-lede">Run SQL against the covered scorecard dataset, right here in your
browser. One table, <code>agencies</code>, holds every published feed record's latest snapshot:
<code>id</code>, <code>name</code>, <code>date</code>, <code>grade</code>,
<code>score</code>, <code>days_until_expiry</code>, producer-version fields,
<code>comparison_eligible</code>, and the
<code>correctness</code>, <code>freshness</code>, <code>completeness</code>, and
<code>realtime</code> category scores. Nothing is sent to a server: the engine
(DuckDB) and the data load into the page and the query runs on your machine.</p>
<p class="page-lede">Prefer a file? The same data is
<a href="/api/v1/agencies.parquet">agencies.parquet</a>,
<a href="/catalog.csv">catalog.csv</a>, and the JSON described in the
<a href="https://github.com/ChelseaKR/gtfs-scorecard/blob/main/docs/api.md">data
dictionary</a>.</p>
<p><strong>Comparison warning:</strong> the table includes old-methodology,
duplicate-identity, and otherwise non-comparable rows so the public record stays complete.
Do not compare or average scores unless you filter
<code>comparison_eligible = true</code> and inspect the current contract in
<a href="/api/v1/agencies.json">agencies.json</a>. The examples below are support
worklists and provenance checks, not rankings.</p>
<form aria-label="SQL query" class="query-form">
<label for="query-sql">SQL to run against the <code>agencies</code> table</label>
<textarea id="query-sql" class="outreach-text" rows="6"
spellcheck="false">SELECT id, name, date, days_until_expiry
FROM agencies
WHERE days_until_expiry BETWEEN -365 AND 60
ORDER BY days_until_expiry, name
LIMIT 50</textarea>
<div class="map-filter-row">
<button type="submit" class="copy-btn" id="query-run">Run</button>
<button type="button" class="copy-btn query-example" data-sql="SELECT id, name, date, days_until_expiry
FROM agencies
WHERE days_until_expiry BETWEEN -365 AND 60
ORDER BY days_until_expiry, name
LIMIT 50">Expiry support worklist</button><button type="button" class="copy-btn query-example" data-sql="SELECT id, name, date, rubric_version, scoring_profile_id, validator_version
FROM agencies
WHERE comparison_eligible = false
ORDER BY name
LIMIT 50">Rows outside the comparison cohort</button><button type="button" class="copy-btn query-example" data-sql="SELECT rubric_version, scoring_profile_id, validator_version,
count(*) AS feed_records
FROM agencies
GROUP BY rubric_version, scoring_profile_id, validator_version
ORDER BY rubric_version, scoring_profile_id, validator_version">Producer provenance</button>
</div>
</form>
<p id="query-status" role="status">The query engine (about 6 MB) downloads the
first time you press Run.</p>
<div id="query-result"></div>
<noscript><p>Running SQL in the page needs JavaScript. The downloads above carry the
same data.</p></noscript>
<p class="fineprint">Remember the sampling frame: this is the covered set of feeds,
not the universe of agencies, and absence means not covered, never failing.
Data CC BY 4.0.</p>
<script>
(function () {
var form = document.querySelector(".query-form");
var sqlEl = document.getElementById("query-sql");
var statusEl = document.getElementById("query-status");
var result = document.getElementById("query-result");
var conn = null;
document.querySelectorAll(".query-example").forEach(function (btn) {
btn.addEventListener("click", function () {
sqlEl.value = btn.getAttribute("data-sql");
sqlEl.focus();
});
});
function el(tag, text) {
var e = document.createElement(tag);
if (text !== undefined) e.textContent = text;
return e;
}
async function ensureEngine() {
if (conn) return conn;
statusEl.textContent = "Loading the query engine\u2026";
var duckdb = await import(
"https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.29.0/+esm");
var bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles());
var workerUrl = URL.createObjectURL(new Blob(
['importScripts("' + bundle.mainWorker + '");'],
{ type: "text/javascript" }));
var db = new duckdb.AsyncDuckDB(new duckdb.VoidLogger(), new Worker(workerUrl));
await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
URL.revokeObjectURL(workerUrl);
statusEl.textContent = "Loading the dataset\u2026";
var buf = new Uint8Array(
await (await fetch("/api/v1/agencies.parquet")).arrayBuffer());
await db.registerFileBuffer("agencies.parquet", buf);
conn = await db.connect();
await conn.query(
"CREATE VIEW agencies AS SELECT * FROM read_parquet('agencies.parquet')");
return conn;
}
async function run(sql) {
var c = await ensureEngine();
statusEl.textContent = "Running\u2026";
var res = await c.query(sql);
var cols = res.schema.fields.map(function (f) { return f.name; });
var rows = res.toArray();
var table = el("table");
table.className = "leaderboard";
table.appendChild(el("caption", "Query result: " + rows.length + " row" +
(rows.length === 1 ? "" : "s") + "."));
var thead = el("thead"), hr = el("tr");
cols.forEach(function (cname) {
hr.appendChild(el("th", cname)).setAttribute("scope", "col");
});
thead.appendChild(hr);
table.appendChild(thead);
var tbody = el("tbody");
rows.slice(0, 500).forEach(function (row) {
var tr = el("tr");
cols.forEach(function (cname) {
var v = row[cname];
tr.appendChild(el("td", v === null || v === undefined ? "" : String(v)));
});
tbody.appendChild(tr);
});
table.appendChild(tbody);
result.textContent = "";
result.appendChild(table);
statusEl.textContent = rows.length > 500
? rows.length + " rows; showing the first 500."
: rows.length + " row" + (rows.length === 1 ? "" : "s") + ".";
}
form.addEventListener("submit", function (ev) {
ev.preventDefault();
run(sqlEl.value).catch(function (err) {
statusEl.textContent = "That query did not run: " + err.message;
});
});
})();
</script>
</main>
<footer class="site-footer">
<div class="wrap">
<div class="footer-intro">
<a class="footer-brand" href="/">GTFS Scorecard</a>
<p>An open-source data quality tool for small and rural transit agencies.</p>
</div>
<nav class="footer-grid" aria-label="Footer">
<section aria-labelledby="footer-find-h">
<h2 id="footer-find-h">Find a scorecard</h2>
<ul>
<li><a href="/agencies/">Agency directory</a></li>
<li><a href="/app/">Interactive search</a></li>
<li><a href="/map/">Agency map</a></li>
<li><a href="/routes/">All routes</a></li>
<li><a href="/compare/">Compare two agencies</a></li>
</ul>
</section>
<section aria-labelledby="footer-improve-h">
<h2 id="footer-improve-h">Improve a feed</h2>
<ul>
<li><a href="/fix/">GTFS errors & fixes</a></li>
<li><a href="/check/">Check before publishing</a></li>
<li><a href="/try.html">Request a one-off score</a></li>
<li><a href="/subscribe.html">Feed-health alerts</a></li>
<li><a href="/submit.html">Add an agency</a></li>
<li><a href="/claim/">Correct or claim a listing</a></li>
<li><a href="/procurement/">Procurement language</a></li>
</ul>
</section>
<section aria-labelledby="footer-explore-h">
<h2 id="footer-explore-h">Explore the data</h2>
<ul>
<li><a href="/pulse/">Coverage overview</a></li>
<li><a href="/problems/">Common problems</a></li>
<li><a href="/realtime/">Realtime quality</a></li>
<li><a href="/adoption/">What feeds publish</a></li>
<li><a href="/focus/">Focus areas</a></li>
<li class="footer-subhead">United States tools</li>
<li><a href="/ntd/">U.S. NTD readiness</a></li>
<li><a href="/equity/">U.S. equity</a></li>
<li><a href="/query/">Query the dataset</a></li>
<li><a href="/data/">Open data</a></li>
</ul>
</section>
<section aria-labelledby="footer-project-h">
<h2 id="footer-project-h">Project</h2>
<ul>
<li><a href="/about/">About</a></li>
<li><a href="/status/">Status</a></li>
<li><a href="/support/">Get help or sponsor</a></li>
<li><a href="/how-to-read/">How to read a scorecard</a></li>
<li><a href="/how-to-read/#glossary">Glossary</a></li>
<li><a href="/crosswalk/">Standards crosswalk</a></li>
<li><a href="/press/">For reporters</a></li>
<li><a href="/accessibility/">Accessibility</a></li>
<li><a href="https://github.com/ChelseaKR/gtfs-scorecard/blob/main/CONTRIBUTING.md">Contribute</a></li>
<li><a href="https://github.com/ChelseaKR/gtfs-scorecard/blob/main/docs/listing-policy.md">Listing & removal policy</a></li>
</ul>
</section>
</nav>
</div>
</footer>
</body>
</html>