I fairly commonly find myself in a situation where I would like to display one label for certain features and another for other features in the same layer. QGIS doesn’t have an official way to split labels up into categories, and until now I’d resorted to having two layers to render otherwise identical features. But, in the new, excellent expression based labeling from Nathan Woodrow, I realized one can use an SQL CASE statement. For instance, one time I need to use two types labels is when labeling roads in OpenStreetMap: I want to use the name tag, unless the feature has a ref tag defined (a name tag might be ‘Capital City Freeway’, while the ref tag would be ‘US 50’). Here’s how I solved that particular problem:

Over the last week I’ve started a new project, GTFSrDB, which allows you to connect GTFS-realtime with an SQL database, allowing app developers to use realtime data through SQL, just as easily as they use static data. Rather than worry about plumbing to connect GTFS and GTFS-realtime, they can focus on writing apps.

It accomplishes two primary tasks:

1. Keeping a database up-to-date with the latest realtime data, and

2. Archiving historic real-time data.

When you load GTFS data into PostGIS using gtfsdb, you can’t access that data in QGIS because the tables don’t have a primary key in int4 format (the primary key is in text format).

If your transit system uses numeric ids in text format, an easy fix is running this against each of your tables:

ALTER TABLE stops ADD COLUMN gid int;
UPDATE stops SET gid = stop_id::int;
ALTER TABLE stops ALTER COLUMN gid SET NOT NULL;
ALTER TABLE stops ADD CONSTRAINT stops_gid_uniq UNIQUE(gid);

What this does is creates an integer ID field, populates it with the stop_id (or shape_id, &c.) cast to an integer, then adds NOT NULL and UNIQUE constraints to the column. You can’t add the constraints beforehand, because the column is initialized to NULL values. Remember you have to run these commands against every table you want to pull into QGIS directly, and remember to change stop_id to shape_id &c.