Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment