Mobsql: CLI Userguide

Mobsql primarily serves as a Go library that navigation apps and libraries (such as Mobroute) can integrate with to provide GTFS SQLite load & querying functionality. That said, the CLI can be used independently as well.

Using the CLI you can load Mobility Database GTFS feeds, load custom GTFS feeds, search feeds, purge data, and more. For full usage of the CLI you can see the CLI doc. This document (the userguide), is not meant to be comprehensive guide for all functionality; but rather steps you as an end-user through some common usecases for the CLI.

Installation

Mobsql functions as both a commandline and as a Go library. Installation as a CLI is as follows:

  • Installation for usage a CLI application:
    • Install Go & SQLite-dev (distro-dependent):
      • Debian: apt-get install go libsqlite3-dev
      • Alpine: apk add go sqlite-dev
    • Clone repo: git clone https://git.sr.ht/~mil/mobsql
    • Build executable: go build -tags=sqlite_math_functions cli/*.go
      • Or use bundled build script: ./build.sh build
      • This produces the single mobsql CLI binary
    • Optionally install binary to $PATH etc.: cp mobsql /usr/local/bin
  • Run via: ./mobsql
    • See below examples for common usecases
    • Use -h flag for docs
    • Or see CLI doc for web version of docs

Load a Mobility Database GTFS Feed

To load a Mobility Database feed, you can use the aptly named loadmdbgtfs subcommand. Information on Mobility Database feed IDs is available at the feed IDs doc.

mobsql loadmdbgtfs -p '{"feed_ids":[1088]}'

After loadmdbgfs, you can either use the status command, or inspect the database yourself via SQLite to observe tables. For example:

sqlite3 ~/.cache/mobroute/sqlite.db 'select * from stop_times where feed_id = 1088 limit 5;'

You'll notice the feed_id 1088 present in all rows loaded to the database.

Load a Custom GTFS Feed

Custom GTFS feeds (specified either from the local filesystem or a remote ZIP) may be loaded using the loadcustomgtfs subcommand. Since all feed_id's in Mobsql are implicitly identified as Mobility Database feed IDs; loading a custom feed you must provide an arbitrary negative feed ID to distinguish your custom feed from internally supported Mobility Database feeds. See the feed IDs doc for more information on feed IDs.

mobsql loadcustomgtfs -p '{"feed_id":-33, "gtfs_uri": "http://web.mta.info/developers/data/nyct/subway/google_transit.zip"}'

After loadcustomgtfs, inspect the with SQLite DB:

sqlite3 ~/.cache/mobroute/sqlite.db 'select * from stop_times where feed_id = -33 limit 5;'

Search for Mobility Database GTFS Feeds (by region, etc.)

Since it may be unclear to you which feeds are available OOTB to load, you can use the built-in feedsearch command to query for feeds by region, country, municipality, etc.

Show all Belgium feeds:

mobsql feedsearch -p '{"searchfilter":{"country": "BE"}}'

Also, this command can be used to quickly determine which feeds have already been loaded and determine metadata. Use the loaded field, for example:

mobsql feedsearch -p '{"searchfilter":{"loaded": true}}'

Query the Status of GTFS Feeds

You may query the status of loaded (or not loaded) GTFS feeds by ID via the status subcommand.

Once a feed is loaded some extra metadata is provided (such as which tables were loaded, time of load, the min/max date covered by calendar/calandar_dates etc.). To query the status of a given set of feed IDs:

mobsql status -p '{"feed_ids":[-33, 1088]}'

Purging a GTFS Feed

To remove a feed from the database, you may use the purge subcommand. Further there is a distinction between 'gtfs' and 'computed' tables available to purge. If you are using the CLI directly only gtfs tables will ever be loaded. If using Mobsql through the Go library or Mobroute, the computed tables are tables that are not from the origin GTFS feed but 'computed' based on input feed IDs.

mobsql purge -p '{"feed_ids":[1088], "purge_tables": "gtfs"}'

Additional Documentation

Full documentation on all CLI functionality is available at: