~ihabunek/triglav

ref: d1b5915f3e023d04850eabf702bda7207a337302 triglav/priv/gtfs/transform.sql -rw-r--r-- 687 bytes
d1b5915fIvan Habunek Validate platform distance from route 5 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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 stops
  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, stops, count(*) AS trip_count
FROM trips
GROUP BY route_id, direction_id, stops
ORDER BY route_id::integer, direction_id, trip_count DESC;