Project

General

Profile

« Previous | Next » 

Revision 6543

schemas/vegbien.sql: analytical_*: Renamed plotName to locationName to match the new VegCore term name

View differences:

schemas/vegbien.my.sql
711 711
    `coordinateUncertaintyInMeters` double precision,
712 712
    geovalid int(1),
713 713
    `isNewWorld` int(1),
714
    `plotName` varchar(255) NOT NULL,
714
    `locationName` varchar(255) NOT NULL,
715 715
    `elevationInMeters` double precision,
716 716
    `plotArea_ha` double precision,
717 717
    `samplingProtocol` varchar(255),
......
755 755
    geovalid int(1),
756 756
    `isNewWorld` int(1),
757 757
    `projectID` varchar(255),
758
    `plotName` varchar(255) NOT NULL,
758
    `locationName` varchar(255) NOT NULL,
759 759
    `elevationInMeters` double precision,
760 760
    `plotArea_ha` double precision,
761 761
    `samplingProtocol` varchar(255),
......
4890 4890

  
4891 4891

  
4892 4892
--
4893
-- Name: analytical_aggregate_institutionCode_plotName_dateCollected_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
4893
-- Name: analytical_aggregate_institutionCode_locationName_dateColle_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
4894 4894
--
4895 4895

  
4896
CREATE INDEX `analytical_aggregate_institutionCode_plotName_dateCollected_idx` ON analytical_aggregate  (`institutionCode`, `plotName`, `dateCollected`);
4896
CREATE INDEX `analytical_aggregate_institutionCode_locationName_dateColle_idx` ON analytical_aggregate  (`institutionCode`, `locationName`, `dateCollected`);
4897 4897

  
4898 4898

  
4899 4899
--
......
4904 4904

  
4905 4905

  
4906 4906
--
4907
-- Name: analytical_stem_institutionCode_projectID_plotName_dateColl_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
4907
-- Name: analytical_stem_institutionCode_projectID_locationName_date_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
4908 4908
--
4909 4909

  
4910
CREATE INDEX `analytical_stem_institutionCode_projectID_plotName_dateColl_idx` ON analytical_stem  (`institutionCode`, `projectID`, `plotName`, `dateCollected`);
4910
CREATE INDEX `analytical_stem_institutionCode_projectID_locationName_date_idx` ON analytical_stem  (`institutionCode`, `projectID`, `locationName`, `dateCollected`);
4911 4911

  
4912 4912

  
4913 4913
--
schemas/vegbien.sql
909 909
CREATE TABLE analytical_aggregate AS SELECT * FROM analytical_aggregate_view LIMIT 0;
910 910

  
911 911
ALTER TABLE analytical_aggregate ALTER COLUMN "institutionCode" SET NOT NULL;
912
ALTER TABLE analytical_aggregate ALTER COLUMN "plotName" SET NOT NULL;
912
ALTER TABLE analytical_aggregate ALTER COLUMN "locationName" SET NOT NULL;
913 913

  
914
CREATE INDEX ON analytical_aggregate ("institutionCode", "plotName", "dateCollected" );
914
CREATE INDEX ON analytical_aggregate ("institutionCode", "locationName", "dateCollected" );
915 915
$$;
916 916

  
917 917

  
......
926 926
CREATE TABLE analytical_stem AS SELECT * FROM analytical_stem_view LIMIT 0;
927 927

  
928 928
ALTER TABLE analytical_stem ALTER COLUMN "institutionCode" SET NOT NULL;
929
ALTER TABLE analytical_stem ALTER COLUMN "plotName" SET NOT NULL;
929
ALTER TABLE analytical_stem ALTER COLUMN "locationName" SET NOT NULL;
930 930

  
931 931
CREATE INDEX ON analytical_stem ("institutionCode", "occurrenceID" );
932 932
CREATE INDEX ON analytical_stem ("institutionCode", "subInstitutionCode", "collectionCode", "catalogNumber" );
933
CREATE INDEX ON analytical_stem ("institutionCode", "projectID", "plotName", "dateCollected" );
933
CREATE INDEX ON analytical_stem ("institutionCode", "projectID", "locationName", "dateCollected" );
934 934
$$;
935 935

  
936 936

  
......
1447 1447
    "coordinateUncertaintyInMeters" double precision,
1448 1448
    geovalid boolean,
1449 1449
    "isNewWorld" boolean,
1450
    "plotName" text NOT NULL,
1450
    "locationName" text NOT NULL,
1451 1451
    "elevationInMeters" double precision,
1452 1452
    "plotArea_ha" double precision,
1453 1453
    "samplingProtocol" text,
......
1491 1491
    geovalid boolean,
1492 1492
    "isNewWorld" boolean,
1493 1493
    "projectID" text,
1494
    "plotName" text NOT NULL,
1494
    "locationName" text NOT NULL,
1495 1495
    "elevationInMeters" double precision,
1496 1496
    "plotArea_ha" double precision,
1497 1497
    "samplingProtocol" text,
......
1529 1529
--
1530 1530

  
1531 1531
CREATE VIEW analytical_aggregate_view AS
1532
    SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem.geovalid, analytical_stem."isNewWorld", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem.threatened, analytical_stem."identifiedBy", analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis", sum(analytical_stem."coverPercent") AS "coverPercent", count(analytical_stem."diameterBreastHeight_cm") AS "individualCount", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision))) AS "individualCount_1cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision))) AS "individualCount_2_5cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((10)::double precision))) AS "individualCount_10cm_or_more" FROM analytical_stem GROUP BY analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem.geovalid, analytical_stem."isNewWorld", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem.threatened, analytical_stem."identifiedBy", analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis";
1532
    SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem.geovalid, analytical_stem."isNewWorld", analytical_stem."locationName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem.threatened, analytical_stem."identifiedBy", analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis", sum(analytical_stem."coverPercent") AS "coverPercent", count(analytical_stem."diameterBreastHeight_cm") AS "individualCount", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision))) AS "individualCount_1cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision))) AS "individualCount_2_5cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((10)::double precision))) AS "individualCount_10cm_or_more" FROM analytical_stem GROUP BY analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem.geovalid, analytical_stem."isNewWorld", analytical_stem."locationName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem.threatened, analytical_stem."identifiedBy", analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis";
1533 1533

  
1534 1534

  
1535 1535
--
......
2334 2334
--
2335 2335

  
2336 2336
CREATE VIEW analytical_stem_view AS
2337
    SELECT source.shortname AS "institutionCode", sourcename.name AS "subInstitutionCode", specimenreplicate.collectioncode_dwc AS "collectionCode", specimenreplicate.catalognumber_dwc AS "catalogNumber", specimenreplicate.sourceaccessioncode AS "occurrenceID", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", coordinates.coordsaccuracy_m AS "coordinateUncertaintyInMeters", canon_place.geovalid, "newWorldCountries"."isNewWorld", project.sourceaccessioncode AS "projectID", COALESCE(location.sourceaccessioncode, location.authorlocationcode) AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", COALESCE(locationevent.obsstartdate, parent_event.obsstartdate, aggregateoccurrence.collectiondate) AS "dateCollected", family_higher_plant_group.higher_plant_group AS "higherPlantGroup", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.taxonname) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[COALESCE(accepted_taxonverbatim.genus, accepted_taxonverbatim.family), COALESCE(accepted_taxonverbatim.specific_epithet, parsed_taxonverbatim.morphospecies)], ' '::text), ''::text) AS "speciesBinomialWithMorphospecies", NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", (threatened_taxonlabel.taxonlabel_id IS NOT NULL) AS threatened, identifiedby.fullname AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", ((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, location.iscultivated)) AS cultivated, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis WHEN (location.iscultivated IS NOT NULL) THEN NULL::text ELSE NULL::text END AS "cultivatedBasis", collector.fullname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber", _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.sourceaccessioncode AS "taxonOccurrenceID", taxonoccurrence.authortaxoncode AS "authorTaxonCode", plantobservation.sourceaccessioncode AS "individualID", stemobservation.authorstemcode AS "authorStemCode" FROM ((((((((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationevent USING (location_id)) LEFT JOIN location parent_location ON ((parent_location.location_id = location.parent_id))) LEFT JOIN locationplace ON ((locationplace.location_id = COALESCE(parent_location.location_id, location.location_id)))) LEFT JOIN place datasource_place USING (place_id)) LEFT JOIN place canon_place ON ((canon_place.place_id = datasource_place.canon_place_id))) LEFT JOIN coordinates ON ((coordinates.coordinates_id = canon_place.coordinates_id))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."GADM country" = canon_place.country))) LEFT JOIN "newWorld"."newWorldCountries" ON (("newWorldCountries"."isoCode" = iso_code_gadm."2-digit iso code"))) LEFT JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN locationevent parent_event ON ((parent_event.locationevent_id = locationevent.parent_id))) LEFT JOIN project ON ((project.project_id = COALESCE(locationevent.project_id, parent_event.project_id)))) LEFT JOIN method ON ((method.method_id = COALESCE(locationevent.method_id, parent_event.method_id)))) LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) LEFT JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) LEFT JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) LEFT JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT 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))) LEFT JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = accepted_taxonverbatim.family))) LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = accepted_taxonverbatim.family) AND (cultivated_family_locations.country = canon_place.country)))) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) LEFT JOIN sourcename ON ((sourcename.sourcename_id = specimenreplicate.institution_id))) WHERE COALESCE(taxondetermination.iscurrent, true);
2337
    SELECT source.shortname AS "institutionCode", sourcename.name AS "subInstitutionCode", specimenreplicate.collectioncode_dwc AS "collectionCode", specimenreplicate.catalognumber_dwc AS "catalogNumber", specimenreplicate.sourceaccessioncode AS "occurrenceID", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", coordinates.coordsaccuracy_m AS "coordinateUncertaintyInMeters", canon_place.geovalid, "newWorldCountries"."isNewWorld", project.sourceaccessioncode AS "projectID", COALESCE(location.sourceaccessioncode, location.authorlocationcode) AS "locationName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", COALESCE(locationevent.obsstartdate, parent_event.obsstartdate, aggregateoccurrence.collectiondate) AS "dateCollected", family_higher_plant_group.higher_plant_group AS "higherPlantGroup", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.taxonname) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[COALESCE(accepted_taxonverbatim.genus, accepted_taxonverbatim.family), COALESCE(accepted_taxonverbatim.specific_epithet, parsed_taxonverbatim.morphospecies)], ' '::text), ''::text) AS "speciesBinomialWithMorphospecies", NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", (threatened_taxonlabel.taxonlabel_id IS NOT NULL) AS threatened, identifiedby.fullname AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", ((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, location.iscultivated)) AS cultivated, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis WHEN (location.iscultivated IS NOT NULL) THEN NULL::text ELSE NULL::text END AS "cultivatedBasis", collector.fullname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber", _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.sourceaccessioncode AS "taxonOccurrenceID", taxonoccurrence.authortaxoncode AS "authorTaxonCode", plantobservation.sourceaccessioncode AS "individualID", stemobservation.authorstemcode AS "authorStemCode" FROM ((((((((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationevent USING (location_id)) LEFT JOIN location parent_location ON ((parent_location.location_id = location.parent_id))) LEFT JOIN locationplace ON ((locationplace.location_id = COALESCE(parent_location.location_id, location.location_id)))) LEFT JOIN place datasource_place USING (place_id)) LEFT JOIN place canon_place ON ((canon_place.place_id = datasource_place.canon_place_id))) LEFT JOIN coordinates ON ((coordinates.coordinates_id = canon_place.coordinates_id))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."GADM country" = canon_place.country))) LEFT JOIN "newWorld"."newWorldCountries" ON (("newWorldCountries"."isoCode" = iso_code_gadm."2-digit iso code"))) LEFT JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN locationevent parent_event ON ((parent_event.locationevent_id = locationevent.parent_id))) LEFT JOIN project ON ((project.project_id = COALESCE(locationevent.project_id, parent_event.project_id)))) LEFT JOIN method ON ((method.method_id = COALESCE(locationevent.method_id, parent_event.method_id)))) LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) LEFT JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) LEFT JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) LEFT JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT 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))) LEFT JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = accepted_taxonverbatim.family))) LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = accepted_taxonverbatim.family) AND (cultivated_family_locations.country = canon_place.country)))) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) LEFT JOIN sourcename ON ((sourcename.sourcename_id = specimenreplicate.institution_id))) WHERE COALESCE(taxondetermination.iscurrent, true);
2338 2338

  
2339 2339

  
2340 2340
--
......
5956 5956

  
5957 5957

  
5958 5958
--
5959
-- Name: analytical_aggregate_institutionCode_plotName_dateCollected_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
5959
-- Name: analytical_aggregate_institutionCode_locationName_dateColle_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
5960 5960
--
5961 5961

  
5962
CREATE INDEX "analytical_aggregate_institutionCode_plotName_dateCollected_idx" ON analytical_aggregate USING btree ("institutionCode", "plotName", "dateCollected");
5962
CREATE INDEX "analytical_aggregate_institutionCode_locationName_dateColle_idx" ON analytical_aggregate USING btree ("institutionCode", "locationName", "dateCollected");
5963 5963

  
5964 5964

  
5965 5965
--
......
5970 5970

  
5971 5971

  
5972 5972
--
5973
-- Name: analytical_stem_institutionCode_projectID_plotName_dateColl_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
5973
-- Name: analytical_stem_institutionCode_projectID_locationName_date_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
5974 5974
--
5975 5975

  
5976
CREATE INDEX "analytical_stem_institutionCode_projectID_plotName_dateColl_idx" ON analytical_stem USING btree ("institutionCode", "projectID", "plotName", "dateCollected");
5976
CREATE INDEX "analytical_stem_institutionCode_projectID_locationName_date_idx" ON analytical_stem USING btree ("institutionCode", "projectID", "locationName", "dateCollected");
5977 5977

  
5978 5978

  
5979 5979
--

Also available in: Unified diff