Missing SRID 4326 for shapes_aggregated.shape

[hbruch]

I suggest to explicitly set SRID 4326 for shape geometries in shapes_aggregated, e.g.

ST_SetSRID(4326, ST_MakeLine(array_agg(shape_pt_loc))) AS shape

That way, subsequent (materialized) views coud create a spatial index like e.g.

CREATE INDEX my_shapes_aggregated_view_shape_idx
ON my_shapes_aggregated_view_shape
USING GIST (shape);

[derhuerst]

I suggest to explicitly set SRID 4326 for shape geometries in shapes_aggregated. That way, subsequent (materialized) views coud create a spatial index […].

I'm not sure what you're hinting at, as AFAICT this is possible even today:

CREATE MATERIALIZED VIEW shapes_aggregated_mat AS
SELECT shape_id, shape
FROM api.shapes_aggregated;

CREATE INDEX ON shapes_aggregated_mat
USING GIST (shape);

-- Using the 2023-09-16 bwgesamt (beta) GTFS feed, I have verified that it uses the spatial index.
SELECT shape_id, shape
FROM shapes_aggregated_mat
WHERE shape && ST_MakeBox2D(ST_Point(9.3, 48.49, 4326), ST_Point(9.29, 48.5, 4326))

Nevertheless, given that GTFS Latitudes & Longitudes are defined as WGS 84, it shouldn't hurt – but instead provide additional context – to specify shapes.shape_pt_loc as SRID 4326; This way, the shapes_aggregated.shape should be in SRID 4326 too, right?