class AgencyLoader(TableLoader):
    fname = 'agency.txt'
    table = 'agencies'
    tabledef = ('(agency_I INTEGER PRIMARY KEY, agency_id TEXT UNIQUE NOT NULL, '
                'name TEXT, url TEXT, timezone TEXT, lang TEXT, phone TEXT)')

    # shape_id,shape_pt_lat,shape_pt_lon,shape_pt_sequence
    # 1001_20140811_1,60.167430,24.951684,1
    def gen_rows(self, readers, prefixes):

        for reader, prefix in zip(readers, prefixes):
            for row in reader:
                yield dict(
                    agency_id     =prefix + decode_six(row.get('agency_id', '1')),
                    name          = decode_six(row['agency_name']),
                    timezone      = decode_six(row['agency_timezone']),
                    url           = decode_six(row['agency_url']),
                    lang          = decode_six(row['agency_lang']) if 'agency_lang' in row else None,
                    phone         = decode_six(row['agency_phone']) if 'agency_phone' in row else None,
                )

    def post_import(self, cur):
        TZs = cur.execute('SELECT DISTINCT timezone FROM agencies').fetchall()
        if len(TZs) == 0:
            raise ValueError("Error: no timezones defined in sources: %s" % self.gtfs_sources)
        elif len(TZs) > 1:
            first_tz = TZs[0][0]
            import pytz
            for tz in TZs[1:]:
                generic_date = datetime(2009, 9, 1)
                ftz = pytz.timezone(first_tz).utcoffset(generic_date, is_dst=True)
                ctz = pytz.timezone(tz[0]).utcoffset(generic_date, is_dst=True)
                if not str(ftz) == str(ctz):
                    raise ValueError("Error: multiple timezones defined in sources:: %s" % self.gtfs_sources)
        TZ = TZs[0][0]
        set_process_timezone(TZ)

    def index(self, cur):
        pass class CalendarDatesLoader(TableLoader):
    fname = 'calendar_dates.txt'
    table = 'calendar_dates'
    tabledef = '(service_I INTEGER NOT NULL, date TEXT, exception_type INT)'
    copy_where = ("WHERE  date({start_ut}, 'unixepoch', 'localtime') <= date "
                  "AND  date < date({end_ut}, 'unixepoch', 'localtime')")

    def gen_rows(self, readers, prefixes):
        conn = self._conn
        cur = conn.cursor()
        for reader, prefix in zip(readers, prefixes):
            for row in reader:
                date = row['date']
                date_str = '%s-%s-%s' % (date[:4], date[4:6], date[6:8])
                service_id = prefix+row['service_id']
                # We need to find the service_I of this.  To do this we
                # need to check the calendar table, since that (and only
                # that) is the absolute list of service_ids.
                service_I = cur.execute(
                    'SELECT service_I FROM calendar WHERE service_id=?',
                    (decode_six(service_id),)).fetchone()
                if service_I is None:
                    # We have to add a new fake row in order to get a
                    # service_I.  calendar is *the* authoritative source
                    # for service_I:s.
                    cur.execute('INSERT INTO calendar '
                                '(service_id, m,t,w,th,f,s,su, start_date,end_date)'
                                'VALUES (?, 0,0,0,0,0,0,0, ?,?)',
                                (decode_six(service_id), date_str, date_str)
                                )
                    service_I = cur.execute(
                        'SELECT service_I FROM calendar WHERE service_id=?',
                        (decode_six(service_id),)).fetchone()
                service_I = service_I[0]  # row tuple -> int

                yield dict(
                    service_I     = int(service_I),
                    date          = date_str,
                    exception_type= int(row['exception_type']),
                ) class CalendarLoader(TableLoader):
    fname = 'calendar.txt'
    table = 'calendar'
    tabledef = '(service_I INTEGER PRIMARY KEY, service_id TEXT UNIQUE NOT NULL, m INT, t INT, w INT, th INT, f INT, s INT, su INT, start_date TEXT, end_date TEXT)'
    copy_where = ("WHERE  date({start_ut}, 'unixepoch', 'localtime') < end_date "
                  "AND  start_date < date({end_ut}, 'unixepoch', 'localtime')")

    # service_id,monday,tuesday,wednesday,thursday,friday,saturday,sunday,start_date,end_date
    # 1001_20150810_20151014_Ke,0,0,1,0,0,0,0,20150810,20151014
    def gen_rows(self, readers, prefixes):
        for reader, prefix in zip(readers, prefixes):
            for row in reader:
                # print row
                start = row['start_date']
                end = row['end_date']
                yield dict(
                    service_id    = prefix + decode_six(row['service_id']),
                    m             = int(row['monday']),
                    t             = int(row['tuesday']),
                    w             = int(row['wednesday']),
                    th            = int(row['thursday']),
                    f             = int(row['friday']),
                    s             = int(row['saturday']),
                    su            = int(row['sunday']),
                    start_date    = '%s-%s-%s' % (start[:4], start[4:6], start[6:8]),
                    end_date      = '%s-%s-%s' % (end[:4], end[4:6], end[6:8]),
                )

    @classmethod
    def index(cls, cur):
        # cur.execute('CREATE INDEX IF NOT EXISTS idx_calendar_svid ON calendar (service_id)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_calendar_s_e ON calendar (start_date, end_date)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_calendar_m  ON calendar (m)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_calendar_t  ON calendar (t)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_calendar_w  ON calendar (w)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_calendar_th ON calendar (th)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_calendar_f  ON calendar (f)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_calendar_s  ON calendar (s)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_calendar_su ON calendar (su)') class DayLoader(TableLoader):
    # Note: calendar and calendar_dates should have been imported before
    # importing with DayLoader
    fname = None
    table = 'days'
    tabledef = '(date TEXT, day_start_ut INT, trip_I INT)'
    copy_where = "WHERE  {start_ut} <= day_start_ut  AND  day_start_ut < {end_ut}"

    def post_import(self, cur):
        insert_data_to_days(cur, self._conn)

    def index(self, cur):
        create_day_table_indices(cur) class DayTripsMaterializer(TableLoader):
    """Make the table day_trips with (date, trip_I, start, end, day_start_ut).

    This replaces the old day_trips view.  This allows direct querying
    on the start_time_ut and end_time_ut, at the cost that this table is
    now O(days * trips).  This makes the following things:

    day_trips2: The actual table
    day_trips: Replacement for the old day_trips view.  day_trips2+trips
    day_stop_times: day_trips2+trips+stop_times
    """
    fname = None
    table = 'day_trips2'
    tabledef = ('(date TEXT, '
                'trip_I INT, '
                'start_time_ut INT, '
                'end_time_ut INT, '
                'day_start_ut INT)')
    copy_where = 'WHERE  {start_ut} < end_time_ut  AND  start_time_ut < {end_ut}'

    @classmethod
    def post_import_round2(cls, conn):
        insert_data_to_day_trips2(conn)

    def index(cls, cur):
        create_day_trips_indices(cur)


    @classmethod
    def make_views(cls, conn):
        """Create day_trips and day_stop_times views.

        day_trips:  day_trips2 x trips  = days x trips
        day_stop_times: day_trips2 x trips x stop_times = days x trips x stop_times
        """
        conn.execute('DROP VIEW IF EXISTS main.day_trips')
        conn.execute('CREATE VIEW day_trips AS   '
                     'SELECT day_trips2.*, trips.* '
                     #'days.day_start_ut+trips.start_time_ds AS start_time_ut, '
                     #'days.day_start_ut+trips.end_time_ds AS end_time_ut   '
                     'FROM day_trips2 JOIN trips USING (trip_I);')
        conn.commit()

        conn.execute('DROP VIEW IF EXISTS main.day_stop_times')
        conn.execute('CREATE VIEW day_stop_times AS   '
                     'SELECT day_trips2.*, trips.*, stop_times.*, '
                     #'days.day_start_ut+trips.start_time_ds AS start_time_ut, '
                     #'days.day_start_ut+trips.end_time_ds AS end_time_ut, '
                     'day_trips2.day_start_ut+stop_times.arr_time_ds AS arr_time_ut, '
                     'day_trips2.day_start_ut+stop_times.dep_time_ds AS dep_time_ut   '
                     'FROM day_trips2 '
                     'JOIN trips USING (trip_I) '
                     'JOIN stop_times USING (trip_I)')
        conn.commit() class FeedInfoLoader(TableLoader):

    """feed_info.txt: various feed metadata"""
    fname = 'feed_info.txt'
    table = 'feed_info'
    tabledef = ('(feed_publisher_name TEXT, '
                'feed_publisher_url TEXT, '
                'feed_lang TEXT, '
                'feed_start_date TEXT, '
                'feed_end_date TEXT, '
                'feed_version TEXT, '
                'feed_id TEXT) ')

    def gen_rows(self, readers, prefixes):
        for reader, prefix in zip(readers, prefixes):
            for row in reader:
                #print row
                start = row['feed_start_date'] if 'feed_start_date' in row else None
                end   = row['feed_end_date']   if 'feed_end_date'   in row else None
                yield dict(
                    feed_publisher_name = decode_six(row['feed_publisher_name']) if 'feed_publisher_name' in row else None,
                    feed_publisher_url  = decode_six(row['feed_publisher_url'])  if 'feed_publisher_url'  in row else None,
                    feed_lang           = decode_six(row['feed_lang'])           if 'feed_lang'           in row else None,
                    feed_start_date     = '%s-%s-%s'%(start[:4], start[4:6], start[6:8])  if start else None,
                    feed_end_date       = '%s-%s-%s'%(end[:4], end[4:6], end[6:8])        if end   else None,
                    feed_version        = decode_six(row['feed_version'])        if 'feed_version'        in row else None,
                    feed_id             = prefix[:-1] if len(prefix) > 0 else prefix
                )

    def post_import2(self, conn):
        # TODO! Something whould be done with this! Multiple feeds are possible, currently only selects one row for all feeds
        G = GTFS(conn)
        for name in ['feed_publisher_name',
                     'feed_publisher_url',
                     'feed_lang',
                     'feed_start_date',
                     'feed_end_date',
                     'feed_version']:
                value = conn.execute('SELECT %s FROM feed_info' % name).fetchone()[0]
                if value:
                    G.meta['feed_info_' + name] = value class FrequenciesLoader(TableLoader):
    """Load the general frequency table."""
    fname = 'frequencies.txt'
    table = 'frequencies'

    tabledef = (u'(trip_I INT, '
                u'start_time TEXT, '
                u'end_time TEXT, '
                u'headway_secs INT,'
                u'exact_times INT, '
                u'start_time_ds INT, '
                u'end_time_ds INT'
                u')')
    extra_keys = [u'trip_I',
                  u'start_time_ds',
                  u'end_time_ds',
                  ]
    extra_values = [u'(SELECT trip_I FROM trips WHERE trip_id=:_trip_id )',
                    '(substr(:start_time,-8,2)*3600 + substr(:start_time,-5,2)*60 + substr(:start_time,-2))',
                    '(substr(:end_time,-8,2)*3600 + substr(:end_time,-5,2)*60 + substr(:end_time,-2))',
                    ]

    def gen_rows(self, readers, prefixes):
        for reader, prefix in zip(readers, prefixes):
            for row in reader:
                yield dict(
                    _trip_id=prefix + decode_six(row['trip_id']),
                    start_time=row['start_time'],
                    end_time=row['end_time'],
                    headway_secs=int(row['headway_secs']),
                    exact_times=int(row['exact_times']) if 'exact_times' in row and row['exact_times'].isdigit() else 0
                )

    def post_import(self, cur):
        # For each (start_time_dependent) trip_I in frequencies.txt
        conn = self._conn
        frequencies_df = pandas.read_sql("SELECT * FROM " + self.table, conn)

        for freq_tuple in frequencies_df.itertuples():
            trip_data = pandas.read_sql_query("SELECT * FROM trips WHERE trip_I= " + str(int(freq_tuple.trip_I)), conn)
            assert len(trip_data) == 1
            trip_data = list(trip_data.itertuples())[0]
            freq_start_time_ds = freq_tuple.start_time_ds
            freq_end_time_ds = freq_tuple.end_time_ds
            trip_duration = cur.execute("SELECT max(arr_time_ds) - min(dep_time_ds) "
                                        "FROM stop_times "
                                        "WHERE trip_I={trip_I}".format(trip_I=str(int(freq_tuple.trip_I)))
                                        ).fetchone()[0]
            if trip_duration is None:
                raise ValueError("Stop times for frequency trip " + trip_data.trip_id + " are not properly defined")
            headway = freq_tuple.headway_secs

            sql = "SELECT * FROM stop_times WHERE trip_I=" + str(trip_data.trip_I) + " ORDER BY seq"
            stop_time_data = pandas.read_sql_query(sql, conn)

            start_times_ds = range(freq_start_time_ds, freq_end_time_ds, headway)
            for i, start_time in enumerate(start_times_ds):
                trip_id = trip_data.trip_id + u"_freq_" + str(start_time)
                route_I = trip_data.route_I
                service_I = trip_data.service_I

                shape_id = trip_data.shape_id
                direction_id = trip_data.direction_id
                headsign = trip_data.headsign
                end_time_ds = start_time + trip_duration

                # insert these into trips
                query = "INSERT INTO trips (trip_id, route_I, service_I, shape_id, direction_id, " \
                            "headsign, start_time_ds, end_time_ds)" \
                        " VALUES (?, ?, ?, ?, ?, ?, ?, ?)"

                params = [trip_id, int(route_I), int(service_I), shape_id, direction_id, headsign, int(start_time), int(end_time_ds)]
                cur.execute(query, params)

                query = "SELECT trip_I FROM trips WHERE trip_id='{trip_id}'".format(trip_id=trip_id)
                trip_I = cur.execute(query).fetchone()[0]

                # insert into stop_times
                # TODO! get the original data
                dep_times_ds = stop_time_data['dep_time_ds']
                dep_times_ds = dep_times_ds - min(dep_times_ds) + start_time
                arr_times_ds = stop_time_data['arr_time_ds']
                arr_times_ds = arr_times_ds - min(arr_times_ds) + start_time
                shape_breaks_series = stop_time_data['shape_break']
                stop_Is = stop_time_data['stop_I']

                shape_breaks = []
                for shape_break in shape_breaks_series:
                    value = None
                    try:
                        value = int(shape_break)
                    except Exception:
                        pass
                    shape_breaks.append(value)

                for seq, (dep_time_ds, arr_time_ds, shape_break, stop_I) in enumerate(zip(dep_times_ds,
                                                                                          arr_times_ds,
                                                                                          shape_breaks,
                                                                                          stop_Is)):
                    arr_time_hour = int(arr_time_ds // 3600)
                    query = "INSERT INTO stop_times (trip_I, stop_I, arr_time, " \
                            "dep_time, seq, arr_time_hour, shape_break, arr_time_ds, dep_time_ds) " \
                            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
                    arr_time = util.day_seconds_to_str_time(arr_time_ds)
                    dep_time = util.day_seconds_to_str_time(dep_time_ds)

                    cur.execute(query, (int(trip_I), int(stop_I), arr_time, dep_time, int(seq + 1),
                                        int(arr_time_hour), shape_break, int(arr_time_ds), int(dep_time_ds)))

        trip_Is = frequencies_df['trip_I'].unique()
        for trip_I in trip_Is:
            for table in ["trips", "stop_times"]:
                cur.execute("DELETE FROM {table} WHERE trip_I={trip_I}".format(table=table, trip_I=trip_I))
        self._conn.commit() class TripLoader(TableLoader):
    fname = 'trips.txt'
    table = 'trips'
    # service_I INT NOT NULL
    tabledef = ('(trip_I INTEGER PRIMARY KEY, trip_id TEXT UNIQUE NOT NULL, '
                'route_I INT, service_I INT, direction_id TEXT, shape_id TEXT, '
                'headsign TEXT, '
                'start_time_ds INT, end_time_ds INT)')
    extra_keys = ['route_I', 'service_I' ] #'shape_I']
    extra_values = ['(SELECT route_I FROM routes WHERE route_id=:_route_id )',
                    '(SELECT service_I FROM calendar WHERE service_id=:_service_id )',
                    #'(SELECT shape_I FROM shapes WHERE shape_id=:_shape_id )'
                    ]

    # route_id,service_id,trip_id,trip_headsign,direction_id,shape_id,wheelchair_accessible,bikes_allowed
    # 1001,1001_20150424_20150426_Ke,1001_20150424_Ke_1_0953,"Kapyla",0,1001_20140811_1,1,2
    def gen_rows(self, readers, prefixes):
        #try:
        for reader, prefix in zip(readers, prefixes):
            for row in reader:
                #print row
                    yield dict(
                        _route_id     = prefix + decode_six(row['route_id']),
                        _service_id   = prefix + decode_six(row['service_id']),
                        trip_id       = prefix + decode_six(row['trip_id']),
                        direction_id  = decode_six(row['direction_id']) if row.get('direction_id','') else None,
                        shape_id      = prefix + decode_six(row['shape_id']) if row.get('shape_id','') else None,
                        headsign      = decode_six(row['trip_headsign']) if 'trip_headsign' in row else None,
                        )
        #except:
            #print(row)

    @classmethod
    def index(cls, cur):
        # cur.execute('CREATE INDEX IF NOT EXISTS idx_trips_tid ON trips (trip_id)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_trips_svid ON trips (service_I)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_trips_rid ON trips (route_I)')

    def post_import_round2(self, conn):
        update_trip_travel_times_ds(conn)

    # This has now been moved to DayTripsMaterializer, but is left
    # here in case we someday want to make DayTripsMaterializer
    # optional.
    #@classmethod
    #def make_views(cls, conn):
    #    conn.execute('DROP VIEW IF EXISTS main.day_trips')
    #    conn.execute('CREATE VIEW day_trips AS   '
    #                 'SELECT trips.*, days.*, '
    #                 'days.day_start_ut+trips.start_time_ds AS start_time_ut, '
    #                 'days.day_start_ut+trips.end_time_ds AS end_time_ut   '
    #                 'FROM days JOIN trips USING (trip_I);')
    #    conn.commit() class MetadataLoader(TableLoader):
    """Table to be used for any type of metadata"""
    fname = None
    table = 'metadata'
    tabledef = '(key TEXT UNIQUE NOT NULL, value BLOB, value2 BLOB)'

    @classmethod
    def index(cls, cur):
        cur.execute('CREATE INDEX IF NOT EXISTS idx_metadata_name '
                    'ON metadata (key)')

    @classmethod
    def copy(cls, conn, **where):
        pass class RouteLoader(TableLoader):
    fname = 'routes.txt'
    table = 'routes'
    tabledef = '(route_I INTEGER PRIMARY KEY, ' \
               'route_id TEXT UNIQUE NOT NULL, ' \
               'agency_I INT, ' \
               'name TEXT, ' \
               'long_name TEXT, ' \
               'desc TEXT, ' \
               'type INT, ' \
               'url TEXT, ' \
               'color TEXT, ' \
               'text_color TEXT' \
               ')'
    extra_keys = ['agency_I', ]
    extra_values = ['(SELECT agency_I FROM agencies WHERE agency_id=:_agency_id )',
                    ]

    # route_id,agency_id,route_short_name,route_long_name,route_desc,route_type,route_url
    # 1001,HSL,1,Kauppatori - Kapyla,0,http://aikataulut.hsl.fi/linjat/fi/h1_1a.html
    def gen_rows(self, readers, prefixes):
        from gtfspy import extended_route_types
        for reader, prefix in zip(readers, prefixes):
            for row in reader:
                #print (row)
                yield dict(
                    route_id      = prefix + decode_six(row['route_id']),
                    _agency_id    = prefix + decode_six(row['agency_id']) if 'agency_id' in row else None,
                    name          = decode_six(row['route_short_name']),
                    long_name     = decode_six(row['route_long_name']),
                    desc          = decode_six(row['route_desc']) if 'route_desc' in row else None,
                    type          = extended_route_types.ROUTE_TYPE_CONVERSION[int(row['route_type'])],
                    url           = decode_six(row['route_url']) if 'route_url' in row else None,
                    color         = decode_six(row['route_color']) if 'route_color' in row else None,
                    text_color    = decode_six(row['route_text_color']) if 'route_text_color' in row else None,
                )

    @classmethod
    def index(cls, cur):
        # cur.execute('CREATE INDEX IF NOT EXISTS idx_rid ON route (route_id)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_route_name ON routes (name)') class ShapeLoader(TableLoader):
    fname = 'shapes.txt'
    table = 'shapes'
    tabledef = '(shape_id TEXT, lat REAL, lon REAL, seq INT, d INT)'

    # shape_id,shape_pt_lat,shape_pt_lon,shape_pt_sequence
    # 1001_20140811_1,60.167430,24.951684,1
    def gen_rows(self, readers, prefixes):
        for reader, prefix in zip(readers, prefixes):
            for row in reader:
                #print row
                yield dict(
                    shape_id      = prefix + decode_six(row['shape_id']),
                    lat           = float(row['shape_pt_lat']),
                    lon           = float(row['shape_pt_lon']),
                    seq           = int(row['shape_pt_sequence'])
                )

    @classmethod
    def index(cls, cur):
        # cur.execute('CREATE INDEX IF NOT EXISTS idx_shapes_shid ON shapes (shape_id)')
        # cur.execute('CREATE INDEX IF NOT EXISTS idx_shapes_id_seq ON shapes (shape_I, seq)')
        cur.execute('CREATE INDEX IF NOT EXISTS idx_shapes_id_seq ON shapes (shape_id, seq)')

    @classmethod
    def post_import(cls, cur):
        from gtfspy import shapes
        cur.execute('SELECT DISTINCT shape_id FROM shapes')
        shape_ids = tuple(x[0] for x in cur)

        # print "Renumbering sequences to start from 0 and Calculating shape cumulative distances"
        for shape_id in shape_ids:
            rows = cur.execute("SELECT shape_id, seq "
                               "FROM shapes "
                               "WHERE shape_id=? "
                               "ORDER BY seq", (shape_id,)
            ).fetchall()
            cur.executemany("UPDATE shapes SET seq=? "
                            "WHERE shape_id=? AND seq=?",
                            ( (i, shape_id, seq)
                             for i, (shape_id, seq) in enumerate(rows))
                            )

        for shape_id in shape_ids:
            shape_points = shapes.get_shape_points(cur, shape_id)
            shapes.gen_cumulative_distances(shape_points)

            cur.executemany('UPDATE shapes SET d=? '
                            'WHERE shape_id=? AND seq=? ',
                            ((pt['d'], shape_id, pt['seq'])
                             for pt in shape_points)) class StopDistancesLoader(TableLoader):
    """Loader to calculate transfer distances.

    The stop_distances table has the following columns:
    from_stop_I:        reference stop_I
    to_stop_I:          reference stop_I
    d:                  straight-line distance (M)
    d_walk:             distance, routed somehow
    min_transfer_time:  from transfers.txt, in seconds.
    """
    # This loader is special.  calc_transfers creates the table there,
    # too.  We put a tabledef here so that copy() will work.
    fname = None
    table = 'stop_distances'
    tabledef = calc_transfers.create_stmt
    threshold = 1000

    def post_import(self, cur):
        # why is cur not used?
        conn = self._conn
        cur = conn.cursor()
        cur2 = conn.cursor()
        if self.print_progress:
            print("Calculating straight-line transfer distances")
        calc_transfers.calc_transfers(conn, threshold_meters=self.threshold)

        # Copy data from transfers table.  Several steps below.
        if self.print_progress:
            print("Copying information from transfers to stop_distances.")
        calc_transfers.bind_functions(conn)

        # Add min transfer times (transfer_type=2).  This just copies
        # min_transfer_time from `transfers` to `stop_distances`.
        stmt = ('SELECT min_transfer_time, from_stop_I, to_stop_I '
                'FROM transfers '
                'WHERE transfer_type=2 '
                'and from_stop_I!=to_stop_I')
        # First we have to run with INSERT OR IGNORE to add in any
        # rows that are missing.  Unfortunately there is no INSERT OR
        # UPDATE, so we do this in two stages.  First we insert any
        # missing rows (there is a unique constraint on (from_stop_I,
        # to_stop_I)) and then we update all rows.
        cur.execute(stmt)
        cur2.executemany('INSERT OR IGNORE INTO stop_distances '
                         '(min_transfer_time, from_stop_I, to_stop_I) '
                         'VALUES (?,?,?)',
                         cur)
        # Now, run again to do UPDATE any pre-existing rows.
        cur.execute(stmt)
        cur2.executemany('UPDATE stop_distances '
                         'SET min_transfer_time=? '
                         'WHERE from_stop_I=? and to_stop_I=?',
                         cur)
        conn.commit()

        # Add timed transfers (transfer_type=1).  This is added with
        # timed_transfer=1 and min_transfer_time=0.  Again, first we
        # add missing rows, and then we update the relevant rows.
        stmt = ('SELECT from_stop_I, to_stop_I '
                'FROM transfers '
                'WHERE transfer_type=1 '
                'and from_stop_I!=to_stop_I')
        cur.execute(stmt)
        cur2.executemany('INSERT OR IGNORE INTO stop_distances '
                         '(from_stop_I, to_stop_I) '
                         'VALUES (?,?)',
                         cur)
        cur.execute(stmt)
        cur2.executemany('UPDATE stop_distances '
                         'SET timed_transfer=1, '
                         '    min_transfer_time=0 '
                         'WHERE from_stop_I=? and to_stop_I=?',
                         cur)
        conn.commit()

        # Excluded transfers.  Delete any transfer point with
        # transfer_type=3.
        cur = conn.cursor()
        cur2 = conn.cursor()
        cur.execute('SELECT from_stop_I, to_stop_I '
                    'FROM transfers '
                    'WHERE transfer_type=3')
        cur2.executemany('DELETE FROM stop_distances '
                         'WHERE from_stop_I=? and to_stop_I=?',
                         cur)
        conn.commit()

        # Calculate any `d`s missing because of inserted rows in the
        # previous two steps.
        cur.execute('UPDATE stop_distances '
                    'SET d=CAST (find_distance('
                    ' (SELECT lat FROM stops WHERE stop_I=from_stop_I), '
                    ' (SELECT lon FROM stops WHERE stop_I=from_stop_I), '
                    ' (SELECT lat FROM stops WHERE stop_I=to_stop_I), '
                    ' (SELECT lon FROM stops WHERE stop_I=to_stop_I)  ) '
                    ' AS INT)'
                    'WHERE d ISNULL'
                    )
        conn.commit()

    def export_stop_distances(self, conn, f_out):
        cur = conn.cursor()
        cur.execute('SELECT '
                    'from_stop_I, to_stop_I, '
                    'S1.lat, S1.lon, S2.lat, S2.lon, '
                    'd, '
                    'min_transfer_time '
                    'FROM stop_distances '
                    'LEFT JOIN stops S1 ON (from_stop_I=S1.stop_I)'
                    'LEFT JOIN stops S2 ON (to_stop_I  =S2.stop_I)'
                    )
        f_out.write('#from_stop_I,to_stop_I,'
                    'lat1,lon1,lat2,lon2,'
                    'd,min_transfer_time\n')
        for row in cur:
            f_out.write(','.join(str(x) for x in row) + '\n') class StopLoader(TableLoader):
    # This class is documented to explain what it does, others are not.
    # Metadata needed to create table.  GTFS filename, table name, and
    # the CREATE TABLE syntax (last part only).
    fname = 'stops.txt'
    table = 'stops'
    tabledef = '''(stop_I INTEGER PRIMARY KEY, stop_id TEXT UNIQUE NOT NULL, code TEXT, name TEXT, desc TEXT, lat REAL, lon REAL, parent_I INT, location_type INT, wheelchair_boarding BOOL, self_or_parent_I INT)'''

    def gen_rows(self, readers, prefixes):
        for reader, prefix in zip(readers, prefixes):
            for row in reader:
                # and transform the "row" dictionary into a new
                # dictionary, which is yielded.  There can be different
                # transformations here, as needed.
                yield dict(
                    stop_id       = prefix + decode_six(row['stop_id']),
                    code          = decode_six(row['stop_code']) if 'stop_code' in row else None,
                    name          = decode_six(row['stop_name']),
                    desc          = decode_six(row['stop_desc']) if 'stop_desc' in row else None,
                    lat           = float(row['stop_lat']),
                    lon           = float(row['stop_lon']),
                    _parent_id    = prefix + decode_six(row['parent_station']) if row.get('parent_station','') else None,
                    location_type = int(row['location_type']) if row.get('location_type') else None,
                    wheelchair_boarding = int(row['wheelchair_boarding']) if row.get('wheelchair_boarding','') else None,
                )

    def post_import(self, cur):
        # if parent_id, set  also parent_I:
        # :_parent_id stands for a named parameter _parent_id
        # inputted through a dictionary in cur.executemany
        stmt = ('UPDATE %s SET parent_I=CASE WHEN (:_parent_id IS NOT "") THEN '
                '(SELECT stop_I FROM %s WHERE stop_id=:_parent_id) END '
                'WHERE stop_id=:stop_id') % (self.table, self.table)
        if self.exists():
            cur.executemany(stmt, self.gen_rows0())
        stmt = 'UPDATE %s ' \
               'SET self_or_parent_I=coalesce(parent_I, stop_I)' % self.table
        cur.execute(stmt)

    def index(self, cur):
        # Make indexes/ views as needed.
        #cur.execute('CREATE INDEX IF NOT EXISTS idx_stop_sid ON stop (stop_id)')
        #cur.execute('CREATE INDEX IF NOT EXISTS idx_stops_pid_sid ON stops (parent_id, stop_I)')
        #conn.commit()
        pass class StopTimesLoader(TableLoader):
    fname = 'stop_times.txt'
    table = 'stop_times'
    tabledef = ('(stop_I INT, trip_I INT, arr_time TEXT, dep_time TEXT, '
                'seq INT, arr_time_hour INT, shape_break INT, '
                'arr_time_ds INT, dep_time_ds INT)')
    extra_keys = ['stop_I',
                  'trip_I',
                  'arr_time_ds',
                  'dep_time_ds',
                  ]
    extra_values = ['(SELECT stop_I FROM stops WHERE stop_id=:_stop_id )',
                    '(SELECT trip_I FROM trips WHERE trip_id=:_trip_id )',
                    '(substr(:arr_time,-8,2)*3600 + substr(:arr_time,-5,2)*60 + substr(:arr_time,-2))',
                    '(substr(:dep_time,-8,2)*3600 + substr(:dep_time,-5,2)*60 + substr(:dep_time,-2))',
                    ]

    # trip_id,arrival_time,departure_time,stop_id,stop_sequence,stop_headsign,pickup_type,drop_off_type,shape_dist_traveled
    # 1001_20150424_Ke_1_0953,09:53:00,09:53:00,1030423,1,,0,1,0.0000
    def gen_rows(self, readers, prefixes):
        for reader, prefix in zip(readers, prefixes):
            for row in reader:
                #print row
                assert row['arrival_time'] != "", "Some stop_times entries is missing arrival time information."
                assert row['departure_time'] != "", "Some stop_times entries is missing departure time information."
                assert row['stop_sequence'] != "", "Some stop_times entries is missing seq information."
                assert row['stop_id'] != "", "Some stop_times entries is missing stop_id information."
                assert row['trip_id'] != "", "Some stop_times entries is missing trip_id information."
                yield dict(
                    _stop_id      = prefix + decode_six(row['stop_id']),
                    _trip_id      = prefix + decode_six(row['trip_id']),
                    arr_time      = row['arrival_time'],
                    dep_time      = row['departure_time'],
                    seq           = int(row['stop_sequence']),
                )

    def post_import(self, cur):
        # The following makes an arr_time_hour column that has an
        # integer of the arrival time hour.  Conversion to integer is
        # done in the sqlite engine, since the column affinity is
        # declared to be INT.
        cur.execute('UPDATE stop_times SET arr_time_hour = substr(arr_time, -8, 2)')
        calculate_trip_shape_breakpoints(self._conn)

        # Resequence seq value to increments of 1 starting from 1
        resequence_stop_times_seq_values(self._conn)


    @classmethod
    def index(cls, cur):
        cur.execute('CREATE INDEX IF NOT EXISTS idx_stop_times_tid_seq ON stop_times (trip_I, seq)')
        # Do *not* use this index, use the one below
        #cur.execute('CREATE INDEX idx_stop_times_tid_ath ON stop_times (trip_id, arr_time_hour)')
        # This is used for the stop frequencies analysis.
        #cur.execute('CREATE INDEX idx_stop_times_tid_ath_sid ON stop_times (trip_I, arr_time_hour, stop_id)')
            # ^-- much slower than the next index.
        cur.execute('CREATE INDEX idx_stop_times_ath_tid_sid ON stop_times (arr_time_hour, trip_I, stop_I)')

    # This has now been moved to DayTripsMaterializer, but is left
    # here in case we someday want to make DayTripsMaterializer
    # optional.
    #def make_views(self, conn):
    #    conn.execute('DROP VIEW IF EXISTS main.day_stop_times')
    #    conn.execute('CREATE VIEW day_stop_times AS   '
    #                 'SELECT stop_times.*, trips.*, days.*, '
    #                 'days.day_start_ut+trips.start_time_ds AS start_time_ut, '
    #                 'days.day_start_ut+trips.end_time_ds AS end_time_ut, '
    #                 'days.day_start_ut+stop_times.arr_time_ds AS arr_time_ut, '
    #                 'days.day_start_ut+stop_times.dep_time_ds AS dep_time_ut   '
    #                 'FROM days '
    #                 'JOIN trips USING (trip_I) '
    #                 'JOIN stop_times USING (trip_I)')
    #    conn.commit() class TransfersLoader(TableLoader):
    """Loader to calculate transfer distances.

    transfer_type, from GTFS spec:
      0/null: recommended transfer point
      1: timed transfer
      2: minimum amount of time
      3: transfers not possible

    """
    # This loader is special.  calc_transfers creates the table there,
    # too.  We put a tabledef here so that copy() will work.
    fname = 'transfers.txt'
    table = 'transfers'
    # TODO: this is copy-pasted from calc_transfers.
    tabledef = ('(from_stop_I INT, '
                'to_stop_I INT, '
                'transfer_type INT, '
                'min_transfer_time INT'
                ')')
    extra_keys = ['from_stop_I',
                  'to_stop_I',
                  ]
    extra_values = ['(SELECT stop_I FROM stops WHERE stop_id=:_from_stop_id)',
                    '(SELECT stop_I FROM stops WHERE stop_id=:_to_stop_id)',
                    ]

    def gen_rows(self, readers, prefixes):
        for reader, prefix in zip(readers, prefixes):
            for row in reader:
                #print row
                yield dict(
                    _from_stop_id     = prefix + decode_six(row['from_stop_id']).strip(),
                    _to_stop_id       = prefix + decode_six(row['to_stop_id']).strip(),
                    transfer_type     = int(row['transfer_type']),
                    min_transfer_time = int(row['min_transfer_time'])
                                        if ('min_transfer_time' in row
                                        and (row.get('min_transfer_time').strip()) )
                                        else None
            ) class TableLoader(object):
    def __init__(self, gtfssource=None, print_progress=True):
    def exists(self):
    def exists_by_source(self):
    def assert_exists_if_required(self):
    def gen_rows0(self):
    def _get_csv_reader_generators(self):
        def _iter_file(file_obj):
    def gen_rows(self, csv_readers, prefixes):
    def create_table(self, conn):
    def insert_data(self, conn):
    def run_post_import(self, conn):
    def create_index(self, conn):
    def import_(self, conn):
    def make_views(cls, conn):
    def copy(cls, conn, **where):
    def post_import_round2(cls, conn):
def decode_six(string):
                    Z = zipfile.ZipFile(source['zipfile'], mode='r')
        REQUIRED_FILES_GTFS = ["agency.txt", "stops.txt", "routes.txt", "trips.txt", "stop_times.txt"]
                        Z = zipfile.ZipFile(source['zipfile'], mode='r') def get_spatial_bounds(gtfs, as_dict=False):
def get_percentile_stop_bounds(gtfs, percentile):
def get_median_lat_lon_of_stops(gtfs):
def get_centroid_of_stops(gtfs):
def write_stats_as_csv(gtfs, path_to_csv, re_write=False):
def get_stats(gtfs):
def _distribution(gtfs, table, column):
def _fleet_size_estimate(gtfs, hour, date):
def _n_gtfs_sources(gtfs):
def _feed_calendar_span(gtfs, stats):
def update_stats(gtfs):
def trip_stats(gtfs, results_by_mode=False):
def get_section_stats(gtfs, results_by_mode=False):
def route_frequencies(gtfs, results_by_mode=False):
def hourly_frequencies(gtfs, st, et, route_type):
def frequencies_by_generated_route(gtfs, st, et, day=None):
def departure_stops(gtfs, st, et):
def get_vehicle_hours_by_type(gtfs, route_type):
def trips_frequencies(gtfs): TORADIANS = 3.141592653589793 / 180.
EARTH_RADIUS = 6378137.
    R2 = EARTH_RADIUS * cos(lat * TORADIANS)
def set_process_timezone(TZ):
def wgs84_distance(lat1, lon1, lat2, lon2):
def wgs84_height(meters):
def wgs84_width(meters, lat):
def create_file(fname=None, fname_tmp=None, tmpdir=None,
                save_tmpfile=False, keepext=False):
def execute(cur, *args):
def to_date_string(date):
def ut_to_utc_datetime_str(time_ut):
def str_time_to_day_seconds(time):
def day_seconds_to_str_time(ds):
def makedirs(path):
def timeit(method):
    def timed(*args, **kw):
def corrupted_zip(zip_path):
def source_csv_to_pandas(path, table, read_csv_args=None):
def write_shapefile(data, shapefile_path):
def zip_open(z, filename):
def draw_net_using_node_coords(net):
def make_sure_path_exists(path):
def difference_of_pandas_dfs(df_self, df_other, col_names=None):
def graph_has_node(g, node):
def graph_node_attrs(g, node):
class SystemTime(ctypes.Structure): class GTFS(object):

    def __init__(self, fname_or_conn):
        """Open a GTFS object

        Parameters
        ----------
        fname_or_conn: str | sqlite3.Connection
            path to the preprocessed gtfs database or a connection to a gtfs database
        """
        if isinstance(fname_or_conn, string_types):
            if os.path.isfile(fname_or_conn):
                self.conn = sqlite3.connect(fname_or_conn)
                self.fname = fname_or_conn
                # memory-mapped IO size, in bytes
                self.conn.execute('PRAGMA mmap_size = 1000000000;')
                # page cache size, in negative KiB.
                self.conn.execute('PRAGMA cache_size = -2000000;')
            else:
                raise FileNotFoundError("File " + fname_or_conn + " missing")
        elif isinstance(fname_or_conn, sqlite3.Connection):
            self.conn = fname_or_conn
            self._dont_close = True
        else:
            raise NotImplementedError(
                "Initiating GTFS using an object with type " + str(type(fname_or_conn)) + " is not supported")

        assert self.conn.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchone() is not None
        self.meta = GTFSMetadata(self.conn)

        # Bind functions
        self.conn.create_function("find_distance", 4, wgs84_distance)

        # Set timezones
        self._timezone = pytz.timezone(self.get_timezone_name())

    def __del__(self):
        if not getattr(self, '_dont_close', False) and hasattr(self, "conn"):
            self.conn.close()

    @classmethod
    def from_directory_as_inmemory_db(cls, gtfs_directory):
        """
        Instantiate a GTFS object by computing

        Parameters
        ----------
        gtfs_directory: str
            path to the directory for importing the database
        """
        # this import is here to avoid circular imports (which turned out to be a problem)
        from gtfspy.import_gtfs import import_gtfs
        conn = sqlite3.connect(":memory:")
        import_gtfs(gtfs_directory,
                    conn,
                    preserve_connection=True,
                    print_progress=False)
        return cls(conn)

    def get_main_database_path(self):
        """
        Should return the path to the database

        Returns
        -------
        path : unicode
            path to the database, empty string for in-memory databases
        """
        cur = self.conn.cursor()
        cur.execute("PRAGMA database_list")
        rows = cur.fetchall()
        for row in rows:
            if row[1] == str("main"):
                return row[2]

    def get_location_name(self):
        return self.meta.get('location_name', "location_unknown")

    def get_shape_distance_between_stops(self, trip_I, from_stop_seq, to_stop_seq):
        """
        Get the distance along a shape between stops

        Parameters
        ----------
        trip_I : int
            trip_ID along which we travel
        from_stop_seq : int
            the sequence number of the 'origin' stop
        to_stop_seq : int
            the sequence number of the 'destination' stop

        Returns
        -------
        distance : float, None
            If the shape calculation succeeded, return a float, otherwise return None
            (i.e. in the case where the shapes table is empty)
        """

        query_template = "SELECT shape_break FROM stop_times WHERE trip_I={trip_I} AND seq={seq} "
        stop_seqs = [from_stop_seq, to_stop_seq]
        shape_breaks = []
        for seq in stop_seqs:
            q = query_template.format(seq=seq, trip_I=trip_I)
            shape_breaks.append(self.conn.execute(q).fetchone())
        query_template = "SELECT max(d) - min(d) " \
                         "FROM shapes JOIN trips ON(trips.shape_id=shapes.shape_id) " \
                         "WHERE trip_I={trip_I} AND shapes.seq>={from_stop_seq} AND shapes.seq<={to_stop_seq};"
        distance_query = query_template.format(trip_I=trip_I, from_stop_seq=from_stop_seq, to_stop_seq=to_stop_seq)
        return self.conn.execute(distance_query).fetchone()[0]

    def get_stop_distance(self, from_stop_I, to_stop_I):
        query_template = "SELECT d_walk FROM stop_distances WHERE from_stop_I={from_stop_I} AND to_stop_I={to_stop_I} "
        q = query_template.format(from_stop_I=int(from_stop_I), to_stop_I=int(to_stop_I))
        if self.conn.execute(q).fetchone():
            return self.conn.execute(q).fetchone()[0]
        else:
            return None

    def get_stops_within_distance(self, stop, distance):
        query = """SELECT stops.* FROM stop_distances, stops
                    WHERE stop_distances.to_stop_I = stops.stop_I 
                    AND d < %s AND from_stop_I = %s""" % (distance, stop)
        return pd.read_sql_query(query, self.conn)

    def get_directly_accessible_stops_within_distance(self, stop, distance):
        """
        Returns stops that are accessible without transfer from the stops that are within a specific walking distance
        :param stop: int
        :param distance: int
        :return:
        """
        query = """SELECT stop.* FROM
                    (SELECT st2.* FROM 
                    (SELECT * FROM stop_distances
                    WHERE from_stop_I = %s) sd,
                    (SELECT * FROM stop_times) st1,
                    (SELECT * FROM stop_times) st2
                    WHERE sd.d < %s AND sd.to_stop_I = st1.stop_I AND st1.trip_I = st2.trip_I 
                    GROUP BY st2.stop_I) sq,
                    (SELECT * FROM stops) stop
                    WHERE sq.stop_I = stop.stop_I""" % (stop, distance)
        return pd.read_sql_query(query, self.conn)

    def get_cursor(self):
        """
        Return a cursor to the underlying sqlite3 object
        """
        return self.conn.cursor()

    def get_table(self, table_name):
        """
        Return a pandas.DataFrame object corresponding to the sql table

        Parameters
        ----------
        table_name: str
            name of the table in the database

        Returns
        -------
        df : pandas.DataFrame
        """
        return pd.read_sql("SELECT * FROM " + table_name, self.conn)

    def get_row_count(self, table):
        """
        Get number of rows in a table
        """
        return self.conn.cursor().execute("SELECT count(*) FROM " + table).fetchone()[0]

    def get_table_names(self):
        """
        Return a list of the underlying tables in the database.

        Returns
        -------
        table_names: list[str]
        """
        return list(pd.read_sql("SELECT * FROM main.sqlite_master WHERE type='table'", self.conn)["name"])

    def set_current_process_time_zone(self):
        """
        This function queries a GTFS connection, finds the timezone of this
        database, and sets it in the TZ environment variable.  This is a
        process-global configuration, by the nature of the C library!

        Returns
        -------
        None

        Alters os.environ['TZ']
        """
        TZ = self.conn.execute('SELECT timezone FROM agencies LIMIT 1').fetchall()[0][0]
        # TODO!: This is dangerous (?).
        # In my opinion, we should get rid of this at some point (RK):
        return set_process_timezone(TZ)

    def get_timezone_pytz(self):
        return self._timezone

    def get_timezone_name(self):
        """
        Get name of the GTFS timezone

        Returns
        -------
        timezone_name : str
            name of the time zone, e.g. "Europe/Helsinki"
        """
        tz_name = self.conn.execute('SELECT timezone FROM agencies LIMIT 1').fetchone()
        if tz_name is None:
            raise ValueError("This database does not have a timezone defined.")
        return tz_name[0]

    def get_timezone_string(self, dt=None):
        """
        Return the timezone of the GTFS database object as a string.
        The assumed time when the timezone (difference) is computed
        is the download date of the file.
        This might not be optimal in all cases.

        So this function should return values like:
            "+0200" or "-1100"

        Parameters
        ----------
        dt : datetime.datetime, optional
            The (unlocalized) date when the timezone should be computed.
            Defaults first to download_date, and then to the runtime date.

        Returns
        -------
        timezone_string : str
        """
        if dt is None:
            download_date = self.meta.get('download_date')
            if download_date:
                dt = datetime.datetime.strptime(download_date, '%Y-%m-%d')
            else:
                dt = datetime.datetime.today()
        loc_dt = self._timezone.localize(dt)
        # get the timezone
        timezone_string = loc_dt.strftime("%z")
        return timezone_string

    def unixtime_seconds_to_gtfs_datetime(self, unixtime):
        """
        Convert unixtime to localized datetime

        Parameters
        ----------
        unixtime : int

        Returns
        -------
        gtfs_datetime: datetime.datetime
            time localized to gtfs_datetime's timezone
        """
        return datetime.datetime.fromtimestamp(unixtime, self._timezone)

    def unlocalized_datetime_to_ut_seconds(self, unlocalized_datetime):
        """
        Convert datetime (in GTFS timezone) to unixtime

        Parameters
        ----------
        unlocalized_datetime : datetime.datetime
            (tz coerced to GTFS timezone, should NOT be UTC.)

        Returns
        -------
        output : int (unixtime)
        """
        loc_dt = self._timezone.localize(unlocalized_datetime)
        unixtime_seconds = calendar.timegm(loc_dt.utctimetuple())
        return unixtime_seconds

    def get_day_start_ut(self, date):
        """
        Get day start time (as specified by GTFS) as unix time in seconds

        Parameters
        ----------
        date : str | unicode | datetime.datetime
            something describing the date

        Returns
        -------
        day_start_ut : int
            start time of the day in unixtime
        """
        if isinstance(date, string_types):
            date = datetime.datetime.strptime(date, '%Y-%m-%d')

        date_noon = datetime.datetime(date.year, date.month, date.day, 12, 0, 0)
        ut_noon = self.unlocalized_datetime_to_ut_seconds(date_noon)
        return ut_noon - 12 * 60 * 60  # this comes from GTFS: noon-12 hrs

    def get_trip_trajectories_within_timespan(self, start, end, use_shapes=True, filter_name=None):
        """
        Get complete trip data for visualizing public transport operation based on gtfs.

        Parameters
        ----------
        start: number
            Earliest position data to return (in unix time)
        end: number
            Latest position data to return (in unix time)
        use_shapes: bool, optional
            Whether or not shapes should be included
        filter_name: str
            Pick only routes having this name.

        Returns
        -------
        trips: dict
            trips['trips'] is a list whose each element (e.g. el = trips['trips'][0])
            is a dict with the following properties:
                el['lats'] -- list of latitudes
                el['lons'] -- list of longitudes
                el['times'] -- list of passage_times
                el['route_type'] -- type of vehicle as specified by GTFS
                el['name'] -- name of the route
        """
        trips = []
        trip_df = self.get_tripIs_active_in_range(start, end)
        print("gtfs_viz.py: fetched " + str(len(trip_df)) + " trip ids")
        shape_cache = {}

        # loop over all trips:
        for row in trip_df.itertuples():
            trip_I = row.trip_I
            day_start_ut = row.day_start_ut
            shape_id = row.shape_id

            trip = {}

            name, route_type = self.get_route_name_and_type_of_tripI(trip_I)
            trip['route_type'] = int(route_type)
            trip['name'] = str(name)

            if filter_name and (name != filter_name):
                continue

            stop_lats = []
            stop_lons = []
            stop_dep_times = []
            shape_breaks = []
            stop_seqs = []

            # get stop_data and store it:
            stop_time_df = self.get_trip_stop_time_data(trip_I, day_start_ut)
            for stop_row in stop_time_df.itertuples():
                stop_lats.append(float(stop_row.lat))
                stop_lons.append(float(stop_row.lon))
                stop_dep_times.append(float(stop_row.dep_time_ut))
                try:
                    stop_seqs.append(int(stop_row.seq))
                except TypeError:
                    stop_seqs.append(None)
                if use_shapes:
                    try:
                        shape_breaks.append(int(stop_row.shape_break))
                    except (TypeError, ValueError):
                        shape_breaks.append(None)

            if use_shapes:
                # get shape data (from cache, if possible)
                if shape_id not in shape_cache:
                    shape_cache[shape_id] = shapes.get_shape_points2(self.conn.cursor(), shape_id)
                shape_data = shape_cache[shape_id]
                # noinspection PyBroadException
                try:
                    trip['times'] = shapes.interpolate_shape_times(shape_data['d'], shape_breaks, stop_dep_times)
                    trip['lats'] = shape_data['lats']
                    trip['lons'] = shape_data['lons']
                    start_break = shape_breaks[0]
                    end_break = shape_breaks[-1]
                    trip['times'] = trip['times'][start_break:end_break + 1]
                    trip['lats'] = trip['lats'][start_break:end_break + 1]
                    trip['lons'] = trip['lons'][start_break:end_break + 1]
                except:
                    # In case interpolation fails:
                    trip['times'] = stop_dep_times
                    trip['lats'] = stop_lats
                    trip['lons'] = stop_lons
            else:
                trip['times'] = stop_dep_times
                trip['lats'] = stop_lats
                trip['lons'] = stop_lons
            trips.append(trip)
        return {"trips": trips}

    def get_stop_count_data(self, start_ut, end_ut):
        """
        Get stop count data.

        Parameters
        ----------
        start_ut : int
            start time in unixtime
        end_ut : int
            end time in unixtime

        Returns
        -------
        stopData : pandas.DataFrame
            each row in the stopData dataFrame is a dictionary with the following elements
                stop_I, count, lat, lon, name
            with data types
                (int, int, float, float, str)
        """
        # TODO! this function could perhaps be made a single sql query now with the new tables?
        trips_df = self.get_tripIs_active_in_range(start_ut, end_ut)
        # stop_I -> count, lat, lon, name
        stop_counts = Counter()

        # loop over all trips:
        for row in trips_df.itertuples():
            # get stop_data and store it:
            stops_seq = self.get_trip_stop_time_data(row.trip_I, row.day_start_ut)
            for stop_time_row in stops_seq.itertuples(index=False):
                if (stop_time_row.dep_time_ut >= start_ut) and (stop_time_row.dep_time_ut <= end_ut):
                    stop_counts[stop_time_row.stop_I] += 1

        all_stop_data = self.stops()
        counts = [stop_counts[stop_I] for stop_I in all_stop_data["stop_I"].values]

        all_stop_data.loc[:, "count"] = pd.Series(counts, index=all_stop_data.index)
        return all_stop_data

    def get_segment_count_data(self, start, end, use_shapes=True):
        """
        Get segment data including PTN vehicle counts per segment that are
        fully _contained_ within the interval (start, end)

        Parameters
        ----------
        start : int
            start time of the simulation in unix time
        end : int
            end time of the simulation in unix time
        use_shapes : bool, optional
            whether to include shapes (if available)

        Returns
        -------
        seg_data : list
            each element in the list is a dict containing keys:
                "trip_I", "lats", "lons", "shape_id", "stop_seqs", "shape_breaks"
        """
        cur = self.conn.cursor()
        # get all possible trip_ids that take place between start and end
        trips_df = self.get_tripIs_active_in_range(start, end)
        # stop_I -> count, lat, lon, name
        segment_counts = Counter()
        seg_to_info = {}
        # tripI_to_seq = "inverted segToShapeData"
        tripI_to_seq = defaultdict(list)

        # loop over all trips:
        for row in trips_df.itertuples():
            # get stop_data and store it:
            stops_df = self.get_trip_stop_time_data(row.trip_I, row.day_start_ut)
            for i in range(len(stops_df) - 1):
                (stop_I, dep_time_ut, s_lat, s_lon, s_seq, shape_break) = stops_df.iloc[i]
                (stop_I_n, dep_time_ut_n, s_lat_n, s_lon_n, s_seq_n, shape_break_n) = stops_df.iloc[i + 1]
                # test if _contained_ in the interval
                # overlap would read:
                #   (dep_time_ut <= end) and (start <= dep_time_ut_n)
                if (dep_time_ut >= start) and (dep_time_ut_n <= end):
                    seg = (stop_I, stop_I_n)
                    segment_counts[seg] += 1
                    if seg not in seg_to_info:
                        seg_to_info[seg] = {
                            u"trip_I": row.trip_I,
                            u"lats": [s_lat, s_lat_n],
                            u"lons": [s_lon, s_lon_n],
                            u"shape_id": row.shape_id,
                            u"stop_seqs": [s_seq, s_seq_n],
                            u"shape_breaks": [shape_break, shape_break_n]
                        }
                        tripI_to_seq[row.trip_I].append(seg)

        stop_names = {}
        for (stop_I, stop_J) in segment_counts.keys():
            for s in [stop_I, stop_J]:
                if s not in stop_names:
                    stop_names[s] = self.stop(s)[u'name'].values[0]

        seg_data = []
        for seg, count in segment_counts.items():
            segInfo = seg_to_info[seg]
            shape_breaks = segInfo[u"shape_breaks"]
            seg_el = {}
            if use_shapes and shape_breaks and shape_breaks[0] and shape_breaks[1]:
                shape = shapes.get_shape_between_stops(
                    cur,
                    segInfo[u'trip_I'],
                    shape_breaks=shape_breaks
                )
                seg_el[u'lats'] = segInfo[u'lats'][:1] + shape[u'lat'] + segInfo[u'lats'][1:]
                seg_el[u'lons'] = segInfo[u'lons'][:1] + shape[u'lon'] + segInfo[u'lons'][1:]
            else:
                seg_el[u'lats'] = segInfo[u'lats']
                seg_el[u'lons'] = segInfo[u'lons']
            seg_el[u'name'] = stop_names[seg[0]] + u"-" + stop_names[seg[1]]
            seg_el[u'count'] = count
            seg_data.append(seg_el)
        return seg_data


    def get_all_route_shapes(self, use_shapes=True):
        """
        Get the shapes of all routes.

        Parameters
        ----------
        use_shapes : bool, optional
            by default True (i.e. use shapes as the name of the function indicates)
            if False (fall back to lats and longitudes)

        Returns
        -------
        routeShapes: list of dicts that should have the following keys
            name, type, agency, lats, lons
            with types
            list, list, str, list, list
        """
        cur = self.conn.cursor()

        # all shape_id:s corresponding to a route_I:
        # query = "SELECT DISTINCT name, shape_id, trips.route_I, route_type
        #          FROM trips LEFT JOIN routes USING(route_I)"
        # data1 = pd.read_sql_query(query, self.conn)
        # one (arbitrary) shape_id per route_I ("one direction") -> less than half of the routes
        query = "SELECT routes.name as name, shape_id, route_I, trip_I, routes.type, " \
                "        agency_id, agencies.name as agency_name, max(end_time_ds-start_time_ds) as trip_duration " \
                "FROM trips " \
                "LEFT JOIN routes " \
                "USING(route_I) " \
                "LEFT JOIN agencies " \
                "USING(agency_I) " \
                "GROUP BY routes.route_I"
        data = pd.read_sql_query(query, self.conn)

        routeShapes = []
        for i, row in enumerate(data.itertuples()):
            datum = {"name": str(row.name), "type": int(row.type), "route_I": row.route_I, "agency": str(row.agency_id),
                     "agency_name": str(row.agency_name)}
            # this function should be made also non-shape friendly (at this point)
            if use_shapes and row.shape_id:
                shape = shapes.get_shape_points2(cur, row.shape_id)
                lats = shape['lats']
                lons = shape['lons']
            else:
                stop_shape = self.get_trip_stop_coordinates(row.trip_I)
                lats = list(stop_shape['lat'])
                lons = list(stop_shape['lon'])
            datum['lats'] = [float(lat) for lat in lats]
            datum['lons'] = [float(lon) for lon in lons]
            routeShapes.append(datum)
        return routeShapes

    def get_tripIs_active_in_range(self, start, end):
        """
        Obtain from the (standard) GTFS database, list of trip_IDs (and other trip_related info)
        that are active between given 'start' and 'end' times.

        The start time of a trip is determined by the departure time at the last stop of the trip.
        The end time of a trip is determined by the arrival time at the last stop of the trip.

        Parameters
        ----------
        start, end : int
            the start and end of the time interval in unix time seconds

        Returns
        -------
        active_trips : pandas.DataFrame with columns
            trip_I, day_start_ut, start_time_ut, end_time_ut, shape_id
        """
        to_select = "trip_I, day_start_ut, start_time_ut, end_time_ut, shape_id "
        query = "SELECT " + to_select + \
                "FROM day_trips " \
                "WHERE " \
                "(end_time_ut > {start_ut} AND start_time_ut < {end_ut})".format(start_ut=start, end_ut=end)
        return pd.read_sql_query(query, self.conn)

    def get_trip_counts_per_day(self):
        """
        Get trip counts per day between the start and end day of the feed.

        Returns
        -------
        trip_counts : pandas.DataFrame
            Has columns "date_str" (dtype str) "trip_counts" (dtype int)
        """
        query = "SELECT date, count(*) AS number_of_trips FROM day_trips GROUP BY date"
        # this yields the actual data
        trip_counts_per_day = pd.read_sql_query(query, self.conn, index_col="date")
        # the rest is simply code for filling out "gaps" in the time span
        # (necessary for some visualizations)
        max_day = trip_counts_per_day.index.max()
        min_day = trip_counts_per_day.index.min()
        min_date = datetime.datetime.strptime(min_day, '%Y-%m-%d')
        max_date = datetime.datetime.strptime(max_day, '%Y-%m-%d')
        num_days = (max_date - min_date).days
        dates = [min_date + datetime.timedelta(days=x) for x in range(num_days + 1)]
        trip_counts = []
        date_strings = []
        for date in dates:
            date_string = date.strftime("%Y-%m-%d")
            date_strings.append(date_string)
            try:
                value = trip_counts_per_day.loc[date_string, 'number_of_trips']
            except KeyError:
                # set value to 0 if dsut is not present, i.e. when no trips
                # take place on that day
                value = 0
            trip_counts.append(value)
        # check that all date_strings are included (move this to tests?)
        for date_string in trip_counts_per_day.index:
            assert date_string in date_strings
        data = {"date": dates, "date_str": date_strings, "trip_counts": trip_counts}
        return pd.DataFrame(data)

    def get_suitable_date_for_daily_extract(self, date=None, ut=False):
        """
        Parameters
        ----------
        date : str
        ut : bool
            Whether to return the date as a string or as a an int (seconds after epoch).

        Returns
        -------
        Selects suitable date for daily extract
        Iterates trough the available dates forward and backward from the download date accepting the first day that has
        at least 90 percent of the number of trips of the maximum date. The condition can be changed to something else.
        If the download date is out of range, the process will look through the dates from first to last.
        """
        daily_trips = self.get_trip_counts_per_day()
        max_daily_trips = daily_trips[u'trip_counts'].max(axis=0)
        if date in daily_trips[u'date_str']:
            start_index = daily_trips[daily_trips[u'date_str'] == date].index.tolist()[0]
            daily_trips[u'old_index'] = daily_trips.index
            daily_trips[u'date_dist'] = abs(start_index - daily_trips.index)
            daily_trips = daily_trips.sort_values(by=[u'date_dist', u'old_index']).reindex()
        for row in daily_trips.itertuples():
            if row.trip_counts >= 0.9 * max_daily_trips:
                if ut:
                    return self.get_day_start_ut(row.date_str)
                else:
                    return row.date_str

    def get_weekly_extract_start_date(self, ut=False, weekdays_at_least_of_max=0.9,
                                      verbose=False, download_date_override=None):
        """
        Find a suitable weekly extract start date (monday).
        The goal is to obtain as 'usual' week as possible.
        The weekdays of the weekly extract week should contain
        at least 0.9 of the total maximum of trips.

        Parameters
        ----------
        ut: return unixtime?
        weekdays_at_least_of_max: float

        download_date_override: str, semi-optional
            Download-date in format %Y-%m-%d, weeks close to this.
            Overrides the (possibly) recorded downloaded date in the database

        Returns
        -------
        date: int or str

        Raises
        ------
        error: RuntimeError
            If no download date could be found.
        """
        daily_trip_counts = self.get_trip_counts_per_day()
        if isinstance(download_date_override, str):
            search_start_date = datetime.datetime.strptime(download_date_override, "%Y-%m-%d")
        elif isinstance(download_date_override, datetime.datetime):
            search_start_date = download_date_override
        else:
            assert download_date_override is None
            download_date_str = self.meta['download_date']
            if download_date_str == "":
                warnings.warn("Download date is not speficied in the database. "
                              "Download date used in GTFS." + self.get_weekly_extract_start_date.__name__ +
                              "() defaults to the smallest date when any operations take place.")
                search_start_date = daily_trip_counts['date'].min()
            else:
                search_start_date = datetime.datetime.strptime(download_date_str, "%Y-%m-%d")

        feed_min_date = daily_trip_counts['date'].min()
        feed_max_date = daily_trip_counts['date'].max()
        assert (feed_max_date - feed_min_date >= datetime.timedelta(days=7)), \
            "Dataset is not long enough for providing week long extracts"

        # get first a valid monday where the search for the week can be started:
        next_monday_from_search_start_date = search_start_date + timedelta(days=(7 - search_start_date.weekday()))
        if not (feed_min_date <= next_monday_from_search_start_date <= feed_max_date):
            warnings.warn("The next monday after the (possibly user) specified download date is not present in the database."
                          "Resorting to first monday after the beginning of operations instead.")
            next_monday_from_search_start_date = feed_min_date + timedelta(days=(7 - feed_min_date.weekday()))

        max_trip_count = daily_trip_counts['trip_counts'].quantile(0.95)
        # Take 95th percentile to omit special days, if any exist.

        threshold = weekdays_at_least_of_max * max_trip_count
        threshold_fulfilling_days = daily_trip_counts['trip_counts'] > threshold

        # look forward first
        # get the index of the trip:
        search_start_monday_index = daily_trip_counts[daily_trip_counts['date'] == next_monday_from_search_start_date].index[0]

        # get starting point
        while_loop_monday_index = search_start_monday_index
        while len(daily_trip_counts.index) >= while_loop_monday_index + 7:
            if all(threshold_fulfilling_days[while_loop_monday_index:while_loop_monday_index + 5]):
                row = daily_trip_counts.iloc[while_loop_monday_index]
                if ut:
                    return self.get_day_start_ut(row.date_str)
                else:
                    return row['date']
            while_loop_monday_index += 7

        while_loop_monday_index = search_start_monday_index - 7
        # then backwards
        while while_loop_monday_index >= 0:
            if all(threshold_fulfilling_days[while_loop_monday_index:while_loop_monday_index + 5]):
                row = daily_trip_counts.iloc[while_loop_monday_index]
                if ut:
                    return self.get_day_start_ut(row.date_str)
                else:
                    return row['date']
            while_loop_monday_index -= 7

        raise RuntimeError("No suitable weekly extract start date could be determined!")

    def get_spreading_trips(self, start_time_ut, lat, lon,
                            max_duration_ut=4 * 3600,
                            min_transfer_time=30,
                            use_shapes=False):
        """
        Starting from a specific point and time, get complete single source
        shortest path spreading dynamics as trips, or "events".

        Parameters
        ----------
        start_time_ut: number
            Start time of the spreading.
        lat: float
            latitude of the spreading seed location
        lon: float
            longitude of the spreading seed location
        max_duration_ut: int
            maximum duration of the spreading process (in seconds)
        min_transfer_time : int
            minimum transfer time in seconds
        use_shapes : bool
            whether to include shapes

        Returns
        -------
        trips: dict
            trips['trips'] is a list whose each element (e.g. el = trips['trips'][0])
            is a dict with the following properties:
                el['lats'] : list of latitudes
                el['lons'] : list of longitudes
                el['times'] : list of passage_times
                el['route_type'] : type of vehicle as specified by GTFS, or -1 if walking
                el['name'] : name of the route
        """
        from gtfspy.spreading.spreader import Spreader
        spreader = Spreader(self, start_time_ut, lat, lon, max_duration_ut, min_transfer_time, use_shapes)
        return spreader.spread()

    def get_closest_stop(self, lat, lon):
        """
        Get closest stop to a given location.

        Parameters
        ----------
        lat: float
            latitude coordinate of the location
        lon: float
            longitude coordinate of the location

        Returns
        -------
        stop_I: int
            the index of the stop in the database
        """
        cur = self.conn.cursor()
        min_dist = float("inf")
        min_stop_I = None
        rows = cur.execute("SELECT stop_I, lat, lon FROM stops")
        for stop_I, lat_s, lon_s in rows:
            dist_now = wgs84_distance(lat, lon, lat_s, lon_s)
            if dist_now < min_dist:
                min_dist = dist_now
                min_stop_I = stop_I
        return min_stop_I

    def get_stop_coordinates(self, stop_I):
        cur = self.conn.cursor()
        results = cur.execute("SELECT lat, lon FROM stops WHERE stop_I={stop_I}".format(stop_I=stop_I))
        lat, lon = results.fetchone()
        return lat, lon

    def get_bounding_box_by_stops(self, stop_Is, buffer_ratio=None):
        lats = []
        lons = []
        for stop_I in stop_Is:
            lat, lon = self.get_stop_coordinates(stop_I)
            lats.append(lat)
            lons.append(lon)
        min_lat = min(lats)
        max_lat = max(lats)
        min_lon = min(lons)
        max_lon = max(lons)
        lon_diff = 0
        lat_diff = 0

        if buffer_ratio:
            distance = buffer_ratio * wgs84_distance(min_lat, min_lon, max_lat, max_lon)
            lat_diff = wgs84_height(distance)
            lon_diff = wgs84_width(distance, (max_lat - min_lat) / 2 + min_lat)

        return {"lat_min": min_lat - lat_diff,
                "lat_max": max_lat + lat_diff,
                "lon_min": min_lon - lon_diff,
                "lon_max": max_lon + lon_diff}

    def get_route_name_and_type_of_tripI(self, trip_I):
        """
        Get route short name and type

        Parameters
        ----------
        trip_I: int
            short trip index created when creating the database

        Returns
        -------
        name: str
            short name of the route, eg. 195N
        type: int
            route_type according to the GTFS standard
        """
        cur = self.conn.cursor()
        results = cur.execute("SELECT name, type FROM routes JOIN trips USING(route_I) WHERE trip_I={trip_I}"
                              .format(trip_I=trip_I))
        name, rtype = results.fetchone()
        return u"%s" % str(name), int(rtype)

    def get_route_name_and_type(self, route_I):
        """
        Get route short name and type

        Parameters
        ----------
        route_I: int
            route index (database specific)

        Returns
        -------
        name: str
            short name of the route, eg. 195N
        type: int
            route_type according to the GTFS standard
        """
        cur = self.conn.cursor()
        results = cur.execute("SELECT name, type FROM routes WHERE route_I=(?)", (route_I,))
        name, rtype = results.fetchone()
        return name, int(rtype)

    def get_trip_stop_coordinates(self, trip_I):
        """
        Get coordinates for a given trip_I

        Parameters
        ----------
        trip_I : int
            the integer id of the trip

        Returns
        -------
        stop_coords : pandas.DataFrame
            with columns "lats" and "lons"
        """
        query = """SELECT lat, lon
                    FROM stop_times
                    JOIN stops
                    USING(stop_I)
                        WHERE trip_I={trip_I}
                    ORDER BY stop_times.seq""".format(trip_I=trip_I)
        stop_coords = pd.read_sql(query, self.conn)
        return stop_coords

    def get_trip_stop_time_data(self, trip_I, day_start_ut):
        """
        Obtain from the (standard) GTFS database, trip stop data
        (departure time in ut, lat, lon, seq, shape_break) as a pandas DataFrame

        Some filtering could be applied here, if only e.g. departure times
        corresponding within some time interval should be considered.

        Parameters
        ----------
        trip_I : int
            integer index of the trip
        day_start_ut : int
            the start time of the day in unix time (seconds)

        Returns
        -------
        df: pandas.DataFrame
            df has the following columns
            'departure_time_ut, lat, lon, seq, shape_break'
        """
        to_select = "stop_I, " + str(day_start_ut) + "+dep_time_ds AS dep_time_ut, lat, lon, seq, shape_break"
        str_to_run = "SELECT " + to_select + """
                        FROM stop_times JOIN stops USING(stop_I)
                        WHERE (trip_I ={trip_I}) ORDER BY seq
                      """
        str_to_run = str_to_run.format(trip_I=trip_I)
        return pd.read_sql_query(str_to_run, self.conn)

    def get_events_by_tripI_and_dsut(self, trip_I, day_start_ut,
                                     start_ut=None, end_ut=None):
        """
        Get trip data as a list of events (i.e. dicts).

        Parameters
        ----------
        trip_I : int
            shorthand index of the trip.
        day_start_ut : int
            the start time of the day in unix time (seconds)
        start_ut : int, optional
            consider only events that start after this time
            If not specified, this filtering is not applied.
        end_ut : int, optional
            Consider only events that end before this time
            If not specified, this filtering is not applied.

        Returns
        -------
        events: list of dicts
            each element contains the following data:
                from_stop: int (stop_I)
                to_stop: int (stop_I)
                dep_time_ut: int (in unix time)
                arr_time_ut: int (in unix time)
        """
        # for checking input:
        assert day_start_ut <= start_ut
        assert day_start_ut <= end_ut
        assert start_ut <= end_ut
        events = []
        # check that trip takes place on that day:
        if not self.tripI_takes_place_on_dsut(trip_I, day_start_ut):
            return events

        query = """SELECT stop_I, arr_time_ds+?, dep_time_ds+?
                    FROM stop_times JOIN stops USING(stop_I)
                    WHERE
                        (trip_I = ?)
                """
        params = [day_start_ut, day_start_ut,
                  trip_I]
        if start_ut:
            query += "AND (dep_time_ds > ?-?)"
            params += [start_ut, day_start_ut]
        if end_ut:
            query += "AND (arr_time_ds < ?-?)"
            params += [end_ut, day_start_ut]
        query += "ORDER BY arr_time_ds"
        cur = self.conn.cursor()
        rows = cur.execute(query, params)
        stop_data = list(rows)
        for i in range(len(stop_data) - 1):
            event = {
                "from_stop": stop_data[i][0],
                "to_stop": stop_data[i + 1][0],
                "dep_time_ut": stop_data[i][2],
                "arr_time_ut": stop_data[i + 1][1]
            }
            events.append(event)
        return events

    def tripI_takes_place_on_dsut(self, trip_I, day_start_ut):
        """
        Check that a trip takes place during a day

        Parameters
        ----------
        trip_I : int
            index of the trip in the gtfs data base
        day_start_ut : int
            the starting time of the day in unix time (seconds)

        Returns
        -------
        takes_place: bool
            boolean value describing whether the trip takes place during
            the given day or not
        """
        query = "SELECT * FROM days WHERE trip_I=? AND day_start_ut=?"
        params = (trip_I, day_start_ut)
        cur = self.conn.cursor()
        rows = list(cur.execute(query, params))
        if len(rows) == 0:
            return False
        else:
            assert len(rows) == 1, 'On a day, a trip_I should be present at most once'
            return True

    # unused and (untested) code:
    #
    # def get_tripIs_from_stopI_within_time_range(self, stop_I, day_start_ut, start_ut, end_ut):
    #     """
    #     Obtain a list of trip_Is that go through some stop during a given time.
    #
    #     Parameters
    #     ----------
    #     stop_I : int
    #         index of the stop to be considered
    #     day_start_ut : int
    #         start of the day in unix time (seconds)
    #     start_ut: int
    #         the first possible departure time from the stop
    #         in unix time (seconds)
    #     end_ut: int
    #         the last possible departure time from the stop
    #         in unix time (seconds)
    #
    #     Returns
    #     -------
    #     trip_Is: list
    #         list of integers (trip_Is)
    #     """
    #     start_ds = start_ut - day_start_ut
    #     end_ds = end_ut - day_start_ut
    #     # is the _distinct_ really required?
    #     query = "SELECT distinct(trip_I) " \
    #             "FROM days " \
    #             "JOIN stop_times " \
    #             "USING(trip_I) " \
    #             "WHERE (days.day_start_ut == ?)" \
    #             "AND (stop_times.stop_I=?) " \
    #             "AND (stop_times.dep_time_ds >= ?) " \
    #             "AND (stop_times.dep_time_ds <= ?)"
    #     params = (day_start_ut, stop_I, start_ds, end_ds)
    #     cur = self.conn.cursor()
    #     trip_Is = [el[0] for el in cur.execute(query, params)]
    #     return trip_Is

    def day_start_ut(self, ut):
        """
        Convert unixtime to unixtime on GTFS start-of-day.

        GTFS defines the start of a day as "noon minus 12 hours" to solve
        most DST-related problems. This means that on DST-changing days,
        the day start isn't midnight. This function isn't idempotent.
        Running it twice on the "move clocks backwards" day will result in
        being one day too early.

        Parameters
        ----------
        ut: int
            Unixtime

        Returns
        -------
        ut: int
            Unixtime corresponding to start of day
        """
        # set timezone to the one of gtfs
        old_tz = self.set_current_process_time_zone()
        ut = time.mktime(time.localtime(ut)[:3] + (12, 00, 0, 0, 0, -1)) - 43200
        set_process_timezone(old_tz)
        return ut

    def increment_day_start_ut(self, day_start_ut, n_days=1):
        """Increment the GTFS-definition of "day start".

        Parameters
        ----------
        day_start_ut : int
            unixtime of the previous start of day.  If this time is between
            12:00 or greater, there *will* be bugs.  To solve this, run the
            input through day_start_ut first.
        n_days: int
            number of days to increment
        """
        old_tz = self.set_current_process_time_zone()
        day0 = time.localtime(day_start_ut + 43200)  # time of noon
        dayN = time.mktime(day0[:2] +  # YYYY, MM
                           (day0[2] + n_days,) +  # DD
                           (12, 00, 0, 0, 0, -1)) - 43200  # HHMM, etc.  Minus 12 hours.
        set_process_timezone(old_tz)
        return dayN

    def _get_possible_day_starts(self, start_ut, end_ut, max_time_overnight=None):
        """
        Get all possible day start times between start_ut and end_ut
        Currently this function is used only by get_tripIs_within_range_by_dsut

        Parameters
        ----------
        start_ut : list<int>
            start time in unix time
        end_ut : list<int>
            end time in unix time
        max_time_overnight : list<int>
            the maximum length of time that a trip can take place on
            during the next day (i.e. after midnight run times like 25:35)

        Returns
        -------
        day_start_times_ut : list
            list of ints (unix times in seconds) for returning all possible day
            start times
        start_times_ds : list
            list of ints (unix times in seconds) stating the valid start time in
            day seconds
        end_times_ds : list
            list of ints (unix times in seconds) stating the valid end times in
            day_seconds
        """
        if max_time_overnight is None:
            # 7 hours:
            max_time_overnight = 7 * 60 * 60

        # sanity checks for the timezone parameter
        # assert timezone < 14
        # assert timezone > -14
        # tz_seconds = int(timezone*3600)
        assert start_ut < end_ut
        start_day_ut = self.day_start_ut(start_ut)
        # start_day_ds = int(start_ut+tz_seconds) % seconds_in_a_day  #??? needed?
        start_day_ds = start_ut - start_day_ut
        # assert (start_day_ut+tz_seconds) % seconds_in_a_day == 0
        end_day_ut = self.day_start_ut(end_ut)
        # end_day_ds = int(end_ut+tz_seconds) % seconds_in_a_day    #??? needed?
        # end_day_ds = end_ut - end_day_ut
        # assert (end_day_ut+tz_seconds) % seconds_in_a_day == 0

        # If we are early enough in a day that we might have trips from
        # the previous day still running, decrement the start day.
        if start_day_ds < max_time_overnight:
            start_day_ut = self.increment_day_start_ut(start_day_ut, n_days=-1)

        # day_start_times_ut = range(start_day_ut, end_day_ut+seconds_in_a_day, seconds_in_a_day)

        # Create a list of all possible day start times.  This is roughly
        # range(day_start_ut, day_end_ut+1day, 1day).
        day_start_times_ut = [start_day_ut]
        while day_start_times_ut[-1] < end_day_ut:
            day_start_times_ut.append(self.increment_day_start_ut(day_start_times_ut[-1]))

        start_times_ds = []
        end_times_ds = []
        # For every possible day start:
        for dsut in day_start_times_ut:
            # start day_seconds starts at either zero, or time - daystart
            day_start_ut = max(0, start_ut - dsut)
            start_times_ds.append(day_start_ut)
            # end day_seconds is time-day_start
            day_end_ut = end_ut - dsut
            end_times_ds.append(day_end_ut)
        # Return three tuples which can be zip:ped together.
        return day_start_times_ut, start_times_ds, end_times_ds

    def get_tripIs_within_range_by_dsut(self,
                                        start_time_ut,
                                        end_time_ut):
        """
        Obtain a list of trip_Is that take place during a time interval.
        The trip needs to be only partially overlapping with the given time interval.
        The grouping by dsut (day_start_ut) is required as same trip_I could
        take place on multiple days.

        Parameters
        ----------
        start_time_ut : int
            start of the time interval in unix time (seconds)
        end_time_ut: int
            end of the time interval in unix time (seconds)

        Returns
        -------
        trip_I_dict: dict
            keys: day_start_times to list of integers (trip_Is)
        """
        cur = self.conn.cursor()
        assert start_time_ut <= end_time_ut
        dst_ut, st_ds, et_ds = \
            self._get_possible_day_starts(start_time_ut, end_time_ut, 7)
        # noinspection PyTypeChecker
        assert len(dst_ut) >= 0
        trip_I_dict = {}
        for day_start_ut, start_ds, end_ds in \
                zip(dst_ut, st_ds, et_ds):
            query = """
                        SELECT distinct(trip_I)
                        FROM days
                            JOIN trips
                            USING(trip_I)
                        WHERE
                            (days.day_start_ut == ?)
                            AND (
                                    (trips.start_time_ds <= ?)
                                    AND
                                    (trips.end_time_ds >= ?)
                                )
                        """
            params = (day_start_ut, end_ds, start_ds)
            trip_Is = [el[0] for el in cur.execute(query, params)]
            if len(trip_Is) > 0:
                trip_I_dict[day_start_ut] = trip_Is
        return trip_I_dict

    def stops(self):
        """
        Get all stop data as a pandas DataFrame

        Returns
        -------
        df: pandas.DataFrame
        """
        return self.get_table("stops")

    def stop(self, stop_I):
        """
        Get all stop data as a pandas DataFrame for all stops, or an individual stop'

        Parameters
        ----------
        stop_I : int
            stop index

        Returns
        -------
        stop: pandas.DataFrame
        """
        return pd.read_sql_query("SELECT * FROM stops WHERE stop_I={stop_I}".format(stop_I=stop_I), self.conn)

    def add_coordinates_to_df(self, df, join_column='stop_I', lat_name="lat", lon_name="lon"):
        assert join_column in df.columns
        stops_df = self.stops()
        coord_df = stops_df[["stop_I", "lat", "lon"]]

        df_merged = pd.merge(coord_df, df, left_on='stop_I', right_on=join_column)

        df_merged.drop(["stop_I"], axis=1, inplace=True)
        df_merged3 = df_merged.rename(columns={"lat": lat_name, "lon": lon_name})
        return df_merged3

    def get_n_stops(self):
        return pd.read_sql_query("SELECT count(*) from stops;", self.conn).values[0, 0]

    def get_modes(self):
        modes = list(pd.read_sql_query("SELECT distinct(type) from routes;", self.conn).values.flatten())
        return modes

    def get_stops_for_route_type(self, route_type):
        """
        Parameters
        ----------
        route_type: int

        Returns
        -------
        stops: pandas.DataFrame

        """
        if route_type is WALK:
            return self.stops()
        else:
            return pd.read_sql_query("SELECT DISTINCT stops.* "
                                     "FROM stops JOIN stop_times ON stops.stop_I == stop_times.stop_I "
                                     "           JOIN trips ON stop_times.trip_I = trips.trip_I"
                                     "           JOIN routes ON trips.route_I == routes.route_I "
                                     "WHERE routes.type=(?)", self.conn, params=(route_type,))

    def get_stops_connected_to_stop(self):
        pass

    def generate_routable_transit_events(self, start_time_ut=None, end_time_ut=None, route_type=None):
        """
        Generates events that take place during a time interval [start_time_ut, end_time_ut].
        Each event needs to be only partially overlap the given time interval.
        Does not include walking events.
        This is just a quick and dirty implementation to get a way of quickly get a
        method for generating events compatible with the routing algorithm

        Parameters
        ----------
        start_time_ut: int
        end_time_ut: int
        route_type: ?

        Yields
        ------
        event: namedtuple
            containing:
                dep_time_ut: int
                arr_time_ut: int
                from_stop_I: int
                to_stop_I: int
                trip_I : int
                route_type : int
                seq: int
        """
        from gtfspy.networks import temporal_network
        df = temporal_network(self, start_time_ut=start_time_ut, end_time_ut=end_time_ut, route_type=route_type)
        df.sort_values("dep_time_ut", ascending=False, inplace=True)

        for row in df.itertuples():
            yield row

    def get_transit_events(self, start_time_ut=None, end_time_ut=None, route_type=None):
        """
        Obtain a list of events that take place during a time interval.
        Each event needs to be only partially overlap the given time interval.
        Does not include walking events.

        Parameters
        ----------
        start_time_ut : int
            start of the time interval in unix time (seconds)
        end_time_ut: int
            end of the time interval in unix time (seconds)
        route_type: int
            consider only events for this route_type

        Returns
        -------
        events: pandas.DataFrame
            with the following columns and types
                dep_time_ut: int
                arr_time_ut: int
                from_stop_I: int
                to_stop_I: int
                trip_I : int
                shape_id : int
                route_type : int

        See also
        --------
        get_transit_events_in_time_span : an older version of the same thing
        """
        table_name = self._get_day_trips_table_name()
        event_query = "SELECT stop_I, seq, trip_I, route_I, routes.route_id AS route_id, routes.type AS route_type, " \
                      "shape_id, day_start_ut+dep_time_ds AS dep_time_ut, day_start_ut+arr_time_ds AS arr_time_ut " \
                      "FROM " + table_name + " " \
                                             "JOIN trips USING(trip_I) " \
                                             "JOIN routes USING(route_I) " \
                                             "JOIN stop_times USING(trip_I)"

        where_clauses = []
        if end_time_ut:
            where_clauses.append(table_name + ".start_time_ut< {end_time_ut}".format(end_time_ut=end_time_ut))
            where_clauses.append("dep_time_ut  <={end_time_ut}".format(end_time_ut=end_time_ut))
        if start_time_ut:
            where_clauses.append(table_name + ".end_time_ut  > {start_time_ut}".format(start_time_ut=start_time_ut))
            where_clauses.append("arr_time_ut  >={start_time_ut}".format(start_time_ut=start_time_ut))
        if route_type is not None:
            assert route_type in ALL_ROUTE_TYPES
            where_clauses.append("routes.type={route_type}".format(route_type=route_type))
        if len(where_clauses) > 0:
            event_query += " WHERE "
            for i, where_clause in enumerate(where_clauses):
                if i is not 0:
                    event_query += " AND "
                event_query += where_clause
        # ordering is required for later stages
        event_query += " ORDER BY trip_I, day_start_ut+dep_time_ds;"
        events_result = pd.read_sql_query(event_query, self.conn)
        # 'filter' results so that only real "events" are taken into account
        from_indices = numpy.nonzero(
            (events_result['trip_I'][:-1].values == events_result['trip_I'][1:].values) *
            (events_result['seq'][:-1].values < events_result['seq'][1:].values)
        )[0]
        to_indices = from_indices + 1
        # these should have same trip_ids
        assert (events_result['trip_I'][from_indices].values == events_result['trip_I'][to_indices].values).all()
        trip_Is = events_result['trip_I'][from_indices]
        from_stops = events_result['stop_I'][from_indices]
        to_stops = events_result['stop_I'][to_indices]
        shape_ids = events_result['shape_id'][from_indices]
        dep_times = events_result['dep_time_ut'][from_indices]
        arr_times = events_result['arr_time_ut'][to_indices]
        route_types = events_result['route_type'][from_indices]
        route_ids = events_result['route_id'][from_indices]
        route_Is = events_result['route_I'][from_indices]
        durations = arr_times.values - dep_times.values
        assert (durations >= 0).all()
        from_seqs = events_result['seq'][from_indices]
        to_seqs = events_result['seq'][to_indices]
        data_tuples = zip(from_stops, to_stops, dep_times, arr_times,
                          shape_ids, route_types, route_ids, trip_Is,
                          durations, from_seqs, to_seqs, route_Is)
        columns = ["from_stop_I", "to_stop_I", "dep_time_ut", "arr_time_ut",
                   "shape_id", "route_type", "route_id", "trip_I",
                   "duration", "from_seq", "to_seq", "route_I"]
        df = pd.DataFrame.from_records(data_tuples, columns=columns)
        return df

    def get_route_difference_with_other_db(self, other_gtfs, start_time, end_time, uniqueness_threshold=None,
                                           uniqueness_ratio=None):
        """
        Compares the routes based on stops in the schedule with the routes in another db and returns the ones without match.
        Uniqueness thresholds or ratio can be used to allow small differences
        :param uniqueness_threshold:
        :param uniqueness_ratio:
        :return:
        """
        from gtfspy.stats import frequencies_by_generated_route

        this_df = frequencies_by_generated_route(self, start_time, end_time)
        other_df = frequencies_by_generated_route(other_gtfs, start_time, end_time)
        this_routes = {x: set(x.split(',')) for x in this_df["route"]}
        other_routes = {x: set(x.split(',')) for x in other_df["route"]}
        # this_df["route_set"] = this_df.apply(lambda x: set(x.route.split(',')), axis=1)
        # other_df["route_set"] = other_df.apply(lambda x: set(x.route.split(',')), axis=1)

        this_uniques = list(this_routes.keys())
        other_uniques = list(other_routes.keys())
        print("initial routes A:", len(this_uniques))
        print("initial routes B:", len(other_uniques))
        for i_key, i in this_routes.items():
            for j_key, j in other_routes.items():
                union = i | j
                intersection = i & j
                symmetric_difference = i ^ j
                if uniqueness_ratio:
                    if len(intersection) / len(union) >= uniqueness_ratio:
                        try:
                            this_uniques.remove(i_key)
                            this_df = this_df[this_df["route"] != i_key]
                        except ValueError:
                            pass
                        try:
                            other_uniques.remove(j_key)
                            other_df = other_df[other_df["route"] != j_key]
                        except ValueError:
                            pass

        print("unique routes A", len(this_df))
        print("unique routes B", len(other_df))
        return this_df, other_df

    def get_section_difference_with_other_db(self, other_conn, start_time, end_time):
        query = """SELECT from_stop_I, to_stop_I, sum(n_trips) AS n_trips, count(*) AS n_routes,
                    group_concat(route_id) AS all_routes FROM
                    (SELECT route_id, from_stop_I, to_stop_I, count(*) AS n_trips FROM
                    (SELECT stop_I AS from_stop_I, seq, trip_I FROM stop_times
                    WHERE dep_time_ds >= %s) t1,
                    (SELECT stop_I AS to_stop_I, seq, trip_I  FROM stop_times
                    WHERE arr_time_ds <= %s) t2,
                    trips,
                    routes
                    WHERE t1.seq +1 = t2.seq AND t1.trip_I = t2.trip_I
                    AND t1.trip_I = trips.trip_I AND trips.route_I = routes.route_I
                    GROUP BY from_stop_I, to_stop_I, routes.route_I
                    ORDER BY route_id) sq1
                    GROUP BY from_stop_I, to_stop_I""" % (start_time, end_time)

        prev_df = None
        result = pd.DataFrame
        for conn in [self.conn, other_conn]:
            df = conn.execute_custom_query_pandas(query)
            df.set_index(["from_stop_I", "to_stop_I"], inplace=True, drop=True)
            if prev_df is not None:
                result = prev_df.merge(df, how="outer", left_index=True, right_index=True, suffixes=["_old", "_new"])
                break

            prev_df = df
        for suffix in ["_new", "_old"]:
            result["all_routes" + suffix] = result["all_routes" + suffix].fillna(value="")
            result["all_routes" + suffix] = result["all_routes" + suffix].apply(lambda x: x.split(","))
        result.reset_index(inplace=True)
        result.fillna(value=0, inplace=True)
        for column in ["n_trips", "n_routes"]:
            result["diff_" + column] = result[column + "_new"] - result[column + "_old"]
        return result

    def get_straight_line_transfer_distances(self, stop_I=None):
        """
        Get (straight line) distances to stations that can be transferred to.

        Parameters
        ----------
        stop_I : int, optional
            If not specified return all possible transfer distances

        Returns
        -------
        distances: pandas.DataFrame
            each row has the following items
                from_stop_I: int
                to_stop_I: int
                d: float or int #distance in meters
        """
        if stop_I is not None:
            query = u""" SELECT from_stop_I, to_stop_I, d
                        FROM stop_distances
                            WHERE
                                from_stop_I=?
                    """
            params = (u"{stop_I}".format(stop_I=stop_I),)
        else:
            query = """ SELECT from_stop_I, to_stop_I, d
                        FROM stop_distances
                    """
            params = None
        stop_data_df = pd.read_sql_query(query, self.conn, params=params)
        return stop_data_df

    def update_stats(self, stats):
        self.meta.update(stats)
        self.meta['stats_calc_at_ut'] = time.time()

    def get_approximate_schedule_time_span_in_ut(self):
        """
        Return conservative estimates of start_time_ut and end_time_uts.
        All trips, events etc. should start after start_time_ut_conservative and end before end_time_ut_conservative

        Returns
        -------
        start_time_ut_conservative : int
        end_time_ut_conservative : int
        """
        first_day_start_ut, last_day_start_ut = self.get_day_start_ut_span()
        # 28 (instead of 24) comes from the GTFS standard
        return first_day_start_ut, last_day_start_ut + 28 * 3600

    def get_day_start_ut_span(self):
        """
        Return the first and last day_start_ut

        Returns
        -------
        first_day_start_ut: int
        last_day_start_ut: int
        """
        cur = self.conn.cursor()
        first_day_start_ut, last_day_start_ut = \
            cur.execute("SELECT min(day_start_ut), max(day_start_ut) FROM days;").fetchone()
        return first_day_start_ut, last_day_start_ut

    def get_min_date(self):
        cur = self.conn.cursor()
        return cur.execute("SELECT min(date) FROM days").fetchone()[0]

    def get_max_date(self):
        cur = self.conn.cursor()
        return cur.execute("SELECT max(date) FROM days").fetchone()[0]

    def print_validation_warnings(self):
        """
        See Validator.validate for more information.

        Returns
        -------
        warnings_container: validator.TimetableValidationWarningsContainer
        """
        from .timetable_validator import TimetableValidator
        validator = TimetableValidator(self)
        return validator.validate_and_get_warnings()

    def execute_custom_query(self, query):
        return self.conn.cursor().execute(query)

    def execute_custom_query_pandas(self, query):
        return pd.read_sql(query, self.conn)

    def get_stats(self):
        from gtfspy import stats
        return stats.get_stats(self)

    def _get_day_trips_table_name(self):
        cur = self.conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='day_trips2'")
        if len(cur.fetchall()) > 0:
            table_name = "day_trips2"
        else:
            table_name = "day_trips"
        return table_name

    # TODO: The following methods could be moved to a "edit gtfs" -module
    def homogenize_stops_table_with_other_db(self, source):
        """
        This function takes an external database, looks of common stops and adds the missing stops to both databases.
        In addition the stop_pair_I column is added. This id links the stops between these two sources.
        :param source: directory of external database
        :return:
        """
        cur = self.conn.cursor()
        self.attach_gtfs_database(source)

        query_inner_join = """SELECT t1.*
                              FROM stops t1
                              INNER JOIN other.stops t2
                              ON t1.stop_id=t2.stop_id
                              AND find_distance(t1.lon, t1.lat, t2.lon, t2.lat) <= 50"""
        df_inner_join = self.execute_custom_query_pandas(query_inner_join)
        print("number of common stops: ", len(df_inner_join.index))
        df_not_in_other = self.execute_custom_query_pandas("SELECT * FROM stops EXCEPT " + query_inner_join)
        print("number of stops missing in second feed: ", len(df_not_in_other.index))
        df_not_in_self = self.execute_custom_query_pandas("SELECT * FROM other.stops EXCEPT " +
                                                          query_inner_join.replace("t1.*", "t2.*"))
        print("number of stops missing in first feed: ", len(df_not_in_self.index))
        try:
            self.execute_custom_query("""ALTER TABLE stops ADD COLUMN stop_pair_I INT """)

            self.execute_custom_query("""ALTER TABLE other.stops ADD COLUMN stop_pair_I INT """)
        except sqlite3.OperationalError:
            pass
        stop_id_stub = "added_stop_"
        counter = 0
        rows_to_update_self = []
        rows_to_update_other = []
        rows_to_add_to_self = []
        rows_to_add_to_other = []

        for items in df_inner_join.itertuples(index=False):
            rows_to_update_self.append((counter, items[1]))
            rows_to_update_other.append((counter, items[1]))
            counter += 1

        for items in df_not_in_other.itertuples(index=False):
            rows_to_update_self.append((counter, items[1]))
            rows_to_add_to_other.append((stop_id_stub + str(counter),) + tuple(items[x] for x in [2, 3, 4, 5, 6, 8, 9])
                                        + (counter,))
            counter += 1

        for items in df_not_in_self.itertuples(index=False):
            rows_to_update_other.append((counter, items[1]))
            rows_to_add_to_self.append((stop_id_stub + str(counter),) + tuple(items[x] for x in [2, 3, 4, 5, 6, 8, 9])
                                       + (counter,))
            counter += 1

        query_add_row = """INSERT INTO stops(
                                    stop_id,
                                    code,
                                    name,
                                    desc,
                                    lat,
                                    lon,
                                    location_type,
                                    wheelchair_boarding,
                                    stop_pair_I) VALUES (%s) """ % (", ".join(["?" for x in range(9)]))

        query_update_row = """UPDATE stops SET stop_pair_I=? WHERE stop_id=?"""
        print("adding rows to databases")
        cur.executemany(query_add_row, rows_to_add_to_self)
        cur.executemany(query_update_row, rows_to_update_self)
        cur.executemany(query_add_row.replace("stops", "other.stops"), rows_to_add_to_other)
        cur.executemany(query_update_row.replace("stops", "other.stops"), rows_to_update_other)
        self.conn.commit()
        print("finished")

    def replace_stop_i_with_stop_pair_i(self):
        cur = self.conn.cursor()
        queries = [
            "UPDATE stop_times SET stop_I = "
            "(SELECT stops.stop_pair_I AS stop_I FROM stops WHERE stops.stop_I = stop_times.stop_I)",
            # Replace stop_distances
            "ALTER TABLE stop_distances RENAME TO stop_distances_old",

            "CREATE TABLE stop_distances (from_stop_I INT, to_stop_I INT, d INT, d_walk INT, min_transfer_time INT, "
            "timed_transfer INT, UNIQUE (from_stop_I, to_stop_I))",

            "INSERT INTO stop_distances(from_stop_I, to_stop_I, d, d_walk, min_transfer_time, timed_transfer) "
            "SELECT f_stop.stop_pair_I AS from_stop_I, t_stop.stop_pair_I AS to_stop_I, d, d_walk, min_transfer_time, "
            "timed_transfer "
            "FROM "
            "(SELECT from_stop_I, to_stop_I, d, d_walk, min_transfer_time, "
            "timed_transfer "
            "FROM stop_distances_old) sd_o "
            "LEFT JOIN "
            "(SELECT stop_I, stop_pair_I FROM stops) f_stop "
            "ON sd_o.from_stop_I = f_stop.stop_I "
            " JOIN "
            "(SELECT stop_I, stop_pair_I FROM stops) t_stop "
            "ON sd_o.to_stop_I = t_stop.stop_I ;",

            "DROP TABLE stop_distances_old",

            # Replace stops table with other
            "ALTER TABLE stops RENAME TO stops_old",

            "CREATE TABLE stops (stop_I INTEGER PRIMARY KEY, stop_id TEXT UNIQUE NOT NULL, code TEXT, name TEXT, "
            "desc TEXT, lat REAL, lon REAL, parent_I INT, location_type INT, wheelchair_boarding BOOL, "
            "self_or_parent_I INT, old_stop_I INT)",

            "INSERT INTO stops(stop_I, stop_id, code, name, desc, lat, lon, parent_I, location_type, "
            "wheelchair_boarding, self_or_parent_I, old_stop_I) "
            "SELECT stop_pair_I AS stop_I, stop_id, code, name, desc, lat, lon, parent_I, location_type, "
            "wheelchair_boarding, self_or_parent_I, stop_I AS old_stop_I "
            "FROM stops_old;",

            "DROP TABLE stops_old",

            "CREATE INDEX idx_stops_sid ON stops (stop_I)"]
        for query in queries:
            cur.execute(query)
        self.conn.commit()

    def regenerate_parent_stop_I(self):
        raise NotImplementedError
        # get max stop_I
        cur = self.conn.cursor()

        query = "SELECT stop_I FROM stops ORDER BY stop_I DESC LIMIT 1"
        max_stop_I = cur.execute(query).fetchall()[0]

        query_update_row = """UPDATE stops SET parent_I=? WHERE parent_I=?"""

    def add_stops_from_csv(self, csv_dir):
        stops_to_add = pd.read_csv(csv_dir, encoding='utf-8')
        assert all([x in stops_to_add.columns for x in ["stop_id", "code", "name", "desc", "lat", "lon"]])
        for s in stops_to_add.itertuples():
            self.add_stop(s.stop_id, s.code, s.name, s.desc, s.lat, s.lon)

    def add_stop(self, stop_id, code, name, desc, lat, lon):
        cur = self.conn.cursor()
        query_add_row = 'INSERT INTO stops( stop_id, code, name, desc, lat, lon) ' \
                        'VALUES (?, ?, ?, ?, ?, ?)'
        cur.executemany(query_add_row, [[stop_id, code, name, desc, lat, lon]])
        self.conn.commit()

    def recalculate_stop_distances(self, max_distance):
        from gtfspy.calc_transfers import calc_transfers
        calc_transfers(self.conn, max_distance)

    def attach_gtfs_database(self, gtfs_dir):
        cur = self.conn.cursor()
        cur.execute("ATTACH '%s' AS 'other'" % str(gtfs_dir))
        cur.execute("PRAGMA database_list")
        print("GTFS database attached:", cur.fetchall())

    def update_stop_coordinates(self, stop_updates):
        """

        :param stop_updates: DataFrame
        :return:
        """
        cur = self.conn.cursor()

        stop_values = [(values.lat, values.lon, values.stop_id) for values in stop_updates.itertuples()]
        cur.executemany("""UPDATE stops SET lat = ?, lon = ? WHERE stop_id = ?""", stop_values)
        self.conn.commit()
from gtfspy.import_loaders import AgencyLoader, CalendarDatesLoader, CalendarLoader, DayLoader, \
    DayTripsMaterializer, FeedInfoLoader, FrequenciesLoader, TripLoader, MetadataLoader, RouteLoader, \
    ShapeLoader, StopDistancesLoader, StopLoader, StopTimesLoader, TransfersLoader
from gtfspy.import_loaders.table_loader import ignore_tables, decode_six
from six import string_types
from gtfspy import stats
from gtfspy import util
from gtfspy.gtfs import GTFS
    from gtfspy import gtfs as mod_gtfs
import re
import sqlite3
import time
    import argparse
            # Extract GTFS date.  Last date pattern in filename.
            filename_date_list = re.findall(r'\d{4}-\d{2}-\d{2}', source)
            if filename_date_list:
                date_str = filename_date_list[-1]
                G.meta[prefix + 'download_date'] = date_str
                download_date_strs.append(date_str)
            if location_name:
                G.meta['location_name'] = location_name
            else:
                location_name_list = re.findall(r'/([^/]+)/\d{4}-\d{2}-\d{2}', source)
                if location_name_list:
                    G.meta[prefix + 'location_name'] = location_name_list[-1]
                else:
                    try:
                        G.meta[prefix + 'location_name'] = source.split("/")[-4]
                    except:
                        G.meta[prefix + 'location_name'] = source

    if G.meta['download_date'] == "":
        unique_download_dates = list(set(download_date_strs))
        if len(unique_download_dates) == 1:
            G.meta['download_date'] = unique_download_dates[0]

    G.meta['timezone'] = cur.execute('SELECT timezone FROM agencies LIMIT 1').fetchone()[0]
    stats.update_stats(G)
    del G

    if print_progress:
        print("Vacuuming...")
    # Next 3 lines are python 3.6 work-arounds again.
    conn.isolation_level = None  # former default of autocommit mode
    cur.execute('VACUUM;')
    conn.isolation_level = ''    # back to python default
    # end python3.6 workaround
    if print_progress:
        print("Analyzing...")
    cur.execute('ANALYZE')
    if not (preserve_connection is True):
        conn.close()

def validate_day_start_ut(conn):
    """This validates the day_start_ut of the days table."""
    G = GTFS(conn)
    cur = conn.execute('SELECT date, day_start_ut FROM days')
    for date, day_start_ut in cur:
        #print date, day_start_ut
        assert day_start_ut == G.get_day_start_ut(date)


def main_make_views(gtfs_fname):
    """Re-create all views.
    """
    print("creating views")
    conn = GTFS(fname_or_conn=gtfs_fname).conn
    for L in Loaders:
        L(None).make_views(conn)
    conn.commit()

def main():

    parser = argparse.ArgumentParser(description="""
    Import GTFS files.  Imports gtfs.  There are two subcommands.  The
    'import' subcommand converts from a GTFS directory or zipfile to a
    sqlite database.  Both must be specified on the command line.  The
    import-auto subcommand is older, and uses the logic in code/db.py to
    automatically find databases and output files (in scratch/gtfs and
    scratch/db) based on the shortname given on the command line.  This
    should probably not be used much anymore, instead automate that before
    calling this program.""")
    subparsers = parser.add_subparsers(dest='cmd')
    # parsing import
    parser_import = subparsers.add_parser('import', help="Direct import GTFS->sqlite")
    parser_import.add_argument('gtfs', help='Input GTFS filename (dir or .zip)')
    parser_import.add_argument('output', help='Output .sqlite filename (must end in .sqlite)')
    parser.add_argument('--fast', action='store_true',
                        help='Skip stop_times and shapes tables.')

    # parsing import-auto
    parser_importauto = subparsers.add_parser('import-auto', help="Automatic GTFS import from files")
    parser_importauto.add_argument('gtfsname', help='Input GTFS filename')

    # parsing import-multiple
    parser_import_multiple = subparsers.add_parser('import-multiple', help="GTFS import from multiple zip-files")
    parser_import_multiple.add_argument('zipfiles', metavar='zipfiles', type=str, nargs=argparse.ONE_OR_MORE,
                                        help='zipfiles for the import')
    parser_import_multiple.add_argument('output', help='Output .sqlite filename (must end in .sqlite)')

    # parsing import-list

    # Parsing copy
    parser_copy = subparsers.add_parser('copy', help="Copy database")
    parser_copy.add_argument('source', help='Input GTFS .sqlite')
    parser_copy.add_argument('dest', help='Output GTFS .sqlite')
    parser_copy.add_argument('--start', help='Start copy time (inclusive)')
    parser_copy.add_argument('--end', help='Start copy time (exclusive)')

    # Parsing copy
    parser_copy = subparsers.add_parser('make-views', help="Re-create views")
    parser_copy.add_argument('gtfs', help='Input GTFS .sqlite')

    # make-weekly-download
    parser_copy = subparsers.add_parser('make-weekly')
    parser_copy.add_argument('source', help='Input GTFS .sqlite')
    parser_copy.add_argument('dest', help='Output GTFS .sqlite')

    parser_copy = subparsers.add_parser('make-daily')
    parser_copy.add_argument('source', help='Input GTFS .sqlite')
    parser_copy.add_argument('dest', help='Output GTFS .sqlite')

    # Export stop distances
    parser_copy = subparsers.add_parser('export-stop-distances')
    parser_copy.add_argument('gtfs', help='Input GTFS .sqlite')
    parser_copy.add_argument('output', help='Output for .txt file')

    # Custom stuff
    parser_copy = subparsers.add_parser('custom')
    parser_copy.add_argument('gtfs', help='Input GTFS .sqlite')

    args = parser.parse_args()
    if args.fast:

        ignore_tables.update(('stop_times', 'shapes'))