id  |         name          | source | target | one_way | cost_s | rev_cost_s
------+-----------------------+--------+--------+---------+--------+------------
  433 | Western Avenue        |      1 |    303 |       0 |   1.30 |       1.30
 4725 | Glenoaks Boulevard    |      1 |   4061 |       1 |   2.58 |      -2.58
  299 | Geneva Street         |      2 |    216 |       0 |  26.01 |      26.01
 1735 | Glenoaks Boulevard    |      2 |   1267 |       0 |   8.40 |       8.40

sources(source,count) as (
   select source,count(*) as count
   from glendale_ways group by source
   ),
targets(target,count) as (
   select target,count(*) as count
   from glendale_ways group by target
   )

possible_interiors as (
   select w.*,s.count as scount, t.count as tcount
   from glendale_ways w
   join targets t on (w.target=t.target)
   join sources s on (w.source=s.source)
   where t.count=1 and s.count=1
   ),

 source | target |         name         | scount | tcount
--------+--------+----------------------+--------+--------
      3 |   1964 | Geneva Street        |      1 |      1
     11 |   5607 | East Colorado Street |      1 |      1
     15 |   3918 | South Central Avenue |      1 |      1
     20 |   4529 | Harvey Drive         |      1 |      1
     31 |   2068 | East Mountain Street |      1 |      1

interiors as (
   select pi.*
   from possible_interiors pi
   join sources s on (pi.target=s.source)
   join targets t on (pi.source=t.target)
   where s.count=1 and t.count=1
   )

  id  | source | target |         name          | scount | tcount
------+--------+--------+-----------------------+--------+--------
 2509 |     37 |   1986 | North Jackson Street  |      1 |      1
 2503 |     57 |   1977 | South Pacific Avenue  |      1 |      1
  398 |    118 |    277 | East Mountain Street  |      1 |      1
 2424 |    127 |   1891 | Harvey Drive          |      1 |      1
 5282 |    148 |   4621 | Flintridge Drive      |      1 |      1

possible_starts as (
   select w.*, s.count as scount, t.count as tcount
   from glendale_ways w
   join targets t on (w.target=t.target)
   join sources s on (w.source=s.source)
   where t.count = 1 -- link is only one touching target
   )

starts as (
   select ps.*
   from possible_starts ps
   where ps.scount > 1
   )

   union
   select ps.*
   from possible_starts ps
   join targets t on (ps.source=t.target)
   where ps.scount=1 and  t.count>1
   -- more than one link target == ps.source

   union
   select ps.*
   from possible_starts ps
   join possible_interiors pi on (ps.source=pi.target)
   where ps.name != pi.name and ps.scount=1 and pi.tcount=1
   -- name change of road

   union
   select ps.*
   from possible_starts ps
   join possible_starts psi on (ps.source = psi.target
                                and ps.name != psi.name)
   -- singletons

starts as (
   select ps.*
   from possible_starts ps
   where ps.scount >1
  union   -- more than one link target == ps.source
   select ps.*
   from possible_starts ps
   join targets t on (ps.source=t.target)
   where ps.scount =1 and  t.count>1
  union   -- name change of road
   select ps.*
   from possible_starts ps
   join possible_interiors pi on (ps.source=pi.target)
   where ps.name != pi.name and ps.scount=1 and pi.tcount=1
  union   -- singletons
   select ps.*
   from possible_starts ps
   join possible_starts psi on (ps.source=psi.target and ps.name != psi.name)
   )

search_graph(gid, length_m, name, source, target, depth,
             path, segments, cycle, ...) as (

   select g.gid, g.length_m, g.name, g.source, g.target,
   1 as depth,
   array[g.gid] as path,
   st_asewkt(g.the_geom) as segments,
   false as cycle,
   ... -- other stuff
   from ends g
   -- initialize with ends, connect interiors to source

st_asewkt(g.the_geom) as segments

  union all
   select g.gid, g.length_m + sg.length_m,
   sg.name, g.source, sg.target,
   sg.depth+1 as depth,
   g.gid || sg.path as path,
   st_asewkt( st_makeline( g.the_geom, sg.segments )),
   g.gid = ANY(sg.path) as cycle,
   ... -- other stuff
   from interiors g -- recurse on interiors
   join search_graph sg on
     (g.target=sg.source -- interior target -> chain source
      and g.name=sg.name)-- but same street name too please
   where sg.depth < 100 and not sg.cycle -- stop guards

st_asewkt( st_makeline( g.the_geom, sg.segments ))

  ARRAY[g.the_geom] as segments
  …
  array_prepend(g.the_geom, sg.segments)
              ::geometry(LineString,4326)[],

WITH RECURSIVE …
select gid,name,source,target,depth
from search_graph order by depth desc,name;
 gid  |         name          | source | target | depth
------+-----------------------+--------+--------+-------
 6344 | North Louise Street   |   5686 |    234 |    19
 6179 | North Louise Street   |   5685 |    234 |    18
 5311 | Emerald Isle Drive    |   4635 |    149 |    17
 6326 | North Louise Street   |   5520 |    234 |    17
 5309 | Emerald Isle Drive    |   4650 |    149 |    16
 5280 | Flintridge Drive      |   4620 |    147 |    16
 6327 | North Louise Street   |   5667 |    234 |    16
 5310 | Emerald Isle Drive    |   4648 |    149 |    15

gid_paths as (select unnest(sg.path) as node,depth
   from search_graph sg ),
gid_max_depth as (
   select node,max(depth) as depth
   from gid_paths group by node ),

distinct_paths as (
  select distinct path
  from search_graph sg
  join gid_max_depth gm
       on (gm.depth=sg.depth and
           gm.node in (select unnest(sg.path)))
  )

segments as (
   select c.name, g.source, c.target, c.depth+1 as depth,
   g.gid || c.path as path,
   ST_SimplifyPreserveTopology(
    ST_GeomFromEWKT(st_asewkt(st_makeline(g.the_geom,
                                          c.segments))),
    0.0000001) as the_geom,  … other_columns …
   from search_graph c
   join distinct_paths dp on (c.path=dp.path)
   join starts g -- add start nodes to chain
        on (g.target=c.source  --start.target == source
            and g.name=c.name) -- same name please
   )

drop sequence if exists curbgraph_v2_serial;
create sequence curbgraph_v2_serial;

drop table if exists curbs_v2_graph cascade;

with
tform as (
    select id, st_transform(the_geom,32611) as geom,reverse_cost
    from new_glendale_ways
    ),
rhs as (
    select ST_Reverse(ST_Transform (
             ST_OffsetCurve(
                geom,
                -2) -- 2 meters offset, 6 feet-ish, reverse of orig direction
                ,4326)) as geom, --  back to source geometry, (need reverse)
           id
    from tform),
lhs_twoway as (
    select ST_Reverse(ST_Transform (
             ST_OffsetCurve(
                geom,
                2) -- 2 meters offset, 6 feet-ish, same sense
                ,4326)) as geom, --  back to source geometry, (need reverse because lhs)
           id
    from tform
    where reverse_cost > 0),
lhs_oneway as (
    select ST_Transform (
             ST_OffsetCurve(
                geom,
                2) -- 2 meters offset, 6 feet-ish, same sense
                ,4326) as geom, --  back to source geometry, no reverse for one-way streets
           id
    from tform
    where reverse_cost < 0),
lhs as (
    select * from lhs_twoway
    union
    select * from lhs_oneway
    ),
lhscurbs as (select
 nextval('curbgraph_v2_serial') as curbid,
 'lhs' as curbside,
 a.id,
 a.osm_ids,
 a.tag_ids,
 a.length,
 a.length_m,
 a.name,
 case when a.one_way=1 then  a.source
      else a.target
 end  as source,
 case when a.one_way=1 then  a.target
      else a.source
 end  as target,
 a.source_osm,
 a.target_osm,
 case when a.one_way=1 then  a.cost
      else a.reverse_cost
 end  as cost,
 -1 as reverse_cost,
 case when a.one_way=1 then  a.cost_s
      else a.reverse_cost_s
 end  as cost_s,
 -1 as reverse_cost_s,
 a.rule,
 a.one_way,
 a.oneway,
 a.maxspeed_forward,
 a.maxspeed_backward,
 a.priority,
 a.the_geom,
 cg.geom as curb_geom
 from new_glendale_ways a
 left outer join lhs cg on (a.id=cg.id)
 where one_way=1 or reverse_cost>0
),
rhscurbs as (select
 nextval('curbgraph_v2_serial') as curbid,
 'rhs' as curbside,
 a.id,
 a.osm_ids,
 a.tag_ids,
 a.length,
 a.length_m,
 a.name,
 a.source,
 a.target,
 a.source_osm,
 a.target_osm,
 a.cost,
 -1 as reverse_cost,
 a.cost_s,
 -1 as reverse_cost_s,
 a.rule,
 a.one_way,
 a.oneway,
 a.maxspeed_forward,
 a.maxspeed_backward,
 a.priority,
 a.the_geom,
 cg.geom as curb_geom
 from new_glendale_ways a
 left outer join rhs cg on (a.id=cg.id)),
curbs as (
 select * from rhscurbs
 union
 select * from lhscurbs)
select * into curbs_v2_graph from curbs;

drop table if exists curbs_v2_linegraph;
SELECT * into curbs_v2_linegraph FROM pgr_lineGraph(
    'SELECT curbid as id, source, target, cost_s as cost, reverse_cost_s as reverse_cost FROM curbs_v2_graph'
);

truncate new_curbs_linegraph_matrix;
with onesteps as (
  select source as start_vid,
      target as end_vid,
      target_length_m as agg_cost
  from new_curbs_v2_linegraph a
  )
insert into new_curbs_linegraph_matrix
   select * from onesteps
   on conflict do nothing;
-- INSERT 0 28067

create or replace function fleshout_2000_curb_linegraph_matrix(starting int)
returns integer as
$BODY$
DECLARE
   i text;
   subsel text := 'SELECT id, source, target, target_length_m as cost, reverse_cost FROM new_curbs_v2_linegraph';
   insert_sql text := '';
   check_sql text := '';
   get_one_sql text := '';
   test_sql text := '';
   startid int := 0;
BEGIN
   insert_sql := '
   with
   id_block (sid) as (
      select distinct source
      from new_curbs_v2_linegraph nl
      where source > $1
      order by source
      limit 3000
      ),
    nextbatch as (
     select * FROM pgr_dijkstraCostMatrix('
   || quote_literal(subsel)
   ||',
      (select array_agg(sid) from id_block)))
   insert into new_curbs_linegraph_matrix
   select * from nextbatch
   on conflict do nothing';
    -- RAISE NOTICE '%', insert_sql;
    RAISE NOTICE 'calling with %', insert_sql;
    FOR startid IN starting..7000 by 1000 LOOP
        RAISE NOTICE 'populate db starting with %', startid;
        EXECUTE  insert_sql using startid;
    END LOOP;
    return  count(*) from new_curbs_linegraph_matrix;
END;
$BODY$
  LANGUAGE plpgsql;

FOR startid IN starting..7000 by 1000 LOOP
    RAISE NOTICE 'populate db starting with %', startid;
    EXECUTE  insert_sql using startid;
END LOOP;

select distinct source
from new_curbs_v2_linegraph nl
where source > $1
order by source
limit 3000

with
 low_block (sid) as (
    select source
    from new_curbs_v2_linegraph nl
    where source <3300
    order by random()
    limit 1000
    ),
 mid_block (sid) as (
    select source
    from new_curbs_v2_linegraph nl
    where source >= 3300 and source <= 6600
    order by random()
    limit 1000
    ),
 hi_block (sid) as (
    select source
    from new_curbs_v2_linegraph nl
    where source > 6600
    order by random()
    limit 1000
    ),
 id_block (sid) as (
    select sid from low_block
    union
    select sid from mid_block
    union
    select sid from hi_block
    ),

with
 sid_count (sid,cnt) as (
   select start_vid, count(*)
   from new_curbs_linegraph_matrix
   group by start_vid
   order by count
   ),
 lo_block (sid) as (
   select sid from sid_count
   limit 500
   ),
 hi_block (sid) as (
   select sid
   from sid_count
   where cnt > 9000
   order by random()
   limit 2500
   ),
 id_block (sid) as (
   select sid from lo_block
   union
   select sid from hi_block
   ),

with
 sid_count (sid,cnt) as (
   select start_vid, count(*)
   from new_curbs_linegraph_matrix
   group by start_vid
   order by count
   ),
 pctl (hicount) as (
   SELECT percentile_cont(0.07) WITHIN GROUP (ORDER BY cnt) FROM sid_count
 ),
 lo_block (sid) as (
   select sid from sid_count
   limit 500
   ),
 hi_block (sid) as (
   select sid
   from sid_count
   cross join pctl
   where cnt > hicount
   order by random()
   limit 2500
   ),
 id_block (sid) as (
   select sid from lo_block
   union
   select sid from hi_block
   ),

with counts as (
   select start_vid,count(*) as cnt
   from new_curbs_linegraph_matrix group by start_vid)
select count(*),floor(cnt) from counts group by floor(cnt);
 count | floor
-------+-------
   201 |  9190
  2374 |  9191
  6607 |  9192
    11 |  9193
(4 rows)

def sequence_to_table(self,vsequence,table_name):
    sequence = 0
    insert_query_string = """insert into {} (veh,sweep,linkid,geom)
    select %s,%s,%s,c.curb_geom as the_geom
    from curbs_v2_graph c
    where c.curbid =%s"""
    insert_query = sql.SQL(insert_query_string).format(sql.Identifier(table_name))
    with self.conn.cursor() as cur:
        cur.execute(
            sql.SQL("drop table if exists {}").format(sql.Identifier(table_name)))

        cur.execute(
            sql.SQL("CREATE TABLE {} (id serial, linkid integer, veh integer, sweep boolean default TRUE, geom geometry(LINESTRING,4326) )").format(sql.Identifier(table_name)))

        for (veh,sequence) in vsequence.items():
            #print(sequence)
            for row in sequence:
                # basic idea.  Save a table.  Each row is
                # shape,sequence number
                sweep = row[1]
                linkid = row[0]
                cur.execute(insert_query,(veh,sweep,linkid,linkid))
        self.conn.commit()

sql.SQL("drop table {}").format(sql.Identifier(table_name)))
...
cur.execute(insert_query,(veh,sweep,linkid,linkid))

SELECT field1, field2,
       ST_LineSubstring(the_geom, 100.00*n/len,
  CASE
    WHEN 100.00*(n+1) < len THEN 100.00*(n+1)/len
    ELSE 1
  END) AS the_geom
FROM
  (SELECT sometable.field1, sometable.field2,
  ST_LineMerge(sometable.the_geom) AS the_geom,
  ST_Length(sometable.the_geom) As len
  FROM sometable ) AS t
CROSS JOIN generate_series(0,10000) AS n
WHERE n*100.00/len < 1;

st_length(st_transform(geom,32611))

select srid,proj4text
 from spatial_ref_sys where srid between 32600 and 32661
 order by srid;
 srid  |    proj4text
-------+---------------------------------------------------
 32601 | +proj=utm +zone=1 +datum=WGS84 +units=m +no_defs
 32602 | +proj=utm +zone=2 +datum=WGS84 +units=m +no_defs
 32603 | +proj=utm +zone=3 +datum=WGS84 +units=m +no_defs
 32604 | +proj=utm +zone=4 +datum=WGS84 +units=m +no_defs
 32605 | +proj=utm +zone=5 +datum=WGS84 +units=m +no_defs
 …

with
 lengthshare as (
   select id,linkid,veh,sweep,geom,
          st_length(st_transform(geom,32611)) as len
   from solver_output
   order by id
   ),
 maxlen as (
   select max(len) as len  from lengthshare
   ),

maxiter as (
   select (ceil(len/25.00)+1)::int as maxiter
   from maxlen
   )

series as (
   select maxiter, generate_series(1,maxiter) - 1 as n
   from maxiter
   )

snipped as (
   select id, id+(n/maxiter::numeric) as frame,
     linkid,veh,sweep,
     st_linesubstring(geom,
          25.00*n/len,
          case
            when 25.00*(n+1) < len then 25.00*(n+1)/len
            else 1
          end) as geom
   from lengthshare l
   cross join series s
   where s.n*25.00/len < 1
   order by frame )

insert into
  solver_output_snipped (id,frame,linkid,veh,sweep,geom)
  select id,frame,linkid,veh,sweep,geom from snipped;

st_linesubstring(geom,25.00*n/len,
                 case when 25.00*(n+1) < len then 25.00*(n+1)/len
                 else 1 end) as geom

with
  idgeom (uid,geom) as (
    select uid,
      st_collect(geom)
      over (order by uid
            rows between 2 preceding
                    and 10 following)
    from curbtable_globalspan_snipped  ),
  centroids as (
    select uid,st_centroid(geom) as geom
    from idgeom   )
insert into pov (uid,geom)
select uid as uid,geom from centroids;

select uid,
  st_collect(geom)
  over (order by uid
        rows between 2 preceding and 10 following)
from curbtable_globalspan_snipped