Skip to content

Data Hub Data Model

Jump to bottom
Brez edited this page May 22, 2019 · 1 revision

Domain Tables

area_type (data download area table)

  • area_type_id (uuid, primary key)
  • area_type (char w/choices)
    • state
    • county
    • quad
    • qquad
    • natgrid
  • area_type_name (char)
  • created (datetime)
  • last_modified (datetime)
  • orig_data_download_id (positive integer)

band_type

  • band_type_id (uuid, primary key)
  • band_name (char)
    • Black and White
    • Color Infrared
    • Natural Color
  • band_abbreviation (char)
    • BW
    • CIR
    • NC
  • created (datetime)
  • last_modified (datetime)

category_type ('category')

  • category_type_id (uuid, primary key)
  • category (char)
    • Bathymetry
    • Boundary
    • Cultural
    • Demographics
    • Elevation
    • Environmental
    • Floodplain
    • Geology
    • Historic Imagery
    • Hydrography
    • Land Use - Land Cover
    • Lidar
    • Natural Regions
    • Orthoimagery
    • Reference Grid
    • Topographic Map
    • Transportation
    • Weather
  • created (datetime)
  • last_modified (datetime)

data_type ('type')

  • data_type_id (uuid, primary key)
  • data_type (char)
    • Lidar
    • Raster
    • Vector
  • created (datetime)
  • last_modified (datetime)

epsg_type ('spatial_reference')

  • epsg_type_id (uuid, primary key)
  • epsg_code (positive integer)
    • 2276
    • 2277
    • 2278
    • 2279
    • 2919
    • 2964
    • 3081
    • 3083
    • 3857
    • 4267
    • 4269
    • 4326
    • 6344
    • 26913
    • 26914
    • 26915
    • 26916
    • 32613
    • 32614
  • created (datetime)
  • last_modified (datetime)

file_type ('available_formats')

  • file_type_id (uuid, primary key)
  • file_type (char)
    • DEM
    • DWG
    • ECW
    • ESRI GRID
    • GDB
    • IMG
    • JP2
    • JPG
    • KML
    • KMZ
    • LAS
    • MrSID
    • PDF
    • SHP
    • SID
    • TIFF
  • created (datetime)
  • last_modified (datetime)

license_type ('license', 'license_text', 'license_url')

resolution_type

  • resolution_type_id (uuid, primary key)
  • resolution (char)
    • 3in
    • 6in
    • 1ft
    • 35cm
    • 50cm
    • 1m
    • 30m
  • created (datetime)
  • last_modified (datetime)

source_type ('source','agency', 'agency_web', 'agency_gis_page')

template_type ('template')

  • template_type_id (uuid, primary key)
  • template (char)
    • data-catalog-entry.html
  • created (datetime)
  • last_modified (datetime)

use_type ('recommended_use')

  • use_type_id (uuid, primary key)
  • use_type (char)
    • Analysis
    • Basemap
    • Cartography
    • Feature Extraction
    • General Large Scale Geologic Information and Mapping
    • Historical Use
    • Location
    • Planning
    • Research
  • created (datetime)
  • last_modified (datetime)

Spatial Domain Tables

state_type

  • state_type_id (uuid, primary key)
  • state_fips (positive integer)
  • state_name (char)
  • created (datetime)
  • last_modified (datetime)

county_type

  • county_type_id (uuid, primary key)
  • county_fips (positive integer)
  • county_name (char)
  • created (datetime)
  • last_modified (datetime)

quad_type

  • quad_type_id (uuid, primary key)
  • quad_id (positive integer)
  • quad_name (char)
  • created (datetime)
  • last_modified (datetime)

q_quad_type

  • q_quad_type_id (uuid, primary key)
  • q_quad_id (positive integer)
  • q_quad_name (char)
  • quad_name (char)
  • created (datetime)
  • last_modified (datetime)

usng_type

  • usng_type_id (uuid, primary key)
  • northing (positive integer)
  • easting (positive integer)
  • utm_grid_zone (char)
  • grid_100k (char)
  • usng_name (char)
  • created (datetime)
  • last_modified (datetime)

Lookup Tables

band_relate

  • band_relate_id (uuid, primary key)
  • band_type_id (uuid, foreign key to band_type)
  • collection_id (uuid, foreign key to collection)
  • created (datetime)
  • last_modified (datetime)

category_relate

  • category_relate_id (uuid, primary key)
  • category_type_id (uuid, foreign key to category_type)
  • collection_id (uuid, foreign key to collection)
  • created (datetime)
  • last_modified (datetime)

data_type_relate

  • data_type_relate_id (uuid, primary key)
  • data_type_id (uuid, foreign key to data_type)
  • collection_id (uuid, foreign key to collection)
  • created (datetime)
  • last_modified (datetime)

epsg_relate

  • epsg_relate_id (uuid, primary key)
  • epsg_type_id (uuid, foreign key to epsg_type)
  • collection_id (uuid, foreign key to collection)
  • created (datetime)
  • last_modified (datetime)

file_type_relate

  • file_type_relate_id (uuid, primary key)
  • file_type_id (uuid, foreign key to file_type)
  • collection_id (uuid, foreign key to collection)
  • created (datetime)
  • last_modified (datetime)

resolution_relate

  • resolution_relate_id (uuid, primary key)
  • resolution_type_id (uuid, foreign key to resolution_type)
  • collection_id (uuid, foreign key to collection)
  • created (datetime)
  • last_modified (datetime)

use_relate

  • use_relate_id (uuid, primary key)
  • use_type_id (uuid, foreign key to use_type)
  • collection_id (uuid, foreign key to collection)
  • created (datetime)
  • last_modified (datetime)

Spatial Lookup Tables

state_resource

  • state_resource_id (uuid, primary key)
  • state_type_id (uuid, foreign key to state_type)
  • resource_id (uuid, foreign key to resource)
  • created (datetime)
  • last_modified (datetime)

county_resource

  • county_resource_id (uuid, primary key)
  • county_type_id (uuid, foreign key to county_type)
  • resource_id (uuid, foreign key to resource)
  • created (datetime)
  • last_modified (datetime)

quad_resource

  • quad_resource_id (uuid, primary key)
  • quad_type_id (uuid, foreign key to quad_type)
  • resource_id (uuid, foreign key to resource)
  • created (datetime)
  • last_modified (datetime)

q_quad_resource

  • q_quad_resource_id (uuid, primary key)
  • q_quad_type_id (uuid, foreign key to q_quad_type)
  • resource_id (uuid, foreign key to resource)
  • created (datetime)
  • last_modified (datetime)

usng_resource

  • usng_resource_id (uuid, primary key)
  • usng_type_id (uuid, foreign key to usng_type)
  • resource_id (uuid, foreign key to resource)
  • created (datetime)
  • last_modified (datetime)

Primary Tables

collection

  • collection_id (uuid, primary key)
  • name (text)
  • acquisition_date (date)
  • short_description (text)
  • description (text)
  • authoritative (boolean)
  • public (boolean)
  • known_issues (text)
  • md_filename (text)
  • wms_link (url field)
  • pop_link (url field)
  • carto_map_id (text)
  • overview_image (text)
  • thumbnail_image (text)
  • natural_image (text)
  • urban_image (text)
  • tile_index_url (url field)
  • supplemental_report_url (url field)
  • lidar_breaklines_url (url field)
  • coverage_extent (text)
  • tags (text)
  • license_type_id (uuid, foreign key to license_type)
  • source_type_id (uuid, foreign key to source_type)
  • template_type_id (uuid, foreign key to template_type)
  • created (datetime)
  • last_modified (datetime)

resource

  • resource_id (uuid, primary key)
  • resource_type (char with choices)
    • download
    • order
    • website
  • resource (url)
  • filesize (positive integer)
  • area_type_id (uuid, foreign key to area_type)
  • collection_id (uuid, foreign key to collection)
  • created (datetime)
  • last_modified (datetime)
Clone this wiki locally