Preliminary Diff Between CAOD and MobilityDatabase

[ianktc]

Perform preliminary diff to identify the feeds that are present and absent in the MobilityDatabase.

Similar to the comparison done for TDG, we want to identify:

• which existing MDB feeds should be deprecated+ redirected to a new CAOD feed
• which CAOD feed will be newly introduced to the MobilityDatabse

Similar methodology to https://github.com/MobilityData/product-tasks/issues/114

Final Output: Similar excel sheet to the one linked

[ianktc]

First pull of CAOD Data:
https://docs.google.com/spreadsheets/d/1ezYLz9hdleL8kZEoZbOJPlsA5m4xnckGkxUo3H6Oes8/edit?gid=0#gid=0

[ianktc]

I will be creating a formal document to present this in more detail. For now, this comment can provide some important information on the import source.

CAOD Data Model Description

The CAOD Data Model consists of many datasets, of which 4 are relevant for the import:

1. organizations_latest_with_caltrans_district
2. services
3. provider_gtfs_data
4. gtfs_datasets

Extraction Flow

The recommended approach for extracting feeds is:

services --> provider_gtfs_data --> gtfs_datasets.

services.source_record_id (PK) is a FK to provider_gtfs_data.service_source_record_id. Within provider_gtfs_data we have the nullable:

• schedule_source_record_id
• service_alerts_source_record_id
• trip_updates__source_record_id
• vehicle_positions_source_record_id

These four source_record_ids are FK corresponding to source_record_id (PK) in gtfs_datasets. This gtfs_datasets finally then contains the url which is the direct_download_url.

March 31 Extraction

Using the CKAN datastore_search_sql we can get all operational services (which includes all corresponding schedule and realtime feeds).

SELECT
    provider_gtfs_data.service_source_record_id,
    provider_gtfs_data.service_name,
    provider_gtfs_data.organization_name,
    provider_gtfs_data.schedule_source_record_id,
    schedule_dataset.name AS schedule_dataset_name,
    schedule_dataset.url AS schedule_dataset_url,
    provider_gtfs_data.service_alerts_source_record_id,
    service_alerts_dataset.name AS service_alerts_dataset_name,
    service_alerts_dataset.url AS service_alerts_dataset_url,
    provider_gtfs_data.trip_updates_source_record_id,
    trip_updates_dataset.name AS trip_updates_dataset_name,
    trip_updates_dataset.url AS trip_updates_dataset_url,
    provider_gtfs_data.vehicle_positions_source_record_id,
    vehicle_positions_dataset.name AS vehicle_positions_dataset_name,
    vehicle_positions_dataset.url AS vehicle_positions_dataset_url
FROM "dbacfa9f-2148-454c-a08f-a77233f2b8c0" services
INNER JOIN "ebe116fb-b9da-4fee-a0c5-497c9d6d61d7" provider_gtfs_data
    ON services.source_record_id = provider_gtfs_data.service_source_record_id
LEFT JOIN "e4ca5bd4-e9ce-40aa-a58a-3a6d78b042bd" schedule_dataset
    ON provider_gtfs_data.schedule_source_record_id = schedule_dataset.source_record_id
LEFT JOIN "e4ca5bd4-e9ce-40aa-a58a-3a6d78b042bd" service_alerts_dataset
    ON provider_gtfs_data.service_alerts_source_record_id = service_alerts_dataset.source_record_id
LEFT JOIN "e4ca5bd4-e9ce-40aa-a58a-3a6d78b042bd" trip_updates_dataset
    ON provider_gtfs_data.trip_updates_source_record_id = trip_updates_dataset.source_record_id
LEFT JOIN "e4ca5bd4-e9ce-40aa-a58a-3a6d78b042bd" vehicle_positions_dataset
    ON provider_gtfs_data.vehicle_positions_source_record_id = vehicle_positions_dataset.source_record_id
WHERE services.is_public= 'Yes'
AND (
    provider_gtfs_data.schedule_source_record_id IS NOT NULL OR
    provider_gtfs_data.service_alerts_source_record_id IS NOT NULL OR
    provider_gtfs_data.trip_updates_source_record_id IS NOT NULL OR
    provider_gtfs_data.vehicle_positions_source_record_id IS NOT NULL
)

Used in:

https://data.ca.gov/api/3/action/datastore_search_sql?sql={sql}

[davidgamez]

The CKAN query looks great. As a side note, I would use deterministic pagination while processing the feeds(out of scope of this PR)

[ianktc]

If there is no match for a Cal-ITP realtime feed do the additional following steps (retrace):

1. Find the corresponding Cal-ITP schedule feed, and retrieve its matching MDB schedule feed (if it exists)
2. Use feedreference table to find the corresponding MDB realtime feed(s) (if they exist)
3. Consider this MDB realtime feed to match the Cal-ITP realtime feed

WITH realtime_feed_ids AS (
    SELECT id, stable_id, gtfs_rt_feed_id, gtfs_feed_id
    FROM feed JOIN feedreference ON feed.id = feedreference.gtfs_feed_id
    WHERE stable_id = :stable_id
)
SELECT f.stable_id, f.operational_status, f.status 
FROM realtime_feed_ids rfid JOIN feed f ON rfid.gtfs_rt_feed_id = f.id

Found an additional 28 matches for Cal-ITP realtime feeds that previously did not have a match with MobilityDatabase.