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
