tdis_layers_ids.sql

-- View: public.tdis_layers_ids

-- DROP VIEW public.tdis_layers_ids;

CREATE OR REPLACE VIEW public.tdis_layers_ids
 AS
 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]), '{}'::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.attribute_accuracy,
    dt.name AS domain_type,
    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,
    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,
    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_domain_type dt ON l.attribute_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 l.horizontal_projection_id = hp.id
     LEFT JOIN tdis_vertical_projection vp ON l.vertical_projection_id = vp.id
     LEFT JOIN tdis_horizontal_datum hd ON l.horizontal_datum_id = hd.id
     LEFT JOIN tdis_agency pss ON a.process_source_id = pss.id
     LEFT JOIN tdis_scale sca ON mem.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.attribute_accuracy, dt.name, 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, 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.score, sa.description, sdt.name, sdt.id;

ALTER TABLE public.tdis_layers_ids
    OWNER TO ms2;


-- FUNCTION: public.tdis_get_layer_with_ids(character varying)

-- DROP FUNCTION IF EXISTS public.tdis_get_layer_with_ids(character varying);

CREATE OR REPLACE FUNCTION public.tdis_get_layer_with_ids(
	p_tdis_identifier character varying)
    RETURNS tdis_layers_ids
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
select *
from tdis_layers_ids
where tdis_identifier ilike p_tdis_identifier
limit 1;
$BODY$;

ALTER FUNCTION public.tdis_get_layer_with_ids(character varying)
    OWNER TO ms2;

COMMENT ON FUNCTION public.tdis_get_layer_with_ids(character varying)
    IS '<placeholder>

    select *
    from tdis_get_layer_with_ids(''''TL-932cbc4e-9688-11ec-ba2a-59ebf1ac4488'''');';

-- FUNCTION: public.tdis_get_layers_by_user_with_ids(character varying)

-- DROP FUNCTION IF EXISTS public.tdis_get_layers_by_user_with_ids(character varying);

CREATE OR REPLACE FUNCTION public.tdis_get_layers_by_user_with_ids(
	p_username character varying)
    RETURNS tdis_layers_ids
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
select *
from tdis_layers_ids
where username ilike p_username
order by title;

$BODY$;

ALTER FUNCTION public.tdis_get_layers_by_user_with_ids(character varying)
    OWNER TO ms2;

COMMENT ON FUNCTION public.tdis_get_layers_by_user_with_ids(character varying)
    IS '<placeholder>

    select *
    from tdis_get_layers_by_user_with_ids(''pbuschow'');';