Skip to content

Instantly share code, notes, and snippets.

@minus34

minus34/sample_trajectory_code.sql

Last active December 11, 2018 09:07
Show Gist options
  • Save minus34/888140bcba6d3f9504fcd0f336f07257 to your computer and use it in GitHub Desktop.
Save minus34/888140bcba6d3f9504fcd0f336f07257 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
sample_trajectory_code.sql
WITH points_m AS (
SELECT user_id,
trip_id,
time_utc,
ST_MakepointM(longitude, latitude, legal_speed_difference) AS pointm_geom
FROM raw_points
), traj AS (
SELECT user_id,
trip_id,
min(time_utc) AS departure_time_utc,
max(time_utc) AS arrival_time_utc,
ST_Makeline(pointm_geom ORDER BY time_utc) AS geom
FROM points_m
GROUP BY user_id,
trip_id
)
SELECT user_id,
trip_id,
departure_time_utc,
arrival_time_utc,
EXTRACT(epoch FROM arrival_time_utc - departure_time_utc) AS duration,
ST_Length(geom::geography, true)::integer AS distance,
geom
FROM traj;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment