Tuesday, July 22, 2014

Etherape as root

This has popped up as an occasional question in the forums.
If you monitor network activity using Etherape, it's worthless as a user level application. It must be run as root. But running as root requires opening a terminal and entering a password.

Here's how to change the settings in Ubuntu 14.04 to create an Etherape (as root) launcher in Unity that won't require a password. It has only been tested with 14.04. Unity development moves fast - this method may not work for a different version of Ubuntu.

It requires a minor edit to the /etc/sudoers file, and minor edits to two .desktop files. It's easy to undo if you change your mind.

1) I like to document my changes. I have three changes to keep track of, in case I change my mind. So I'm adding a new subdirectory to keep track of this change.

mkdir my_customizations/etherape_launcher

2) Edit the sudoers file. This is the dangerous part. Pay close attention.

You need to know your login account name (fred, Jane, dog, etc.)
You need to know your hostname as shown in /etc/hostname.

$ sudo visudo

At the bottom of the file, add one line. Spacing, spelling, and CAPS are important.

account_name hostname = NOPASSWD: /usr/bin/etherape

Use your account_name and hostname, of course.

Do a CTRL+X to exit, and hit Y to confirm the exit.
Now look carefully at the screen.

If visudo is happy with your changes, it will print no output, but simply return you to a command prompt.

If the screen shows an error, type 'edit' and go right back in to fix the error. If you don't know how to fix the error, delete your added line and save. You MUST NOT exit with a broken sudoers file - a broken sudoers file is 100% guaranteed to break your system! It's very important that you pay close attention to any error messages visudo prints when you try to exit.

3) Test: Open a NEW terminal window, and try 'sudo etherape'.
It should no longer ask for a password.

If you get sudo errors instead, you have broken your system! Stop! Save all your data! Reboot into a recovery console (since you cannot use sudo) and edit /etc/sudoers to undo your changes. Go to an Ubuntu help channel and ask for help on how to do this!

4) Now that we know the sudo change works, move the sudoers line to a separate file

We used visudo to ensure the correct syntax and a working sudoers file. But it's not a great idea to leave undocumented system changes laying around in system-installed (and system-rewritable) files. So let's move the customization to a separate file so we can keep track of it.

$ sudo nano /etc/sudoers.d/etherape

File contents:

# Allows 'sudo etherape' to be run, including from a launcher, wthout password

my_account my_hostname = NOPASSWD: /usr/bin/etherape

Remember to change my_account and my_hostname to the proper values!

Test your change using 'sudo etherape'. It should work without a password, and without an error message.

Go back into visudo and delete the etherape line at the bottom.

Test your change once more using 'sudo etherape'. It should work without a password, and without an error message.

Now /etc/sudoers is back to it's original state, and our change is safely in a separate file. If we want to delete the change, we simply delete /etc/sudoers.d/etherape.

5) Let's link this new sudoers file to our documentation so we can find it again.

$ sudo ln /etc/sudoers.d/etherape \

6) Edit the etherape-root .desktop file

$ sudo nano /usr/share/applications/etherape-root.desktop

File contents:

[Desktop Entry]
Name=EtherApe (as root)
Comment=Graphical Network Monitor
Comment[es]=Monitor Gráfico de Red
#TryExec=su-to-root                            <-- Comment out
#Exec=su-to-root -X -c /usr/bin/etherape        <-- Comment out
Exec=/usr/bin/sudo /usr/bin/etherape -i wlan0     <-- Add line

7) Edit the etherape (user) .desktop file to hide it. We don't want to delete it; the package manager will expect it to be there.

$ sudo nano /usr/share/applications/etherape.desktop

#[Desktop Entry]           <-- Comment out
Comment=Graphical Network Monitor
Comment[es]=Monitor Gráfico de Red

8) Let's save links to the changed desktop files so I can find them again someday:

$ sudo ln /usr/share/applications/etherape-root.desktop \
$ sudo ln /usr/share/applications/etherape.desktop \

9) Test the new .desktop file.

Open Unity's Dash.
Close any existing search.
Open a new search for Etherape. There should be only one option now: Etherape (as root).
Open it. It should show all interfaces without requiring a password or prompting an error.
Lock it to the Launcher (right-click).
Close etherape. The launcher icon should remain.
Open it again from the launcher. It should work

How to recover from a broken sudo

You cannot fix a broken sudoers file while logged in. You would need access to sudo for that...and it's broken. You can't get to the root account because it's locked in normal use.

So you need to reboot into a condition where root is unlocked and available.

1) Save all your work and then reboot.

2) Hold down the left SHIFT button to get to the GRUB prompt.
If you get a pretty splash screen, reboot and hold down the button earlier.

3) At the Grub prompt, select Advanced Options

4) At the Advanced Options prompt, select any Recovery Console

5) Watch the boot technobabble run across your screen.

6) At the Recovery Console prompt, select Drop To A Root Shell

7) You should see a warning about a read-only filesystem.
The prompt should look like '#'

8) Remount the root filesystem as read-write instead of read-only. We want to make changes that stick.

# mount -o remount /

9) Try a test write. It should not cause an error:

# date > /tmp/test

10) Okay, now go into visudo and fix your mistake.

11) When complete, reboot into normal Ubuntu.

# shutdown -r now

Tuesday, July 8, 2014

Simple geolocation in Ubuntu 14.04

Geolocation means 'figuring out where a spot on the Earth is'.

Usually, it's the even more limited question 'where am I?'


The default install of Ubuntu includes GeoClue, a dbus service that checks IP address and GPS data. Since 2012, when I last looked at GeoClue, it's changed a bit, and it has more backends available in the Ubuntu Repositories.



Some commenters on the interwebs have claimed that GeoClue is privacy-intrusive. It's not. It merely tries to figure out your location, which can be handy for various services on your system. It doesn't share or send your location to anybody else.

dbus introspection and d-feet

You would expect that a dbus application like GeoClue would be visible using a dbus introspection tool like d-feet (provided by the d-feet package).

But there's a small twist: D-feet can only see dbus applications that are running. It can only see dbus applications that active, or are inactive daemons.

It's possible (and indeed preferable in many circumstances) to write a dbus application that is not a daemon - it starts at first connection, terminates when complete, and restarts at the next connection. D-feet cannot see these when they are not running.

Back in 2012, GeoClue was an always-on daemon, and always visible to d-feet.
But in 2014 GeoClue is (properly) no longer a daemon, and d-feet won't see GeoClue if it's not active.

This simply means we must trigger a connection to GeoClue to make it visible.
Below are two ways to do so: The geoclue-test-gui application, and a Python3 example.


One easy way to see GeoClue in action, and to make it visible to d-feet, is to use the geoclue-test-gui application (included in the geoclue-examples package)

$ sudo apt-get install geoclue-examples
$ geoclue-test-gui

GeoClue Python3 example

Once GeoClue is visible in d-feet (look in the 'session' tab), you can see the interfaces and try them out.

Here's an example of the GetAddress() and GetLocation() methods using Python3:

>>> import dbus

>>> dest           = "org.freedesktop.Geoclue.Master"
>>> path           = "/org/freedesktop/Geoclue/Master/client0"
>>> addr_interface = "org.freedesktop.Geoclue.Address"
>>> posn_interface = "org.freedesktop.Geoclue.Position"

>>> bus        = dbus.SessionBus()
>>> obj        = bus.get_object(dest, path)
>>> addr_iface = dbus.Interface(obj, addr_interface)
>>> posn_iface = dbus.Interface(obj, posn_interface)

>>> addr_iface.GetAddress()
(dbus.Int32(1404823176),          # Timestamp
     dbus.String('locality')   : dbus.String('Milwaukee'),
     dbus.String('country')    : dbus.String('United States'),
     dbus.String('countrycode'): dbus.String('US'),
     dbus.String('region')     : dbus.String('Wisconsin'), 
     dbus.String('timezone')   : dbus.String('America/Chicago')}, 
 dbus.Struct(                 # Accuracy

>>> posn_iface.GetPosition()
(dbus.Int32(3),               # Num of fields
 dbus.Int32(1404823176),      # Timestamp
 dbus.Double(43.0389),        # Latitude
 dbus.Double(-87.9065),       # Longitude
 dbus.Double(0.0),            # Altitude
 dbus.Struct((dbus.Int32(3),  # Accuracy

>>> addr_dict = addr_iface.GetAddress()[1]
>>> str(addr_dict['locality'])

>>> posn_iface.GetPosition()[2]
>>> posn_iface.GetPosition()[3]
>>> lat = float(posn_iface.GetPosition()[2])
>>> lon = float(posn_iface.GetPosition()[3])
>>> lat,lon
(43.0389, -87.9065)

Note: Geoclue's accuracy codes

Ubuntu GeoIP Service

When you run geoclue-test-gui, you discover that only one backend service is installed with the default install of Ubuntu - the Ubuntu GeoIP service.

The Ubuntu GeoIP service is provided by the geoclue-ubuntu-geoip package, and is included with the default install of Ubuntu 14.04. It simply queries an ubuntu.com server, and parses the XML response.

You can do it yourself, too:

$ wget -q -O - http://geoip.ubuntu.com/lookup

<?xml version="1.0" encoding="UTF-8"?>
  <CountryName>United States</CountryName>


The default install of Ubuntu 14.04 also includes (the confusingly-named) GeoIP. While it has the prefix 'Geo', it's not a geolocator. It's completely unrelated to the Ubuntu GeoIP service. Instead, GeoIP is a database the IP addresses assigned to each country, provided by the geoip-database package. Knowing the country of origin of a packet or server or connection can be handy.

geoip-database has many bindings, including Python 2.7 (but sadly not Python 3). Easiest is the command line, provided by the additional geoip-bin package.

$ sudo apt-get install geoip-bin
$ geoiplookup
GeoIP Country Edition: US, United States


Back in 2012, I compared the two methods of geolocation in Ubuntu: GeoClue and GeocodeGlib. GeocodeGlib was originally intended as a smaller, easier to maintain replacement for GeoClue. But as we have already seen, GeoClue has thrived instead of withering. The only two packages that seem to require GeocodeGlib in 14.04 are gnome-core-devel and gnome-clocks
GeocodeGlib, provided by the libgeocode-glib0 package, is no longer included with a default Ubuntu installation anymore, but it is easily available in the Software Center.

sudo apt-get install gir1.2-geocodeglib-1.0

That is the GTK introspection package for geocodeglib, and it pulls in libgeocode-glib0 as a dependency. The introspection package is necessary.

Useful documentation and code examples are non-existent. My python code sample from 2012 no longer works. It's easy to create a GeocodeGlib.Place() object, and to assign various values to it (town name, postal code, state), but I can't figure out how to get GeocoddeGlib to automatically determine and fill in other properties. So even though it seems maintained, I'm not recommending it as a useful geolocation service.

Friday, June 13, 2014

Intermediate GTFS: How to prevent stop_times.txt from hogging all your RAM

In a GTFS file for a large provider (example: Chicago), the stop_times.txt file can be huge.

Here's an example:

$ unzip -v 20140611.cta.gtfs

 Archive:  20140611.cta.gtfs
  Length   Method    Size  Cmpr    Date    Time   CRC-32   Name
 --------  ------  ------- ---- ---------- ----- --------  ----
      235  Defl:N      153  35% 2014-06-10 22:49 6dfdcbd7  agency.txt
     4889  Defl:N      554  89% 2014-06-10 22:49 38544274  calendar.txt
     2139  Defl:N      335  84% 2014-06-10 22:49 f69f2cb4  calendar_dates.txt
    13000  Defl:N     2279  83% 2014-06-10 22:49 59bf453e  routes.txt
 29896363  Defl:N  7158190  76% 2014-06-10 22:54 21c7d003  shapes.txt
  1332298  Defl:N   264714  80% 2014-06-10 23:00 48a736df  stops.txt
355900629  Defl:N 57437701  84% 2014-06-10 23:24 e13831ff  stop_times.txt
     2514  Defl:N      575  77% 2014-06-10 23:24 51dca8cf  transfers.txt
  5654714  Defl:N   656156  88% 2014-06-10 23:25 7056aa15  trips.txt
       42  Defl:N       39   7% 2014-06-10 22:49 87676593  frequencies.txt
    18959  Defl:N     6180  67% 2011-06-20 11:07 bcf138d1  developers_license_agreement.htm
 --------          -------  ---                            -------
392825782         65526876  83%                            11 files

Uncompressed, the stop_times.txt file is 356 MB, 91% of the entire archive.

You can see right away that loading that behemoth into memory will cause a pause (30 seconds, on my hardware) that a user will probably notice. And the way Python dicts use memory, 356 MB of CSV data can easily explode into 3 or 4 GB of a comparable python dict.  And that leaves us with a problem: We can't load the file on-demand because it takes too darn long, we can't keep it stored in an easily-accessible form in memory because it's too darn big. While we could keep it in CSV format in RAM, that's not a fomat we can do anything with - every query would need to iterate through the entire file, and that's sort of the worst of both worlds.

So what can we do?

First, we can filter data immediately upon load, and discard big chunks of it right away. This means the structure of the program must change from:

cal = load_table(calendar.txt)
rou = load_table(routes.txt)
tri = load_table(trips.txt)
sto = load_table(stops.txt)
tim = load_table(stop_times.txt)
svcid = valid_service_ids_for_today(cal)
trips = trips_that_stop_there_today(tri, svc_id, tim)

to something more like:

cal = load_table(calendar.txt)
rou = load_table(routes.txt)
tri = load_table(trips.txt)
sto = load_table(stops.txt)
tim = load_and_filter_stop_times_table(stop_times.txt, filter_criteria)
svcid = valid_service_ids_for_today(cal)
trips = trips_that_stop_there_today(tri, svc_id, tim)

Instead of loading the entire table and holding it in memory, a customized loader filters the data right away, and chuck out the 90% we don't actually want.

Second, we can use Python's read size parameter to prevent a spike in RAM usage (and consequent slowdown of the entire system).

For example, normally you read data with something like:

with gtfs_file.open('stop_times.txt', mode='r') as infile:
    input = infile.read()
output = do_stuff_to(input.decode())
return output

And we can rearrange it to something like:

eof    = False
output = {}
with gtfs_file.open('stop_times.txt', mode='r') as infile:
    while eof == False:
        input_block  = infile.readlines(4096)
        if len(input_block) == 0:
            eof = True

        output_block = do_stuff_to(input_block)

return output

The EOF flag controls the process, and an empty read triggers the flag.
The input RAM requirements are tiny, since each block re-uses the same memory over and over. Only the filtered output grows.

The performance penalty of this kind of loading seems about 30% (10 seconds) longer than a single read(), but now the process can run happily in the background without slowing other processes. Filtering the huge file down to a manageable size and searchable format also make subsequent use of the data _much_ faster.

Working examples

Here's working sample code of a generic load_table(), which I called map_gtfs_table_to_dict()

Here's working sample code of a load_stop_times(), which I called map_stop_times_to_dict()

Let's use the generic sample code to load trips.txt from a CTA GTFS file:

>>> import map_gtfs_table_to_dict as load_table
>>> import zipfile
>>> # You must provide your own GTFS file, of course!
>>> gtfs_path = "/home/ian/gtfs/20140611.cta.gtfs"
>>> gtfs_file = zipfile.ZipFile(gtfs_path, mode='r')
>>> all_trips = load_table(gtfs_file, 'trips.txt')
>>> len(all_trips)
>>> for trip_id in list(all_trips)[0:5]:
...      print(trip_id, all_trips[trip_id])
45072053995 {'route_id': 'Y', 'direction_id': '0', 'wheelchair_accessible': '1',
'service_id': '104509', 'direction': '0', 'shape_id': '304500033',
'block_id': '45007747580', 'schd_trip_id': 'R501'}
437076941189 {'route_id': '147', 'direction_id': '1', 'wheelchair_accessible': '1',
'service_id': '43701', 'direction': 'North', 'shape_id': '4374519',
'block_id': '437008159276', 'schd_trip_id': '76941189'}
437076941185 {'route_id': '97', 'direction_id': '1', 'wheelchair_accessible': '1',
'service_id': '43701', 'direction': 'East', 'shape_id': '4374369',
'block_id': '437008158984', 'schd_trip_id': '76941185'}
437076941184 {'route_id': '97', 'direction_id': '1', 'wheelchair_accessible': '1',
'service_id': '43701', 'direction': 'East', 'shape_id': '4374369',
'block_id': '437008159008', 'schd_trip_id': '76941184'}
437076941186 {'route_id': '97', 'direction_id': '1', 'wheelchair_accessible': '1',
'service_id': '43701', 'direction': 'East', 'shape_id': '4374369',
'block_id': '437008158986', 'schd_trip_id': '76941186'}

There we are, all 90,000 trips in a few seconds. Each line of the GTFS table broken into a dict for easy access.

Now, let's try using the same generic load function to load stop_times.txt

>>> import map_gtfs_table_to_dict as load_table
>>> import zipfile
>>> # You must provide your own GTFS file, of course!
>>> gtfs_path = "/home/ian/gtfs/20140611.cta.gtfs"
>>> gtfs_file = zipfile.ZipFile(gtfs_path, mode='r')
>>> all_stops = load_table(gtfs_file, 'stop_times.txt')
Traceback (most recent call last):
  File "", line 1, in 
  File "./test.py", line 56, in map_gtfs_table_to_dict
    line_dict[column] = line.split(',')[columns[column]].strip('" ')

It took a few minutes to consume all that memory and fail, and everything else on the system slowed to a crawl.

Finally, let's use the custom load_stop_table() to load-and-filter stop_times.txt without reducing the rest of the system to a memory-starved crawl:

>>> import map_stop_times_to_dict as load_table
>>> import zipfile
>>> gtfs_path = "/home/ian/gtfs/20140611.cta.gtfs"
>>> gtfs_file = zipfile.ZipFile(gtfs_path, mode='r')
>>> stop_list = ['3661', '3698', '15026', '17433']
>>> filtered_stops = load_table(gtfs_file, stop_list)
>>> for line in list(filtered_stops)[0:5]:
...     print(line, filtered_stops[line])
4521987 {'stop_headsign': '79th Red Line', 'departure_time': '13:53:57',
'shape_dist_traveled': '22219', 'arrival_time': '13:53:57',
'pickup_type': '0', 'stop_id': '3661', 'stop_sequence': '35',
'trip_id': '437077075442'}
4538374 {'stop_headsign': '79th Red Line', 'departure_time': '15:04:01',
'shape_dist_traveled': '37847', 'arrival_time': '15:04:01',
'pickup_type': '0', 'stop_id': '3680', 'stop_sequence': '58',
'trip_id': '437077077228'}
4325384 {'stop_headsign': '127th/Lowe', 'departure_time': '17:26:00',
'shape_dist_traveled': '27644', 'arrival_time': '17:26:00',
'pickup_type': '0', 'stop_id': '5992', 'stop_sequence': '42',
'trip_id': '437077000628'}
475149 {'stop_headsign': '95th Red Line', 'departure_time': '18:01:18',
'shape_dist_traveled': '14028', 'arrival_time': '18:01:18',
'pickup_type': '0', 'stop_id': '15026', 'stop_sequence': '20',
'trip_id': '436073915060'}
4112403 {'stop_headsign': '95th Red Line', 'departure_time': '22:08:53',
'shape_dist_traveled': '29370', 'arrival_time': '22:08:53',
'pickup_type': '0', 'stop_id': '15026', 'stop_sequence': '42',
'trip_id': '437076995010'}

It's still slow to load the entire file. On my equipment, I can load stop_times.txt in about 30 seconds this way. Varying the block size has some benefit, but you can play with that yourself.

In the final example, the 'line' variable -- the key to that subdict of one trip serving a stop -- is just a counter, the (approximate) CSV row number. stop_times.txt has no unique item on each line that can be used as a dict key.

Thursday, May 15, 2014

ssmtp to email cron output

Cron output in Ubuntu gets discarded. The default install of Ubuntu does not install a Mail Transport Agent (MTA), so cron cannot mail you the output.

You can install an MTA -several are easily available in the Ubuntu repositories- but configuration is not trivial. MTAs date from before the IMAP- and SMTP-based e-mail systems we use today, which makes them highly flexible and configurable...but not necessarily the easiest solution.

ssmtp is a limited-purpose MTA intended to replace the 'mail' command and send all output to an SMTP mailserver. ssmtp is provided by the 'ssmtp' package.


sudo apt-get install ssmtp

Configuration file #1: /etc/ssmtp/ssmpt.conf

This tells ssmtp how to send email to my account.
It's not the best idea to store your e-mail password in world-readable plain text. See how to protect the password properly.

# Config file for sSMTP sendmail
# The person who gets all mail for userids < 1000
# Make this empty to disable rewriting.

# The place where the mail goes. The actual machine name is required no
# MX records are consulted. Commonly mailhosts are named mail.domain.com

# Where will the mail seem to come from?

# The full hostname

# Are users allowed to set their own From: address?
# YES - Allow the user to specify their own From: address
# NO - Use the system generated From: address

Configuration File #2: /etc/ssmtp/

This directs root mail to my account.

# sSMTP aliases
# Format:       local_account:outgoing_address:mailhub
# Example: root:your_login@your.domain:mailhub.your.domain[:port]
# where [:port] is an optional port number that defaults to 25.

And now cron output lands in my normal inbox.

Tuesday, April 1, 2014

tkinter clock

I did some playing around with tkinter after seeing this post.

Here is my solution: How to easily display a running clock (text, not graphical) using tkinter.

The Clock Class

A running clock requires two functions: One to create the tkinter widget, and one to continuously update the widget with the current time.

If we house these in a class, then creating a clock (or multiple clocks) gets much easier. All the complexity is hidden from the mainloop and the rest of your script.

A newly created Clock widget will *automatically* update itself. Both functions call tick(). Yeah, tick() calls itself upon completion. Cool little trick tkinter offers for precisely uses like this.

Here's an example class:

import tkinter
import time

class Clock():
    """ Class that contains the clock widget and clock refresh """

    def __init__(self, parent):
        Create the clock widget
        It's an ordinary Label element
        self.time = time.strftime('%H:%M:%S')
        self.widget = tkinter.Label(parent, text=self.time)
        self.widget.after(200, self.tick)      # Wait 200 ms, then run tick()

    def tick(self):
        """ Update the display clock """
        new_time = time.strftime('%H:%M:%S')
        if new_time != self.time:
            self.time = new_time
        self.widget.after(200, self.tick)      # 200 =  millisecond delay
                                               #        before running tick() again 

And you implement it rather like this:

if __name__ == "__main__":
    Create a tkinter window and populate it with elements
    One of those elements merely happens to include the clock.

    # Generic window elements

    window = tkinter.Tk()
    frame  = tkinter.Frame(window, width=400, height=400 )

    # Add the frame elements, including the clock

    label1 = tkinter.Label(frame, text="Ordinary label")
    clock2  = Clock(frame)             # Create the clock widget
    clock2.widget.pack()               # Add the clock widget to frame
    label3 = tkinter.Label(frame, text="Ordinary label")


We created and placed the clock2 widget almost like any other widget. We simply used the Clock class instead of the tkinter.Label class. There's no need to start() or stop() the clock widget - once created, it automatically updates itself.

We can place multiple clock widgets within the same frame without problems.

Advanced Placement

The Clock class works will all geometry managers, just like any widget:


If we don't need to retain the 'clock' variable, or customize it's appearance, then we can simply place it like any other widget:

    tkinter.Label(frame, text="Ordinary label").pack()


One easy way to customize the clock's appearance is to use .configure
All Label configure options will work. The clock is simply a label.

    clock2 = Clock(frame)         # Create the clock widget
    clock2.widget.pack()          # Add the clock widget to frame


There is one more simplification we can make, detailed as part of this tkinter example. We can change the class to inherit Label properties instead of creating a .widget attribute.If you're new to Python classes, the concept of inheriting from a class may take a few tries to wrap your brain around.


class Clock():
    def __init__(self, parent):
        self.widget = tkinter.Label(parent, text=self.time)

if __name__ == "__main__":


class Clock3(tkinter.Label):
    def __init__(self, parent=None):
        tkinter.Label.__init__(self, parent)

if __name__ == "__main__":

Final result:

The final clock class looks like:

class Clock3(tkinter.Label):
    """ Class that contains the clock widget and clock refresh """

    def __init__(self, parent=None):
        tkinter.Label.__init__(self, parent)
        Create and place the clock widget into the parent element
        It's an ordinary Label element.
        self.time = time.strftime('%H:%M:%S')
        self.configure(text=self.time, bg='yellow')
        self.after(200, self.tick)

    def tick(self):
        """ Update the display clock every 200 milliseconds """
        new_time = time.strftime('%H:%M:%S')
        if new_time != self.time:
            self.time = new_time
        self.after(200, self.tick)

And you implement it rather like this:

if __name__ == "__main__":
    Create a tkinter window and populate it with elements
    One of those elements merely happens to include the clock.

    # Generic window elements

    window = tkinter.Tk()
    frame  = tkinter.Frame(window, width=400, height=400 )

    # Add the frame elements, including the clock

    label1 = tkinter.Label(frame, text="Ordinary label")
    clock2 = Clock(frame)             # Create the clock widget
    clock2.configure(bg='yellow')     # Customize the widget
    clock2.pack()                     # Add the clock widget to frame
    label3 = tkinter.Label(frame, text="Ordinary label")


Wednesday, March 12, 2014

Importing GTFS files into SQLite

A great comment in my previous post about demystifying GTFS transit schedule data pointed out that the various files in a GTFS file are simply database tables. Each file can be imported into a relational database as a separate table, and queried using SQL instead of the custom scripts I used.

In fact, I found SQL to be faster and easier to maintain than the Python script...for a while. Eventually I rewrote my Python code, added a preprocessor, and found just as fast and easier to maintain than mixed Python/SQLite.
Nevertheless, thanks to Stefan, for the tip!

Here's a little more detail about exactly how to do it.

We will use the very simple, fast application SQLite for this, since our tables and queries will be rather simple and straightforward. Other possible databases include MongoDB and CouchDB. Indeed, for the very simple queries we used before, a series of good-old gdbm key-value databases could work.

Setup and importing GTFS tables into SQLite

In Ubuntu, installing SQLite3 is very simple:

sudo apt-get install sqlite3

Next, let's manually download the GTFS file for the Milwaukee County Transit System, uncompress it, create a new database, add a table to the database for the stops file, import stops file into the database, and save the database.

$ mkdir /home/me/GTFS                               # Create a working directory 
$ wget -O /home/me/GTFS/mcts.gtfs http://kamino.mcts.org/gtfs/google_transit.zip
                                                    # Download the GTFS file
$ unzip -d /home/me/GTFS /home/me/GTFS/mcts.gtfs    # Unzip the GTFS file
$ sqlite3

sqlite> attach /home/me/GTFS/mcts.db as mcts        # Create a new database
sqlite> create table stops(stop_id TEXT,stop_code TEXT,stop_name TEXT,
                           stop_desc TEXT,stop_lat REAL,stop_lon REAL,
                           zone_id NUMERIC,stop_url TEXT,timepoint NUMERIC);
sqlite> .separator ","                              # Tell SQLite that it's a CSV file
sqlite> .import /home/me/GTFS/stops.txt stops       # Import the file into a db table
sqlite> .dump                                       # Test the import
sqlite> delete from main.stops where stop_id like 'stop_id';  # Delete the header line
sqlite> select * from mcts.stops where stop_id == 5505;       # Test the import
sqlite> .backup mcts /home/me/GTFS/mcts.db          # Save the database
sqlite> .quit

Scripting imports

We can also script it. Here's a more robust script that creates multiple tables. The column names are explained on the first line of each table (which is why we must delete that line). The data types -TEXT, REAL, and NUMERIC-, and conversions from various Java, Python, C, and other datatypes are clearly explained in the SQLite documentation. The explanation of the field names, and expected datatype, is explained in the GTFS documentation. Each provider's GTFS file can include many optional fields, and may use different optional fields over time, so you are *likely* to need to tweak this script a bit to get it to work:

create table agency(agency_id TEXT,agency_name TEXT,agency_url TEXT,
                    agency_timezone TEXT,agency_lang TEXT, agency_phone TEXT);
create table calendar_dates(service_id TEXT,date NUMERIC,exception_type NUMERIC);
create table routes(route_id TEXT,agency_id TEXT,route_short_name TEXT,
                    route_long_name TEXT,route_desc TEXT,route_type NUMERIC,
                    route_url TEXT,route_color TEXT,route_text_color TEXT);
create table shapes(shape_id TEXT,shape_pt_lat REAL,shape_pt_lon REAL,
                    shape_pt_sequence NUMERIC);
create table stops(stop_id TEXT,stop_code TEXT,stop_name TEXT,
                   stop_desc TEXT,stop_lat REAL,stop_lon REAL,
                   zone_id NUMERIC,stop_url TEXT,timepoint NUMERIC);
create table stop_times(trip_id TEXT,arrival_time TEXT,departure_time TEXT,
                        stop_id TEXT,stop_sequence NUMERIC,stop_headsign TEXT,
                        pickup_type NUMERIC,drop_off_type NUMERIC);
create table trips(route_id TEXT,service_id TEXT,trip_id TEXT,
                   trip_headsign TEXT,direction_id NUMERIC,
                   block_id TEXT,shape_id TEXT);
.separator ','
.import /home/me/GTFS/agency.txt agency
.import /home/me/GTFS/calendar_dates.txt calendar_dates
.import /home/me/GTFS/routes.txt routes
.import /home/me/GTFS/shapes.txt shapes
.import /home/me/GTFS/stops.txt stops
.import /home/me/GTFS/stop_times.txt stop_times
.import /home/me/GTFS/trips.txt trips
delete from agency where agency_id like 'agency_id';
delete from calendar_dates where service_id like 'service_id';
delete from routes where route_id like 'route_id';
delete from shapes where shape_id like 'shape_id';
delete from stops where stop_id like 'stop_id';
delete from stop_times where trip_id like 'trip_id';
delete from trips where route_id like 'route_id';
select * from stops where stop_id == 5505;

And run that script using:

$ sqlite3 mcts.db < mcts_creator_script

Reading GTFS data from SQLite

Now, like in the previous GTFS post, let's find the next buses at the intersection of Howell and Oklahoma. There are four stops at that location: 658, 709, 5068, and 5152.

First, let's find the appropriate service codes for today's date:

# Query: The list of all service_ids for one date.
sqlite> SELECT service_id FROM calendar_dates WHERE date == 20140310;
[...long list...]

Scripting reads

These queries can also be scripted. Here's an example script that looks up the four stops we care about for a two-hour window:

-- Usage:  $ sqlite3 GTFS/mcts.db < GTFS/mcts_lookup.sh
-- Usage:  sqlite> .read GTFS/mcts_lookup.sh 

-- List of the valid service_id codes for the current date
CREATE VIEW valid_service_ids AS
   SELECT service_id 
   FROM calendar_dates 
   WHERE date == strftime('%Y%m%d', 'now', 'localtime')

SELECT stop_times.arrival_time, trips.route_id, trips.trip_headsign
   FROM trips, stop_times

   -- Match the trip_id field between the two tables
   WHERE stop_times.trip_id == trips.trip_id

   -- Limit selection to the stops we care about 
   AND stop_times.stop_id IN (658,709,5068,5152)

   -- Limit selection to service_ids for the correct day
   AND trips.service_id IN valid_service_ids

   -- Limit selection to the next hour from now
   AND stop_times.arrival_time > strftime(
                                 '%H:%M:%S', 'now', 'localtime', '-5 minutes')
   AND stop_times.arrival_time < strftime(
                                 '%H:%M:%S', 'now', 'localtime', '+1 hour')
   ORDER BY stop_times.arrival_time

-- Clean Up
DROP VIEW valid_service_ids;

And there are two ways to run the script:

sqlite&gt> .read lookup_script         # Within sqlite
$ sqlite3 mcts.db < lookup_script      # Shell script


I found that importing GTFS files into SQLite requires a lot of memory and CPU...but is faster than Python, and the scripts are smaller and easier to maintain. SQLite is a good, fast processor or pre-processor.

File sizes:

  • mcts.gtfs: 5.6M
  • mcts.db: 79M
  • mtcs.gtfs unzipped files: 86M
I think that preprocessing or shrinking those files will be important for low-power or low-bandwidth applications.

Query times:

Here are the query times for buses within a two-hour window at Howell and Oklahoma:
  • Original Python: 13 sec
  • SQLite: 2.5 sec
  • Rewritten Python w/Preprocessor: 0.2 sec

Code size:

To do those queries,
The original Python 3 script: 206 lines.
SQLite script to import the GTFS file: 33 lines.
SQLite script to lookup the stops: 32 lines.