-- Name: locationcoords; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE locationcoords (

    locationcoords_id int(11) NOT NULL,

    location_id int(11) NOT NULL,

    latitude_deg double precision,

    longitude_deg double precision,

    verbatimlatitude text,

    verbatimlongitude text,

    verbatimcoordinates text,

    footprintgeometry_dwc text,

    coordsaccuracy_deg double precision,

    identifier_id int(11),

    determinationdate date,

    isoriginal int(1) DEFAULT false NOT NULL,

    iscurrent int(1) DEFAULT false NOT NULL,

    calculated int(1)

);

--

-- Name: COLUMN locationcoords.footprintgeometry_dwc; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN locationcoords.coordsaccuracy_deg; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: coordinates; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE coordinates (

    coordinates_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    latitude_deg double precision NOT NULL,

    longitude_deg double precision NOT NULL,

    verbatimlatitude text,

    verbatimlongitude text,

    verbatimcoordinates text,

    footprintgeometry_dwc text,

    coordsaccuracy_deg double precision

);

--

-- Name: COLUMN coordinates.footprintgeometry_dwc; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN coordinates.coordsaccuracy_deg; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: locationcoords; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE locationcoords (

    locationcoords_id integer NOT NULL,

    location_id integer NOT NULL,

    latitude_deg double precision,

    longitude_deg double precision,

    verbatimlatitude text,

    verbatimlongitude text,

    verbatimcoordinates text,

    footprintgeometry_dwc text,

    coordsaccuracy_deg double precision,

    identifier_id integer,

    determinationdate date,

    isoriginal boolean DEFAULT false NOT NULL,

    iscurrent boolean DEFAULT false NOT NULL,

    calculated boolean

);

--

-- Name: COLUMN locationcoords.footprintgeometry_dwc; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN locationcoords.footprintgeometry_dwc IS 'DwC''s footprintWKT field. "A Well-Known Text (WKT) representation of the shape (footprint, geometry) that defines the Location" (http://rs.tdwg.org/dwc/terms/#footprintWKT).';

--

-- Name: COLUMN locationcoords.coordsaccuracy_deg; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN locationcoords.coordsaccuracy_deg IS 'Accuracy of latitude/longitude or footprint geometry, in meters. This should generally be at least 1 m, which is the accuracy of the best GPSes.';

--

    SELECT datasource.organizationname AS "institutionCode", place.country, place.stateprovince AS "stateProvince", place.county, locationcoords.latitude_deg AS "decimalLatitude", locationcoords.longitude_deg AS "decimalLongitude", location.sourceaccessioncode AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", aggregateoccurrence.collectiondate AS "dateCollected", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.binomial) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", taxonoccurrence.iscultivated AS cultivated, taxonoccurrence.cultivatedbasis AS "cultivatedBasis", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", taxonoccurrence.verbatimcollectorname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM (((((((((((((((((((party datasource JOIN location ON ((location.creator_id = datasource.party_id))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place USING (place_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) WHERE ((datasource.creator_id = datasource.party_id) AND (NOT taxondetermination.isoriginal));

-- Name: coordinates; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE coordinates (

    coordinates_id integer NOT NULL,

    creator_id integer NOT NULL,

    latitude_deg double precision NOT NULL,

    longitude_deg double precision NOT NULL,

    verbatimlatitude text,

    verbatimlongitude text,

    verbatimcoordinates text,

    footprintgeometry_dwc text,

    coordsaccuracy_deg double precision

);

--

-- Name: COLUMN coordinates.footprintgeometry_dwc; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN coordinates.footprintgeometry_dwc IS 'DwC''s footprintWKT field. "A Well-Known Text (WKT) representation of the shape (footprint, geometry) that defines the Location" (http://rs.tdwg.org/dwc/terms/#footprintWKT).';

--

-- Name: COLUMN coordinates.coordsaccuracy_deg; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN coordinates.coordsaccuracy_deg IS 'Accuracy of latitude/longitude or footprint geometry, in meters. This should generally be at least 1 m, which is the accuracy of the best GPSes.';

--