tdis_layers_ids

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

tdis_identifier

text

2147483647

√

null

element_id

int4

√

null

title

varchar

2147483647

√

null

description

text

2147483647

√

null

purpose

text

2147483647

√

null

element_last_updated

date

√

null

element_created

date

√

null

username

varchar

2147483647

√

null

user_first_name

varchar

2147483647

√

null

user_last_name

varchar

2147483647

√

null

user_id

int4

√

null

collection

varchar

2147483647

√

null

collection_id

int4

√

null

contacts

_int4

√

null

collection_jurisdiction

text

2147483647

√

null

collection_jurisdiction_id

int4

√

null

collection_jurisdiction_code

text

2147483647

√

null

collection_jurisdiction_type

varchar

2147483647

√

null

jurisdictions

_text

2147483647

√

null

extent

text

2147483647

√

null

custody_chain

_varchar

2147483647

√

null

license

varchar

2147483647

√

null

keyword_term

varchar

2147483647

√

null

keyword_vocabulary_source

varchar

2147483647

√

null

keyword_vocabulary_source_id

int4

√

null

metadata_date

date

√

null

status

varchar

2147483647

√

null

status_id

int4

√

null

endpoint_type

varchar

2147483647

√

null

endpoint_type_id

int4

√

null

distribution_method

varchar

2147483647

√

null

distribution_method_id

int4

√

null

update_interval

varchar

2147483647

√

null

update_interval_id

int4

√

null

publisher_distributor_agency

varchar

2147483647

√

null

publisher_distributor_agency_id

int4

√

null

documentation_filenames

_varchar

2147483647

√

null

creator

varchar

2147483647

√

null

distribution_url

varchar

2147483647

√

null

software

varchar

2147483647

√

null

software_id

int4

√

null

software_distributor

varchar

2147483647

√

null

software_version

varchar

2147483647

√

null

tdis_collection_identifier

text

2147483647

√

null

time_periods

_daterange

2147483647

√

null

use_constraints

_int4

√

null

accessibility_score

int4

√

null

accessibility_score_description

varchar

2147483647

√

null

accessibility_id

int4

√

null

data_stage_score

int4

√

null

data_stage_score_id

int4

√

null

data_stage_description

varchar

2147483647

√

null

file_format

varchar

2147483647

√

null

file_format_id

int4

√

null

process_step_date

date

√

null

process_step_source_agency_id

int4

√

null

process_step_description

text

2147483647

√

null

process_step_source_agency

varchar

2147483647

√

null

codeset_name

varchar

2147483647

√

null

codeset_url

varchar

2147483647

√

null

completeness_score

int4

√

null

completeness_id

int4

√

null

completeness_description

varchar

2147483647

√

null

domain_value

varchar

2147483647

√

null

domain_value_description

text

2147483647

√

null

horizontal_coordinate_system

varchar

2147483647

√

null

horizontal_coordinate_system_id

int4

√

null

horizontal_datum

varchar

2147483647

√

null

horizontal_datum_id

int4

√

null

vertical_coordinate_system

varchar

2147483647

√

null

vertical_coordinate_system_id

int4

√

null

vertical_datum

varchar

2147483647

√

null

vertical_datum_id

int4

√

null

value_range_maximum

varchar

2147483647

√

null

value_range_minimum

varchar

2147483647

√

null

scale

varchar

2147483647

√

null

scale_id

int4

√

null

attributes

json

2147483647

√

null

spatial_accuracy_id

int4

√

null

spatial_accuracy_score

int4

√

null

spatial_accuracy_description

varchar

2147483647

√

null

spatial_data_type

varchar

2147483647

√

null

spatial_data_type_id

int4

√

null

most_recent_status_update

timestamptz

35,6

√

null

archive_path

text

2147483647

√

null

record_created_date

timestamptz

35,6

√

null

record_updated_date

timestamptz

35,6

√

null

textsearchable_index_col

tsvector

2147483647

√

null

View Definition

SELECT concat(e.element_type, '-', e.guid) AS tdis_identifier,
    e.id AS element_id,
    e.title,
    e.description,
    e.purpose,
    mem.element_last_updated,
    mem.element_created,
    u.username,
    u.first_name AS user_first_name,
    u.last_name AS user_last_name,
    u.contact_id AS user_id,
    col_e.title AS collection,
    col.element_id AS collection_id,
    COALESCE(array_agg(DISTINCT ARRAY[ec.contact_id, ec.role_id]) FILTER (WHERE (ec.contact_id IS NOT NULL)), '{}'::integer[]) AS contacts,
    col_j.name AS collection_jurisdiction,
    col_j.id AS collection_jurisdiction_id,
    col_j.code AS collection_jurisdiction_code,
    col_jt.name AS collection_jurisdiction_type,
    COALESCE(array_agg(DISTINCT ARRAY[(j.id)::text, j.name, (jt.name)::text]), (('{}'::text)::character varying[])::text[]) AS jurisdictions,
    st_astext(st_envelope(mem.geom)) AS extent,
    e.custody_chain,
    e.license,
    e.keyword_term,
    cv.name AS keyword_vocabulary_source,
    cv.id AS keyword_vocabulary_source_id,
    e.metadata_date,
    s.name AS status,
    s.id AS status_id,
    ept.name AS endpoint_type,
    ept.id AS endpoint_type_id,
    dm.name AS distribution_method,
    dm.id AS distribution_method_id,
    ui.name AS update_interval,
    ui.id AS update_interval_id,
    pda.name AS publisher_distributor_agency,
    pda.id AS publisher_distributor_agency_id,
    mem.documentation_filenames,
    mem.creator,
    mem.distribution_url,
    sw.name AS software,
    sw.id AS software_id,
    sw.distributor AS software_distributor,
    mem.software_version,
    concat(col_e.element_type, '-', col_e.guid) AS tdis_collection_identifier,
    mem.time_periods,
    COALESCE(array_agg(DISTINCT con.id) FILTER (WHERE (con.id IS NOT NULL)), '{}'::integer[]) AS use_constraints,
    acc.score AS accessibility_score,
    acc.description AS accessibility_score_description,
    acc.id AS accessibility_id,
    ds.score AS data_stage_score,
    ds.id AS data_stage_score_id,
    ds.description AS data_stage_description,
    ff.name AS file_format,
    ff.id AS file_format_id,
    a.process_date AS process_step_date,
    a.process_source_id AS process_step_source_agency_id,
    a.process_description AS process_step_description,
    pss.name AS process_step_source_agency,
    l.codeset_name,
    l.codeset_url,
    com.score AS completeness_score,
    com.id AS completeness_id,
    com.description AS completeness_description,
    l.domain_value,
    l.domain_value_description,
    hp.name AS horizontal_coordinate_system,
    hp.id AS horizontal_coordinate_system_id,
    hd.name AS horizontal_datum,
    hd.id AS horizontal_datum_id,
    vp.name AS vertical_coordinate_system,
    vp.id AS vertical_coordinate_system_id,
    vd.name AS vertical_datum,
    vd.id AS vertical_datum_id,
    l.value_range_max AS value_range_maximum,
    l.value_range_min AS value_range_minimum,
    sca.name AS scale,
    sca.id AS scale_id,
    json_agg(DISTINCT jsonb_build_object('name', att.name, 'description', att.description, 'accuracy', att.accuracy, 'domain_type', dt.name)) AS attributes,
    sa.id AS spatial_accuracy_id,
    sa.score AS spatial_accuracy_score,
    sa.description AS spatial_accuracy_description,
    sdt.name AS spatial_data_type,
    sdt.id AS spatial_data_type_id,
    lsu.most_recent_update AS most_recent_status_update,
    string_agg(DISTINCT (tu.path)::text, ';'::text) AS archive_path,
    e.created AS record_created_date,
    e.updated AS record_updated_date,
    e.textsearchable_index_col
   FROM (((((((((((((((((((((((((((((((((((((tdis_element e
     JOIN tdis_member mem ON ((e.id = mem.element_id)))
     JOIN tdis_artifact a ON ((mem.element_id = a.element_id)))
     JOIN tdis_layer l ON ((mem.element_id = l.element_id)))
     JOIN tdis_users u ON ((e.user_id = u.contact_id)))
     JOIN tdis_collection col ON ((mem.collection_id = col.element_id)))
     JOIN tdis_element col_e ON ((col.element_id = col_e.id)))
     JOIN tdis_status s ON ((mem.status_id = s.id)))
     JOIN tdis_jurisdiction col_j ON ((col.jurisdiction_id = col_j.id)))
     JOIN tdis_jurisdiction_type col_jt ON ((col_j.jurisdiction_type_id = col_jt.id)))
     JOIN tdis_member_jurisdiction mj ON ((mj.member_id = mem.element_id)))
     JOIN tdis_jurisdiction j ON ((mj.jurisdiction_id = j.id)))
     JOIN tdis_jurisdiction_type jt ON ((j.jurisdiction_type_id = jt.id)))
     LEFT JOIN tdis_element_contact ec ON ((e.id = ec.element_id)))
     LEFT JOIN tdis_endpoint_type ept ON ((mem.end_point_type_id = ept.id)))
     LEFT JOIN tdis_distribution_method dm ON ((mem.distribution_method_id = dm.id)))
     LEFT JOIN tdis_update_interval ui ON ((mem.update_interval_id = ui.id)))
     LEFT JOIN tdis_software sw ON ((mem.software_id = sw.id)))
     LEFT JOIN tdis_agency pda ON ((mem.publisher_distributor_agency_id = pda.id)))
     LEFT JOIN tdis_controlled_vocabulary cv ON ((e.controlled_vocabulary_id = cv.id)))
     LEFT JOIN tdis_member_constraint mc ON ((mem.element_id = mc.member_id)))
     LEFT JOIN tdis_constraint con ON ((mc.constraint_id = con.id)))
     LEFT JOIN tdis_accessibility acc ON ((mem.accessibility_id = acc.id)))
     LEFT JOIN tdis_attribute att ON ((l.element_id = att.layer_id)))
     LEFT JOIN tdis_domain_type dt ON ((att.domain_type_id = dt.id)))
     LEFT JOIN tdis_completeness com ON ((mem.completeness_id = com.id)))
     LEFT JOIN tdis_data_stage ds ON ((mem.data_stage_id = ds.id)))
     LEFT JOIN tdis_file_format ff ON ((a.file_format_id = ff.id)))
     LEFT JOIN tdis_horizontal_projection hp ON ((mem.horizontal_projection_id = hp.id)))
     LEFT JOIN tdis_vertical_projection vp ON ((mem.vertical_projection_id = vp.id)))
     LEFT JOIN tdis_horizontal_datum hd ON ((mem.horizontal_datum_id = hd.id)))
     LEFT JOIN tdis_vertical_datum vd ON ((mem.vertical_datum_id = vd.id)))
     LEFT JOIN tdis_agency pss ON ((a.process_source_id = pss.id)))
     LEFT JOIN tdis_scale sca ON ((e.scale_id = sca.id)))
     LEFT JOIN tdis_spatial_accuracy sa ON ((mem.spatial_accuracy_id = sa.id)))
     LEFT JOIN tdis_spatial_data_type sdt ON ((l.spatial_data_type_id = sdt.id)))
     LEFT JOIN tdis_latest_status_updates lsu ON ((mem.element_id = lsu.member_id)))
     LEFT JOIN tdis_upload tu ON (((e.id = tu.element_id) AND (tu.type_id = 3))))
  WHERE ((e.deleted = false) AND ((e.element_type)::text = 'TL'::text))
  GROUP BY (concat(e.element_type, '-', e.guid)), e.id, u.username, u.first_name, u.last_name, u.contact_id, cv.name, cv.id, col.element_id, col_e.title, col_j.name, col_j.id, col_j.code, col_jt.name, e.created, e.updated, e.title, e.description, e.purpose, mem.element_last_updated, mem.element_created, mem.geom, e.custody_chain, e.license, e.keyword_term, e.metadata_date, s.name, s.id, ept.name, ept.id, dm.name, dm.id, ui.name, ui.id, mem.documentation_filenames, pda.name, pda.id, mem.creator, mem.distribution_url, sw.name, sw.id, sw.distributor, mem.software_version, (concat(col_e.element_type, '-', col_e.guid)), mem.time_periods, lsu.most_recent_update, e.textsearchable_index_col, acc.score, acc.description, acc.id, l.codeset_name, l.codeset_url, com.score, com.id, com.description, ds.score, ds.id, ds.description, l.domain_value, l.domain_value_description, ff.name, ff.id, hp.name, hp.id, hd.name, hd.id, vp.name, vp.id, vd.name, vd.id, l.value_range_max, l.value_range_min, a.process_date, a.process_source_id, a.process_description, pss.name, sca.name, sca.id, sa.id, sa.score, sa.description, sdt.name, sdt.id;

Columns

View Definition

Possibly Referenced Tables/Views