Allow filter to take coords instead of coord to match multiple coordinates
Add logic so compute only runs where source < source_pair for paired tables
Add check constraint for _cvtransfersgenerated so that source <= source_pair
Mobsql is a Go library and commandline application which facilitates loading one or multiple Mobility Database source GTFS archives into a SQLite database. Its internal SQLite schema mirrors GTFS's spec but adds a source field to each table (thus allowing multiple sources to be loaded to the database simulatenously). Mobsql's API is designed to be as simple as possible: the user defines a filter specification to determine which source(s) should be addressed, and from there can download, compute, purge, and query the status(es) of the specified one or more Mobility DB sources with a single command or library call.
While primarily developed to be used by Mobroute, the minimal GTFS router and related project, mobsql itself is a fully independent tool and can be used as a standalone general-purpose GTFS-to-SQLite ETL & import utility (either via CLI or as a Go library).
Supported functionality:
Planned functionality per roadmap (but not-yet implemented):
Mobsql functions as both a commandline and as a Go library.
git clone https://git.sr.ht/~mil/mobsqlgo build -tags=sqlite_math_functions cli/mobsql.go./mobsqlcp mobsql /usr/local/bincd foogo get git.sr.ht/~mil/mobsqlMobsql's commands (purge, load, compute, status) are built around the idea of addressing a (subset) of the Mobility Database Catalog with a single command. Rather then manually specifying the specific sources you want to address by the identifier in the Mobility Database (mdbid) in cases where you want to work with multiple sources, you instead define a 'sourceset filter' which is translated to a 'sourceset' of mdbids.
The basic logic behind the sourceset filter concept is:
{country: FR, municipality: Paris} (filter) -> [1026 1069 1159 1283 1291 1314](mdbids)By default (with a sourceset filter specified as {}); the entire
catalog is addressed (e.g. all ~1400 sources). As such, in almost all
cases you will want to specify particular properties on the filter to
more specifically address whichever sources you wish to perform the
requested command / operation on. Some examples might be that maybe you
want might want to load all GTFS sources for France ({country: FR}),
2 specific mdbids (mdbid: [510, 515]), or address only sources you've
already loaded previously in the database ({loaded: true}) so that
you can update these sources.
In the CLI the sourceset filter is expected to be passed as a YAML object (see below); and meanwhile for the Go API, the sourceset filter is passed as a struct for the Load, Compute, Status, and Purge commands.
YAML Sourceset Filter Specification:
Filter is defined as a YAML object. Note all string properties use a substring incasesensitive match policy. The following properties are valid and will be use in a combinatory fashion (e.g. properties translate to AND conditions):
country: References the country per location.country_code in schema (string)municipality: References the municipality per location.municipality in schema (string)subdivision: References the subdivision per location.subdivision_name in schema (string)mdbid: References the unique mobility database source id per mdb_source_id in schema ([]int)provider: References the unique mobility database provider provider in schema (string)name: References the unique mobility database provider name in schema (string)glob: Wildcard match {country,municipality,subdivision,provider,name} (sring)coords: Bounding box match ensuring contains coordinates ([][2]float)maxkm: Bounding box match ensuring bbox area is less then specification (float)YAML Sourceset Filter Examples:
{mdbid: [510, 516]}: Mdbid is 510 or 516{country: BE}: BE (Belgium) country matches{municipality: paris}: Paris municipality matches{subdivision: ontario}: Ontario subdivsion matches{glob: foo}: Matches for {country,municipality,subdivision,provider,name} containing subtring foo{coords: [[40.512764, -74.251961]]}: Matches where coordinate is within source bounding box{maxkm: 20}: Matches with bounding box maximum size of 20km (kilometers){loaded: true}: Matches where sources are in DB/ previously downloaded (useful for updates, querying etc.){loaded: true, country: FR}: Matches where source previously loaded & country matches FR{}: All sources / entire catalogCommandline documentation is available via running mobsql -h; for reference
the generated documentation is available below:
Usage:
./mobsql [FLAGS]
Flags:
-c string
Command: should be one of {status,load,compute,purge}; note, you must pass -f when using -c.
---
load: Load downloads the associated GTFS zip archives from the MobilityDB's
URL; and then performs an import, pulling each GTFS Zip CSV file into
the local SQLite database. Note that this operation is cached (e.g.
subsequent requests with the same source will skip tables already imported
if underlying GTFS CSV file checksums are unchanged).
compute: Compute recomputes the application's computed tables (contrived from the
source loaded GTFS tables) specified in application's configuration. Each
computed table is based on a underlying view (see config). The concept of
a computed table itself is similar to concept of a SQL materialized view;
however 'recomputation' occurs on a per-source level. So you don't need
to 'rematerialize' an entire table.
status: Status queries the database for sources that match the given sourceset.
This is a read-only operation which can be used to determine the effected
sources for subsequent requests with the same sourceset.
purge: Purge removes (deletes) all associated data from underlying GTFS tables
in the SQLite database for the given sourceset.
-db string
Location of SQLite DB path; if unset uses ~/.cache/mobroute/sqlite.db
-f string
YAML filter specification to apply to -c command.
Pass as a YAML object containing optional properties:
{mdbid, country, municipality, subdivision, provider, name, glob, bbox, maxkm, loaded}
Example YAML filter specifications:
-f '{}'
-f 'mdbid: [510, 516]'
-f 'country: BE'
-f 'municipality: paris'
-f 'subdivision: ontario'
-f 'glob: foo'
-f 'coords: [[40.512764, -74.251961]]'
-f 'maxkm: 20'
-f 'loaded: true'
-f '{glob: foo, country: US}'
-h Display help information
-v Display version information
-vd
Enable verbose debug messages
-vi
Enable verbose info messages (default true)
-vvv
Enable verbose info+warnings+debug messages (equivilant to -vi + -vw + -vd)
-vw
Enable verbose warnings messages
Examples:
mobsql -f '{}' -c status (View all sources status)
mobsql -f '{loaded: true}' -c status (View all loaded sources status)
mobsql -f '{glob: nyc}' -c status (View all sources matching glob of nyc)
mobsql -f '{country: FR}' -c load (Load all souces matching France country)
mobsql -f '{country: FR}' -c purge (Purge database of all sources matching France country)
mobsql -f '{}' -c purge (Purge database of all sources)
mobsql -h (Display help text)
mobsql -v (Display version)
Documentation for the Go module is available through pkg.go.dev. Note that the major functionalities of the go package (Load, Compute, Purge, and Status) are also available through the commandline application.