citi_bike_functions.sql

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;