Bug: Some agencies have speedmaps but don't match to reports

[vevetron]

Describe the bug
Through fixing #333, we see these agencies have speedmaps but don't match to reports. The reasons could be:

• they don't have a report or a calitp id
• naming is different

university-of-california-davis
dumbarton-bridge-regional-operations-consortium
emeryville-transportation-management-agency
golden-gate-bridge-highway-and-transportation-district
mountain-view-transportation-management-association
solano-transportation-authority
university-of-california-berkeley
san-luis-obispo-regional-transit-authority
university-of-california-los-angeles
city-of-banning
san-joaquin-regional-transit-district
flagship-cruises-and-events-inc
city-of-irvine

[csuyat-dot]

Double checked the Reports Site. Was able to recover some names and calitp IDs.

Some agencies exist in the 2024 Reports page (Solano Transportation Authority, San Joaquin Regional Transit District) but have not appeared in 2025 reports.

Agencies that were recovered from the Reports Site:

speedmaps name	reports name	reports itp id
university-of-california-davis	University of California, Davis	351
golden-gate-bridge-highway-and-transportation-district	Golden Gate Bridge, Highway and Transportation District	127
solano-transportation-authority	Solano Transportation Authority	381
san-luis-obispo-regional-transit-authority	San Luis Obispo Regional Transit Authority	289
san-joaquin-regional-transit-district	San Joaquin Regional Transit District	284

Then queried the remaining agencies on dim_organizations and to also see if they have any GTFS. SQL query used:

SELECT
  DISTINCT name,
  itp_id,
  alias,
  gtfs_realtime_status,
  gtfs_static_status,
  ntd_id,
FROM
  `cal-itp-data-infra.mart_transit_database.dim_organizations`
WHERE
  REGEXP_CONTAINS(name, r'City of Irvine|Dumbarton Bridge|Emeryville Transportation|Mountain View Transportation|University of California, Berkeley|University of California, Los Angeles|City of Banning|Flagship Cruises')
  AND _is_current is True
LIMIT
  1000

Results. Some agencies did not return a Cal-ITP ID but have GTFS RT/static and are currently operating.

speedmaps name	name	itp_id	gtfs_realtime_status	gtfs_static_status	organization_type	public_currently_operating	public_currently_operating_fixed_route
emeryville-transportation-management-agency	Emeryville Transportation Management Agency	106	RT OK	Static OK	Non-Profit Organization	FALSE	FALSE
flagship-cruises-and-events-inc	Flagship Cruises and Events Inc.	32	RT OK	Static OK	Company	FALSE	FALSE
mountain-view-transportation-management-association	Mountain View Transportation Management Association	217	RT OK	Static OK	Non-Profit Organization	FALSE	FALSE
university-of-california-berkeley	University of California, Berkeley	33	RT OK	Static OK	University - Public	FALSE	FALSE
university-of-california-los-angeles	University of California, Los Angeles	486	RT OK	Static OK	University - Public	FALSE	FALSE
city-of-banning	City of Banning	null	RT OK	Static OK	City/Town	TRUE	TRUE
city-of-irvine	City of Irvine	null	RT OK	Static OK	City/Town	TRUE	TRUE
dumbarton-bridge-regional-operations-consortium	Dumbarton Bridge Regional Operations Consortium	null	RT OK	Static OK	Joint Powers Agency	TRUE	TRUE

will try to assign Cal-ITP IDs in Airtable to the following agencies:

dim_org names	possible new calitp id
Dumbarton Bridge Regional Operations Consortium	491
City of Banning	492
City of Irvine	493

[csuyat-dot]

in Airtable, California Transit. Copied the All Organizations view. Filtered Name for Dumbatron Bridge Regional, City of Banning and City of Irvine. Clicked into each org and added the Cal-ITP Id as stated in last comment.

[vevetron]

ITP ids are showing up in 4-03 data.

select * from cal-itp-data-infra.external_airtable.california_transit__organizations
-- where itp_id = 492
where website='http://www.banning.ca.us/'
-- where itp_id is not null
order by dt DESC
limit 5

It doesn't show up in the map yet

select *  FROM cal-itp-data-infra.staging.int_gtfs_quality__organization_dataset_map
where date = "2025-04-02"
AND organization_name = 'City of Banning'
limit 10
(4-03 data doesn't exist yet, only 4-02)

According to
https://dbt-docs.calitp.org/#!/model/model.calitp_warehouse.int_gtfs_quality__daily_assessment_candidate_entities

public_customer_facing_or_regional_subfeed_fixed_route

For entities associated with MTC 511, use the regional subfeed instead of the combined regional feed; i.e., for organization/services where the MTC combined regional feed is the customer-facing feed and "assessed" for guideline purposes, this feed will mark the associated regional subfeed (rather than the combined feed) as assessed for reports site (reports.calitp.org) purposes.
Entity must include an organization with an ITP ID assigned
Entity must include at least one assessed schedule feed

I wonder, what if we remove the requirement that an ITP ID is assigned. What would break.

[vevetron]

IN 4-03 in cal-itp-data-infra.staging.int_gtfs_quality__organization_dataset_map there is now data for City of Banning.

But there isn't data for Banning in the reports site for March-April

SELECT idx.`organization_itp_id`,
  REPLACE(LOWER(idx.`organization_name`), ' ', '-') AS transformed_string,
  idx.`organization_name`, 
FROM `mart_gtfs_quality.idx_monthly_reports_site` AS idx
where publish_date in (select idx.`publish_date` FROM `mart_gtfs_quality.idx_monthly_reports_site` AS idx order by publish_date DESC limit 1)
and organization_itp_id = 492
order by organization_name