Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
color lines by gradient
create table flight_segments as
SELECT hexid,start_time,end_time,callsign,point,
-- take a substring if the length reamining in the segment is greater than 5280 feet (1609.34 m)
-- otherwise take the remainder
ST_LineSubstring(geom, 1609.34*n/length,
CASE
WHEN 1609.34*(n+1) < length THEN 1609.34*(n+1)/length
ELSE 1
END) as geom
FROM
(SELECT hexid,start_time,end_time,callsign,point,
-- reverse the vertex order so that the upstream end of each
-- linestring will be the remainder
ST_LineMerge(ST_Reverse(geom)) AS geom,
ST_Length(geom::geography) As length
FROM flights ts
WHERE ST_Length(geom) > 0
) t
CROSS JOIN generate_series(0,1000) n -- 10000 is arbitrary, but must be greater than the number of segments you're getting
WHERE n*1609.34/length < 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.