Skip to content

Accessing GTFS Data in QGIS

July 25, 2011

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.

The advantage to this approach is that the gids are the same as the stop_ids. The disadvantage of this approach is that it doesn’t work with systems like BART that have alphanumeric stop ids, like ‘SHAY’ or ’24TH.’ If your system has a few ids like ‘3104_A’, don’t worry that you won’t notice them; Postgres won’t cast that to a number but will throw an error like:

ERROR:  invalid input syntax for integer: "3104_A"

At least, that’s what it did in Postgres 9.0.4.

Update 2011-11-05:

If your transit system has non-numeric IDs and you can’t use the casting trick, you can create numerical IDs that are not tied to the  Stop IDs like so:


ALTER TABLE <table> ADD COLUMN nid SERIAL;

Thanks, underdark!

Advertisements
4 Comments
  1. underdark permalink
    September 26, 2011 10:46 am

    One solution for alphanumeric stop ids could be to simply recreate the table with OIDs.

    • mattwigway permalink*
      September 30, 2011 8:45 pm

      Sorry for the delay in approving that comment.

      Yes, that is an option generally, but in this case, the data is loaded to the database using the program gtfsdb, so it creates the tables. Perhaps a patch is warranted.

    • mattwigway permalink*
      November 5, 2011 10:09 am

      You’re right, I didn’t realize that you could add a SERIAL column after table creation. I’ll update the post. I think the first approach is still valid because it lets you preserve the indexing that is already present.

  2. mattwigway permalink*
    December 14, 2011 11:34 pm

    Removed the confusion vis-a-vis SERIAL columns and OIDs – they aren’t the same thing.

Comments are closed.

%d bloggers like this: