Sample queries to obtain tables relevant for a resolution, then columns in a tag for that resolution.

sample-bigmetadata.sql

/* Determine tablenames for data at a specific resolution.
 */

/* data_table_id: ID to use to find columns in data tables at this resolution
 * from_tablename: data table with data specified resolution
 * from_column: column to use when joining against the data table
 * to_tablename: table with geom data
 * to_column: column to use when joining against the geom table
 * geom_column: column containing geom data
 */
SELECT from_table_geoid.id data_table_id,
       from_table_geoid.tablename from_tablename,
       from_column_table_geoid.colname from_column,
       to_table_geom.tablename to_tablename,
       to_column_table_geoid.colname to_column,
       to_column_table_geom.colname geom_column
FROM bmd_column_table from_column_table_geoid,
     bmd_column_table to_column_table_geoid,
     bmd_column_2_column rel,
     bmd_column_table to_column_table_geom,
     bmd_table from_table_geoid,
     bmd_table to_table_geoid,
     bmd_table to_table_geom
WHERE from_column_table_geoid.column_id = to_column_table_geoid.column_id
  AND to_column_table_geoid.column_id = rel.from_id
  AND rel.reltype = 'geom_ref'
  AND rel.to_id = to_column_table_geom.column_id
  AND to_column_table_geom.column_id = '"us.census.tiger".tract_2013'
  AND from_table_geoid.id = from_column_table_geoid.table_id
  AND to_table_geoid.id = to_column_table_geoid.table_id
  AND to_table_geom.id = to_column_table_geom.table_id;


/* This query will obtain tablenames and column names for population columns
 * in tables with resolutions as determined by "from_tablename" in query
 * above.
 */
SELECT bmd_table.tablename,
       bmd_column_table.colname
FROM bmd_column,
     bmd_column_table,
     bmd_table,
     bmd_tag,
     bmd_column_tag
WHERE bmd_column_table.column_id = bmd_column.id
  AND bmd_column_table.table_id = bmd_table.id
  AND bmd_column_tag.tag_id = bmd_tag.id
  AND bmd_column_tag.column_id = bmd_column.id
  AND bmd_tag.name ILIKE 'population' -- look for population columns
  AND bmd_table.id IN ('"us.census.acs".extract_2013_5yr_puma')