v_specimen_summary

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

specimen_num

int4

√

null

specimen_code

varchar

√

null

igsn

varchar

256

√

null

station_num

int4

√

null

station_code

varchar

√

null

geometry_text

text

2147483647

√

null

elevation_min

float8

17,17

√

null

elevation_max

float8

17,17

√

null

location_precisions

_varchar

2147483647

√

null

geographic_location

_text

2147483647

√

null

tectonic_settings

_varchar

2147483647

√

null

expedition_num

int4

√

null

expedition_code

varchar

256

√

null

sampling_technique_code

varchar

√

null

sampling_technique_name

varchar

2000

√

null

alterations

_text

2147483647

√

null

rock_textures

_text

2147483647

√

null

rock_class_details

_text

2147483647

√

null

geological_ages

_text

2147483647

√

null

archive_institution

varchar

2000

√

null

sample_names

_text

2147483647

√

null

sample_comments

_text

2147483647

√

null

specimen_material

varchar

255

√

null

specimen_material_name

varchar

255

√

null

taxon

_text

2147483647

√

null

analyzed_samples

_varchar

2147483647

√

null

citation_nums

_int4

√

null

citation_codes

_text

2147483647

√

null

minerals

_varchar

2147483647

√

null

material_codes

_varchar

2147483647

√

null

material_names

_varchar

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT DISTINCT vsp.sampling_feature_num AS specimen_num,
    vsp.sampling_feature_code AS specimen_code,
    sfi.igsn,
    sptr.station_num,
    sptr.station_code,
    sptr.geometry_text,
    sptr.elevation_min,
    sptr.elevation_max,
    sptr.location_precisions,
    sptr.geographic_location,
    sptr.tectonic_settings,
    sptr.expedition_num,
    sptr.expedition_code,
    f2.method_code AS sampling_technique_code,
    f2.method_name AS sampling_technique_name,
    sfal.alterations,
    sfrt.rock_textures,
    sfrcd.rock_class_details,
    sfga.geological_ages,
    ant6.annotation_text AS archive_institution,
    antsn.sample_names,
    antsc.sample_comments,
    material.material_code AS specimen_material,
    material.material_name AS specimen_material_name,
    sptc.taxon,
    spar.analyzed_samples,
    vdrc.citation_nums,
    vdrc.citation_codes,
    vdrc.minerals,
    vdrc.material_codes,
    vdrc.material_names
   FROM ((((((((((((((v_specimens vsp
     LEFT JOIN ( SELECT vsp1.sampling_feature_num,
            sfei.sampling_feature_external_id AS igsn
           FROM v_specimens vsp1,
            sampling_feature_external_identifier sfei
          WHERE ((sfei.sample_feature_num = vsp1.sampling_feature_num) AND (sfei.external_identifier_system_num = 2))) sfi ON ((sfi.sampling_feature_num = vsp.sampling_feature_num)))
     LEFT JOIN ( SELECT sf.sampling_feature_num,
            array_agg(DISTINCT (((f1.alteration)::text || '^'::text) || (f1.sources)::text)) AS alterations
           FROM (sampling_feature sf
             JOIN ( SELECT sfa.sampling_feature_num,
                    ant.annotation_text AS alteration,
                    array_agg(((mcs.citation_code || '|'::text) || ant.data_source_num)) AS sources
                   FROM sampling_feature_annotation sfa,
                    annotation ant,
                    v_citation_summary mcs
                  WHERE ((ant.annotation_num = sfa.annotation_num) AND (ant.annotation_type_num = 13) AND (ant.data_source_num = mcs.citation_num))
                  GROUP BY sfa.sampling_feature_num, ant.annotation_text) f1 ON ((f1.sampling_feature_num = sf.sampling_feature_num)))
          GROUP BY sf.sampling_feature_num) sfal ON ((sfal.sampling_feature_num = vsp.sampling_feature_num)))
     LEFT JOIN ( SELECT sf.sampling_feature_num,
            array_agg(DISTINCT (((f1.rock_texture)::text || '^'::text) || (f1.sources)::text)) AS rock_textures
           FROM (sampling_feature sf
             JOIN ( SELECT sfa.sampling_feature_num,
                    ant.annotation_text AS rock_texture,
                    array_agg(((mcs.citation_code || '|'::text) || ant.data_source_num)) AS sources
                   FROM sampling_feature_annotation sfa,
                    annotation ant,
                    v_citation_summary mcs
                  WHERE ((ant.annotation_num = sfa.annotation_num) AND (ant.annotation_type_num = 16) AND (ant.data_source_num = mcs.citation_num))
                  GROUP BY sfa.sampling_feature_num, ant.annotation_text) f1 ON ((f1.sampling_feature_num = sf.sampling_feature_num)))
          GROUP BY sf.sampling_feature_num) sfrt ON ((sfrt.sampling_feature_num = vsp.sampling_feature_num)))
     LEFT JOIN ( SELECT sf.sampling_feature_num,
            array_agg(DISTINCT (((f1.rock_class_detail)::text || '^'::text) || (f1.sources)::text)) AS rock_class_details
           FROM (sampling_feature sf
             JOIN ( SELECT sfa.sampling_feature_num,
                    ant.annotation_text AS rock_class_detail,
                    array_agg(((mcs.citation_code || '|'::text) || ant.data_source_num)) AS sources
                   FROM sampling_feature_annotation sfa,
                    annotation ant,
                    v_citation_summary mcs
                  WHERE ((ant.annotation_num = sfa.annotation_num) AND (ant.annotation_type_num = 15) AND (ant.data_source_num = mcs.citation_num))
                  GROUP BY sfa.sampling_feature_num, ant.annotation_text) f1 ON ((f1.sampling_feature_num = sf.sampling_feature_num)))
          GROUP BY sf.sampling_feature_num) sfrcd ON ((sfrcd.sampling_feature_num = vsp.sampling_feature_num)))
     LEFT JOIN ( SELECT sf.sampling_feature_num,
            array_agg(DISTINCT (((f1.geological_age)::text || '^'::text) || (f1.sources)::text)) AS geological_ages
           FROM (sampling_feature sf
             JOIN ( SELECT sfa.sampling_feature_num,
                    ant.annotation_text AS geological_age,
                    array_agg(((mcs.citation_code || '|'::text) || ant.data_source_num)) AS sources
                   FROM sampling_feature_annotation sfa,
                    annotation ant,
                    v_citation_summary mcs
                  WHERE ((ant.annotation_num = sfa.annotation_num) AND (ant.annotation_type_num = 26) AND (ant.data_source_num = mcs.citation_num))
                  GROUP BY sfa.sampling_feature_num, ant.annotation_text) f1 ON ((f1.sampling_feature_num = sf.sampling_feature_num)))
          GROUP BY sf.sampling_feature_num) sfga ON ((sfga.sampling_feature_num = vsp.sampling_feature_num)))
     LEFT JOIN (sampling_feature_annotation sfa6
     JOIN annotation ant6 ON (((ant6.annotation_num = sfa6.annotation_num) AND (ant6.annotation_type_num = 81)))) ON ((sfa6.sampling_feature_num = vsp.sampling_feature_num)))
     LEFT JOIN ( SELECT sf.sampling_feature_num,
            array_agg(DISTINCT (((f1.sample_name)::text || '^'::text) || (f1.sources)::text)) AS sample_names
           FROM (sampling_feature sf
             JOIN ( SELECT sfa.sampling_feature_num,
                    ant.annotation_text AS sample_name,
                    array_agg(((mcs.citation_code || '|'::text) || ant.data_source_num)) AS sources
                   FROM sampling_feature_annotation sfa,
                    annotation ant,
                    v_citation_summary mcs
                  WHERE ((ant.annotation_num = sfa.annotation_num) AND (ant.annotation_type_num = 11) AND (ant.data_source_num = mcs.citation_num))
                  GROUP BY sfa.sampling_feature_num, ant.annotation_text) f1 ON ((f1.sampling_feature_num = sf.sampling_feature_num)))
          GROUP BY sf.sampling_feature_num) antsn ON ((antsn.sampling_feature_num = vsp.sampling_feature_num)))
     LEFT JOIN ( SELECT sf.sampling_feature_num,
            array_agg(DISTINCT (((f1.sample_comment)::text || '^'::text) || (f1.sources)::text)) AS sample_comments
           FROM (sampling_feature sf
             JOIN ( SELECT sfa.sampling_feature_num,
                    ant.annotation_text AS sample_comment,
                    array_agg(((mcs.citation_code || '|'::text) || ant.data_source_num)) AS sources
                   FROM sampling_feature_annotation sfa,
                    annotation ant,
                    v_citation_summary mcs
                  WHERE ((ant.annotation_num = sfa.annotation_num) AND (ant.annotation_type_num = 22) AND (ant.data_source_num = mcs.citation_num))
                  GROUP BY sfa.sampling_feature_num, ant.annotation_text) f1 ON ((f1.sampling_feature_num = sf.sampling_feature_num)))
          GROUP BY sf.sampling_feature_num) antsc ON ((antsc.sampling_feature_num = vsp.sampling_feature_num)))
     LEFT JOIN (specimen
     JOIN material ON ((material.material_num = specimen.material_num))) ON ((specimen.sampling_feature_num = vsp.sampling_feature_num)))
     LEFT JOIN ( SELECT sftc.sampling_feature_num,
            array_agg(DISTINCT (((((((tc.rock_type)::text || '|'::text) || (tc.rock_class)::text) || '|'::text) || cia.citation_code) || '|'::text) || cia.citation_num)) AS taxon
           FROM ((sampling_feature_taxonomic_classifier sftc
             JOIN ( SELECT tc1.taxonomic_classifier_num AS rock_class_num,
                    tc2.taxonomic_classifier_common_name AS rock_type,
                    tc1.taxonomic_classifier_name AS rock_class
                   FROM (taxonomic_classifier tc1
                     JOIN taxonomic_classifier tc2 ON ((tc1.parent_taxonomic_classifier_num = tc2.taxonomic_classifier_num)))
                  WHERE ((tc1.taxonomic_classifier_type_cv)::text = 'Rock Class'::text)
                  ORDER BY tc2.taxonomic_classifier_name, tc1.taxonomic_classifier_name) tc ON ((sftc.taxonomic_classifier_num = tc.rock_class_num)))
             JOIN ( SELECT ci.citation_num,
                    (((p.last_name)::text || ','::text) || ci.publication_year) AS citation_code
                   FROM citation ci,
                    author_list al,
                    person p
                  WHERE ((ci.citation_num = al.citation_num) AND (al.person_num = p.person_num) AND (al.author_order = 1))) cia ON ((cia.citation_num = sftc.citation_num)))
          GROUP BY sftc.sampling_feature_num) sptc ON ((sptc.sampling_feature_num = vsp.sampling_feature_num)))
     LEFT JOIN ( SELECT sp.sampling_feature_code AS specimen_code,
            sp.sampling_feature_num AS specimen_num,
            st.station_num,
            st.station_code,
            st.geometry_text,
            st.elevation_min,
            st.elevation_max,
            st.geographic_location,
            st.location_precisions,
            st.tectonic_settings,
            st.expedition_num,
            st.expedition_code
           FROM related_feature rf,
            v_station_summary st,
            sampling_feature sp
          WHERE ((rf.related_sampling_feature_num = st.station_num) AND (sp.sampling_feature_num = rf.sampling_feature_num) AND (rf.relationship_type_num = 22))) sptr ON ((sptr.specimen_num = vsp.sampling_feature_num)))
     LEFT JOIN ( SELECT s.sampling_feature_code,
            s.sampling_feature_num,
            a.action_num,
            a.action_description,
            a.action_name,
            me.method_code,
            me.method_name
           FROM sampling_feature s,
            feature_action fa,
            action a,
            method me
          WHERE ((a.action_num = fa.action_num) AND (a.action_type_num = 21) AND (s.sampling_feature_type_num = 1) AND (fa.sampling_feature_num = s.sampling_feature_num) AND (a.method_num = me.method_num))) f2 ON ((f2.sampling_feature_num = vsp.sampling_feature_num)))
     LEFT JOIN ( SELECT sp1.sampling_feature_code AS specimen_code,
            sp1.sampling_feature_num AS specimen_num,
            array_agg(sa.sampling_feature_code ORDER BY sa.sampling_feature_code) AS analyzed_samples
           FROM related_feature rf1,
            sampling_feature sa,
            sampling_feature sp1
          WHERE ((rf1.related_sampling_feature_num = sp1.sampling_feature_num) AND (sa.sampling_feature_num = rf1.sampling_feature_num) AND (rf1.relationship_type_num = 9))
          GROUP BY sp1.sampling_feature_num) spar ON ((spar.specimen_num = vsp.sampling_feature_num)))
     LEFT JOIN ( SELECT array_agg(DISTINCT vdrc_sub.citation_num) AS citation_nums,
            array_agg(DISTINCT vdrc_sub.citation_code) AS citation_codes,
            array_agg(DISTINCT vdrc_sub.taxon) AS minerals,
            array_agg(DISTINCT vdrc_sub.material_code) AS material_codes,
            array_agg(DISTINCT vdrc_sub.material_name) AS material_names,
            vdrc_sub.specimen_num
           FROM ( SELECT DISTINCT v_dataset_result_summary.citation_num,
                    v_dataset_result_summary.citation_code,
                    v_dataset_result_summary.specimen_num,
                    v_dataset_result_summary.taxon,
                    v_dataset_result_summary.material_code,
                    v_dataset_result_summary.material_name
                   FROM v_dataset_result_summary) vdrc_sub
          GROUP BY vdrc_sub.specimen_num) vdrc ON ((vdrc.specimen_num = vsp.sampling_feature_num)));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views