~ihabunek/triglav

ref: 1a8ba58407fc58b933347c9bec5bcb4358191cbb triglav/priv/gtfs/transform.sql -rw-r--r-- 770 bytes
1a8ba584Ivan Habunek Use derived data for route detail view 4 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
ALTER TABLE zet.stops ADD COLUMN geom geometry(Point, 4326);

UPDATE zet.stops SET geom = ST_SetSRID(ST_Point(stop_lon, stop_lat), 4326);

CREATE INDEX ON zet.stops USING GIST (geom);

CREATE MATERIALIZED VIEW zet.v_distinct_trips AS
WITH trips AS (
  SELECT t.route_id,
         t.trip_id,
         t.direction_id,
         array_agg(s.stop_id ORDER BY st.stop_sequence) AS stop_ids
  FROM zet.stop_times st
  JOIN zet.trips t ON st.trip_id = t.trip_id
  JOIN zet.stops s ON s.stop_id = st.stop_id
  GROUP BY 1, 2, 3
)
  SELECT route_id,
         direction_id,
         stop_ids,
         count(*) AS trip_count,
         min(trip_id) AS sample_trip_id
    FROM trips
GROUP BY route_id, direction_id, stop_ids
ORDER BY route_id::integer, direction_id, trip_count DESC;