CREATE TYPE axh_linestring_to_points AS (geom GEOMETRY, when_at TIMESTAMP); | |
-- Input is a | |
-- * linestring geometry | |
-- * start timestamp | |
-- * end timestamp | |
-- * frequency integer (number of seconds between each point | |
-- Run it, SELECT (axh_linetime_to_points(the_geom, starttime, stoptime, 20)).* from andrew_20_281_29 WHERE cartodb_id = 581 | |
CREATE OR REPLACE FUNCTION axh_linetime_to_points(input GEOMETRY, start TIMESTAMP WITH TIME ZONE, endt TIMESTAMP WITH TIME ZONE, frequency NUMERIC) | |
RETURNS SETOF axh_linestring_to_points AS $$ | |
DECLARE | |
rettype axh_linestring_to_points%ROWTYPE; | |
ret GEOMETRY; | |
units INT; | |
counter INT := 1; | |
BEGIN | |
input := ST_GeometryN(input, 1); | |
units := floor(extract(EPOCH FROM (endt - start))::numeric / frequency); | |
FOR rettype IN SELECT ST_PointN(input, 1), start LOOP | |
RETURN NEXT rettype; | |
END LOOP; | |
WHILE counter <= units LOOP | |
FOR rettype IN SELECT ST_Line_Interpolate_Point(input, counter/units::numeric), start + INTERVAL '1 seconds' * frequency * counter LOOP | |
RETURN NEXT rettype; | |
END LOOP; | |
counter := counter +1; | |
END LOOP; | |
FOR rettype IN SELECT ST_PointN(ST_Reverse(input), 1), endt LOOP | |
RETURN NEXT rettype; | |
END LOOP; | |
RETURN; | |
END | |
$$ language plpgsql; | |
CREATE OR REPLACE FUNCTION axh_blend_lines(input GEOMETRY) | |
RETURNS GEOMETRY AS $$ | |
DECLARE | |
-- sql TEXT; | |
first GEOMETRY; | |
next GEOMETRY; | |
back GEOMETRY; | |
last GEOMETRY; | |
invert GEOMETRY; | |
linestring GEOMETRY; | |
backstring GEOMETRY; | |
-- mem GEOMETRY; | |
nearest GEOMETRY; | |
closest GEOMETRY; | |
-- start BOOLEAN := TRUE; | |
ret axh_miles_test_ret%rowtype; | |
ret_id INT[]; | |
ret_geom GEOMETRY[]; | |
ret_line GEOMETRY[]; | |
size INT; | |
curr INT; | |
top GEOMETRY[]; | |
bot GEOMETRY[]; | |
-- allret axh_miles_test_ret%rowtype[]; | |
--CREATE TYPE axh_miles_test_ret AS (i INT, id INT, geom GEOMETRY, line GEOMETRY); | |
BEGIN | |
first := ST_PointN(input, 1); | |
last := ST_PointN(ST_Reverse(input), 1); | |
SELECT INTO nearest line_interpolate_point(ST_GeometryN(the_geom,1), line_locate_point(ST_GeometryN(the_geom,1), first)) FROM new_york_osm_line_clean WHERE z_order > 2 AND the_geom IS NOT NULL ORDER BY the_geom <-> first LIMIT 1; | |
-- top := top || first; | |
select into top array_agg(i) from (values (first), (nearest)) n(i); | |
-- top := top || nearest; | |
-- init our linestring, we'll skip the first array point later | |
linestring := ST_MakeLine(first, nearest); | |
-- DO IT BACKWARDS | |
invert = ST_Reverse(input); | |
SELECT INTO closest line_interpolate_point(ST_GeometryN(the_geom,1), line_locate_point(ST_GeometryN(the_geom,1), last)) FROM new_york_osm_line_clean WHERE z_order > 2 AND the_geom IS NOT NULL ORDER BY the_geom <-> last LIMIT 1; | |
select into bot array_agg(i) from (values (last), (closest)) n(i); | |
backstring := ST_MakeLine(last, closest); | |
size := 200; | |
curr := 1; | |
WHILE curr <= size LOOP | |
-- Do it from the top | |
-- first := top[1:array_upper(top, 1) - 1]; | |
first := ST_Line_Interpolate_Point(ST_MakeLine(nearest, last), (curr+0.0)/(2.0*size+1.0)); | |
IF curr <(size/4) THEN | |
SELECT INTO nearest line_interpolate_point(ST_GeometryN(the_geom,1), line_locate_point(ST_GeometryN(the_geom,1), first)) FROM new_york_osm_line_clean WHERE z_order > 2 AND the_geom IS NOT NULL ORDER BY the_geom <-> first LIMIT 1; | |
ELSE | |
SELECT INTO nearest line_interpolate_point(ST_GeometryN(the_geom,1), line_locate_point(ST_GeometryN(the_geom,1), first)) FROM new_york_osm_line_clean WHERE z_order > 3 AND the_geom IS NOT NULL ORDER BY the_geom <-> first LIMIT 1; | |
END IF; | |
-- linestring = ST_AddPoint(linestring, nearest); | |
-- top := array_append(top, nearest); | |
linestring = ST_AddPoint(linestring, nearest); | |
curr := curr + 1; | |
END LOOP; | |
curr := 1; | |
WHILE curr <= size LOOP | |
-- And reverse from the bottom | |
last = ST_Line_Interpolate_Point(ST_MakeLine(closest, first), (curr+0.0)/(2.0*size+1.0)); | |
IF curr <(size/4) THEN | |
SELECT INTO closest line_interpolate_point(ST_GeometryN(the_geom,1), line_locate_point(ST_GeometryN(the_geom,1), last)) FROM new_york_osm_line_clean WHERE z_order > 2 AND the_geom IS NOT NULL ORDER BY the_geom <-> last LIMIT 1; | |
ELSE | |
SELECT INTO closest line_interpolate_point(ST_GeometryN(the_geom,1), line_locate_point(ST_GeometryN(the_geom,1), last)) FROM new_york_osm_line_clean WHERE z_order > 3 AND the_geom IS NOT NULL ORDER BY the_geom <-> last LIMIT 1; | |
END IF; | |
backstring = ST_AddPoint(backstring, closest); | |
bot := array_append(bot, closest); | |
curr := curr + 1; | |
END LOOP; | |
-- linestring = ST_LineMerge(ST_Collect(linestring, ST_Reverse(backstring))); | |
curr := array_upper(bot, 1); | |
WHILE curr > 0 LOOP | |
linestring = ST_AddPoint(linestring, bot[curr]); | |
curr := curr - 1; | |
END LOOP; | |
RETURN linestring; | |
END | |
$$ language plpgsql; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment