from psycopg2 import connect
from psycopg2.extras import RealDictCursor
from collections import defaultdict

connection = connect(database='nskroads', cursor_factory=RealDictCursor)
cursor = connection.cursor()

connection2 = connect(database='nskroads')
write_cursor = connection2.cursor()

cursor.execute("""
    select a.osm_id, b.nodes
    from rd_line a, rd_ways b
    where
        a.osm_id = b.id and
        a.highway in ('trunk', 'trunk_link', 'primary', 'primary_link', 'secondary',
            'secondary_link', 'tertiary', 'tertiary_link', 'unclassified')""")


nodes = defaultdict(list)
ways = {}
result = {}

for row in cursor:
    for n in row['nodes']:
        nodes[n].append(row['osm_id'])
        ways[row['osm_id']] = row['nodes']

split_nodes = {k: v for k, v in nodes.items() if len(v) > 1}

for osm_id, nodes in ways.items():
    start = 0
    result[osm_id] = [0]
    for i, node in enumerate(nodes):
        if node in split_nodes and start < i < len(nodes) - 1:
            result[osm_id].append(i)
            start = i
    result[osm_id].append(len(nodes))


# write_cursor.execute('drop table if exists slice_order')
# write_cursor.execute('create table slice_order (osm_id bigint, slices int[])')
write_cursor.execute('truncate slice_order')

args_str = ','.join(write_cursor.mogrify("(%s,%s::int[])", (osm_id, '{' + ','.join(map(str, slices))+ '}'))
    for osm_id, slices in result.items() if True or len(slices) > 2)
print args_str
write_cursor.execute("""
    insert into slice_order values %s
    """ % args_str)

connection2.commit()