-
Notifications
You must be signed in to change notification settings - Fork 1
/
postgis-queries2.txt
98 lines (92 loc) · 2.24 KB
/
postgis-queries2.txt
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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
ALTER TABLE stop_times ADD COLUMN geom geometry(POINT,4326);
UPDATE stop_times
SET geom = subquery.geom
FROM (
SELECT ST_SetSRID(ST_MakePoint(stop_lon, stop_lat), 4326) AS geom, stop_id
FROM stops
) AS subquery
WHERE stop_times.trip_id = 90653027
AND stop_times.stop_id = subquery.stop_id
SELECT *
FROM stop_times
WHERE trip_id = 90653027
SELECT *,
ST_Distance_Sphere('SRID=4326;POINT(4.9364 52.32373)', ST_LocateAlong(geom, 1562702340)) AS distance,
(
SELECT MIN(ST_Distance_Sphere('SRID=4326;POINT(4.9364 52.32373)', geom))
FROM stop_times
WHERE stop_times.trip_id = trajectories.trip_id
) AS nearest_stop
FROM trajectories
WHERE start_planned <= 1562674680
AND end_planned >= 1562674680
WHERE ST_DWithin(ST_LocateAlong(geom, 1562702340), 'SRID=4326;POINT(4.9364 52.32373)', 0.002690)
ORDER BY distance ASC
SELECT ST_AsText(geom), trip_id
FROM trajectories
WHERE trip_id = 90653027
LIMIT 10
-- DROP TABLE tmp_stop_times;
--
-- CREATE TABLE tmp_stop_times
-- (
-- trip_id int8,
-- stop_sequence int4,
-- stop_id varchar(255),
-- stop_headsign varchar(255),
-- arrival_time varchar(255),
-- departure_time varchar(255),
-- pickup_type int4,
-- drop_off_type int4,
-- timepoint int8,
-- shape_dist_traveled int4,
-- fare_units_traveled int8,
-- geom geometry(POINT,4326)
-- );
--
-- -- Drop indexes
-- INSERT INTO tmp_stop_times(
-- trip_id,
-- stop_sequence,
-- stop_id,
-- stop_headsign,
-- arrival_time,
-- departure_time,
-- pickup_type,
-- drop_off_type,
-- timepoint,
-- shape_dist_traveled,
-- fare_units_traveled,
-- geom
-- )
-- (
-- SELECT
-- trip_id,
-- stop_sequence,
-- ST.stop_id,
-- stop_headsign,
-- arrival_time,
-- departure_time,
-- pickup_type,
-- drop_off_type,
-- timepoint,
-- shape_dist_traveled,
-- fare_units_traveled,
-- ST_SetSRID(ST_MakePoint(S.stop_lon, S.stop_lat), 4326)
-- FROM stop_times ST
-- JOIN stops S
-- ON ST.stop_id = S.stop_id
-- );
--
-- DROP stop_times;
--
-- DROP INDEX idx_stoptimes_stop_id;
-- DROP INDEX idx_stoptimes_trip_id;
--
-- ALTER TABLE tmp_stop_times RENAME TO stop_times;
--
-- CREATE INDEX idx_stoptimes_stop_id ON stop_times(stop_id);
-- CREATE INDEX idx_stoptimes_trip_id ON stop_times(trip_id);
--
-- UPDATE stop_times
-- SET