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
|
CREATE TABLE geo_data.movement_pattern (
start_lat DOUBLE PRECISION,
start_lon DOUBLE PRECISION,
end_lat DOUBLE PRECISION,
end_lon DOUBLE PRECISION,
distance DOUBLE PRECISION,
duration DOUBLE PRECISION,
"timestamp" TIMESTAMP
);
INSERT INTO geo_data.movement_pattern
SELECT res.*
FROM
(SELECT
t.latitude::DOUBLE PRECISION AS start_lat,
t.longitude::DOUBLE PRECISION AS start_lon,
lead(t.latitude) OVER(ORDER BY t.timestamp)::double precision AS end_lat,
lead(t.longitude) OVER(ORDER BY t.timestamp)::double precision AS end_lon,
6371 * acos(
cos(radians(t.latitude)) * cos(radians(lead(t.latitude) OVER(ORDER BY t.timestamp))) *
cos(radians(lead(t.longitude) OVER(ORDER BY t.timestamp)) - radians(t.longitude)) +
sin(radians(t.latitude)) * sin(radians(lead(t.latitude) OVER(ORDER BY t.timestamp)))
)::double precision AS distance,
extract(second from (lead(t.timestamp) OVER(ORDER BY t.timestamp) - t.timestamp))::DOUBLE PRECISION AS duration,
t.timestamp
FROM geo_data.population_tracking AS t
) AS res,
geo_data.points_of_interest AS p
WHERE p.name = 'Family Business, Pizza Place'
AND 300 >= 6371 * acos(
cos(radians(p.latitude)) * cos(radians(res.start_lat)) *
cos(radians(res.start_lon) - radians(p.longitude)) +
sin(radians(p.latitude)) * sin(radians(res.start_lat))
)
AND duration < 10
ORDER BY res.timestamp
|
