Revision 13687
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 4201 | 4201 |
|
| 4202 | 4202 |
|
| 4203 | 4203 |
-- |
| 4204 |
-- Name: COLUMN analytical_stem_view.country; Type: COMMENT; Schema: public; Owner: -
|
|
| 4204 |
-- Name: COLUMN analytical_stem_view.`country@DwC@vegpath.org`; Type: COMMENT; Schema: public; Owner: -
|
|
| 4205 | 4205 |
-- |
| 4206 | 4206 |
|
| 4207 | 4207 |
|
| 4208 | 4208 |
|
| 4209 | 4209 |
|
| 4210 | 4210 |
-- |
| 4211 |
-- Name: COLUMN analytical_stem_view.`stateProvince`; Type: COMMENT; Schema: public; Owner: - |
|
| 4212 |
-- |
|
| 4213 |
|
|
| 4214 |
|
|
| 4215 |
|
|
| 4216 |
|
|
| 4217 |
-- |
|
| 4218 |
-- Name: COLUMN analytical_stem_view.county; Type: COMMENT; Schema: public; Owner: - |
|
| 4219 |
-- |
|
| 4220 |
|
|
| 4221 |
|
|
| 4222 |
|
|
| 4223 |
|
|
| 4224 |
-- |
|
| 4225 |
-- Name: COLUMN analytical_stem_view.locality; Type: COMMENT; Schema: public; Owner: - |
|
| 4226 |
-- |
|
| 4227 |
|
|
| 4228 |
|
|
| 4229 |
|
|
| 4230 |
|
|
| 4231 |
-- |
|
| 4232 |
-- Name: COLUMN analytical_stem_view.`decimalLatitude`; Type: COMMENT; Schema: public; Owner: - |
|
| 4233 |
-- |
|
| 4234 |
|
|
| 4235 |
|
|
| 4236 |
|
|
| 4237 |
|
|
| 4238 |
-- |
|
| 4239 |
-- Name: COLUMN analytical_stem_view.`decimalLongitude`; Type: COMMENT; Schema: public; Owner: - |
|
| 4240 |
-- |
|
| 4241 |
|
|
| 4242 |
|
|
| 4243 |
|
|
| 4244 |
|
|
| 4245 |
-- |
|
| 4246 |
-- Name: COLUMN analytical_stem_view.`coordinateUncertaintyInMeters`; Type: COMMENT; Schema: public; Owner: - |
|
| 4247 |
-- |
|
| 4248 |
|
|
| 4249 |
|
|
| 4250 |
|
|
| 4251 |
|
|
| 4252 |
-- |
|
| 4253 | 4211 |
-- Name: COLUMN analytical_stem_view.`coordinateSource_bien`; Type: COMMENT; Schema: public; Owner: - |
| 4254 | 4212 |
-- |
| 4255 | 4213 |
|
| ... | ... | |
| 4278 | 4236 |
|
| 4279 | 4237 |
|
| 4280 | 4238 |
-- |
| 4281 |
-- Name: COLUMN analytical_stem_view.`projectID`; Type: COMMENT; Schema: public; Owner: - |
|
| 4239 |
-- Name: COLUMN analytical_stem_view.`projectID@VegX@vegpath.org`; Type: COMMENT; Schema: public; Owner: -
|
|
| 4282 | 4240 |
-- |
| 4283 | 4241 |
|
| 4284 | 4242 |
|
| ... | ... | |
| 4292 | 4250 |
|
| 4293 | 4251 |
|
| 4294 | 4252 |
-- |
| 4295 |
-- Name: COLUMN analytical_stem_view.`locationID`; Type: COMMENT; Schema: public; Owner: -
|
|
| 4253 |
-- Name: COLUMN analytical_stem_view.`subplot@SALVIAS@vegpath.org`; Type: COMMENT; Schema: public; Owner: -
|
|
| 4296 | 4254 |
-- |
| 4297 | 4255 |
|
| 4298 | 4256 |
|
| 4299 | 4257 |
|
| 4300 | 4258 |
|
| 4301 | 4259 |
-- |
| 4302 |
-- Name: COLUMN analytical_stem_view.`locationName`; Type: COMMENT; Schema: public; Owner: - |
|
| 4303 |
-- |
|
| 4304 |
|
|
| 4305 |
|
|
| 4306 |
|
|
| 4307 |
|
|
| 4308 |
-- |
|
| 4309 |
-- Name: COLUMN analytical_stem_view.subplot; Type: COMMENT; Schema: public; Owner: - |
|
| 4310 |
-- |
|
| 4311 |
|
|
| 4312 |
|
|
| 4313 |
|
|
| 4314 |
|
|
| 4315 |
-- |
|
| 4316 | 4260 |
-- Name: COLUMN analytical_stem_view.location__cultivated__bien; Type: COMMENT; Schema: public; Owner: - |
| 4317 | 4261 |
-- |
| 4318 | 4262 |
|
| ... | ... | |
| 4320 | 4264 |
|
| 4321 | 4265 |
|
| 4322 | 4266 |
-- |
| 4323 |
-- Name: COLUMN analytical_stem_view.locationevent__pkey; Type: COMMENT; Schema: public; Owner: -
|
|
| 4267 |
-- Name: COLUMN analytical_stem_view.`locationevent.locationevent_id@VegBIEN.public@vegpath.org`; Type: COMMENT; Schema: public; Owner: -
|
|
| 4324 | 4268 |
-- |
| 4325 | 4269 |
|
| 4326 | 4270 |
|
| 4327 | 4271 |
|
| 4328 | 4272 |
|
| 4329 | 4273 |
-- |
| 4330 |
-- Name: COLUMN analytical_stem_view.`eventDate`; Type: COMMENT; Schema: public; Owner: - |
|
| 4331 |
-- |
|
| 4332 |
|
|
| 4333 |
|
|
| 4334 |
|
|
| 4335 |
|
|
| 4336 |
-- |
|
| 4337 | 4274 |
-- Name: COLUMN analytical_stem_view.`elevationInMeters`; Type: COMMENT; Schema: public; Owner: - |
| 4338 | 4275 |
-- |
| 4339 | 4276 |
|
| ... | ... | |
| 4341 | 4278 |
|
| 4342 | 4279 |
|
| 4343 | 4280 |
-- |
| 4344 |
-- Name: COLUMN analytical_stem_view.`slopeAspect`; Type: COMMENT; Schema: public; Owner: - |
|
| 4345 |
-- |
|
| 4346 |
|
|
| 4347 |
|
|
| 4348 |
|
|
| 4349 |
|
|
| 4350 |
-- |
|
| 4351 |
-- Name: COLUMN analytical_stem_view.`slopeGradient`; Type: COMMENT; Schema: public; Owner: - |
|
| 4352 |
-- |
|
| 4353 |
|
|
| 4354 |
|
|
| 4355 |
|
|
| 4356 |
|
|
| 4357 |
-- |
|
| 4358 | 4281 |
-- Name: COLUMN analytical_stem_view.`plotArea_ha`; Type: COMMENT; Schema: public; Owner: - |
| 4359 | 4282 |
-- |
| 4360 | 4283 |
|
| ... | ... | |
| 4362 | 4285 |
|
| 4363 | 4286 |
|
| 4364 | 4287 |
-- |
| 4365 |
-- Name: COLUMN analytical_stem_view.`samplingProtocol`; Type: COMMENT; Schema: public; Owner: - |
|
| 4366 |
-- |
|
| 4367 |
|
|
| 4368 |
|
|
| 4369 |
|
|
| 4370 |
|
|
| 4371 |
-- |
|
| 4372 | 4288 |
-- Name: COLUMN analytical_stem_view.`temperature_C`; Type: COMMENT; Schema: public; Owner: - |
| 4373 | 4289 |
-- |
| 4374 | 4290 |
|
| ... | ... | |
| 4425 | 4341 |
|
| 4426 | 4342 |
|
| 4427 | 4343 |
-- |
| 4428 |
-- Name: COLUMN analytical_stem_view.`occurrenceID`; Type: COMMENT; Schema: public; Owner: - |
|
| 4429 |
-- |
|
| 4430 |
|
|
| 4431 |
|
|
| 4432 |
|
|
| 4433 |
|
|
| 4434 |
-- |
|
| 4435 |
-- Name: COLUMN analytical_stem_view.`recordedBy`; Type: COMMENT; Schema: public; Owner: - |
|
| 4436 |
-- |
|
| 4437 |
|
|
| 4438 |
|
|
| 4439 |
|
|
| 4440 |
|
|
| 4441 |
-- |
|
| 4442 |
-- Name: COLUMN analytical_stem_view.`recordNumber`; Type: COMMENT; Schema: public; Owner: - |
|
| 4443 |
-- |
|
| 4444 |
|
|
| 4445 |
|
|
| 4446 |
|
|
| 4447 |
|
|
| 4448 |
-- |
|
| 4449 | 4344 |
-- Name: COLUMN analytical_stem_view.`dateCollected`; Type: COMMENT; Schema: public; Owner: - |
| 4450 | 4345 |
-- |
| 4451 | 4346 |
|
| ... | ... | |
| 4467 | 4362 |
|
| 4468 | 4363 |
|
| 4469 | 4364 |
-- |
| 4470 |
-- Name: COLUMN analytical_stem_view.`identifiedBy`; Type: COMMENT; Schema: public; Owner: -
|
|
| 4365 |
-- Name: COLUMN analytical_stem_view.`Family_matched@TNRS@vegpath.org`; Type: COMMENT; Schema: public; Owner: -
|
|
| 4471 | 4366 |
-- |
| 4472 | 4367 |
|
| 4473 | 4368 |
|
| 4474 | 4369 |
|
| 4475 | 4370 |
|
| 4476 | 4371 |
-- |
| 4477 |
-- Name: COLUMN analytical_stem_view.`dateIdentified`; Type: COMMENT; Schema: public; Owner: - |
|
| 4478 |
-- |
|
| 4479 |
|
|
| 4480 |
|
|
| 4481 |
|
|
| 4482 |
|
|
| 4483 |
-- |
|
| 4484 |
-- Name: COLUMN analytical_stem_view.`identificationRemarks`; Type: COMMENT; Schema: public; Owner: - |
|
| 4485 |
-- |
|
| 4486 |
|
|
| 4487 |
|
|
| 4488 |
|
|
| 4489 |
|
|
| 4490 |
-- |
|
| 4491 |
-- Name: COLUMN analytical_stem_view.family_matched; Type: COMMENT; Schema: public; Owner: - |
|
| 4492 |
-- |
|
| 4493 |
|
|
| 4494 |
|
|
| 4495 |
|
|
| 4496 |
|
|
| 4497 |
-- |
|
| 4498 | 4372 |
-- Name: COLUMN analytical_stem_view.`taxonName_matched`; Type: COMMENT; Schema: public; Owner: - |
| 4499 | 4373 |
-- |
| 4500 | 4374 |
|
| ... | ... | |
| 4572 | 4446 |
|
| 4573 | 4447 |
|
| 4574 | 4448 |
-- |
| 4575 |
-- Name: COLUMN analytical_stem_view.scrubbed_morphospecies_binomial; Type: COMMENT; Schema: public; Owner: -
|
|
| 4449 |
-- Name: COLUMN analytical_stem_view.`speciesBinomialWithMorphospecies@VegCore@vegpath.org`; Type: COMMENT; Schema: public; Owner: -
|
|
| 4576 | 4450 |
-- |
| 4577 | 4451 |
|
| 4578 | 4452 |
|
| ... | ... | |
| 4586 | 4460 |
|
| 4587 | 4461 |
|
| 4588 | 4462 |
-- |
| 4589 |
-- Name: COLUMN analytical_stem_view.`reproductiveCondition`; Type: COMMENT; Schema: public; Owner: - |
|
| 4590 |
-- |
|
| 4591 |
|
|
| 4592 |
|
|
| 4593 |
|
|
| 4594 |
|
|
| 4595 |
-- |
|
| 4596 | 4463 |
-- Name: COLUMN analytical_stem_view.threatened_bien; Type: COMMENT; Schema: public; Owner: - |
| 4597 | 4464 |
-- |
| 4598 | 4465 |
|
| ... | ... | |
| 4614 | 4481 |
|
| 4615 | 4482 |
|
| 4616 | 4483 |
-- |
| 4617 |
-- Name: COLUMN analytical_stem_view.`occurrenceRemarks`; Type: COMMENT; Schema: public; Owner: - |
|
| 4618 |
-- |
|
| 4619 |
|
|
| 4620 |
|
|
| 4621 |
|
|
| 4622 |
|
|
| 4623 |
-- |
|
| 4624 |
-- Name: COLUMN analytical_stem_view.`coverPercent`; Type: COMMENT; Schema: public; Owner: - |
|
| 4625 |
-- |
|
| 4626 |
|
|
| 4627 |
|
|
| 4628 |
|
|
| 4629 |
|
|
| 4630 |
-- |
|
| 4631 | 4484 |
-- Name: COLUMN analytical_stem_view.`diameterBreastHeight_cm`; Type: COMMENT; Schema: public; Owner: - |
| 4632 | 4485 |
-- |
| 4633 | 4486 |
|
| ... | ... | |
| 4698 | 4551 |
|
| 4699 | 4552 |
|
| 4700 | 4553 |
-- |
| 4701 |
-- Name: COLUMN analytical_stem_view.`individualCount`; Type: COMMENT; Schema: public; Owner: -
|
|
| 4554 |
-- Name: COLUMN analytical_stem_view.`stemCode@stemlocation@VegBank@vegpath.org`; Type: COMMENT; Schema: public; Owner: -
|
|
| 4702 | 4555 |
-- |
| 4703 | 4556 |
|
| 4704 | 4557 |
|
| 4705 | 4558 |
|
| 4706 | 4559 |
|
| 4707 | 4560 |
-- |
| 4708 |
-- Name: COLUMN analytical_stem_view.`authorStemCode`; Type: COMMENT; Schema: public; Owner: - |
|
| 4709 |
-- |
|
| 4710 |
|
|
| 4711 |
|
|
| 4712 |
|
|
| 4713 |
|
|
| 4714 |
-- |
|
| 4715 | 4561 |
-- Name: classcontributor; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 4716 | 4562 |
-- |
| 4717 | 4563 |
|
| trunk/schemas/public_.sql | ||
|---|---|---|
| 6735 | 6735 |
|
| 6736 | 6736 |
CREATE VIEW analytical_stem_view AS |
| 6737 | 6737 |
SELECT "plot.**".datasource, |
| 6738 |
"plot.**".country, |
|
| 6739 |
"plot.**"."stateProvince", |
|
| 6740 |
"plot.**".county, |
|
| 6741 |
"plot.**".locality, |
|
| 6742 |
"plot.**"."decimalLatitude", |
|
| 6743 |
"plot.**"."decimalLongitude", |
|
| 6744 |
"plot.**"."coordinateUncertaintyInMeters", |
|
| 6738 |
"plot.**".country AS "country@DwC@vegpath.org",
|
|
| 6739 |
"plot.**"."stateProvince" AS "stateProvince@DwC@vegpath.org",
|
|
| 6740 |
"plot.**".county AS "county@DwC@vegpath.org",
|
|
| 6741 |
"plot.**".locality AS "locality@DwC@vegpath.org",
|
|
| 6742 |
"plot.**"."decimalLatitude" AS "decimalLatitude@DwC@vegpath.org",
|
|
| 6743 |
"plot.**"."decimalLongitude" AS "decimalLongitude@DwC@vegpath.org",
|
|
| 6744 |
"plot.**"."coordinateUncertaintyInMeters" AS "coordinateUncertaintyInMeters@DwC@vegpath.org",
|
|
| 6745 | 6745 |
"plot.**"."coordinateSource_bien", |
| 6746 | 6746 |
"plot.**"."georeferenceProtocol_bien", |
| 6747 | 6747 |
"plot.**".geovalid_bien, |
| 6748 | 6748 |
"plot.**"."isNewWorld_bien", |
| 6749 |
"plot.**"."projectID", |
|
| 6749 |
"plot.**"."projectID" AS "projectID@VegX@vegpath.org",
|
|
| 6750 | 6750 |
"plot.**".project_contributors, |
| 6751 |
"plot.**"."locationID", |
|
| 6752 |
"plot.**"."locationName", |
|
| 6753 |
"plot.**".subplot, |
|
| 6751 |
"plot.**"."locationID" AS "locationID@DwC@vegpath.org",
|
|
| 6752 |
"plot.**"."locationName" AS "locationName@DwC@vegpath.org",
|
|
| 6753 |
"plot.**".subplot AS "subplot@SALVIAS@vegpath.org",
|
|
| 6754 | 6754 |
"plot.**".location__cultivated__bien, |
| 6755 |
"plot.**".locationevent__pkey, |
|
| 6756 |
"plot.**"."eventDate", |
|
| 6755 |
"plot.**".locationevent__pkey AS "locationevent.locationevent_id@VegBIEN.public@vegpath.org",
|
|
| 6756 |
"plot.**"."eventDate" AS "eventDate@DwC@vegpath.org",
|
|
| 6757 | 6757 |
"plot.**"."elevationInMeters", |
| 6758 |
"plot.**"."slopeAspect", |
|
| 6759 |
"plot.**"."slopeGradient", |
|
| 6758 |
"plot.**"."slopeAspect" AS "slopeAspect@VegX@vegpath.org",
|
|
| 6759 |
"plot.**"."slopeGradient" AS "slopeGradient@VegX@vegpath.org",
|
|
| 6760 | 6760 |
"plot.**"."plotArea_ha", |
| 6761 |
"plot.**"."samplingProtocol", |
|
| 6761 |
"plot.**"."samplingProtocol" AS "samplingProtocol@DwC@vegpath.org",
|
|
| 6762 | 6762 |
"plot.**"."temperature_C", |
| 6763 | 6763 |
"plot.**".precipitation_m, |
| 6764 | 6764 |
"plot.**".stratum__name, |
| ... | ... | |
| 6767 | 6767 |
sourcelist.name AS "specimenHolderInstitutions", |
| 6768 | 6768 |
specimenreplicate.collectioncode_dwc AS collection, |
| 6769 | 6769 |
specimenreplicate.catalognumber_dwc AS "accessionNumber", |
| 6770 |
specimenreplicate.sourceaccessioncode AS "occurrenceID", |
|
| 6771 |
collector.fullname AS "recordedBy", |
|
| 6772 |
plantobservation.authorplantcode AS "recordNumber", |
|
| 6770 |
specimenreplicate.sourceaccessioncode AS "occurrenceID@DwC@vegpath.org",
|
|
| 6771 |
collector.fullname AS "recordedBy@DwC@vegpath.org",
|
|
| 6772 |
plantobservation.authorplantcode AS "recordNumber@DwC@vegpath.org",
|
|
| 6773 | 6773 |
COALESCE(aggregateoccurrence.collectiondate, "plot.**"."eventDate") AS "dateCollected", |
| 6774 | 6774 |
taxonverbatim.family AS family_verbatim, |
| 6775 | 6775 |
COALESCE(taxonverbatim.taxonomicname, (taxonverbatim.taxonname || COALESCE((' '::text || taxonverbatim.author), ''::text)), taxonlabel.taxonomicname) AS "scientificName_verbatim",
|
| 6776 |
identifiedby.fullname AS "identifiedBy", |
|
| 6777 |
taxondetermination.determinationdate AS "dateIdentified", |
|
| 6778 |
taxondetermination.notes AS "identificationRemarks", |
|
| 6779 |
taxon_scrub."matchedFamily" AS family_matched,
|
|
| 6776 |
identifiedby.fullname AS "identifiedBy@DwC@vegpath.org",
|
|
| 6777 |
taxondetermination.determinationdate AS "dateIdentified@DwC@vegpath.org",
|
|
| 6778 |
taxondetermination.notes AS "identificationRemarks@DwC@vegpath.org",
|
|
| 6779 |
taxon_scrub."matchedFamily" AS "Family_matched@TNRS@vegpath.org",
|
|
| 6780 | 6780 |
taxon_scrub."matchedTaxonName" AS "taxonName_matched", |
| 6781 | 6781 |
taxon_scrub."matchedScientificNameAuthorship" AS "scientificNameAuthorship_matched", |
| 6782 | 6782 |
family_higher_plant_group.higher_plant_group AS "higherPlantGroup_bien", |
| ... | ... | |
| 6788 | 6788 |
taxon_scrub.scrubbed_taxon_name_no_author, |
| 6789 | 6789 |
taxon_scrub.scrubbed_author, |
| 6790 | 6790 |
taxon_scrub.scrubbed_taxon_name_with_author, |
| 6791 |
taxon_scrub.scrubbed_morphospecies_binomial, |
|
| 6791 |
taxon_scrub.scrubbed_morphospecies_binomial AS "speciesBinomialWithMorphospecies@VegCore@vegpath.org",
|
|
| 6792 | 6792 |
taxonoccurrence.growthform AS "growthForm", |
| 6793 |
plantobservation.reproductivecondition AS "reproductiveCondition", |
|
| 6793 |
plantobservation.reproductivecondition AS "reproductiveCondition@DwC@vegpath.org",
|
|
| 6794 | 6794 |
((threatened_taxonlabel.taxonlabel_id IS NOT NULL))::integer AS threatened_bien, |
| 6795 | 6795 |
(((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, "plot.**".location__cultivated__bien)))::integer AS cultivated_bien, |
| 6796 | 6796 |
CASE |
| ... | ... | |
| 6798 | 6798 |
WHEN ("plot.**".location__cultivated__bien IS NOT NULL) THEN NULL::text
|
| 6799 | 6799 |
ELSE NULL::text |
| 6800 | 6800 |
END AS "cultivatedBasis_bien", |
| 6801 |
aggregateoccurrence.notes AS "occurrenceRemarks", |
|
| 6802 |
_fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", |
|
| 6801 |
aggregateoccurrence.notes AS "occurrenceRemarks@DwC@vegpath.org",
|
|
| 6802 |
_fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent@VegX@vegpath.org",
|
|
| 6803 | 6803 |
_m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", |
| 6804 | 6804 |
stemobservation.height_m, |
| 6805 | 6805 |
stemobservation.tag, |
| ... | ... | |
| 6810 | 6810 |
aggregateoccurrence.sourceaccessioncode AS "aggregateOrganismObservationID", |
| 6811 | 6811 |
plantobservation.sourceaccessioncode AS "individualObservationID", |
| 6812 | 6812 |
plantobservation.authorplantcode AS "individualCode", |
| 6813 |
aggregateoccurrence.count AS "individualCount", |
|
| 6814 |
stemobservation.authorstemcode AS "authorStemCode"
|
|
| 6813 |
aggregateoccurrence.count AS "individualCount@DwC@vegpath.org",
|
|
| 6814 |
stemobservation.authorstemcode AS "stemCode@stemlocation@VegBank@vegpath.org"
|
|
| 6815 | 6815 |
FROM ((((((((((((((("plot.**"
|
| 6816 | 6816 |
LEFT JOIN taxonoccurrence ON ((taxonoccurrence.locationevent_id = "plot.**".locationevent__pkey))) |
| 6817 | 6817 |
LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) |
| ... | ... | |
| 6849 | 6849 |
|
| 6850 | 6850 |
|
| 6851 | 6851 |
-- |
| 6852 |
-- Name: COLUMN analytical_stem_view.country; Type: COMMENT; Schema: public; Owner: -
|
|
| 6852 |
-- Name: COLUMN analytical_stem_view."country@DwC@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 6853 | 6853 |
-- |
| 6854 | 6854 |
|
| 6855 |
COMMENT ON COLUMN analytical_stem_view.country IS 'country@DwC@vegpath.org; "The name of the country or major administrative unit in which the Location occurs"';
|
|
| 6855 |
COMMENT ON COLUMN analytical_stem_view."country@DwC@vegpath.org" IS '"The name of the country or major administrative unit in which the Location occurs"';
|
|
| 6856 | 6856 |
|
| 6857 | 6857 |
|
| 6858 | 6858 |
-- |
| 6859 |
-- Name: COLUMN analytical_stem_view."stateProvince"; Type: COMMENT; Schema: public; Owner: - |
|
| 6860 |
-- |
|
| 6861 |
|
|
| 6862 |
COMMENT ON COLUMN analytical_stem_view."stateProvince" IS 'stateProvince@DwC@vegpath.org'; |
|
| 6863 |
|
|
| 6864 |
|
|
| 6865 |
-- |
|
| 6866 |
-- Name: COLUMN analytical_stem_view.county; Type: COMMENT; Schema: public; Owner: - |
|
| 6867 |
-- |
|
| 6868 |
|
|
| 6869 |
COMMENT ON COLUMN analytical_stem_view.county IS 'county@DwC@vegpath.org'; |
|
| 6870 |
|
|
| 6871 |
|
|
| 6872 |
-- |
|
| 6873 |
-- Name: COLUMN analytical_stem_view.locality; Type: COMMENT; Schema: public; Owner: - |
|
| 6874 |
-- |
|
| 6875 |
|
|
| 6876 |
COMMENT ON COLUMN analytical_stem_view.locality IS 'locality@DwC@vegpath.org'; |
|
| 6877 |
|
|
| 6878 |
|
|
| 6879 |
-- |
|
| 6880 |
-- Name: COLUMN analytical_stem_view."decimalLatitude"; Type: COMMENT; Schema: public; Owner: - |
|
| 6881 |
-- |
|
| 6882 |
|
|
| 6883 |
COMMENT ON COLUMN analytical_stem_view."decimalLatitude" IS 'decimalLatitude@DwC@vegpath.org'; |
|
| 6884 |
|
|
| 6885 |
|
|
| 6886 |
-- |
|
| 6887 |
-- Name: COLUMN analytical_stem_view."decimalLongitude"; Type: COMMENT; Schema: public; Owner: - |
|
| 6888 |
-- |
|
| 6889 |
|
|
| 6890 |
COMMENT ON COLUMN analytical_stem_view."decimalLongitude" IS 'decimalLongitude@DwC@vegpath.org'; |
|
| 6891 |
|
|
| 6892 |
|
|
| 6893 |
-- |
|
| 6894 |
-- Name: COLUMN analytical_stem_view."coordinateUncertaintyInMeters"; Type: COMMENT; Schema: public; Owner: - |
|
| 6895 |
-- |
|
| 6896 |
|
|
| 6897 |
COMMENT ON COLUMN analytical_stem_view."coordinateUncertaintyInMeters" IS 'coordinateUncertaintyInMeters@DwC@vegpath.org'; |
|
| 6898 |
|
|
| 6899 |
|
|
| 6900 |
-- |
|
| 6901 | 6859 |
-- Name: COLUMN analytical_stem_view."coordinateSource_bien"; Type: COMMENT; Schema: public; Owner: - |
| 6902 | 6860 |
-- |
| 6903 | 6861 |
|
| ... | ... | |
| 6926 | 6884 |
|
| 6927 | 6885 |
|
| 6928 | 6886 |
-- |
| 6929 |
-- Name: COLUMN analytical_stem_view."projectID"; Type: COMMENT; Schema: public; Owner: - |
|
| 6887 |
-- Name: COLUMN analytical_stem_view."projectID@VegX@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 6930 | 6888 |
-- |
| 6931 | 6889 |
|
| 6932 |
COMMENT ON COLUMN analytical_stem_view."projectID" IS 'projectID@VegX@vegpath.org; "A reference to a specific ''project''"';
|
|
| 6890 |
COMMENT ON COLUMN analytical_stem_view."projectID@VegX@vegpath.org" IS '"A reference to a specific ''project''"';
|
|
| 6933 | 6891 |
|
| 6934 | 6892 |
|
| 6935 | 6893 |
-- |
| ... | ... | |
| 6940 | 6898 |
|
| 6941 | 6899 |
|
| 6942 | 6900 |
-- |
| 6943 |
-- Name: COLUMN analytical_stem_view."locationID"; Type: COMMENT; Schema: public; Owner: -
|
|
| 6901 |
-- Name: COLUMN analytical_stem_view."subplot@SALVIAS@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 6944 | 6902 |
-- |
| 6945 | 6903 |
|
| 6946 |
COMMENT ON COLUMN analytical_stem_view."locationID" IS 'locationID@DwC@vegpath.org';
|
|
| 6904 |
COMMENT ON COLUMN analytical_stem_view."subplot@SALVIAS@vegpath.org" IS '"Code for subplot, line, or any other subsample or subdivision of plot"';
|
|
| 6947 | 6905 |
|
| 6948 | 6906 |
|
| 6949 | 6907 |
-- |
| 6950 |
-- Name: COLUMN analytical_stem_view."locationName"; Type: COMMENT; Schema: public; Owner: - |
|
| 6951 |
-- |
|
| 6952 |
|
|
| 6953 |
COMMENT ON COLUMN analytical_stem_view."locationName" IS 'locationName@DwC@vegpath.org'; |
|
| 6954 |
|
|
| 6955 |
|
|
| 6956 |
-- |
|
| 6957 |
-- Name: COLUMN analytical_stem_view.subplot; Type: COMMENT; Schema: public; Owner: - |
|
| 6958 |
-- |
|
| 6959 |
|
|
| 6960 |
COMMENT ON COLUMN analytical_stem_view.subplot IS 'subplot@SALVIAS@vegpath.org; "Code for subplot, line, or any other subsample or subdivision of plot"'; |
|
| 6961 |
|
|
| 6962 |
|
|
| 6963 |
-- |
|
| 6964 | 6908 |
-- Name: COLUMN analytical_stem_view.location__cultivated__bien; Type: COMMENT; Schema: public; Owner: - |
| 6965 | 6909 |
-- |
| 6966 | 6910 |
|
| ... | ... | |
| 6968 | 6912 |
|
| 6969 | 6913 |
|
| 6970 | 6914 |
-- |
| 6971 |
-- Name: COLUMN analytical_stem_view.locationevent__pkey; Type: COMMENT; Schema: public; Owner: -
|
|
| 6915 |
-- Name: COLUMN analytical_stem_view."locationevent.locationevent_id@VegBIEN.public@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 6972 | 6916 |
-- |
| 6973 | 6917 |
|
| 6974 |
COMMENT ON COLUMN analytical_stem_view.locationevent__pkey IS 'locationevent.locationevent_id@VegBIEN.public@vegpath.org; autogenerated ID for locationevent';
|
|
| 6918 |
COMMENT ON COLUMN analytical_stem_view."locationevent.locationevent_id@VegBIEN.public@vegpath.org" IS 'autogenerated ID for locationevent';
|
|
| 6975 | 6919 |
|
| 6976 | 6920 |
|
| 6977 | 6921 |
-- |
| 6978 |
-- Name: COLUMN analytical_stem_view."eventDate"; Type: COMMENT; Schema: public; Owner: - |
|
| 6979 |
-- |
|
| 6980 |
|
|
| 6981 |
COMMENT ON COLUMN analytical_stem_view."eventDate" IS 'eventDate@DwC@vegpath.org'; |
|
| 6982 |
|
|
| 6983 |
|
|
| 6984 |
-- |
|
| 6985 | 6922 |
-- Name: COLUMN analytical_stem_view."elevationInMeters"; Type: COMMENT; Schema: public; Owner: - |
| 6986 | 6923 |
-- |
| 6987 | 6924 |
|
| ... | ... | |
| 6989 | 6926 |
|
| 6990 | 6927 |
|
| 6991 | 6928 |
-- |
| 6992 |
-- Name: COLUMN analytical_stem_view."slopeAspect"; Type: COMMENT; Schema: public; Owner: - |
|
| 6993 |
-- |
|
| 6994 |
|
|
| 6995 |
COMMENT ON COLUMN analytical_stem_view."slopeAspect" IS 'slopeAspect@VegX@vegpath.org'; |
|
| 6996 |
|
|
| 6997 |
|
|
| 6998 |
-- |
|
| 6999 |
-- Name: COLUMN analytical_stem_view."slopeGradient"; Type: COMMENT; Schema: public; Owner: - |
|
| 7000 |
-- |
|
| 7001 |
|
|
| 7002 |
COMMENT ON COLUMN analytical_stem_view."slopeGradient" IS 'slopeGradient@VegX@vegpath.org'; |
|
| 7003 |
|
|
| 7004 |
|
|
| 7005 |
-- |
|
| 7006 | 6929 |
-- Name: COLUMN analytical_stem_view."plotArea_ha"; Type: COMMENT; Schema: public; Owner: - |
| 7007 | 6930 |
-- |
| 7008 | 6931 |
|
| ... | ... | |
| 7010 | 6933 |
|
| 7011 | 6934 |
|
| 7012 | 6935 |
-- |
| 7013 |
-- Name: COLUMN analytical_stem_view."samplingProtocol"; Type: COMMENT; Schema: public; Owner: - |
|
| 7014 |
-- |
|
| 7015 |
|
|
| 7016 |
COMMENT ON COLUMN analytical_stem_view."samplingProtocol" IS 'samplingProtocol@DwC@vegpath.org'; |
|
| 7017 |
|
|
| 7018 |
|
|
| 7019 |
-- |
|
| 7020 | 6936 |
-- Name: COLUMN analytical_stem_view."temperature_C"; Type: COMMENT; Schema: public; Owner: - |
| 7021 | 6937 |
-- |
| 7022 | 6938 |
|
| ... | ... | |
| 7073 | 6989 |
|
| 7074 | 6990 |
|
| 7075 | 6991 |
-- |
| 7076 |
-- Name: COLUMN analytical_stem_view."occurrenceID"; Type: COMMENT; Schema: public; Owner: - |
|
| 7077 |
-- |
|
| 7078 |
|
|
| 7079 |
COMMENT ON COLUMN analytical_stem_view."occurrenceID" IS 'occurrenceID@DwC@vegpath.org'; |
|
| 7080 |
|
|
| 7081 |
|
|
| 7082 |
-- |
|
| 7083 |
-- Name: COLUMN analytical_stem_view."recordedBy"; Type: COMMENT; Schema: public; Owner: - |
|
| 7084 |
-- |
|
| 7085 |
|
|
| 7086 |
COMMENT ON COLUMN analytical_stem_view."recordedBy" IS 'recordedBy@DwC@vegpath.org'; |
|
| 7087 |
|
|
| 7088 |
|
|
| 7089 |
-- |
|
| 7090 |
-- Name: COLUMN analytical_stem_view."recordNumber"; Type: COMMENT; Schema: public; Owner: - |
|
| 7091 |
-- |
|
| 7092 |
|
|
| 7093 |
COMMENT ON COLUMN analytical_stem_view."recordNumber" IS 'recordNumber@DwC@vegpath.org'; |
|
| 7094 |
|
|
| 7095 |
|
|
| 7096 |
-- |
|
| 7097 | 6992 |
-- Name: COLUMN analytical_stem_view."dateCollected"; Type: COMMENT; Schema: public; Owner: - |
| 7098 | 6993 |
-- |
| 7099 | 6994 |
|
| ... | ... | |
| 7115 | 7010 |
|
| 7116 | 7011 |
|
| 7117 | 7012 |
-- |
| 7118 |
-- Name: COLUMN analytical_stem_view."identifiedBy"; Type: COMMENT; Schema: public; Owner: -
|
|
| 7013 |
-- Name: COLUMN analytical_stem_view."Family_matched@TNRS@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 7119 | 7014 |
-- |
| 7120 | 7015 |
|
| 7121 |
COMMENT ON COLUMN analytical_stem_view."identifiedBy" IS 'identifiedBy@DwC@vegpath.org';
|
|
| 7016 |
COMMENT ON COLUMN analytical_stem_view."Family_matched@TNRS@vegpath.org" IS '"The closest matching family in the TNRS database to the family submitted"';
|
|
| 7122 | 7017 |
|
| 7123 | 7018 |
|
| 7124 | 7019 |
-- |
| 7125 |
-- Name: COLUMN analytical_stem_view."dateIdentified"; Type: COMMENT; Schema: public; Owner: - |
|
| 7126 |
-- |
|
| 7127 |
|
|
| 7128 |
COMMENT ON COLUMN analytical_stem_view."dateIdentified" IS 'dateIdentified@DwC@vegpath.org'; |
|
| 7129 |
|
|
| 7130 |
|
|
| 7131 |
-- |
|
| 7132 |
-- Name: COLUMN analytical_stem_view."identificationRemarks"; Type: COMMENT; Schema: public; Owner: - |
|
| 7133 |
-- |
|
| 7134 |
|
|
| 7135 |
COMMENT ON COLUMN analytical_stem_view."identificationRemarks" IS 'identificationRemarks@DwC@vegpath.org'; |
|
| 7136 |
|
|
| 7137 |
|
|
| 7138 |
-- |
|
| 7139 |
-- Name: COLUMN analytical_stem_view.family_matched; Type: COMMENT; Schema: public; Owner: - |
|
| 7140 |
-- |
|
| 7141 |
|
|
| 7142 |
COMMENT ON COLUMN analytical_stem_view.family_matched IS 'Family_matched@TNRS@vegpath.org; "The closest matching family in the TNRS database to the family submitted"'; |
|
| 7143 |
|
|
| 7144 |
|
|
| 7145 |
-- |
|
| 7146 | 7020 |
-- Name: COLUMN analytical_stem_view."taxonName_matched"; Type: COMMENT; Schema: public; Owner: - |
| 7147 | 7021 |
-- |
| 7148 | 7022 |
|
| ... | ... | |
| 7220 | 7094 |
|
| 7221 | 7095 |
|
| 7222 | 7096 |
-- |
| 7223 |
-- Name: COLUMN analytical_stem_view.scrubbed_morphospecies_binomial; Type: COMMENT; Schema: public; Owner: -
|
|
| 7097 |
-- Name: COLUMN analytical_stem_view."speciesBinomialWithMorphospecies@VegCore@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 7224 | 7098 |
-- |
| 7225 | 7099 |
|
| 7226 |
COMMENT ON COLUMN analytical_stem_view.scrubbed_morphospecies_binomial IS 'speciesBinomialWithMorphospecies@VegCore@vegpath.org; "combine the `taxon` plus `morphospecies` to produce a unique string, `taxonMorphospecies`"';
|
|
| 7100 |
COMMENT ON COLUMN analytical_stem_view."speciesBinomialWithMorphospecies@VegCore@vegpath.org" IS '"combine the `taxon` plus `morphospecies` to produce a unique string, `taxonMorphospecies`"';
|
|
| 7227 | 7101 |
|
| 7228 | 7102 |
|
| 7229 | 7103 |
-- |
| ... | ... | |
| 7234 | 7108 |
|
| 7235 | 7109 |
|
| 7236 | 7110 |
-- |
| 7237 |
-- Name: COLUMN analytical_stem_view."reproductiveCondition"; Type: COMMENT; Schema: public; Owner: - |
|
| 7238 |
-- |
|
| 7239 |
|
|
| 7240 |
COMMENT ON COLUMN analytical_stem_view."reproductiveCondition" IS 'reproductiveCondition@DwC@vegpath.org'; |
|
| 7241 |
|
|
| 7242 |
|
|
| 7243 |
-- |
|
| 7244 | 7111 |
-- Name: COLUMN analytical_stem_view.threatened_bien; Type: COMMENT; Schema: public; Owner: - |
| 7245 | 7112 |
-- |
| 7246 | 7113 |
|
| ... | ... | |
| 7262 | 7129 |
|
| 7263 | 7130 |
|
| 7264 | 7131 |
-- |
| 7265 |
-- Name: COLUMN analytical_stem_view."occurrenceRemarks"; Type: COMMENT; Schema: public; Owner: - |
|
| 7266 |
-- |
|
| 7267 |
|
|
| 7268 |
COMMENT ON COLUMN analytical_stem_view."occurrenceRemarks" IS 'occurrenceRemarks@DwC@vegpath.org'; |
|
| 7269 |
|
|
| 7270 |
|
|
| 7271 |
-- |
|
| 7272 |
-- Name: COLUMN analytical_stem_view."coverPercent"; Type: COMMENT; Schema: public; Owner: - |
|
| 7273 |
-- |
|
| 7274 |
|
|
| 7275 |
COMMENT ON COLUMN analytical_stem_view."coverPercent" IS 'coverPercent@VegX@vegpath.org'; |
|
| 7276 |
|
|
| 7277 |
|
|
| 7278 |
-- |
|
| 7279 | 7132 |
-- Name: COLUMN analytical_stem_view."diameterBreastHeight_cm"; Type: COMMENT; Schema: public; Owner: - |
| 7280 | 7133 |
-- |
| 7281 | 7134 |
|
| ... | ... | |
| 7346 | 7199 |
|
| 7347 | 7200 |
|
| 7348 | 7201 |
-- |
| 7349 |
-- Name: COLUMN analytical_stem_view."individualCount"; Type: COMMENT; Schema: public; Owner: -
|
|
| 7202 |
-- Name: COLUMN analytical_stem_view."stemCode@stemlocation@VegBank@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 7350 | 7203 |
-- |
| 7351 | 7204 |
|
| 7352 |
COMMENT ON COLUMN analytical_stem_view."individualCount" IS 'individualCount@DwC@vegpath.org';
|
|
| 7205 |
COMMENT ON COLUMN analytical_stem_view."stemCode@stemlocation@VegBank@vegpath.org" IS '"Name or code applied to a specific stem in the plot. This is generally a numeric label to associate a field data entry with a stem in the database."';
|
|
| 7353 | 7206 |
|
| 7354 | 7207 |
|
| 7355 | 7208 |
-- |
| 7356 |
-- Name: COLUMN analytical_stem_view."authorStemCode"; Type: COMMENT; Schema: public; Owner: - |
|
| 7357 |
-- |
|
| 7358 |
|
|
| 7359 |
COMMENT ON COLUMN analytical_stem_view."authorStemCode" IS 'stemCode@stemlocation@VegBank@vegpath.org; "Name or code applied to a specific stem in the plot. This is generally a numeric label to associate a field data entry with a stem in the database."'; |
|
| 7360 |
|
|
| 7361 |
|
|
| 7362 |
-- |
|
| 7363 | 7209 |
-- Name: classcontributor; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 7364 | 7210 |
-- |
| 7365 | 7211 |
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 6735 | 6735 |
|
| 6736 | 6736 |
CREATE VIEW analytical_stem_view AS |
| 6737 | 6737 |
SELECT "plot.**".datasource, |
| 6738 |
"plot.**".country, |
|
| 6739 |
"plot.**"."stateProvince", |
|
| 6740 |
"plot.**".county, |
|
| 6741 |
"plot.**".locality, |
|
| 6742 |
"plot.**"."decimalLatitude", |
|
| 6743 |
"plot.**"."decimalLongitude", |
|
| 6744 |
"plot.**"."coordinateUncertaintyInMeters", |
|
| 6738 |
"plot.**".country AS "country@DwC@vegpath.org",
|
|
| 6739 |
"plot.**"."stateProvince" AS "stateProvince@DwC@vegpath.org",
|
|
| 6740 |
"plot.**".county AS "county@DwC@vegpath.org",
|
|
| 6741 |
"plot.**".locality AS "locality@DwC@vegpath.org",
|
|
| 6742 |
"plot.**"."decimalLatitude" AS "decimalLatitude@DwC@vegpath.org",
|
|
| 6743 |
"plot.**"."decimalLongitude" AS "decimalLongitude@DwC@vegpath.org",
|
|
| 6744 |
"plot.**"."coordinateUncertaintyInMeters" AS "coordinateUncertaintyInMeters@DwC@vegpath.org",
|
|
| 6745 | 6745 |
"plot.**"."coordinateSource_bien", |
| 6746 | 6746 |
"plot.**"."georeferenceProtocol_bien", |
| 6747 | 6747 |
"plot.**".geovalid_bien, |
| 6748 | 6748 |
"plot.**"."isNewWorld_bien", |
| 6749 |
"plot.**"."projectID", |
|
| 6749 |
"plot.**"."projectID" AS "projectID@VegX@vegpath.org",
|
|
| 6750 | 6750 |
"plot.**".project_contributors, |
| 6751 |
"plot.**"."locationID", |
|
| 6752 |
"plot.**"."locationName", |
|
| 6753 |
"plot.**".subplot, |
|
| 6751 |
"plot.**"."locationID" AS "locationID@DwC@vegpath.org",
|
|
| 6752 |
"plot.**"."locationName" AS "locationName@DwC@vegpath.org",
|
|
| 6753 |
"plot.**".subplot AS "subplot@SALVIAS@vegpath.org",
|
|
| 6754 | 6754 |
"plot.**".location__cultivated__bien, |
| 6755 |
"plot.**".locationevent__pkey, |
|
| 6756 |
"plot.**"."eventDate", |
|
| 6755 |
"plot.**".locationevent__pkey AS "locationevent.locationevent_id@VegBIEN.public@vegpath.org",
|
|
| 6756 |
"plot.**"."eventDate" AS "eventDate@DwC@vegpath.org",
|
|
| 6757 | 6757 |
"plot.**"."elevationInMeters", |
| 6758 |
"plot.**"."slopeAspect", |
|
| 6759 |
"plot.**"."slopeGradient", |
|
| 6758 |
"plot.**"."slopeAspect" AS "slopeAspect@VegX@vegpath.org",
|
|
| 6759 |
"plot.**"."slopeGradient" AS "slopeGradient@VegX@vegpath.org",
|
|
| 6760 | 6760 |
"plot.**"."plotArea_ha", |
| 6761 |
"plot.**"."samplingProtocol", |
|
| 6761 |
"plot.**"."samplingProtocol" AS "samplingProtocol@DwC@vegpath.org",
|
|
| 6762 | 6762 |
"plot.**"."temperature_C", |
| 6763 | 6763 |
"plot.**".precipitation_m, |
| 6764 | 6764 |
"plot.**".stratum__name, |
| ... | ... | |
| 6767 | 6767 |
sourcelist.name AS "specimenHolderInstitutions", |
| 6768 | 6768 |
specimenreplicate.collectioncode_dwc AS collection, |
| 6769 | 6769 |
specimenreplicate.catalognumber_dwc AS "accessionNumber", |
| 6770 |
specimenreplicate.sourceaccessioncode AS "occurrenceID", |
|
| 6771 |
collector.fullname AS "recordedBy", |
|
| 6772 |
plantobservation.authorplantcode AS "recordNumber", |
|
| 6770 |
specimenreplicate.sourceaccessioncode AS "occurrenceID@DwC@vegpath.org",
|
|
| 6771 |
collector.fullname AS "recordedBy@DwC@vegpath.org",
|
|
| 6772 |
plantobservation.authorplantcode AS "recordNumber@DwC@vegpath.org",
|
|
| 6773 | 6773 |
COALESCE(aggregateoccurrence.collectiondate, "plot.**"."eventDate") AS "dateCollected", |
| 6774 | 6774 |
taxonverbatim.family AS family_verbatim, |
| 6775 | 6775 |
COALESCE(taxonverbatim.taxonomicname, (taxonverbatim.taxonname || COALESCE((' '::text || taxonverbatim.author), ''::text)), taxonlabel.taxonomicname) AS "scientificName_verbatim",
|
| 6776 |
identifiedby.fullname AS "identifiedBy", |
|
| 6777 |
taxondetermination.determinationdate AS "dateIdentified", |
|
| 6778 |
taxondetermination.notes AS "identificationRemarks", |
|
| 6779 |
taxon_scrub."matchedFamily" AS family_matched,
|
|
| 6776 |
identifiedby.fullname AS "identifiedBy@DwC@vegpath.org",
|
|
| 6777 |
taxondetermination.determinationdate AS "dateIdentified@DwC@vegpath.org",
|
|
| 6778 |
taxondetermination.notes AS "identificationRemarks@DwC@vegpath.org",
|
|
| 6779 |
taxon_scrub."matchedFamily" AS "Family_matched@TNRS@vegpath.org",
|
|
| 6780 | 6780 |
taxon_scrub."matchedTaxonName" AS "taxonName_matched", |
| 6781 | 6781 |
taxon_scrub."matchedScientificNameAuthorship" AS "scientificNameAuthorship_matched", |
| 6782 | 6782 |
family_higher_plant_group.higher_plant_group AS "higherPlantGroup_bien", |
| ... | ... | |
| 6788 | 6788 |
taxon_scrub.scrubbed_taxon_name_no_author, |
| 6789 | 6789 |
taxon_scrub.scrubbed_author, |
| 6790 | 6790 |
taxon_scrub.scrubbed_taxon_name_with_author, |
| 6791 |
taxon_scrub.scrubbed_morphospecies_binomial, |
|
| 6791 |
taxon_scrub.scrubbed_morphospecies_binomial AS "speciesBinomialWithMorphospecies@VegCore@vegpath.org",
|
|
| 6792 | 6792 |
taxonoccurrence.growthform AS "growthForm", |
| 6793 |
plantobservation.reproductivecondition AS "reproductiveCondition", |
|
| 6793 |
plantobservation.reproductivecondition AS "reproductiveCondition@DwC@vegpath.org",
|
|
| 6794 | 6794 |
((threatened_taxonlabel.taxonlabel_id IS NOT NULL))::integer AS threatened_bien, |
| 6795 | 6795 |
(((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, "plot.**".location__cultivated__bien)))::integer AS cultivated_bien, |
| 6796 | 6796 |
CASE |
| ... | ... | |
| 6798 | 6798 |
WHEN ("plot.**".location__cultivated__bien IS NOT NULL) THEN NULL::text
|
| 6799 | 6799 |
ELSE NULL::text |
| 6800 | 6800 |
END AS "cultivatedBasis_bien", |
| 6801 |
aggregateoccurrence.notes AS "occurrenceRemarks", |
|
| 6802 |
_fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", |
|
| 6801 |
aggregateoccurrence.notes AS "occurrenceRemarks@DwC@vegpath.org",
|
|
| 6802 |
_fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent@VegX@vegpath.org",
|
|
| 6803 | 6803 |
_m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", |
| 6804 | 6804 |
stemobservation.height_m, |
| 6805 | 6805 |
stemobservation.tag, |
| ... | ... | |
| 6810 | 6810 |
aggregateoccurrence.sourceaccessioncode AS "aggregateOrganismObservationID", |
| 6811 | 6811 |
plantobservation.sourceaccessioncode AS "individualObservationID", |
| 6812 | 6812 |
plantobservation.authorplantcode AS "individualCode", |
| 6813 |
aggregateoccurrence.count AS "individualCount", |
|
| 6814 |
stemobservation.authorstemcode AS "authorStemCode"
|
|
| 6813 |
aggregateoccurrence.count AS "individualCount@DwC@vegpath.org",
|
|
| 6814 |
stemobservation.authorstemcode AS "stemCode@stemlocation@VegBank@vegpath.org"
|
|
| 6815 | 6815 |
FROM ((((((((((((((("plot.**"
|
| 6816 | 6816 |
LEFT JOIN taxonoccurrence ON ((taxonoccurrence.locationevent_id = "plot.**".locationevent__pkey))) |
| 6817 | 6817 |
LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) |
| ... | ... | |
| 6849 | 6849 |
|
| 6850 | 6850 |
|
| 6851 | 6851 |
-- |
| 6852 |
-- Name: COLUMN analytical_stem_view.country; Type: COMMENT; Schema: public; Owner: -
|
|
| 6852 |
-- Name: COLUMN analytical_stem_view."country@DwC@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 6853 | 6853 |
-- |
| 6854 | 6854 |
|
| 6855 |
COMMENT ON COLUMN analytical_stem_view.country IS 'country@DwC@vegpath.org; "The name of the country or major administrative unit in which the Location occurs"';
|
|
| 6855 |
COMMENT ON COLUMN analytical_stem_view."country@DwC@vegpath.org" IS '"The name of the country or major administrative unit in which the Location occurs"';
|
|
| 6856 | 6856 |
|
| 6857 | 6857 |
|
| 6858 | 6858 |
-- |
| 6859 |
-- Name: COLUMN analytical_stem_view."stateProvince"; Type: COMMENT; Schema: public; Owner: - |
|
| 6860 |
-- |
|
| 6861 |
|
|
| 6862 |
COMMENT ON COLUMN analytical_stem_view."stateProvince" IS 'stateProvince@DwC@vegpath.org'; |
|
| 6863 |
|
|
| 6864 |
|
|
| 6865 |
-- |
|
| 6866 |
-- Name: COLUMN analytical_stem_view.county; Type: COMMENT; Schema: public; Owner: - |
|
| 6867 |
-- |
|
| 6868 |
|
|
| 6869 |
COMMENT ON COLUMN analytical_stem_view.county IS 'county@DwC@vegpath.org'; |
|
| 6870 |
|
|
| 6871 |
|
|
| 6872 |
-- |
|
| 6873 |
-- Name: COLUMN analytical_stem_view.locality; Type: COMMENT; Schema: public; Owner: - |
|
| 6874 |
-- |
|
| 6875 |
|
|
| 6876 |
COMMENT ON COLUMN analytical_stem_view.locality IS 'locality@DwC@vegpath.org'; |
|
| 6877 |
|
|
| 6878 |
|
|
| 6879 |
-- |
|
| 6880 |
-- Name: COLUMN analytical_stem_view."decimalLatitude"; Type: COMMENT; Schema: public; Owner: - |
|
| 6881 |
-- |
|
| 6882 |
|
|
| 6883 |
COMMENT ON COLUMN analytical_stem_view."decimalLatitude" IS 'decimalLatitude@DwC@vegpath.org'; |
|
| 6884 |
|
|
| 6885 |
|
|
| 6886 |
-- |
|
| 6887 |
-- Name: COLUMN analytical_stem_view."decimalLongitude"; Type: COMMENT; Schema: public; Owner: - |
|
| 6888 |
-- |
|
| 6889 |
|
|
| 6890 |
COMMENT ON COLUMN analytical_stem_view."decimalLongitude" IS 'decimalLongitude@DwC@vegpath.org'; |
|
| 6891 |
|
|
| 6892 |
|
|
| 6893 |
-- |
|
| 6894 |
-- Name: COLUMN analytical_stem_view."coordinateUncertaintyInMeters"; Type: COMMENT; Schema: public; Owner: - |
|
| 6895 |
-- |
|
| 6896 |
|
|
| 6897 |
COMMENT ON COLUMN analytical_stem_view."coordinateUncertaintyInMeters" IS 'coordinateUncertaintyInMeters@DwC@vegpath.org'; |
|
| 6898 |
|
|
| 6899 |
|
|
| 6900 |
-- |
|
| 6901 | 6859 |
-- Name: COLUMN analytical_stem_view."coordinateSource_bien"; Type: COMMENT; Schema: public; Owner: - |
| 6902 | 6860 |
-- |
| 6903 | 6861 |
|
| ... | ... | |
| 6926 | 6884 |
|
| 6927 | 6885 |
|
| 6928 | 6886 |
-- |
| 6929 |
-- Name: COLUMN analytical_stem_view."projectID"; Type: COMMENT; Schema: public; Owner: - |
|
| 6887 |
-- Name: COLUMN analytical_stem_view."projectID@VegX@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 6930 | 6888 |
-- |
| 6931 | 6889 |
|
| 6932 |
COMMENT ON COLUMN analytical_stem_view."projectID" IS 'projectID@VegX@vegpath.org; "A reference to a specific ''project''"';
|
|
| 6890 |
COMMENT ON COLUMN analytical_stem_view."projectID@VegX@vegpath.org" IS '"A reference to a specific ''project''"';
|
|
| 6933 | 6891 |
|
| 6934 | 6892 |
|
| 6935 | 6893 |
-- |
| ... | ... | |
| 6940 | 6898 |
|
| 6941 | 6899 |
|
| 6942 | 6900 |
-- |
| 6943 |
-- Name: COLUMN analytical_stem_view."locationID"; Type: COMMENT; Schema: public; Owner: -
|
|
| 6901 |
-- Name: COLUMN analytical_stem_view."subplot@SALVIAS@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 6944 | 6902 |
-- |
| 6945 | 6903 |
|
| 6946 |
COMMENT ON COLUMN analytical_stem_view."locationID" IS 'locationID@DwC@vegpath.org';
|
|
| 6904 |
COMMENT ON COLUMN analytical_stem_view."subplot@SALVIAS@vegpath.org" IS '"Code for subplot, line, or any other subsample or subdivision of plot"';
|
|
| 6947 | 6905 |
|
| 6948 | 6906 |
|
| 6949 | 6907 |
-- |
| 6950 |
-- Name: COLUMN analytical_stem_view."locationName"; Type: COMMENT; Schema: public; Owner: - |
|
| 6951 |
-- |
|
| 6952 |
|
|
| 6953 |
COMMENT ON COLUMN analytical_stem_view."locationName" IS 'locationName@DwC@vegpath.org'; |
|
| 6954 |
|
|
| 6955 |
|
|
| 6956 |
-- |
|
| 6957 |
-- Name: COLUMN analytical_stem_view.subplot; Type: COMMENT; Schema: public; Owner: - |
|
| 6958 |
-- |
|
| 6959 |
|
|
| 6960 |
COMMENT ON COLUMN analytical_stem_view.subplot IS 'subplot@SALVIAS@vegpath.org; "Code for subplot, line, or any other subsample or subdivision of plot"'; |
|
| 6961 |
|
|
| 6962 |
|
|
| 6963 |
-- |
|
| 6964 | 6908 |
-- Name: COLUMN analytical_stem_view.location__cultivated__bien; Type: COMMENT; Schema: public; Owner: - |
| 6965 | 6909 |
-- |
| 6966 | 6910 |
|
| ... | ... | |
| 6968 | 6912 |
|
| 6969 | 6913 |
|
| 6970 | 6914 |
-- |
| 6971 |
-- Name: COLUMN analytical_stem_view.locationevent__pkey; Type: COMMENT; Schema: public; Owner: -
|
|
| 6915 |
-- Name: COLUMN analytical_stem_view."locationevent.locationevent_id@VegBIEN.public@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 6972 | 6916 |
-- |
| 6973 | 6917 |
|
| 6974 |
COMMENT ON COLUMN analytical_stem_view.locationevent__pkey IS 'locationevent.locationevent_id@VegBIEN.public@vegpath.org; autogenerated ID for locationevent';
|
|
| 6918 |
COMMENT ON COLUMN analytical_stem_view."locationevent.locationevent_id@VegBIEN.public@vegpath.org" IS 'autogenerated ID for locationevent';
|
|
| 6975 | 6919 |
|
| 6976 | 6920 |
|
| 6977 | 6921 |
-- |
| 6978 |
-- Name: COLUMN analytical_stem_view."eventDate"; Type: COMMENT; Schema: public; Owner: - |
|
| 6979 |
-- |
|
| 6980 |
|
|
| 6981 |
COMMENT ON COLUMN analytical_stem_view."eventDate" IS 'eventDate@DwC@vegpath.org'; |
|
| 6982 |
|
|
| 6983 |
|
|
| 6984 |
-- |
|
| 6985 | 6922 |
-- Name: COLUMN analytical_stem_view."elevationInMeters"; Type: COMMENT; Schema: public; Owner: - |
| 6986 | 6923 |
-- |
| 6987 | 6924 |
|
| ... | ... | |
| 6989 | 6926 |
|
| 6990 | 6927 |
|
| 6991 | 6928 |
-- |
| 6992 |
-- Name: COLUMN analytical_stem_view."slopeAspect"; Type: COMMENT; Schema: public; Owner: - |
|
| 6993 |
-- |
|
| 6994 |
|
|
| 6995 |
COMMENT ON COLUMN analytical_stem_view."slopeAspect" IS 'slopeAspect@VegX@vegpath.org'; |
|
| 6996 |
|
|
| 6997 |
|
|
| 6998 |
-- |
|
| 6999 |
-- Name: COLUMN analytical_stem_view."slopeGradient"; Type: COMMENT; Schema: public; Owner: - |
|
| 7000 |
-- |
|
| 7001 |
|
|
| 7002 |
COMMENT ON COLUMN analytical_stem_view."slopeGradient" IS 'slopeGradient@VegX@vegpath.org'; |
|
| 7003 |
|
|
| 7004 |
|
|
| 7005 |
-- |
|
| 7006 | 6929 |
-- Name: COLUMN analytical_stem_view."plotArea_ha"; Type: COMMENT; Schema: public; Owner: - |
| 7007 | 6930 |
-- |
| 7008 | 6931 |
|
| ... | ... | |
| 7010 | 6933 |
|
| 7011 | 6934 |
|
| 7012 | 6935 |
-- |
| 7013 |
-- Name: COLUMN analytical_stem_view."samplingProtocol"; Type: COMMENT; Schema: public; Owner: - |
|
| 7014 |
-- |
|
| 7015 |
|
|
| 7016 |
COMMENT ON COLUMN analytical_stem_view."samplingProtocol" IS 'samplingProtocol@DwC@vegpath.org'; |
|
| 7017 |
|
|
| 7018 |
|
|
| 7019 |
-- |
|
| 7020 | 6936 |
-- Name: COLUMN analytical_stem_view."temperature_C"; Type: COMMENT; Schema: public; Owner: - |
| 7021 | 6937 |
-- |
| 7022 | 6938 |
|
| ... | ... | |
| 7073 | 6989 |
|
| 7074 | 6990 |
|
| 7075 | 6991 |
-- |
| 7076 |
-- Name: COLUMN analytical_stem_view."occurrenceID"; Type: COMMENT; Schema: public; Owner: - |
|
| 7077 |
-- |
|
| 7078 |
|
|
| 7079 |
COMMENT ON COLUMN analytical_stem_view."occurrenceID" IS 'occurrenceID@DwC@vegpath.org'; |
|
| 7080 |
|
|
| 7081 |
|
|
| 7082 |
-- |
|
| 7083 |
-- Name: COLUMN analytical_stem_view."recordedBy"; Type: COMMENT; Schema: public; Owner: - |
|
| 7084 |
-- |
|
| 7085 |
|
|
| 7086 |
COMMENT ON COLUMN analytical_stem_view."recordedBy" IS 'recordedBy@DwC@vegpath.org'; |
|
| 7087 |
|
|
| 7088 |
|
|
| 7089 |
-- |
|
| 7090 |
-- Name: COLUMN analytical_stem_view."recordNumber"; Type: COMMENT; Schema: public; Owner: - |
|
| 7091 |
-- |
|
| 7092 |
|
|
| 7093 |
COMMENT ON COLUMN analytical_stem_view."recordNumber" IS 'recordNumber@DwC@vegpath.org'; |
|
| 7094 |
|
|
| 7095 |
|
|
| 7096 |
-- |
|
| 7097 | 6992 |
-- Name: COLUMN analytical_stem_view."dateCollected"; Type: COMMENT; Schema: public; Owner: - |
| 7098 | 6993 |
-- |
| 7099 | 6994 |
|
| ... | ... | |
| 7115 | 7010 |
|
| 7116 | 7011 |
|
| 7117 | 7012 |
-- |
| 7118 |
-- Name: COLUMN analytical_stem_view."identifiedBy"; Type: COMMENT; Schema: public; Owner: -
|
|
| 7013 |
-- Name: COLUMN analytical_stem_view."Family_matched@TNRS@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 7119 | 7014 |
-- |
| 7120 | 7015 |
|
| 7121 |
COMMENT ON COLUMN analytical_stem_view."identifiedBy" IS 'identifiedBy@DwC@vegpath.org';
|
|
| 7016 |
COMMENT ON COLUMN analytical_stem_view."Family_matched@TNRS@vegpath.org" IS '"The closest matching family in the TNRS database to the family submitted"';
|
|
| 7122 | 7017 |
|
| 7123 | 7018 |
|
| 7124 | 7019 |
-- |
| 7125 |
-- Name: COLUMN analytical_stem_view."dateIdentified"; Type: COMMENT; Schema: public; Owner: - |
|
| 7126 |
-- |
|
| 7127 |
|
|
| 7128 |
COMMENT ON COLUMN analytical_stem_view."dateIdentified" IS 'dateIdentified@DwC@vegpath.org'; |
|
| 7129 |
|
|
| 7130 |
|
|
| 7131 |
-- |
|
| 7132 |
-- Name: COLUMN analytical_stem_view."identificationRemarks"; Type: COMMENT; Schema: public; Owner: - |
|
| 7133 |
-- |
|
| 7134 |
|
|
| 7135 |
COMMENT ON COLUMN analytical_stem_view."identificationRemarks" IS 'identificationRemarks@DwC@vegpath.org'; |
|
| 7136 |
|
|
| 7137 |
|
|
| 7138 |
-- |
|
| 7139 |
-- Name: COLUMN analytical_stem_view.family_matched; Type: COMMENT; Schema: public; Owner: - |
|
| 7140 |
-- |
|
| 7141 |
|
|
| 7142 |
COMMENT ON COLUMN analytical_stem_view.family_matched IS 'Family_matched@TNRS@vegpath.org; "The closest matching family in the TNRS database to the family submitted"'; |
|
| 7143 |
|
|
| 7144 |
|
|
| 7145 |
-- |
|
| 7146 | 7020 |
-- Name: COLUMN analytical_stem_view."taxonName_matched"; Type: COMMENT; Schema: public; Owner: - |
| 7147 | 7021 |
-- |
| 7148 | 7022 |
|
| ... | ... | |
| 7220 | 7094 |
|
| 7221 | 7095 |
|
| 7222 | 7096 |
-- |
| 7223 |
-- Name: COLUMN analytical_stem_view.scrubbed_morphospecies_binomial; Type: COMMENT; Schema: public; Owner: -
|
|
| 7097 |
-- Name: COLUMN analytical_stem_view."speciesBinomialWithMorphospecies@VegCore@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 7224 | 7098 |
-- |
| 7225 | 7099 |
|
| 7226 |
COMMENT ON COLUMN analytical_stem_view.scrubbed_morphospecies_binomial IS 'speciesBinomialWithMorphospecies@VegCore@vegpath.org; "combine the `taxon` plus `morphospecies` to produce a unique string, `taxonMorphospecies`"';
|
|
| 7100 |
COMMENT ON COLUMN analytical_stem_view."speciesBinomialWithMorphospecies@VegCore@vegpath.org" IS '"combine the `taxon` plus `morphospecies` to produce a unique string, `taxonMorphospecies`"';
|
|
| 7227 | 7101 |
|
| 7228 | 7102 |
|
| 7229 | 7103 |
-- |
| ... | ... | |
| 7234 | 7108 |
|
| 7235 | 7109 |
|
| 7236 | 7110 |
-- |
| 7237 |
-- Name: COLUMN analytical_stem_view."reproductiveCondition"; Type: COMMENT; Schema: public; Owner: - |
|
| 7238 |
-- |
|
| 7239 |
|
|
| 7240 |
COMMENT ON COLUMN analytical_stem_view."reproductiveCondition" IS 'reproductiveCondition@DwC@vegpath.org'; |
|
| 7241 |
|
|
| 7242 |
|
|
| 7243 |
-- |
|
| 7244 | 7111 |
-- Name: COLUMN analytical_stem_view.threatened_bien; Type: COMMENT; Schema: public; Owner: - |
| 7245 | 7112 |
-- |
| 7246 | 7113 |
|
| ... | ... | |
| 7262 | 7129 |
|
| 7263 | 7130 |
|
| 7264 | 7131 |
-- |
| 7265 |
-- Name: COLUMN analytical_stem_view."occurrenceRemarks"; Type: COMMENT; Schema: public; Owner: - |
|
| 7266 |
-- |
|
| 7267 |
|
|
| 7268 |
COMMENT ON COLUMN analytical_stem_view."occurrenceRemarks" IS 'occurrenceRemarks@DwC@vegpath.org'; |
|
| 7269 |
|
|
| 7270 |
|
|
| 7271 |
-- |
|
| 7272 |
-- Name: COLUMN analytical_stem_view."coverPercent"; Type: COMMENT; Schema: public; Owner: - |
|
| 7273 |
-- |
|
| 7274 |
|
|
| 7275 |
COMMENT ON COLUMN analytical_stem_view."coverPercent" IS 'coverPercent@VegX@vegpath.org'; |
|
| 7276 |
|
|
| 7277 |
|
|
| 7278 |
-- |
|
| 7279 | 7132 |
-- Name: COLUMN analytical_stem_view."diameterBreastHeight_cm"; Type: COMMENT; Schema: public; Owner: - |
| 7280 | 7133 |
-- |
| 7281 | 7134 |
|
| ... | ... | |
| 7346 | 7199 |
|
| 7347 | 7200 |
|
| 7348 | 7201 |
-- |
| 7349 |
-- Name: COLUMN analytical_stem_view."individualCount"; Type: COMMENT; Schema: public; Owner: -
|
|
| 7202 |
-- Name: COLUMN analytical_stem_view."stemCode@stemlocation@VegBank@vegpath.org"; Type: COMMENT; Schema: public; Owner: -
|
|
| 7350 | 7203 |
-- |
| 7351 | 7204 |
|
| 7352 |
COMMENT ON COLUMN analytical_stem_view."individualCount" IS 'individualCount@DwC@vegpath.org';
|
|
| 7205 |
COMMENT ON COLUMN analytical_stem_view."stemCode@stemlocation@VegBank@vegpath.org" IS '"Name or code applied to a specific stem in the plot. This is generally a numeric label to associate a field data entry with a stem in the database."';
|
|
| 7353 | 7206 |
|
| 7354 | 7207 |
|
| 7355 | 7208 |
-- |
| 7356 |
-- Name: COLUMN analytical_stem_view."authorStemCode"; Type: COMMENT; Schema: public; Owner: - |
|
| 7357 |
-- |
|
| 7358 |
|
|
| 7359 |
COMMENT ON COLUMN analytical_stem_view."authorStemCode" IS 'stemCode@stemlocation@VegBank@vegpath.org; "Name or code applied to a specific stem in the plot. This is generally a numeric label to associate a field data entry with a stem in the database."'; |
|
| 7360 |
|
|
| 7361 |
|
|
| 7362 |
-- |
|
| 7363 | 7209 |
-- Name: classcontributor; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 7364 | 7210 |
-- |
| 7365 | 7211 |
|
Also available in: Unified diff
schemas/public_.sql: analytical_stem_view: renamed terms to link URLs, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#data-dictionary-rename-terms-to-link-URLs