Add agency to materialized view shapes_with_routes

[richardforsterNVBW]

It makes sense to add the agency_id, agency_name and agency_url to the materialized view shapes_with_routes.

As far as I understand, the file 32-geoserver-shapes.sql in the etc/gtfs/postprocessing.d folder must be edited to do so.

In our test system I tried to implement an augmented materialized view. For this purpose, I created the following branch.
Nonetheless, the gtfs import which I started on Dagster failed on creating the adapted materialized view. Most likely, there might be some mistakes in the sql file. My attempt was as follows:

CREATE MATERIALIZED VIEW geoserver.shapes_with_routes AS
	SELECT
		shape_id,
		st_setsrid(min(shape), 4326) AS shape,
		route_type,
		array_to_string(array_agg(DISTINCT route_id), ', ') AS route_ids,
		array_to_string(array_agg(DISTINCT route_name), ', ') AS route_names,
		agency_id,
		agency_name,
		agency_url
	FROM (
		SELECT DISTINCT ON (shape_id, route_id)
			shapes.shape_id,
			shape,
			routes.route_id,
			route_type,
			coalesce(route_short_name, route_long_name) AS route_name,
			agency.agency_id,
			agency.agency_name,
			agency.agency_url
		FROM api.shapes_aggregated shapes
		JOIN api.trips ON shapes.shape_id = trips.shape_id
		JOIN api.routes ON trips.route_id = routes.route_id
		JOIN api.agency ON routes.agency_id = agency.agency_id
		WHERE route_short_name NOT LIKE '%SEV%'
	)	
	GROUP BY shape_id, route_type, agency_id, agency_name, agency_url;

-- allow filtering via Geoserver's CQL
DROP CAST IF EXISTS (api.route_type_val AS text);
CREATE CAST (api.route_type_val AS text) WITH INOUT AS ASSIGNMENT;

-- todo: primary/unique key?
CREATE INDEX shapes_with_routes_shape_idx
  ON geoserver.shapes_with_routes
  USING GIST (shape);

@derhuerst: Can you please check if there are any mistakes and, if necessary, tell us which other files must be edited to achieve the described objective?

[derhuerst]

Nonetheless, the gtfs import which I started on Dagster failed on creating the adapted materialized view.

Can you provide the link to the Dagster run that failed?

[richardforsterNVBW]

Nonetheless, the gtfs import which I started on Dagster failed on creating the adapted materialized view.

Can you provide the link to the Dagster run that failed?

Unfortunately, this is not possible since test was fully replaced and updated this morning.
I just saw the mail from Thorsten to you from yesterday in which he says that the gtfs import problem is nothing new. Against this background, it is likely that the gtfs import didn't fail as a consequence of my sql changes.
Anyway, can you please validate my changes?

[richardforsterNVBW]

@derhuerst: I startet the gtfs import again. It failed: http://10.70.172.21:3000/locations/mobidata_bw/asset-groups/gtfs/lineage/gtfs?logFileKey=axsmncze

[hbruch]

The script fails, as the sub select has no alias (see the dagster jobs stdout for details):

'+ psql -b -1 -v ON_ERROR_STOP=1 --set=SHELL=/bin/bash -f /etc/gtfs/postprocessing.d/32-geoserver-shapes.sql\n'
'psql:/etc/gtfs/postprocessing.d/32-geoserver-shapes.sql:27: ERROR:  subquery in FROM must have an alias\n'
'LINE 11:  FROM (\n'
'               ^\n'
'HINT:  For example, FROM (SELECT ...) [AS] foo.\n'
'psql:/etc/gtfs/postprocessing.d/32-geoserver-shapes.sql:27: STATEMENT:  CREATE MATERIALIZED VIEW geoserver.shapes_with_routes AS\n'

[richardforsterNVBW]

Update

Unlike test, the addition of the the three attributes did not work. The materialized view geoserver.shapes_with_routes in the latest database gtfs_1734656453_0444e7 does not contain these extra columns as well. I restarted the materialization via Dagster several times, but it did not help. I cannot find any hint within the Dagster logs why prod differs from test.
My guess is that the incremental prod updates cause this strange behavior.