Database Queries

Datasets ingested through Tilezilla are indexed in a database (see Database). The tilez db command and subcommands allows the user to easily query this database without requiring knowledge of SQL.

Examples

Note

All of the usage examples assume the Tilezilla configuration file has been specified by defining an environment variable, TILEZILLA_CONFIG. For example, using the Bash shell:

export TILEZILLA_CONFIG=/path/to/configuration_file.yaml

Specifying using an environment variable takes the place of `tilez -C <config_file> or tilez --config <config_file>.

1. Find information about the products table

> tilez db info product
11:30:36:INFO:*   Information about: <class 'tilezilla.db.sqlite.tables.TableProduct'>
11:30:36:INFO:==> Number of entries: 6
11:30:36:INFO:==> Enumerating columns in table: <class 'tilezilla.db.sqlite.tables.TableProduct'>
11:30:36:INFO:-       COLUMN #   NAME                TYPE
11:30:36:INFO:-       Col 00     "created"           DATETIME
11:30:36:INFO:-       Col 01     "updated"           DATETIME
11:30:36:INFO:-       Col 02     "id"                INTEGER (PRIMARY KEY)
11:30:36:INFO:-       Col 03     "ref_tile_id"       INTEGER
11:30:36:INFO:-       Col 04     "timeseries_id"     VARCHAR
11:30:36:INFO:-       Col 05     "platform"          VARCHAR
11:30:36:INFO:-       Col 06     "instrument"        VARCHAR
11:30:36:INFO:-       Col 07     "acquired"          DATETIME
11:30:36:INFO:-       Col 08     "processed"         DATETIME
11:30:36:INFO:-       Col 09     "metadata_"         TEXT
11:30:36:INFO:-       Col 10     "metadata_files_"   TEXT
11:30:36:INFO:-       HYBRID 00  "n_bands"           ?

2. Find products that weren’t fully ingested

Sometimes a product ingest can fail. In order to help recover, we can query the database to find all bands from products that were partially ingested. In this example, we know that all products should contain 8 bands.

> tilez db search --filter "n_bands != 8" --group_by timeseries_id product
Searching table "product" where:
n_bands != 8
Results:
<ESPALandsat(timeseries_id=LT50130311995272AAA01, platform/instrument=LANDSAT_5/TM, acquired=1995-09-29T14:34:30.702088+00:00, n_bands=6)>
<ESPALandsat(timeseries_id=LT50120311995345XXX01, platform/instrument=LANDSAT_5/TM, acquired=1995-12-11T14:27:53.716000+00:00, n_bands=6)>
<ESPALandsat(timeseries_id=LT50120311996236XXX01, platform/instrument=LANDSAT_5/TM, acquired=1996-08-23T14:43:34.531031+00:00, n_bands=6)>
<ESPALandsat(timeseries_id=LT50120311999116XXX01, platform/instrument=LANDSAT_5/TM, acquired=1999-04-26T15:05:49.636038+00:00, n_bands=4)>
<ESPALandsat(timeseries_id=LT50120312011133EDC00, platform/instrument=LANDSAT_5/TM, acquired=2011-05-13T15:16:41.279038+00:00, n_bands=0)>
<ESPALandsat(timeseries_id=LE70130312012119EDC00, platform/instrument=LANDSAT_7/ETM, acquired=2012-04-28T15:27:40.959821+00:00, n_bands=6)>