Something went wrong. Try again.
Live STIB-MIVB departures on the Pebble Time 2. It covers the bus, tram, and metro network of Brussels
Something went wrong. Try again.
8.7 kB · 273 lines
SQL
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274PRAGMA auto_vacuum = INCREMENTAL;SELECT InitSpatialMetaData(1);DROP TABLE IF EXISTS knn2;CREATE VIRTUAL TABLE knn2 USING VirtualKNN2();
CREATE TABLE schema_meta ( version INTEGER PRIMARY KEY, applied_at INTEGER NOT NULL);
CREATE TABLE provider_batch ( id INTEGER PRIMARY KEY, provider TEXT NOT NULL, captured_at INTEGER NOT NULL);
CREATE TABLE provider_feed ( id INTEGER PRIMARY KEY, provider TEXT NOT NULL, feed TEXT NOT NULL, required INTEGER NOT NULL CHECK (required IN (0, 1)), current_snapshot_id INTEGER REFERENCES provider_snapshot(id), UNIQUE (provider, feed));
CREATE TABLE provider_snapshot ( id INTEGER PRIMARY KEY, batch_id INTEGER NOT NULL REFERENCES provider_batch(id), feed_id INTEGER NOT NULL REFERENCES provider_feed(id), scope TEXT NOT NULL DEFAULT '', captured_at INTEGER NOT NULL, source_at INTEGER, content_type TEXT NOT NULL, payload_sha256 BLOB NOT NULL CHECK (length(payload_sha256) = 32), payload_zstd BLOB NOT NULL);
CREATE INDEX provider_snapshot_latestON provider_snapshot(feed_id, scope, captured_at DESC, id DESC);
CREATE TABLE feed_failure ( feed_id INTEGER PRIMARY KEY REFERENCES provider_feed(id), stage TEXT NOT NULL, message TEXT NOT NULL, failed_at INTEGER NOT NULL);
CREATE TABLE place ( id INTEGER PRIMARY KEY, provider TEXT NOT NULL, kind TEXT NOT NULL, external_id TEXT NOT NULL, name_fr TEXT NOT NULL, name_nl TEXT NOT NULL, folded_name TEXT NOT NULL, collapsed_name TEXT NOT NULL, normalized_id TEXT NOT NULL, source_order INTEGER NOT NULL, active INTEGER NOT NULL CHECK (active IN (0, 1)), UNIQUE (provider, kind, external_id));CREATE INDEX place_normalized_lookupON place(provider, kind, normalized_id, active, source_order);SELECT AddGeometryColumn('place', 'geom', 4326, 'POINT', 'XY', 1);SELECT CreateSpatialIndex('place', 'geom');
CREATE TABLE place_alias ( provider TEXT NOT NULL, external_id TEXT NOT NULL, place_id INTEGER NOT NULL REFERENCES place(id), priority INTEGER NOT NULL, PRIMARY KEY (provider, external_id, priority, place_id));
CREATE TABLE line ( id INTEGER PRIMARY KEY, provider TEXT NOT NULL, external_id TEXT NOT NULL, display_name TEXT NOT NULL, kind TEXT NOT NULL, color TEXT NOT NULL, text_color TEXT NOT NULL, active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1)), UNIQUE (provider, external_id));
CREATE TABLE route_variant ( id INTEGER PRIMARY KEY, line_id INTEGER NOT NULL REFERENCES line(id), destination_fr TEXT NOT NULL, destination_nl TEXT NOT NULL, source_order INTEGER NOT NULL);
CREATE TABLE route_variant_stop ( variant_id INTEGER NOT NULL REFERENCES route_variant(id), ordinal INTEGER NOT NULL, external_id TEXT NOT NULL, place_id INTEGER REFERENCES place(id), PRIMARY KEY (variant_id, ordinal));CREATE INDEX route_variant_by_placeON route_variant_stop(place_id, variant_id);
CREATE TABLE route_shape ( id INTEGER PRIMARY KEY, line_id INTEGER NOT NULL REFERENCES line(id), destination_key TEXT NOT NULL, UNIQUE (line_id, destination_key));SELECT AddGeometryColumn('route_shape', 'geom', 4326, 'LINESTRING', 'XY', 1);SELECT CreateSpatialIndex('route_shape', 'geom');
CREATE TABLE current_passage ( snapshot_id INTEGER NOT NULL REFERENCES provider_snapshot(id), place_id INTEGER NOT NULL REFERENCES place(id), line_id INTEGER NOT NULL REFERENCES line(id), destination_fr TEXT NOT NULL, destination_nl TEXT NOT NULL, expected_at INTEGER NOT NULL);CREATE INDEX current_passage_lookupON current_passage(place_id, line_id, expected_at);
CREATE TABLE current_end_notice ( snapshot_id INTEGER NOT NULL REFERENCES provider_snapshot(id), place_id INTEGER NOT NULL REFERENCES place(id), line_id INTEGER NOT NULL REFERENCES line(id), message_fr TEXT, message_nl TEXT, PRIMARY KEY (place_id, line_id));
CREATE TABLE current_vehicle_position ( snapshot_id INTEGER NOT NULL REFERENCES provider_snapshot(id), line_id INTEGER NOT NULL REFERENCES line(id), terminus_external_id TEXT NOT NULL, point_external_id TEXT NOT NULL, terminus_place_id INTEGER REFERENCES place(id), point_place_id INTEGER REFERENCES place(id), distance_m INTEGER NOT NULL);CREATE INDEX current_vehicle_by_lineON current_vehicle_position(line_id, terminus_place_id, terminus_external_id);
CREATE TABLE current_disruption ( id INTEGER PRIMARY KEY, snapshot_id INTEGER NOT NULL REFERENCES provider_snapshot(id), priority INTEGER NOT NULL, message_en TEXT, message_fr TEXT, message_nl TEXT);CREATE TABLE current_disruption_line ( disruption_id INTEGER NOT NULL REFERENCES current_disruption(id) ON DELETE CASCADE, line_id INTEGER NOT NULL REFERENCES line(id), PRIMARY KEY (disruption_id, line_id));CREATE TABLE current_disruption_place ( disruption_id INTEGER NOT NULL REFERENCES current_disruption(id) ON DELETE CASCADE, place_id INTEGER NOT NULL REFERENCES place(id), PRIMARY KEY (disruption_id, place_id));
CREATE TABLE current_train_departure ( snapshot_id INTEGER NOT NULL REFERENCES provider_snapshot(id), station_id INTEGER NOT NULL REFERENCES place(id), label TEXT NOT NULL, destination TEXT NOT NULL, expected_at INTEGER NOT NULL, platform TEXT, delay_seconds INTEGER, PRIMARY KEY (station_id, label, destination, expected_at));
CREATE TABLE current_villo_status ( snapshot_id INTEGER NOT NULL REFERENCES provider_snapshot(id), stand_id INTEGER PRIMARY KEY REFERENCES place(id), bikes INTEGER NOT NULL, docks INTEGER NOT NULL, mechanical INTEGER, electrical INTEGER);
CREATE TABLE transit_with_passages ( place_id INTEGER PRIMARY KEY REFERENCES place(id), source_order INTEGER NOT NULL);SELECT AddGeometryColumn('transit_with_passages', 'geom', 4326, 'POINT', 'XY', 1);SELECT CreateSpatialIndex('transit_with_passages', 'geom');
CREATE TABLE transit_with_notices ( place_id INTEGER PRIMARY KEY REFERENCES place(id), source_order INTEGER NOT NULL);SELECT AddGeometryColumn('transit_with_notices', 'geom', 4326, 'POINT', 'XY', 1);SELECT CreateSpatialIndex('transit_with_notices', 'geom');
CREATE TABLE transit_silent ( place_id INTEGER PRIMARY KEY REFERENCES place(id), source_order INTEGER NOT NULL);SELECT AddGeometryColumn('transit_silent', 'geom', 4326, 'POINT', 'XY', 1);SELECT CreateSpatialIndex('transit_silent', 'geom');
CREATE VIRTUAL TABLE place_search USING fts5( place_id UNINDEXED, folded_name, collapsed_name, normalized_id, tokenize = 'trigram');
CREATE TABLE arrival_fact ( place_id INTEGER NOT NULL REFERENCES place(id), line_id INTEGER NOT NULL REFERENCES line(id), destination_fr TEXT NOT NULL, expected_at INTEGER NOT NULL, first_seen_at INTEGER NOT NULL, last_seen_at INTEGER NOT NULL, PRIMARY KEY (place_id, line_id, destination_fr, expected_at));
CREATE TABLE train_departure_fact ( station_id INTEGER NOT NULL REFERENCES place(id), label TEXT NOT NULL, destination TEXT NOT NULL, expected_at INTEGER NOT NULL, first_seen_at INTEGER NOT NULL, last_seen_at INTEGER NOT NULL, PRIMARY KEY (station_id, label, destination, expected_at));
CREATE TABLE disruption_fact ( payload_sha256 BLOB PRIMARY KEY CHECK (length(payload_sha256) = 32), first_seen_at INTEGER NOT NULL, last_seen_at INTEGER NOT NULL, payload_zstd BLOB NOT NULL);
CREATE TABLE vehicle_position_fact ( line_id INTEGER NOT NULL REFERENCES line(id), terminus_place_id INTEGER NOT NULL REFERENCES place(id), point_place_id INTEGER NOT NULL REFERENCES place(id), distance_m INTEGER NOT NULL, bucket_at INTEGER NOT NULL, observed_at INTEGER NOT NULL, PRIMARY KEY (line_id, terminus_place_id, point_place_id, distance_m, bucket_at));
CREATE TABLE villo_status_fact ( stand_id INTEGER NOT NULL REFERENCES place(id), bucket_at INTEGER NOT NULL, bikes INTEGER NOT NULL, docks INTEGER NOT NULL, mechanical INTEGER, electrical INTEGER, observed_at INTEGER NOT NULL, PRIMARY KEY (stand_id, bucket_at));
INSERT INTO provider_feed(provider, feed, required) VALUES ('bmc', 'static', 1), ('bmc', 'waiting', 0), ('bmc', 'positions', 0), ('bmc', 'disruptions', 0), ('irail', 'stations', 0), ('irail', 'liveboard', 0), ('villo', 'discovery', 0), ('villo', 'status', 0);
INSERT INTO schema_meta(version, applied_at)VALUES (1, unixepoch('subsec') * 1000);