1 files changed, 36 insertions, 0 deletions
diff --git a/rushs/data-clash/step-2/geo_pattern.sql b/rushs/data-clash/step-2/geo_pattern.sql
new file mode 100644
index 0000000..b4a298f
--- /dev/null
+++ b/rushs/data-clash/step-2/geo_pattern.sql
@@ -0,0 +1,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