Revision 13836
Added by Aaron Marcuse-Kubitza over 10 years ago
| vegbien.my.sql | ||
|---|---|---|
| 3175 | 3175 |
-- |
| 3176 | 3176 |
|
| 3177 | 3177 |
CREATE TABLE analytical_stem ( |
| 3178 |
datasource varchar(255) NOT NULL,
|
|
| 3179 |
country varchar(255),
|
|
| 3180 |
`stateProvince` varchar(255), |
|
| 3181 |
county varchar(255),
|
|
| 3182 |
locality varchar(255),
|
|
| 3183 |
`decimalLatitude` double, |
|
| 3184 |
`decimalLongitude` double, |
|
| 3185 |
`coordinateUncertaintyInMeters` double, |
|
| 3186 |
`coordinateSource_bien` varchar(255),
|
|
| 3187 |
`georeferenceProtocol_bien` varchar(255),
|
|
| 3178 |
`datasource__@Brad__.identifier_examples@vegpath.org` varchar(255) NOT NULL,
|
|
| 3179 |
`country__@DwC__@vegpath.org` varchar(255),
|
|
| 3180 |
`stateProvince__@DwC__@vegpath.org` varchar(255),
|
|
| 3181 |
`county__@DwC__@vegpath.org` varchar(255),
|
|
| 3182 |
`locality__@DwC__@vegpath.org` varchar(255),
|
|
| 3183 |
`decimalLatitude__@DwC__@vegpath.org` double,
|
|
| 3184 |
`decimalLongitude__@DwC__@vegpath.org` double,
|
|
| 3185 |
`coordinateUncertaintyInMeters__@DwC__@vegpath.org` double,
|
|
| 3186 |
`georeferenceSources__@DwC__@vegpath.org` coordinatesource,
|
|
| 3187 |
`georeferenceProtocol__@DwC__@vegpath.org` varchar(255),
|
|
| 3188 | 3188 |
geovalid_bien int(11), |
| 3189 | 3189 |
`isNewWorld_bien` int(11), |
| 3190 |
`projectID` varchar(255), |
|
| 3191 |
project_contributors varchar(255),
|
|
| 3192 |
`locationID` varchar(255) NOT NULL, |
|
| 3193 |
`locationName` varchar(255),
|
|
| 3194 |
subplot varchar(255),
|
|
| 3190 |
`projectID__@VegX__.plotObservation@vegpath.org` varchar(255),
|
|
| 3191 |
`projectContributor[s]__@VegBank__@vegpath.org` varchar(255),
|
|
| 3192 |
`locationID__@DwC__@vegpath.org` varchar(255) NOT NULL,
|
|
| 3193 |
`plotName__@VegX__.plot@vegpath.org` varchar(255),
|
|
| 3194 |
`subplot__@SALVIAS__.Plot_data@vegpath.org` varchar(255),
|
|
| 3195 | 3195 |
location__cultivated__bien int(1), |
| 3196 |
locationevent__pkey int(11),
|
|
| 3197 |
`eventDate` date, |
|
| 3198 |
`elevationInMeters` double,
|
|
| 3199 |
`slopeAspect` double, |
|
| 3200 |
`slopeGradient` double, |
|
| 3201 |
`plotArea_ha` double,
|
|
| 3202 |
`samplingProtocol` varchar(255), |
|
| 3203 |
`temperature_C` double,
|
|
| 3204 |
precipitation_m double,
|
|
| 3205 |
stratum__name varchar(255),
|
|
| 3206 |
communities varchar(255),
|
|
| 3207 |
plot__collectors varchar(255),
|
|
| 3208 |
`specimenHolderInstitutions` varchar(255),
|
|
| 3209 |
collection varchar(255),
|
|
| 3210 |
`accessionNumber` varchar(255),
|
|
| 3211 |
`occurrenceID` varchar(255), |
|
| 3212 |
`recordedBy` varchar(255), |
|
| 3213 |
`recordNumber` varchar(255), |
|
| 3214 |
`dateCollected` date,
|
|
| 3215 |
family_verbatim varchar(255),
|
|
| 3216 |
`scientificName_verbatim` varchar(255),
|
|
| 3217 |
`identifiedBy` varchar(255), |
|
| 3218 |
`dateIdentified` date, |
|
| 3219 |
`identificationRemarks` varchar(255), |
|
| 3220 |
family_matched varchar(255),
|
|
| 3221 |
`taxonName_matched` varchar(255),
|
|
| 3222 |
`scientificNameAuthorship_matched` varchar(255),
|
|
| 3223 |
`higherPlantGroup_bien` varchar(255),
|
|
| 3224 |
taxonomic_status varchar(255),
|
|
| 3196 |
`locationevent.locationevent_id__@VegBIEN__.public@vegpath.org` int(11),
|
|
| 3197 |
`eventDate__@DwC__@vegpath.org` date,
|
|
| 3198 |
`(-minimum-)ElevationInMeters__@DwC__@vegpath.org` double,
|
|
| 3199 |
`slopeAspect[_deg]__@VegX__.plot@vegpath.org` double,
|
|
| 3200 |
`slopeGradient[_deg]__@VegX__.plot@vegpath.org` double,
|
|
| 3201 |
`plot.area[_ha]__@VegX__@vegpath.org` double,
|
|
| 3202 |
`samplingProtocol__@DwC__@vegpath.org` varchar(255),
|
|
| 3203 |
`temperature[_C]__@VegX__.plotObs.obsCond@vegpath.org` double,
|
|
| 3204 |
`precip_mm__@SALVIAS__.Plot_metadata@vegpath.org` double,
|
|
| 3205 |
`stratumName__@VegX__.stratum@vegpath.org` varchar(255),
|
|
| 3206 |
`communityConcept.name__@VegX__.communityDet@vegpath.org` varchar(255),
|
|
| 3207 |
`observationContributor[s]__@VegBank__@vegpath.org` varchar(255),
|
|
| 3208 |
`[custodial_]institutionCode[s]__@DwC__@vegpath.org` varchar(255),
|
|
| 3209 |
`collectionCode__@DwC__@vegpath.org` varchar(255),
|
|
| 3210 |
`catalogNumber__@DwC__@vegpath.org` varchar(255),
|
|
| 3211 |
`occurrenceID__@DwC__@vegpath.org` varchar(255),
|
|
| 3212 |
`recordedBy__@DwC__@vegpath.org` varchar(255),
|
|
| 3213 |
`recordNumber__@DwC__@vegpath.org` varchar(255),
|
|
| 3214 |
`(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org` date,
|
|
| 3215 |
`[verbatim_]family__@DwC__@vegpath.org` varchar(255),
|
|
| 3216 |
`[verbatim_]scientificName__@DwC__@vegpath.org` varchar(255),
|
|
| 3217 |
`identifiedBy__@DwC__@vegpath.org` varchar(255),
|
|
| 3218 |
`dateIdentified__@DwC__@vegpath.org` date,
|
|
| 3219 |
`identificationRemarks__@DwC__@vegpath.org` varchar(255),
|
|
| 3220 |
`Family_matched__@TNRS__@vegpath.org` varchar(255),
|
|
| 3221 |
`Name_matched__@TNRS__@vegpath.org` varchar(255),
|
|
| 3222 |
`Name_matched_author__@TNRS__@vegpath.org` varchar(255),
|
|
| 3223 |
`[higher_plant_group~]higherClassification__@DwC__@vegpath.org` higher_plant_group,
|
|
| 3224 |
`taxonomicStatus__@DwC__@vegpath.org` varchar(255),
|
|
| 3225 | 3225 |
scrubbed_family varchar(255), |
| 3226 | 3226 |
scrubbed_genus varchar(255), |
| 3227 | 3227 |
scrubbed_specific_epithet varchar(255), |
| ... | ... | |
| 3229 | 3229 |
scrubbed_taxon_name_no_author varchar(255), |
| 3230 | 3230 |
scrubbed_author varchar(255), |
| 3231 | 3231 |
scrubbed_taxon_name_with_author varchar(255), |
| 3232 |
scrubbed_morphospecies_binomial varchar(255),
|
|
| 3233 |
`growthForm` varchar(255),
|
|
| 3234 |
`reproductiveCondition` varchar(255), |
|
| 3232 |
`speciesBinomialWithMorphospecies__@VegCore__@vegpath.org` varchar(255),
|
|
| 3233 |
`[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org` growthform,
|
|
| 3234 |
`reproductiveCondition__@DwC__@vegpath.org` varchar(255),
|
|
| 3235 | 3235 |
threatened_bien int(11), |
| 3236 | 3236 |
cultivated_bien int(11), |
| 3237 | 3237 |
`cultivatedBasis_bien` varchar(255), |
| 3238 |
`occurrenceRemarks` varchar(255), |
|
| 3239 |
`coverPercent` double, |
|
| 3240 |
`diameterBreastHeight_cm` double,
|
|
| 3241 |
height_m double,
|
|
| 3242 |
tag varchar(255),
|
|
| 3243 |
`organismX_m` double,
|
|
| 3244 |
`organismY_m` double,
|
|
| 3245 |
`taxonOccurrenceID` varchar(255),
|
|
| 3246 |
`authorTaxonCode` varchar(255),
|
|
| 3247 |
`aggregateOrganismObservationID` varchar(255),
|
|
| 3248 |
`individualObservationID` varchar(255),
|
|
| 3249 |
`individualCode` varchar(255),
|
|
| 3250 |
`individualCount` int(11), |
|
| 3251 |
`authorStemCode` varchar(255),
|
|
| 3252 |
taxon_occurrence__pkey int(11) NOT NULL
|
|
| 3238 |
`occurrenceRemarks__@DwC__@vegpath.org` varchar(255),
|
|
| 3239 |
`coverPercent__@VegX__.attribute.ordinal@vegpath.org` double,
|
|
| 3240 |
`stem_dbh[_cm]__@SALVIAS__.Plot_data@vegpath.org` double,
|
|
| 3241 |
`stemHeight[_m]__@VegBank__.stemCount@vegpath.org` double,
|
|
| 3242 |
`[tag=]identificationLabel__@VegX__.individual@vegpath.org` varchar(255),
|
|
| 3243 |
`relativeX[_m]__@VegX__.individualOrganismObs@vegpath.org` double,
|
|
| 3244 |
`relativeY[_m]__@VegX__.individualOrganismObs@vegpath.org` double,
|
|
| 3245 |
`taxonObservation[.id]__@VegBank__@vegpath.org` varchar(255),
|
|
| 3246 |
`taxonNameUsageConcept.authorCode__@VegX__@vegpath.org` varchar(255),
|
|
| 3247 |
`aggregateOrganismObservation.id__@VegX__@vegpath.org` varchar(255),
|
|
| 3248 |
`individualOrganismObservation.id__@VegX__@vegpath.org` varchar(255),
|
|
| 3249 |
`individualID__@DwC__@vegpath.org` varchar(255),
|
|
| 3250 |
`individualCount__@DwC__@vegpath.org` int(11),
|
|
| 3251 |
`stemCode__@VegBank__.stemLocation@vegpath.org` varchar(255),
|
|
| 3252 |
`TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org` int(11) NOT NULL
|
|
| 3253 | 3253 |
); |
| 3254 | 3254 |
|
| 3255 | 3255 |
|
| ... | ... | |
| 6788 | 6788 |
|
| 6789 | 6789 |
|
| 6790 | 6790 |
-- |
| 6791 |
-- Name: taxon_occurrence__pkey; Type: DEFAULT; Schema: public; Owner: -
|
|
| 6791 |
-- Name: TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org; Type: DEFAULT; Schema: public; Owner: -
|
|
| 6792 | 6792 |
-- |
| 6793 | 6793 |
|
| 6794 | 6794 |
|
| ... | ... | |
| 9874 | 9874 |
-- |
| 9875 | 9875 |
|
| 9876 | 9876 |
ALTER TABLE analytical_stem |
| 9877 |
ADD CONSTRAINT analytical_stem_pkey PRIMARY KEY (taxon_occurrence__pkey);
|
|
| 9877 |
ADD CONSTRAINT analytical_stem_pkey PRIMARY KEY (`TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org`);
|
|
| 9878 | 9878 |
|
| 9879 | 9879 |
|
| 9880 | 9880 |
-- |
| ... | ... | |
| 10568 | 10568 |
-- Name: analytical_stem_datasource_occurrenceID_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 10569 | 10569 |
-- |
| 10570 | 10570 |
|
| 10571 |
CREATE INDEX `analytical_stem_datasource_occurrenceID_idx` ON analytical_stem (datasource, `occurrenceID`);
|
|
| 10571 |
CREATE INDEX `analytical_stem_datasource_occurrenceID_idx` ON analytical_stem (`datasource__@Brad__.identifier_examples@vegpath.org`, `occurrenceID__@DwC__@vegpath.org`);
|
|
| 10572 | 10572 |
|
| 10573 | 10573 |
|
| 10574 | 10574 |
-- |
| 10575 | 10575 |
-- Name: analytical_stem_datasource_projectID_locationID_eventDate_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 10576 | 10576 |
-- |
| 10577 | 10577 |
|
| 10578 |
CREATE INDEX `analytical_stem_datasource_projectID_locationID_eventDate_idx` ON analytical_stem (datasource, `projectID`, `locationID`, `eventDate`);
|
|
| 10578 |
CREATE INDEX `analytical_stem_datasource_projectID_locationID_eventDate_idx` ON analytical_stem (`datasource__@Brad__.identifier_examples@vegpath.org`, `projectID__@VegX__.plotObservation@vegpath.org`, `locationID__@DwC__@vegpath.org`, `eventDate__@DwC__@vegpath.org`);
|
|
| 10579 | 10579 |
|
| 10580 | 10580 |
|
| 10581 | 10581 |
-- |
| 10582 | 10582 |
-- Name: analytical_stem_datasource_specimenHolderInstitutions_colle_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 10583 | 10583 |
-- |
| 10584 | 10584 |
|
| 10585 |
CREATE INDEX `analytical_stem_datasource_specimenHolderInstitutions_colle_idx` ON analytical_stem (datasource, `specimenHolderInstitutions`, collection, `accessionNumber`);
|
|
| 10585 |
CREATE INDEX `analytical_stem_datasource_specimenHolderInstitutions_colle_idx` ON analytical_stem (`datasource__@Brad__.identifier_examples@vegpath.org`, `[custodial_]institutionCode[s]__@DwC__@vegpath.org`, `collectionCode__@DwC__@vegpath.org`, `catalogNumber__@DwC__@vegpath.org`);
|
|
| 10586 | 10586 |
|
| 10587 | 10587 |
|
| 10588 | 10588 |
-- |
| 10589 | 10589 |
-- Name: analytical_stem_scrubbed_morphospecies_binomial_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 10590 | 10590 |
-- |
| 10591 | 10591 |
|
| 10592 |
CREATE INDEX analytical_stem_scrubbed_morphospecies_binomial_idx ON analytical_stem (scrubbed_morphospecies_binomial);
|
|
| 10592 |
CREATE INDEX analytical_stem_scrubbed_morphospecies_binomial_idx ON analytical_stem (`speciesBinomialWithMorphospecies__@VegCore__@vegpath.org`);
|
|
| 10593 | 10593 |
|
| 10594 | 10594 |
|
| 10595 | 10595 |
-- |
Also available in: Unified diff
fix: schemas/public_.sql: analytical_stem_view derived and related views: applied data dictionary renamings, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#apply-data-dictionary-renamings-to-database but with the current columns of analytical_stem as the left-hand column