FieldTrialStats

DROP VIEW IF EXISTS ft_trips;
DROP VIEW IF EXISTS ft_stats;

-- Create view with basic gps statistics:
-- a) trip_id
-- b) Sample Count
-- c) Maximal Distance to Helsinki Train Station 
CREATE VIEW ft_stats(trip_id, count, dist) AS
  SELECT trip_id, count(lonlat), Max(st_distance(lonlat, St_geometryfromtext('POINT(24.6006695 60.1893798)',4326)))
  FROM sensor_gps
  GROUP BY trip_id;


-- Creeate view with trips from Helsinki with  more than 10 samples
CREATE VIEW ft_trips(trip_id, user_id) AS
  SELECT trip.trip_id, trip.user_id
  FROM ft_stats, trip
  WHERE trip.trip_id = ft_stats.trip_id
    AND count > 10
    AND dist < 1000000;

-- Export basic stats
COPY (
SELECT user_id, trip_id, tag, count(har_annotation.tag)
FROM ft_trips, har_annotation
WHERE ft_trips.trip_id = har_annotation.trip_id
GROUP BY user_id, ft_trips.trip_id, har_annotation.tag
ORDER BY user_id, trip_id
) TO STDOUT WITH HEADER CSV;

DROP VIEW ft_trips;
DROP VIEW ft_stats;