v_station_summary_with_geom

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

station_num

int4

√

null

station_code

varchar

√

null

igsn

varchar

256

√

null

geometry_text

text

2147483647

√

null

geom

geometry

2147483647

√

null

elevation_min

float8

17,17

√

null

elevation_max

float8

17,17

√

null

tectonic_settings

_varchar

2147483647

√

null

location_precisions

_varchar

2147483647

√

null

nav_method

varchar

2000

√

null

geographic_location

_text

2147483647

√

null

expedition_num

int4

√

null

expedition_code

varchar

256

√

null

specimens

_text

2147483647

√

null

citations

_text

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT vst.sampling_feature_num AS station_num,
    vst.sampling_feature_code AS station_code,
    sfi.igsn,
    st_astext(vst.feature_geometry) AS geometry_text,
    vst.feature_geometry AS geom,
    vst.elevation_min,
    vst.elevation_max,
    sft.tectonic_settings,
    sflp.location_precisions,
    ant2.annotation_text AS nav_method,
    f.geographic_location,
    f2.action_num AS expedition_num,
    f2.action_name AS expedition_code,
    f3.specimens,
    mdrc.citations
   FROM ((((((((v_stations vst
     LEFT JOIN ( SELECT vst1.sampling_feature_num,
            sfei.sampling_feature_external_id AS igsn
           FROM v_stations vst1,
            sampling_feature_external_identifier sfei
          WHERE ((sfei.sample_feature_num = vst1.sampling_feature_num) AND (sfei.external_identifier_system_num = 2))) sfi ON ((sfi.sampling_feature_num = vst.sampling_feature_num)))
     LEFT JOIN ( SELECT sfa1.sampling_feature_num,
            array_agg(ant1.annotation_text) AS tectonic_settings
           FROM sampling_feature_annotation sfa1,
            annotation ant1
          WHERE ((ant1.annotation_num = sfa1.annotation_num) AND (ant1.annotation_type_num = 62))
          GROUP BY sfa1.sampling_feature_num) sft ON ((sft.sampling_feature_num = vst.sampling_feature_num)))
     LEFT JOIN (sampling_feature_annotation sfa2
     JOIN annotation ant2 ON (((ant2.annotation_num = sfa2.annotation_num) AND (ant2.annotation_type_num = 84)))) ON ((sfa2.sampling_feature_num = vst.sampling_feature_num)))
     LEFT JOIN ( SELECT sfa3.sampling_feature_num,
            array_agg(DISTINCT ant3.annotation_text) AS location_precisions
           FROM sampling_feature_annotation sfa3,
            annotation ant3
          WHERE ((ant3.annotation_num = sfa3.annotation_num) AND (ant3.annotation_type_num = 78))
          GROUP BY sfa3.sampling_feature_num) sflp ON ((sflp.sampling_feature_num = vst.sampling_feature_num)))
     LEFT JOIN ( SELECT s.sampling_feature_num,
            array_agg((((ft.feature_of_interest_type_name)::text || '|'::text) || (fv.feature_of_interest_cv_name)::text)) AS geographic_location
           FROM (((sampling_feature s
             JOIN feature_of_interest fi ON (((fi.sampling_feature_num = s.sampling_feature_num) AND (fi.feature_of_interest_cv_num <> 1))))
             JOIN feature_of_interest_type ft ON ((ft.feature_of_interest_type_num = fi.feature_of_interest_type_num)))
             JOIN feature_of_interest_cv fv ON ((fv.feature_of_interest_cv_num = fi.feature_of_interest_cv_num)))
          GROUP BY s.sampling_feature_num) f ON ((f.sampling_feature_num = vst.sampling_feature_num)))
     LEFT JOIN ( SELECT s.sampling_feature_num,
            a.action_num,
            a.action_description,
            a.action_name
           FROM sampling_feature s,
            feature_action fa,
            action a
          WHERE ((a.action_num = fa.action_num) AND (a.action_type_num = 11) AND (fa.sampling_feature_num = s.sampling_feature_num))) f2 ON ((f2.sampling_feature_num = vst.sampling_feature_num)))
     LEFT JOIN ( SELECT sp.sampling_feature_num AS station_num,
            array_agg(DISTINCT ((s.sampling_feature_num || '|'::text) || (s.sampling_feature_code)::text)) AS specimens
           FROM related_feature rf,
            sampling_feature s,
            sampling_feature sp
          WHERE ((rf.related_sampling_feature_num = sp.sampling_feature_num) AND (s.sampling_feature_num = rf.sampling_feature_num) AND (rf.relationship_type_num = 22))
          GROUP BY sp.sampling_feature_num) f3 ON ((f3.station_num = vst.sampling_feature_num)))
     LEFT JOIN ( SELECT mdrs.station_num,
            array_agg(DISTINCT ((mdrs.citation_num || '|'::text) || mdrs.citation_code)) AS citations
           FROM v_dataset_result_summary mdrs
          GROUP BY mdrs.station_num) mdrc ON ((mdrc.station_num = vst.sampling_feature_num)));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views