Revision 13836
Added by Aaron Marcuse-Kubitza over 10 years ago
| vegbien.sql | ||
|---|---|---|
| 5375 | 5375 |
-- |
| 5376 | 5376 |
|
| 5377 | 5377 |
CREATE TABLE analytical_stem ( |
| 5378 |
datasource text NOT NULL,
|
|
| 5379 |
country text,
|
|
| 5380 |
"stateProvince" text, |
|
| 5381 |
county text,
|
|
| 5382 |
locality text,
|
|
| 5383 |
"decimalLatitude" double precision, |
|
| 5384 |
"decimalLongitude" double precision, |
|
| 5385 |
"coordinateUncertaintyInMeters" double precision, |
|
| 5386 |
"coordinateSource_bien" coordinatesource,
|
|
| 5387 |
"georeferenceProtocol_bien" text,
|
|
| 5378 |
"datasource__@Brad__.identifier_examples@vegpath.org" text NOT NULL,
|
|
| 5379 |
"country__@DwC__@vegpath.org" text,
|
|
| 5380 |
"stateProvince__@DwC__@vegpath.org" text,
|
|
| 5381 |
"county__@DwC__@vegpath.org" text,
|
|
| 5382 |
"locality__@DwC__@vegpath.org" text,
|
|
| 5383 |
"decimalLatitude__@DwC__@vegpath.org" double precision,
|
|
| 5384 |
"decimalLongitude__@DwC__@vegpath.org" double precision,
|
|
| 5385 |
"coordinateUncertaintyInMeters__@DwC__@vegpath.org" double precision,
|
|
| 5386 |
"georeferenceSources__@DwC__@vegpath.org" coordinatesource,
|
|
| 5387 |
"georeferenceProtocol__@DwC__@vegpath.org" text,
|
|
| 5388 | 5388 |
geovalid_bien integer, |
| 5389 | 5389 |
"isNewWorld_bien" integer, |
| 5390 |
"projectID" text, |
|
| 5391 |
project_contributors text[],
|
|
| 5392 |
"locationID" text NOT NULL, |
|
| 5393 |
"locationName" text,
|
|
| 5394 |
subplot text,
|
|
| 5390 |
"projectID__@VegX__.plotObservation@vegpath.org" text,
|
|
| 5391 |
"projectContributor[s]__@VegBank__@vegpath.org" text[],
|
|
| 5392 |
"locationID__@DwC__@vegpath.org" text NOT NULL,
|
|
| 5393 |
"plotName__@VegX__.plot@vegpath.org" text,
|
|
| 5394 |
"subplot__@SALVIAS__.Plot_data@vegpath.org" text,
|
|
| 5395 | 5395 |
location__cultivated__bien boolean, |
| 5396 |
locationevent__pkey integer,
|
|
| 5397 |
"eventDate" date, |
|
| 5398 |
"elevationInMeters" double precision,
|
|
| 5399 |
"slopeAspect" double precision, |
|
| 5400 |
"slopeGradient" double precision, |
|
| 5401 |
"plotArea_ha" double precision,
|
|
| 5402 |
"samplingProtocol" text, |
|
| 5403 |
"temperature_C" double precision,
|
|
| 5404 |
precipitation_m double precision,
|
|
| 5405 |
stratum__name text,
|
|
| 5406 |
communities text[],
|
|
| 5407 |
plot__collectors text[],
|
|
| 5408 |
"specimenHolderInstitutions" text,
|
|
| 5409 |
collection text,
|
|
| 5410 |
"accessionNumber" text,
|
|
| 5411 |
"occurrenceID" text, |
|
| 5412 |
"recordedBy" text, |
|
| 5413 |
"recordNumber" text, |
|
| 5414 |
"dateCollected" date,
|
|
| 5415 |
family_verbatim text,
|
|
| 5416 |
"scientificName_verbatim" text,
|
|
| 5417 |
"identifiedBy" text, |
|
| 5418 |
"dateIdentified" date, |
|
| 5419 |
"identificationRemarks" text, |
|
| 5420 |
family_matched text,
|
|
| 5421 |
"taxonName_matched" text,
|
|
| 5422 |
"scientificNameAuthorship_matched" text,
|
|
| 5423 |
"higherPlantGroup_bien" higher_plant_group,
|
|
| 5424 |
taxonomic_status text,
|
|
| 5396 |
"locationevent.locationevent_id__@VegBIEN__.public@vegpath.org" integer,
|
|
| 5397 |
"eventDate__@DwC__@vegpath.org" date,
|
|
| 5398 |
"(-minimum-)ElevationInMeters__@DwC__@vegpath.org" double precision,
|
|
| 5399 |
"slopeAspect[_deg]__@VegX__.plot@vegpath.org" double precision,
|
|
| 5400 |
"slopeGradient[_deg]__@VegX__.plot@vegpath.org" double precision,
|
|
| 5401 |
"plot.area[_ha]__@VegX__@vegpath.org" double precision,
|
|
| 5402 |
"samplingProtocol__@DwC__@vegpath.org" text,
|
|
| 5403 |
"temperature[_C]__@VegX__.plotObs.obsCond@vegpath.org" double precision,
|
|
| 5404 |
"precip_mm__@SALVIAS__.Plot_metadata@vegpath.org" double precision,
|
|
| 5405 |
"stratumName__@VegX__.stratum@vegpath.org" text,
|
|
| 5406 |
"communityConcept.name__@VegX__.communityDet@vegpath.org" text[],
|
|
| 5407 |
"observationContributor[s]__@VegBank__@vegpath.org" text[],
|
|
| 5408 |
"[custodial_]institutionCode[s]__@DwC__@vegpath.org" text,
|
|
| 5409 |
"collectionCode__@DwC__@vegpath.org" text,
|
|
| 5410 |
"catalogNumber__@DwC__@vegpath.org" text,
|
|
| 5411 |
"occurrenceID__@DwC__@vegpath.org" text,
|
|
| 5412 |
"recordedBy__@DwC__@vegpath.org" text,
|
|
| 5413 |
"recordNumber__@DwC__@vegpath.org" text,
|
|
| 5414 |
"(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org" date,
|
|
| 5415 |
"[verbatim_]family__@DwC__@vegpath.org" text,
|
|
| 5416 |
"[verbatim_]scientificName__@DwC__@vegpath.org" text,
|
|
| 5417 |
"identifiedBy__@DwC__@vegpath.org" text,
|
|
| 5418 |
"dateIdentified__@DwC__@vegpath.org" date,
|
|
| 5419 |
"identificationRemarks__@DwC__@vegpath.org" text,
|
|
| 5420 |
"Family_matched__@TNRS__@vegpath.org" text,
|
|
| 5421 |
"Name_matched__@TNRS__@vegpath.org" text,
|
|
| 5422 |
"Name_matched_author__@TNRS__@vegpath.org" text,
|
|
| 5423 |
"[higher_plant_group~]higherClassification__@DwC__@vegpath.org" higher_plant_group,
|
|
| 5424 |
"taxonomicStatus__@DwC__@vegpath.org" text,
|
|
| 5425 | 5425 |
scrubbed_family text, |
| 5426 | 5426 |
scrubbed_genus text, |
| 5427 | 5427 |
scrubbed_specific_epithet text, |
| ... | ... | |
| 5429 | 5429 |
scrubbed_taxon_name_no_author text, |
| 5430 | 5430 |
scrubbed_author text, |
| 5431 | 5431 |
scrubbed_taxon_name_with_author text, |
| 5432 |
scrubbed_morphospecies_binomial text,
|
|
| 5433 |
"growthForm" growthform,
|
|
| 5434 |
"reproductiveCondition" text, |
|
| 5432 |
"speciesBinomialWithMorphospecies__@VegCore__@vegpath.org" text,
|
|
| 5433 |
"[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org" growthform,
|
|
| 5434 |
"reproductiveCondition__@DwC__@vegpath.org" text,
|
|
| 5435 | 5435 |
threatened_bien integer, |
| 5436 | 5436 |
cultivated_bien integer, |
| 5437 | 5437 |
"cultivatedBasis_bien" text, |
| 5438 |
"occurrenceRemarks" text, |
|
| 5439 |
"coverPercent" double precision, |
|
| 5440 |
"diameterBreastHeight_cm" double precision,
|
|
| 5441 |
height_m double precision,
|
|
| 5442 |
tag text,
|
|
| 5443 |
"organismX_m" double precision,
|
|
| 5444 |
"organismY_m" double precision,
|
|
| 5445 |
"taxonOccurrenceID" text,
|
|
| 5446 |
"authorTaxonCode" text,
|
|
| 5447 |
"aggregateOrganismObservationID" text,
|
|
| 5448 |
"individualObservationID" text,
|
|
| 5449 |
"individualCode" text,
|
|
| 5450 |
"individualCount" integer, |
|
| 5451 |
"authorStemCode" text,
|
|
| 5452 |
taxon_occurrence__pkey integer NOT NULL
|
|
| 5438 |
"occurrenceRemarks__@DwC__@vegpath.org" text,
|
|
| 5439 |
"coverPercent__@VegX__.attribute.ordinal@vegpath.org" double precision,
|
|
| 5440 |
"stem_dbh[_cm]__@SALVIAS__.Plot_data@vegpath.org" double precision,
|
|
| 5441 |
"stemHeight[_m]__@VegBank__.stemCount@vegpath.org" double precision,
|
|
| 5442 |
"[tag=]identificationLabel__@VegX__.individual@vegpath.org" text,
|
|
| 5443 |
"relativeX[_m]__@VegX__.individualOrganismObs@vegpath.org" double precision,
|
|
| 5444 |
"relativeY[_m]__@VegX__.individualOrganismObs@vegpath.org" double precision,
|
|
| 5445 |
"taxonObservation[.id]__@VegBank__@vegpath.org" text,
|
|
| 5446 |
"taxonNameUsageConcept.authorCode__@VegX__@vegpath.org" text,
|
|
| 5447 |
"aggregateOrganismObservation.id__@VegX__@vegpath.org" text,
|
|
| 5448 |
"individualOrganismObservation.id__@VegX__@vegpath.org" text,
|
|
| 5449 |
"individualID__@DwC__@vegpath.org" text,
|
|
| 5450 |
"individualCount__@DwC__@vegpath.org" integer,
|
|
| 5451 |
"stemCode__@VegBank__.stemLocation@vegpath.org" text,
|
|
| 5452 |
"TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org" integer NOT NULL
|
|
| 5453 | 5453 |
); |
| 5454 | 5454 |
|
| 5455 | 5455 |
|
| ... | ... | |
| 5458 | 5458 |
-- |
| 5459 | 5459 |
|
| 5460 | 5460 |
CREATE VIEW "2013-10-18.Brian_Enquist.Canadensys" AS |
| 5461 |
SELECT analytical_stem.scrubbed_morphospecies_binomial AS species,
|
|
| 5462 |
analytical_stem."decimalLatitude" AS latitude__deg, |
|
| 5463 |
analytical_stem."decimalLongitude" AS longitude__deg, |
|
| 5464 |
analytical_stem."coordinateUncertaintyInMeters" AS coords__uncertainty__m |
|
| 5461 |
SELECT analytical_stem."speciesBinomialWithMorphospecies__@VegCore__@vegpath.org" AS species,
|
|
| 5462 |
analytical_stem."decimalLatitude__@DwC__@vegpath.org" AS latitude__deg,
|
|
| 5463 |
analytical_stem."decimalLongitude__@DwC__@vegpath.org" AS longitude__deg,
|
|
| 5464 |
analytical_stem."coordinateUncertaintyInMeters__@DwC__@vegpath.org" AS coords__uncertainty__m
|
|
| 5465 | 5465 |
FROM analytical_stem |
| 5466 |
WHERE (((((analytical_stem.scrubbed_morphospecies_binomial = ANY (ARRAY['Juniperus scopulorum'::text, 'Picea engelmannii'::text, 'Pinus contorta'::text, 'Pinus edulis'::text, 'Pinus ponderosa'::text, 'Populus tremuloides'::text, 'Pseudotsuga menziesii'::text, 'Quercus gambelii'::text])) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND COALESCE((analytical_stem.geovalid_bien)::boolean, true)) AND (analytical_stem."decimalLatitude" IS NOT NULL)) AND (analytical_stem."decimalLongitude" IS NOT NULL))
|
|
| 5467 |
ORDER BY analytical_stem.scrubbed_morphospecies_binomial;
|
|
| 5466 |
WHERE (((((analytical_stem."speciesBinomialWithMorphospecies__@VegCore__@vegpath.org" = ANY (ARRAY['Juniperus scopulorum'::text, 'Picea engelmannii'::text, 'Pinus contorta'::text, 'Pinus edulis'::text, 'Pinus ponderosa'::text, 'Populus tremuloides'::text, 'Pseudotsuga menziesii'::text, 'Quercus gambelii'::text])) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND COALESCE((analytical_stem.geovalid_bien)::boolean, true)) AND (analytical_stem."decimalLatitude__@DwC__@vegpath.org" IS NOT NULL)) AND (analytical_stem."decimalLongitude__@DwC__@vegpath.org" IS NOT NULL))
|
|
| 5467 |
ORDER BY analytical_stem."speciesBinomialWithMorphospecies__@VegCore__@vegpath.org";
|
|
| 5468 | 5468 |
|
| 5469 | 5469 |
|
| 5470 | 5470 |
-- |
| ... | ... | |
| 5472 | 5472 |
-- |
| 5473 | 5473 |
|
| 5474 | 5474 |
CREATE VIEW "2013-7-10.Naia.range_limiting_factors" AS |
| 5475 |
SELECT analytical_stem.taxon_occurrence__pkey AS occurrence_id,
|
|
| 5476 |
analytical_stem.scrubbed_morphospecies_binomial AS species,
|
|
| 5477 |
analytical_stem."decimalLatitude" AS latitude__deg, |
|
| 5478 |
analytical_stem."decimalLongitude" AS longitude__deg |
|
| 5475 |
SELECT analytical_stem."TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org" AS occurrence_id,
|
|
| 5476 |
analytical_stem."speciesBinomialWithMorphospecies__@VegCore__@vegpath.org" AS species,
|
|
| 5477 |
analytical_stem."decimalLatitude__@DwC__@vegpath.org" AS latitude__deg,
|
|
| 5478 |
analytical_stem."decimalLongitude__@DwC__@vegpath.org" AS longitude__deg
|
|
| 5479 | 5479 |
FROM analytical_stem |
| 5480 |
WHERE ((((((NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false)) AND COALESCE((analytical_stem.geovalid_bien)::boolean, true)) AND (analytical_stem.scrubbed_morphospecies_binomial IS NOT NULL)) AND (analytical_stem."decimalLatitude" IS NOT NULL)) AND (analytical_stem."decimalLongitude" IS NOT NULL)) AND COALESCE((analytical_stem."coordinateUncertaintyInMeters" <= _km_to_m((10)::double precision)), true));
|
|
| 5480 |
WHERE ((((((NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false)) AND COALESCE((analytical_stem.geovalid_bien)::boolean, true)) AND (analytical_stem."speciesBinomialWithMorphospecies__@VegCore__@vegpath.org" IS NOT NULL)) AND (analytical_stem."decimalLatitude__@DwC__@vegpath.org" IS NOT NULL)) AND (analytical_stem."decimalLongitude__@DwC__@vegpath.org" IS NOT NULL)) AND COALESCE((analytical_stem."coordinateUncertaintyInMeters__@DwC__@vegpath.org" <= _km_to_m((10)::double precision)), true));
|
|
| 5481 | 5481 |
|
| 5482 | 5482 |
|
| 5483 | 5483 |
-- |
| ... | ... | |
| 5485 | 5485 |
-- |
| 5486 | 5486 |
|
| 5487 | 5487 |
CREATE VIEW "2014-3-11.Jeff_Ott.climatic_range_determinants" AS |
| 5488 |
SELECT analytical_stem.datasource, |
|
| 5489 |
analytical_stem."decimalLatitude", |
|
| 5490 |
analytical_stem."decimalLongitude", |
|
| 5491 |
analytical_stem."higherPlantGroup_bien" AS "higherPlantGroup",
|
|
| 5488 |
SELECT analytical_stem."datasource__@Brad__.identifier_examples@vegpath.org" AS datasource,
|
|
| 5489 |
analytical_stem."decimalLatitude__@DwC__@vegpath.org" AS "decimalLatitude",
|
|
| 5490 |
analytical_stem."decimalLongitude__@DwC__@vegpath.org" AS "decimalLongitude",
|
|
| 5491 |
analytical_stem."[higher_plant_group~]higherClassification__@DwC__@vegpath.org" AS "higherPlantGroup",
|
|
| 5492 | 5492 |
analytical_stem.scrubbed_family AS family, |
| 5493 | 5493 |
analytical_stem.scrubbed_genus AS genus, |
| 5494 | 5494 |
analytical_stem.scrubbed_species_binomial AS "speciesBinomial", |
| 5495 | 5495 |
analytical_stem.scrubbed_taxon_name_with_author AS "scientificName" |
| 5496 | 5496 |
FROM analytical_stem |
| 5497 |
WHERE (((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND COALESCE((analytical_stem."isNewWorld_bien")::boolean, false)) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem.scrubbed_taxon_name_with_author IS NOT NULL)) AND (analytical_stem."decimalLatitude" IS NOT NULL)) AND (analytical_stem."decimalLongitude" IS NOT NULL));
|
|
| 5497 |
WHERE (((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND COALESCE((analytical_stem."isNewWorld_bien")::boolean, false)) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem.scrubbed_taxon_name_with_author IS NOT NULL)) AND (analytical_stem."decimalLatitude__@DwC__@vegpath.org" IS NOT NULL)) AND (analytical_stem."decimalLongitude__@DwC__@vegpath.org" IS NOT NULL));
|
|
| 5498 | 5498 |
|
| 5499 | 5499 |
|
| 5500 | 5500 |
-- |
| ... | ... | |
| 5502 | 5502 |
-- |
| 5503 | 5503 |
|
| 5504 | 5504 |
CREATE VIEW "2014-6-12.Jeff_Ott.climatic_range_determinants" AS |
| 5505 |
SELECT analytical_stem.datasource, |
|
| 5506 |
analytical_stem."decimalLatitude", |
|
| 5507 |
analytical_stem."decimalLongitude", |
|
| 5508 |
analytical_stem."coordinateUncertaintyInMeters", |
|
| 5505 |
SELECT analytical_stem."datasource__@Brad__.identifier_examples@vegpath.org" AS datasource,
|
|
| 5506 |
analytical_stem."decimalLatitude__@DwC__@vegpath.org" AS "decimalLatitude",
|
|
| 5507 |
analytical_stem."decimalLongitude__@DwC__@vegpath.org" AS "decimalLongitude",
|
|
| 5508 |
analytical_stem."coordinateUncertaintyInMeters__@DwC__@vegpath.org" AS "coordinateUncertaintyInMeters",
|
|
| 5509 | 5509 |
analytical_stem.geovalid_bien, |
| 5510 |
analytical_stem."dateCollected", |
|
| 5511 |
analytical_stem."higherPlantGroup_bien" AS "higherPlantGroup",
|
|
| 5510 |
analytical_stem."(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org" AS "dateCollected",
|
|
| 5511 |
analytical_stem."[higher_plant_group~]higherClassification__@DwC__@vegpath.org" AS "higherPlantGroup",
|
|
| 5512 | 5512 |
analytical_stem.scrubbed_family AS family, |
| 5513 | 5513 |
analytical_stem.scrubbed_genus AS genus, |
| 5514 | 5514 |
analytical_stem.scrubbed_species_binomial AS "speciesBinomial", |
| 5515 | 5515 |
analytical_stem.scrubbed_taxon_name_with_author AS "scientificName" |
| 5516 | 5516 |
FROM analytical_stem |
| 5517 |
WHERE ((((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND (COALESCE((analytical_stem."isNewWorld_bien")::boolean, false) OR lat_long_in_new_world(analytical_stem."decimalLatitude", analytical_stem."decimalLongitude"))) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem.scrubbed_family IS NOT NULL)) AND (analytical_stem."decimalLatitude" IS NOT NULL)) AND (analytical_stem."decimalLongitude" IS NOT NULL)) AND COALESCE((analytical_stem."coordinateUncertaintyInMeters" <= _km_to_m((10)::double precision)), true));
|
|
| 5517 |
WHERE ((((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND (COALESCE((analytical_stem."isNewWorld_bien")::boolean, false) OR lat_long_in_new_world(analytical_stem."decimalLatitude__@DwC__@vegpath.org", analytical_stem."decimalLongitude__@DwC__@vegpath.org"))) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem.scrubbed_family IS NOT NULL)) AND (analytical_stem."decimalLatitude__@DwC__@vegpath.org" IS NOT NULL)) AND (analytical_stem."decimalLongitude__@DwC__@vegpath.org" IS NOT NULL)) AND COALESCE((analytical_stem."coordinateUncertaintyInMeters__@DwC__@vegpath.org" <= _km_to_m((10)::double precision)), true));
|
|
| 5518 | 5518 |
|
| 5519 | 5519 |
|
| 5520 | 5520 |
-- |
| ... | ... | |
| 5522 | 5522 |
-- |
| 5523 | 5523 |
|
| 5524 | 5524 |
CREATE VIEW "2014-6-4.Iara_Lacher.reserve_prioritization" AS |
| 5525 |
SELECT analytical_stem.country, |
|
| 5526 |
analytical_stem."decimalLatitude", |
|
| 5527 |
analytical_stem."decimalLongitude", |
|
| 5528 |
analytical_stem."dateCollected", |
|
| 5529 |
COALESCE(analytical_stem."specimenHolderInstitutions", analytical_stem.datasource) AS orig_datasource,
|
|
| 5525 |
SELECT analytical_stem."country__@DwC__@vegpath.org" AS country,
|
|
| 5526 |
analytical_stem."decimalLatitude__@DwC__@vegpath.org" AS "decimalLatitude",
|
|
| 5527 |
analytical_stem."decimalLongitude__@DwC__@vegpath.org" AS "decimalLongitude",
|
|
| 5528 |
analytical_stem."(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org" AS "dateCollected",
|
|
| 5529 |
COALESCE(analytical_stem."[custodial_]institutionCode[s]__@DwC__@vegpath.org", analytical_stem."datasource__@Brad__.identifier_examples@vegpath.org") AS orig_datasource,
|
|
| 5530 | 5530 |
analytical_stem.scrubbed_family AS accepted_family, |
| 5531 | 5531 |
analytical_stem.scrubbed_genus AS accepted_genus, |
| 5532 | 5532 |
analytical_stem.scrubbed_species_binomial AS accepted_species_binomial, |
| 5533 | 5533 |
analytical_stem.scrubbed_taxon_name_with_author AS accepted_taxon_name_with_author, |
| 5534 |
analytical_stem."growthForm" AS lifeform
|
|
| 5534 |
analytical_stem."[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org" AS lifeform
|
|
| 5535 | 5535 |
FROM analytical_stem |
| 5536 |
WHERE ((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND COALESCE((analytical_stem.taxonomic_status = 'accepted'::text), false));
|
|
| 5536 |
WHERE ((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND COALESCE((analytical_stem."taxonomicStatus__@DwC__@vegpath.org" = 'accepted'::text), false));
|
|
| 5537 | 5537 |
|
| 5538 | 5538 |
|
| 5539 | 5539 |
-- |
| ... | ... | |
| 5652 | 5652 |
-- |
| 5653 | 5653 |
|
| 5654 | 5654 |
CREATE VIEW analytical_plot AS |
| 5655 |
SELECT analytical_stem.datasource,
|
|
| 5656 |
analytical_stem.country,
|
|
| 5657 |
analytical_stem."stateProvince", |
|
| 5658 |
analytical_stem.county,
|
|
| 5659 |
analytical_stem.locality,
|
|
| 5660 |
analytical_stem."decimalLatitude", |
|
| 5661 |
analytical_stem."decimalLongitude", |
|
| 5662 |
analytical_stem."coordinateUncertaintyInMeters", |
|
| 5663 |
analytical_stem."coordinateSource_bien",
|
|
| 5664 |
analytical_stem."georeferenceProtocol_bien",
|
|
| 5655 |
SELECT analytical_stem."datasource__@Brad__.identifier_examples@vegpath.org",
|
|
| 5656 |
analytical_stem."country__@DwC__@vegpath.org",
|
|
| 5657 |
analytical_stem."stateProvince__@DwC__@vegpath.org",
|
|
| 5658 |
analytical_stem."county__@DwC__@vegpath.org",
|
|
| 5659 |
analytical_stem."locality__@DwC__@vegpath.org",
|
|
| 5660 |
analytical_stem."decimalLatitude__@DwC__@vegpath.org",
|
|
| 5661 |
analytical_stem."decimalLongitude__@DwC__@vegpath.org",
|
|
| 5662 |
analytical_stem."coordinateUncertaintyInMeters__@DwC__@vegpath.org",
|
|
| 5663 |
analytical_stem."georeferenceSources__@DwC__@vegpath.org",
|
|
| 5664 |
analytical_stem."georeferenceProtocol__@DwC__@vegpath.org",
|
|
| 5665 | 5665 |
analytical_stem.geovalid_bien, |
| 5666 | 5666 |
analytical_stem."isNewWorld_bien", |
| 5667 |
analytical_stem."projectID", |
|
| 5668 |
analytical_stem.project_contributors,
|
|
| 5669 |
analytical_stem."locationID", |
|
| 5670 |
analytical_stem."locationName",
|
|
| 5671 |
analytical_stem.subplot,
|
|
| 5667 |
analytical_stem."projectID__@VegX__.plotObservation@vegpath.org",
|
|
| 5668 |
analytical_stem."projectContributor[s]__@VegBank__@vegpath.org",
|
|
| 5669 |
analytical_stem."locationID__@DwC__@vegpath.org",
|
|
| 5670 |
analytical_stem."plotName__@VegX__.plot@vegpath.org",
|
|
| 5671 |
analytical_stem."subplot__@SALVIAS__.Plot_data@vegpath.org",
|
|
| 5672 | 5672 |
analytical_stem.location__cultivated__bien, |
| 5673 |
analytical_stem."eventDate", |
|
| 5674 |
analytical_stem."elevationInMeters",
|
|
| 5675 |
analytical_stem."slopeAspect", |
|
| 5676 |
analytical_stem."slopeGradient", |
|
| 5677 |
analytical_stem."plotArea_ha",
|
|
| 5678 |
analytical_stem."samplingProtocol", |
|
| 5679 |
analytical_stem."temperature_C",
|
|
| 5680 |
analytical_stem.precipitation_m,
|
|
| 5681 |
analytical_stem.stratum__name,
|
|
| 5682 |
analytical_stem.communities,
|
|
| 5683 |
analytical_stem.plot__collectors,
|
|
| 5684 |
analytical_stem."recordedBy", |
|
| 5685 |
analytical_stem."recordNumber", |
|
| 5686 |
analytical_stem."dateCollected",
|
|
| 5687 |
analytical_stem.family_verbatim,
|
|
| 5688 |
analytical_stem."scientificName_verbatim",
|
|
| 5689 |
analytical_stem."identifiedBy", |
|
| 5690 |
analytical_stem."dateIdentified", |
|
| 5691 |
analytical_stem."identificationRemarks", |
|
| 5692 |
analytical_stem.family_matched,
|
|
| 5693 |
analytical_stem."taxonName_matched",
|
|
| 5694 |
analytical_stem."scientificNameAuthorship_matched",
|
|
| 5673 |
analytical_stem."eventDate__@DwC__@vegpath.org",
|
|
| 5674 |
analytical_stem."(-minimum-)ElevationInMeters__@DwC__@vegpath.org",
|
|
| 5675 |
analytical_stem."slopeAspect[_deg]__@VegX__.plot@vegpath.org",
|
|
| 5676 |
analytical_stem."slopeGradient[_deg]__@VegX__.plot@vegpath.org",
|
|
| 5677 |
analytical_stem."plot.area[_ha]__@VegX__@vegpath.org",
|
|
| 5678 |
analytical_stem."samplingProtocol__@DwC__@vegpath.org",
|
|
| 5679 |
analytical_stem."temperature[_C]__@VegX__.plotObs.obsCond@vegpath.org",
|
|
| 5680 |
analytical_stem."precip_mm__@SALVIAS__.Plot_metadata@vegpath.org",
|
|
| 5681 |
analytical_stem."stratumName__@VegX__.stratum@vegpath.org",
|
|
| 5682 |
analytical_stem."communityConcept.name__@VegX__.communityDet@vegpath.org",
|
|
| 5683 |
analytical_stem."observationContributor[s]__@VegBank__@vegpath.org",
|
|
| 5684 |
analytical_stem."recordedBy__@DwC__@vegpath.org",
|
|
| 5685 |
analytical_stem."recordNumber__@DwC__@vegpath.org",
|
|
| 5686 |
analytical_stem."(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org",
|
|
| 5687 |
analytical_stem."[verbatim_]family__@DwC__@vegpath.org",
|
|
| 5688 |
analytical_stem."[verbatim_]scientificName__@DwC__@vegpath.org",
|
|
| 5689 |
analytical_stem."identifiedBy__@DwC__@vegpath.org",
|
|
| 5690 |
analytical_stem."dateIdentified__@DwC__@vegpath.org",
|
|
| 5691 |
analytical_stem."identificationRemarks__@DwC__@vegpath.org",
|
|
| 5692 |
analytical_stem."Family_matched__@TNRS__@vegpath.org",
|
|
| 5693 |
analytical_stem."Name_matched__@TNRS__@vegpath.org",
|
|
| 5694 |
analytical_stem."Name_matched_author__@TNRS__@vegpath.org",
|
|
| 5695 | 5695 |
analytical_stem.scrubbed_family, |
| 5696 | 5696 |
analytical_stem.scrubbed_genus, |
| 5697 |
analytical_stem.scrubbed_morphospecies_binomial,
|
|
| 5697 |
analytical_stem."speciesBinomialWithMorphospecies__@VegCore__@vegpath.org",
|
|
| 5698 | 5698 |
analytical_stem.scrubbed_taxon_name_no_author, |
| 5699 | 5699 |
analytical_stem.scrubbed_author, |
| 5700 |
analytical_stem."growthForm",
|
|
| 5701 |
analytical_stem."reproductiveCondition", |
|
| 5700 |
analytical_stem."[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org",
|
|
| 5701 |
analytical_stem."reproductiveCondition__@DwC__@vegpath.org",
|
|
| 5702 | 5702 |
analytical_stem.cultivated_bien, |
| 5703 | 5703 |
analytical_stem."cultivatedBasis_bien", |
| 5704 |
analytical_stem."occurrenceRemarks", |
|
| 5705 |
analytical_stem."coverPercent", |
|
| 5706 |
analytical_stem."diameterBreastHeight_cm",
|
|
| 5707 |
analytical_stem.height_m,
|
|
| 5708 |
analytical_stem.tag,
|
|
| 5709 |
analytical_stem."organismX_m",
|
|
| 5710 |
analytical_stem."organismY_m",
|
|
| 5711 |
analytical_stem."taxonOccurrenceID",
|
|
| 5712 |
analytical_stem."authorTaxonCode",
|
|
| 5713 |
analytical_stem."aggregateOrganismObservationID",
|
|
| 5714 |
analytical_stem."individualObservationID",
|
|
| 5715 |
analytical_stem."individualCode",
|
|
| 5716 |
analytical_stem."individualCount", |
|
| 5717 |
analytical_stem."authorStemCode"
|
|
| 5704 |
analytical_stem."occurrenceRemarks__@DwC__@vegpath.org",
|
|
| 5705 |
analytical_stem."coverPercent__@VegX__.attribute.ordinal@vegpath.org",
|
|
| 5706 |
analytical_stem."stem_dbh[_cm]__@SALVIAS__.Plot_data@vegpath.org",
|
|
| 5707 |
analytical_stem."stemHeight[_m]__@VegBank__.stemCount@vegpath.org",
|
|
| 5708 |
analytical_stem."[tag=]identificationLabel__@VegX__.individual@vegpath.org",
|
|
| 5709 |
analytical_stem."relativeX[_m]__@VegX__.individualOrganismObs@vegpath.org",
|
|
| 5710 |
analytical_stem."relativeY[_m]__@VegX__.individualOrganismObs@vegpath.org",
|
|
| 5711 |
analytical_stem."taxonObservation[.id]__@VegBank__@vegpath.org",
|
|
| 5712 |
analytical_stem."taxonNameUsageConcept.authorCode__@VegX__@vegpath.org",
|
|
| 5713 |
analytical_stem."aggregateOrganismObservation.id__@VegX__@vegpath.org",
|
|
| 5714 |
analytical_stem."individualOrganismObservation.id__@VegX__@vegpath.org",
|
|
| 5715 |
analytical_stem."individualID__@DwC__@vegpath.org",
|
|
| 5716 |
analytical_stem."individualCount__@DwC__@vegpath.org",
|
|
| 5717 |
analytical_stem."stemCode__@VegBank__.stemLocation@vegpath.org"
|
|
| 5718 | 5718 |
FROM analytical_stem; |
| 5719 | 5719 |
|
| 5720 | 5720 |
|
| ... | ... | |
| 5723 | 5723 |
-- |
| 5724 | 5724 |
|
| 5725 | 5725 |
CREATE VIEW analytical_specimen AS |
| 5726 |
SELECT analytical_stem.datasource,
|
|
| 5727 |
analytical_stem.country,
|
|
| 5728 |
analytical_stem."stateProvince", |
|
| 5729 |
analytical_stem.county,
|
|
| 5730 |
analytical_stem.locality,
|
|
| 5731 |
analytical_stem."decimalLatitude", |
|
| 5732 |
analytical_stem."decimalLongitude", |
|
| 5733 |
analytical_stem."coordinateUncertaintyInMeters", |
|
| 5734 |
analytical_stem."coordinateSource_bien",
|
|
| 5735 |
analytical_stem."georeferenceProtocol_bien",
|
|
| 5726 |
SELECT analytical_stem."datasource__@Brad__.identifier_examples@vegpath.org",
|
|
| 5727 |
analytical_stem."country__@DwC__@vegpath.org",
|
|
| 5728 |
analytical_stem."stateProvince__@DwC__@vegpath.org",
|
|
| 5729 |
analytical_stem."county__@DwC__@vegpath.org",
|
|
| 5730 |
analytical_stem."locality__@DwC__@vegpath.org",
|
|
| 5731 |
analytical_stem."decimalLatitude__@DwC__@vegpath.org",
|
|
| 5732 |
analytical_stem."decimalLongitude__@DwC__@vegpath.org",
|
|
| 5733 |
analytical_stem."coordinateUncertaintyInMeters__@DwC__@vegpath.org",
|
|
| 5734 |
analytical_stem."georeferenceSources__@DwC__@vegpath.org",
|
|
| 5735 |
analytical_stem."georeferenceProtocol__@DwC__@vegpath.org",
|
|
| 5736 | 5736 |
analytical_stem.geovalid_bien, |
| 5737 | 5737 |
analytical_stem."isNewWorld_bien", |
| 5738 | 5738 |
analytical_stem.location__cultivated__bien, |
| 5739 |
analytical_stem."eventDate", |
|
| 5740 |
analytical_stem."elevationInMeters",
|
|
| 5741 |
analytical_stem."temperature_C",
|
|
| 5742 |
analytical_stem.precipitation_m,
|
|
| 5743 |
analytical_stem."specimenHolderInstitutions",
|
|
| 5744 |
analytical_stem.collection,
|
|
| 5745 |
analytical_stem."accessionNumber",
|
|
| 5746 |
analytical_stem."occurrenceID", |
|
| 5747 |
analytical_stem."recordedBy", |
|
| 5748 |
analytical_stem."recordNumber", |
|
| 5749 |
analytical_stem."dateCollected",
|
|
| 5750 |
analytical_stem.family_verbatim,
|
|
| 5751 |
analytical_stem."scientificName_verbatim",
|
|
| 5752 |
analytical_stem."identifiedBy", |
|
| 5753 |
analytical_stem."dateIdentified", |
|
| 5754 |
analytical_stem."identificationRemarks", |
|
| 5755 |
analytical_stem.family_matched,
|
|
| 5756 |
analytical_stem."taxonName_matched",
|
|
| 5757 |
analytical_stem."scientificNameAuthorship_matched",
|
|
| 5739 |
analytical_stem."eventDate__@DwC__@vegpath.org",
|
|
| 5740 |
analytical_stem."(-minimum-)ElevationInMeters__@DwC__@vegpath.org",
|
|
| 5741 |
analytical_stem."temperature[_C]__@VegX__.plotObs.obsCond@vegpath.org",
|
|
| 5742 |
analytical_stem."precip_mm__@SALVIAS__.Plot_metadata@vegpath.org",
|
|
| 5743 |
analytical_stem."[custodial_]institutionCode[s]__@DwC__@vegpath.org",
|
|
| 5744 |
analytical_stem."collectionCode__@DwC__@vegpath.org",
|
|
| 5745 |
analytical_stem."catalogNumber__@DwC__@vegpath.org",
|
|
| 5746 |
analytical_stem."occurrenceID__@DwC__@vegpath.org",
|
|
| 5747 |
analytical_stem."recordedBy__@DwC__@vegpath.org",
|
|
| 5748 |
analytical_stem."recordNumber__@DwC__@vegpath.org",
|
|
| 5749 |
analytical_stem."(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org",
|
|
| 5750 |
analytical_stem."[verbatim_]family__@DwC__@vegpath.org",
|
|
| 5751 |
analytical_stem."[verbatim_]scientificName__@DwC__@vegpath.org",
|
|
| 5752 |
analytical_stem."identifiedBy__@DwC__@vegpath.org",
|
|
| 5753 |
analytical_stem."dateIdentified__@DwC__@vegpath.org",
|
|
| 5754 |
analytical_stem."identificationRemarks__@DwC__@vegpath.org",
|
|
| 5755 |
analytical_stem."Family_matched__@TNRS__@vegpath.org",
|
|
| 5756 |
analytical_stem."Name_matched__@TNRS__@vegpath.org",
|
|
| 5757 |
analytical_stem."Name_matched_author__@TNRS__@vegpath.org",
|
|
| 5758 | 5758 |
analytical_stem.scrubbed_family, |
| 5759 | 5759 |
analytical_stem.scrubbed_genus, |
| 5760 | 5760 |
analytical_stem.scrubbed_taxon_name_no_author, |
| 5761 | 5761 |
analytical_stem.scrubbed_author, |
| 5762 |
analytical_stem."growthForm",
|
|
| 5763 |
analytical_stem."reproductiveCondition", |
|
| 5762 |
analytical_stem."[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org",
|
|
| 5763 |
analytical_stem."reproductiveCondition__@DwC__@vegpath.org",
|
|
| 5764 | 5764 |
analytical_stem.cultivated_bien, |
| 5765 | 5765 |
analytical_stem."cultivatedBasis_bien", |
| 5766 |
analytical_stem."occurrenceRemarks", |
|
| 5767 |
analytical_stem."diameterBreastHeight_cm",
|
|
| 5768 |
analytical_stem.height_m,
|
|
| 5769 |
analytical_stem.tag
|
|
| 5766 |
analytical_stem."occurrenceRemarks__@DwC__@vegpath.org",
|
|
| 5767 |
analytical_stem."stem_dbh[_cm]__@SALVIAS__.Plot_data@vegpath.org",
|
|
| 5768 |
analytical_stem."stemHeight[_m]__@VegBank__.stemCount@vegpath.org",
|
|
| 5769 |
analytical_stem."[tag=]identificationLabel__@VegX__.individual@vegpath.org"
|
|
| 5770 | 5770 |
FROM analytical_stem; |
| 5771 | 5771 |
|
| 5772 | 5772 |
|
| ... | ... | |
| 5786 | 5786 |
-- Name: analytical_stem_taxon_occurrence__pkey_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
| 5787 | 5787 |
-- |
| 5788 | 5788 |
|
| 5789 |
ALTER SEQUENCE analytical_stem_taxon_occurrence__pkey_seq OWNED BY analytical_stem.taxon_occurrence__pkey;
|
|
| 5789 |
ALTER SEQUENCE analytical_stem_taxon_occurrence__pkey_seq OWNED BY analytical_stem."TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org";
|
|
| 5790 | 5790 |
|
| 5791 | 5791 |
|
| 5792 | 5792 |
-- |
| ... | ... | |
| 6414 | 6414 |
-- |
| 6415 | 6415 |
|
| 6416 | 6416 |
CREATE VIEW "plot.**" AS |
| 6417 |
SELECT source.shortname AS datasource,
|
|
| 6418 |
COALESCE(geoscrub_output."acceptedCountry", place.country) AS country,
|
|
| 6419 |
COALESCE(geoscrub_output."acceptedStateProvince", place.stateprovince) AS "stateProvince", |
|
| 6420 |
COALESCE(geoscrub_output."acceptedCounty", place.county) AS county,
|
|
| 6421 |
location.locationnarrative AS locality,
|
|
| 6417 |
SELECT source.shortname AS "datasource__@Brad__.identifier_examples@vegpath.org",
|
|
| 6418 |
COALESCE(geoscrub_output."acceptedCountry", place.country) AS "country__@DwC__@vegpath.org",
|
|
| 6419 |
COALESCE(geoscrub_output."acceptedStateProvince", place.stateprovince) AS "stateProvince__@DwC__@vegpath.org",
|
|
| 6420 |
COALESCE(geoscrub_output."acceptedCounty", place.county) AS "county__@DwC__@vegpath.org",
|
|
| 6421 |
location.locationnarrative AS "locality__@DwC__@vegpath.org",
|
|
| 6422 | 6422 |
CASE |
| 6423 | 6423 |
WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.latitude_deg |
| 6424 | 6424 |
ELSE county_centroids."decimalLatitude" |
| 6425 |
END AS "decimalLatitude", |
|
| 6425 |
END AS "decimalLatitude__@DwC__@vegpath.org",
|
|
| 6426 | 6426 |
CASE |
| 6427 | 6427 |
WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.longitude_deg |
| 6428 | 6428 |
ELSE county_centroids."decimalLongitude" |
| 6429 |
END AS "decimalLongitude", |
|
| 6429 |
END AS "decimalLongitude__@DwC__@vegpath.org",
|
|
| 6430 | 6430 |
CASE |
| 6431 | 6431 |
WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.coordsaccuracy_m |
| 6432 | 6432 |
ELSE _km_to_m(county_centroids."*error_km") |
| 6433 |
END AS "coordinateUncertaintyInMeters", |
|
| 6433 |
END AS "coordinateUncertaintyInMeters__@DwC__@vegpath.org",
|
|
| 6434 | 6434 |
CASE |
| 6435 | 6435 |
WHEN (coordinates.latitude_deg IS NOT NULL) THEN 'source data'::coordinatesource |
| 6436 | 6436 |
WHEN (county_centroids."*row_num" IS NOT NULL) THEN 'georeferencing'::coordinatesource |
| 6437 | 6437 |
ELSE NULL::coordinatesource |
| 6438 |
END AS "coordinateSource_bien",
|
|
| 6438 |
END AS "georeferenceSources__@DwC__@vegpath.org",
|
|
| 6439 | 6439 |
CASE |
| 6440 | 6440 |
WHEN (coordinates.latitude_deg IS NOT NULL) THEN NULL::text |
| 6441 | 6441 |
WHEN (county_centroids."*row_num" IS NOT NULL) THEN 'county centroid'::text |
| 6442 | 6442 |
ELSE NULL::text |
| 6443 |
END AS "georeferenceProtocol_bien",
|
|
| 6443 |
END AS "georeferenceProtocol__@DwC__@vegpath.org",
|
|
| 6444 | 6444 |
(geoscrub_output.geovalid)::integer AS geovalid_bien, |
| 6445 | 6445 |
("newWorldCountries"."isNewWorld")::integer AS "isNewWorld_bien",
|
| 6446 |
COALESCE(project.sourceaccessioncode, project.projectname) AS "projectID", |
|
| 6447 |
ARRAY( SELECT project_contributors(project.project_id) AS project_contributors) AS project_contributors,
|
|
| 6448 |
COALESCE(location.sourceaccessioncode, NULLIF(concat_ws('; '::text, COALESCE(parent_location.sourceaccessioncode, parent_location.authorlocationcode), location.authorlocationcode), ''::text)) AS "locationID",
|
|
| 6449 |
COALESCE(parent_location.authorlocationcode, location.authorlocationcode) AS "locationName",
|
|
| 6446 |
COALESCE(project.sourceaccessioncode, project.projectname) AS "projectID__@VegX__.plotObservation@vegpath.org",
|
|
| 6447 |
ARRAY( SELECT project_contributors(project.project_id) AS project_contributors) AS "projectContributor[s]__@VegBank__@vegpath.org",
|
|
| 6448 |
COALESCE(location.sourceaccessioncode, NULLIF(concat_ws('; '::text, COALESCE(parent_location.sourceaccessioncode, parent_location.authorlocationcode), location.authorlocationcode), ''::text)) AS "locationID__@DwC__@vegpath.org",
|
|
| 6449 |
COALESCE(parent_location.authorlocationcode, location.authorlocationcode) AS "plotName__@VegX__.plot@vegpath.org",
|
|
| 6450 | 6450 |
CASE |
| 6451 | 6451 |
WHEN (parent_location.location_id IS NOT NULL) THEN location.authorlocationcode |
| 6452 | 6452 |
ELSE NULL::text |
| 6453 |
END AS subplot,
|
|
| 6453 |
END AS "subplot__@SALVIAS__.Plot_data@vegpath.org",
|
|
| 6454 | 6454 |
location.iscultivated AS location__cultivated__bien, |
| 6455 |
locationevent.locationevent_id AS locationevent__pkey,
|
|
| 6456 |
COALESCE(locationevent.obsstartdate, parent_event.obsstartdate) AS "eventDate", |
|
| 6457 |
COALESCE(location.elevation_m, parent_location.elevation_m) AS "elevationInMeters",
|
|
| 6458 |
COALESCE(location.slopeaspect_deg, parent_location.slopeaspect_deg) AS "slopeAspect", |
|
| 6459 |
COALESCE(location.slopegradient_fraction, parent_location.slopegradient_fraction) AS "slopeGradient", |
|
| 6460 |
_m2_to_ha(COALESCE(location.area_m2, parent_location.area_m2)) AS "plotArea_ha",
|
|
| 6461 |
method.name AS "samplingProtocol", |
|
| 6462 |
COALESCE(locationevent.temperature_c, parent_event.temperature_c) AS "temperature_C",
|
|
| 6463 |
COALESCE(locationevent.precipitation_m, parent_event.precipitation_m) AS precipitation_m,
|
|
| 6464 |
stratum.stratumname AS stratum__name,
|
|
| 6465 |
COALESCE(locationevent__communities__array(locationevent.locationevent_id), locationevent__communities__array(parent_event.locationevent_id)) AS communities,
|
|
| 6466 |
COALESCE(locationevent__contributors__array(locationevent.locationevent_id), locationevent__contributors__array(parent_event.locationevent_id)) AS plot__collectors
|
|
| 6455 |
locationevent.locationevent_id AS "locationevent.locationevent_id__@VegBIEN__.public@vegpath.org",
|
|
| 6456 |
COALESCE(locationevent.obsstartdate, parent_event.obsstartdate) AS "eventDate__@DwC__@vegpath.org",
|
|
| 6457 |
COALESCE(location.elevation_m, parent_location.elevation_m) AS "(-minimum-)ElevationInMeters__@DwC__@vegpath.org",
|
|
| 6458 |
COALESCE(location.slopeaspect_deg, parent_location.slopeaspect_deg) AS "slopeAspect[_deg]__@VegX__.plot@vegpath.org",
|
|
| 6459 |
COALESCE(location.slopegradient_fraction, parent_location.slopegradient_fraction) AS "slopeGradient[_deg]__@VegX__.plot@vegpath.org",
|
|
| 6460 |
_m2_to_ha(COALESCE(location.area_m2, parent_location.area_m2)) AS "plot.area[_ha]__@VegX__@vegpath.org",
|
|
| 6461 |
method.name AS "samplingProtocol__@DwC__@vegpath.org",
|
|
| 6462 |
COALESCE(locationevent.temperature_c, parent_event.temperature_c) AS "temperature[_C]__@VegX__.plotObs.obsCond@vegpath.org",
|
|
| 6463 |
COALESCE(locationevent.precipitation_m, parent_event.precipitation_m) AS "precip_mm__@SALVIAS__.Plot_metadata@vegpath.org",
|
|
| 6464 |
stratum.stratumname AS "stratumName__@VegX__.stratum@vegpath.org",
|
|
| 6465 |
COALESCE(locationevent__communities__array(locationevent.locationevent_id), locationevent__communities__array(parent_event.locationevent_id)) AS "communityConcept.name__@VegX__.communityDet@vegpath.org",
|
|
| 6466 |
COALESCE(locationevent__contributors__array(locationevent.locationevent_id), locationevent__contributors__array(parent_event.locationevent_id)) AS "observationContributor[s]__@VegBank__@vegpath.org"
|
|
| 6467 | 6467 |
FROM (((((((((((((source |
| 6468 | 6468 |
JOIN location USING (source_id)) |
| 6469 | 6469 |
LEFT JOIN locationevent USING (location_id)) |
| ... | ... | |
| 6793 | 6793 |
-- |
| 6794 | 6794 |
|
| 6795 | 6795 |
CREATE VIEW analytical_stem_view AS |
| 6796 |
SELECT "plot.**".datasource AS "datasource__@Brad__.identifier_examples@vegpath.org",
|
|
| 6797 |
"plot.**".country AS "country__@DwC__@vegpath.org",
|
|
| 6798 |
"plot.**"."stateProvince" AS "stateProvince__@DwC__@vegpath.org",
|
|
| 6799 |
"plot.**".county AS "county__@DwC__@vegpath.org",
|
|
| 6800 |
"plot.**".locality AS "locality__@DwC__@vegpath.org",
|
|
| 6801 |
"plot.**"."decimalLatitude" AS "decimalLatitude__@DwC__@vegpath.org",
|
|
| 6802 |
"plot.**"."decimalLongitude" AS "decimalLongitude__@DwC__@vegpath.org",
|
|
| 6803 |
"plot.**"."coordinateUncertaintyInMeters" AS "coordinateUncertaintyInMeters__@DwC__@vegpath.org",
|
|
| 6804 |
"plot.**"."coordinateSource_bien" AS "georeferenceSources__@DwC__@vegpath.org",
|
|
| 6805 |
"plot.**"."georeferenceProtocol_bien" AS "georeferenceProtocol__@DwC__@vegpath.org",
|
|
| 6796 |
SELECT "plot.**"."datasource__@Brad__.identifier_examples@vegpath.org", |
|
| 6797 |
"plot.**"."country__@DwC__@vegpath.org", |
|
| 6798 |
"plot.**"."stateProvince__@DwC__@vegpath.org", |
|
| 6799 |
"plot.**"."county__@DwC__@vegpath.org", |
|
| 6800 |
"plot.**"."locality__@DwC__@vegpath.org", |
|
| 6801 |
"plot.**"."decimalLatitude__@DwC__@vegpath.org", |
|
| 6802 |
"plot.**"."decimalLongitude__@DwC__@vegpath.org", |
|
| 6803 |
"plot.**"."coordinateUncertaintyInMeters__@DwC__@vegpath.org", |
|
| 6804 |
"plot.**"."georeferenceSources__@DwC__@vegpath.org", |
|
| 6805 |
"plot.**"."georeferenceProtocol__@DwC__@vegpath.org", |
|
| 6806 | 6806 |
"plot.**".geovalid_bien, |
| 6807 | 6807 |
"plot.**"."isNewWorld_bien", |
| 6808 |
"plot.**"."projectID" AS "projectID__@VegX__.plotObservation@vegpath.org",
|
|
| 6809 |
"plot.**".project_contributors AS "projectContributor[s]__@VegBank__@vegpath.org",
|
|
| 6810 |
"plot.**"."locationID" AS "locationID__@DwC__@vegpath.org",
|
|
| 6811 |
"plot.**"."locationName" AS "plotName__@VegX__.plot@vegpath.org",
|
|
| 6812 |
"plot.**".subplot AS "subplot__@SALVIAS__.Plot_data@vegpath.org",
|
|
| 6808 |
"plot.**"."projectID__@VegX__.plotObservation@vegpath.org", |
|
| 6809 |
"plot.**"."projectContributor[s]__@VegBank__@vegpath.org", |
|
| 6810 |
"plot.**"."locationID__@DwC__@vegpath.org", |
|
| 6811 |
"plot.**"."plotName__@VegX__.plot@vegpath.org", |
|
| 6812 |
"plot.**"."subplot__@SALVIAS__.Plot_data@vegpath.org", |
|
| 6813 | 6813 |
"plot.**".location__cultivated__bien, |
| 6814 |
"plot.**".locationevent__pkey AS "locationevent.locationevent_id__@VegBIEN__.public@vegpath.org",
|
|
| 6815 |
"plot.**"."eventDate" AS "eventDate__@DwC__@vegpath.org",
|
|
| 6816 |
"plot.**"."elevationInMeters" AS "(-minimum-)ElevationInMeters__@DwC__@vegpath.org",
|
|
| 6817 |
"plot.**"."slopeAspect" AS "slopeAspect[_deg]__@VegX__.plot@vegpath.org",
|
|
| 6818 |
"plot.**"."slopeGradient" AS "slopeGradient[_deg]__@VegX__.plot@vegpath.org",
|
|
| 6819 |
"plot.**"."plotArea_ha" AS "plot.area[_ha]__@VegX__@vegpath.org",
|
|
| 6820 |
"plot.**"."samplingProtocol" AS "samplingProtocol__@DwC__@vegpath.org",
|
|
| 6821 |
"plot.**"."temperature_C" AS "temperature[_C]__@VegX__.plotObs.obsCond@vegpath.org",
|
|
| 6822 |
"plot.**".precipitation_m AS "precip_mm__@SALVIAS__.Plot_metadata@vegpath.org",
|
|
| 6823 |
"plot.**".stratum__name AS "stratumName__@VegX__.stratum@vegpath.org",
|
|
| 6824 |
"plot.**".communities AS "communityConcept.name__@VegX__.communityDet@vegpath.org",
|
|
| 6825 |
"plot.**".plot__collectors AS "observationContributor[s]__@VegBank__@vegpath.org",
|
|
| 6814 |
"plot.**"."locationevent.locationevent_id__@VegBIEN__.public@vegpath.org", |
|
| 6815 |
"plot.**"."eventDate__@DwC__@vegpath.org", |
|
| 6816 |
"plot.**"."(-minimum-)ElevationInMeters__@DwC__@vegpath.org", |
|
| 6817 |
"plot.**"."slopeAspect[_deg]__@VegX__.plot@vegpath.org", |
|
| 6818 |
"plot.**"."slopeGradient[_deg]__@VegX__.plot@vegpath.org", |
|
| 6819 |
"plot.**"."plot.area[_ha]__@VegX__@vegpath.org", |
|
| 6820 |
"plot.**"."samplingProtocol__@DwC__@vegpath.org", |
|
| 6821 |
"plot.**"."temperature[_C]__@VegX__.plotObs.obsCond@vegpath.org", |
|
| 6822 |
"plot.**"."precip_mm__@SALVIAS__.Plot_metadata@vegpath.org", |
|
| 6823 |
"plot.**"."stratumName__@VegX__.stratum@vegpath.org", |
|
| 6824 |
"plot.**"."communityConcept.name__@VegX__.communityDet@vegpath.org", |
|
| 6825 |
"plot.**"."observationContributor[s]__@VegBank__@vegpath.org", |
|
| 6826 | 6826 |
sourcelist.name AS "[custodial_]institutionCode[s]__@DwC__@vegpath.org", |
| 6827 | 6827 |
specimenreplicate.collectioncode_dwc AS "collectionCode__@DwC__@vegpath.org", |
| 6828 | 6828 |
specimenreplicate.catalognumber_dwc AS "catalogNumber__@DwC__@vegpath.org", |
| 6829 | 6829 |
specimenreplicate.sourceaccessioncode AS "occurrenceID__@DwC__@vegpath.org", |
| 6830 | 6830 |
collector.fullname AS "recordedBy__@DwC__@vegpath.org", |
| 6831 | 6831 |
plantobservation.authorplantcode AS "recordNumber__@DwC__@vegpath.org", |
| 6832 |
COALESCE(aggregateoccurrence.collectiondate, "plot.**"."eventDate") AS "(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org", |
|
| 6832 |
COALESCE(aggregateoccurrence.collectiondate, "plot.**"."eventDate__@DwC__@vegpath.org") AS "(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org",
|
|
| 6833 | 6833 |
taxonverbatim.family AS "[verbatim_]family__@DwC__@vegpath.org", |
| 6834 | 6834 |
COALESCE(taxonverbatim.taxonomicname, (taxonverbatim.taxonname || COALESCE((' '::text || taxonverbatim.author), ''::text)), taxonlabel.taxonomicname) AS "[verbatim_]scientificName__@DwC__@vegpath.org",
|
| 6835 | 6835 |
identifiedby.fullname AS "identifiedBy__@DwC__@vegpath.org", |
| ... | ... | |
| 6872 | 6872 |
aggregateoccurrence.count AS "individualCount__@DwC__@vegpath.org", |
| 6873 | 6873 |
stemobservation.authorstemcode AS "stemCode__@VegBank__.stemLocation@vegpath.org" |
| 6874 | 6874 |
FROM ((((((((((((((("plot.**"
|
| 6875 |
LEFT JOIN taxonoccurrence ON ((taxonoccurrence.locationevent_id = "plot.**".locationevent__pkey)))
|
|
| 6875 |
LEFT JOIN taxonoccurrence ON ((taxonoccurrence.locationevent_id = "plot.**"."locationevent.locationevent_id__@VegBIEN__.public@vegpath.org")))
|
|
| 6876 | 6876 |
LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) |
| 6877 | 6877 |
LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) |
| 6878 | 6878 |
LEFT JOIN plantobservation USING (aggregateoccurrence_id)) |
| ... | ... | |
| 6885 | 6885 |
LEFT JOIN taxonlabel USING (taxonlabel_id)) |
| 6886 | 6886 |
LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."concatenatedScientificName" = taxonlabel.taxonomicname))) |
| 6887 | 6887 |
LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = taxon_scrub.scrubbed_family))) |
| 6888 |
LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = taxon_scrub.scrubbed_family) AND (cultivated_family_locations.country = "plot.**".country))))
|
|
| 6888 |
LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = taxon_scrub.scrubbed_family) AND (cultivated_family_locations.country = "plot.**"."country__@DwC__@vegpath.org"))))
|
|
| 6889 | 6889 |
LEFT JOIN threatened_taxonlabel USING (taxonlabel_id)); |
| 6890 | 6890 |
|
| 6891 | 6891 |
|
| ... | ... | |
| 7461 | 7461 |
-- |
| 7462 | 7462 |
|
| 7463 | 7463 |
CREATE VIEW collected_dates AS |
| 7464 |
SELECT DISTINCT analytical_stem."dateCollected"
|
|
| 7464 |
SELECT DISTINCT analytical_stem."(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org"
|
|
| 7465 | 7465 |
FROM analytical_stem |
| 7466 |
ORDER BY analytical_stem."dateCollected";
|
|
| 7466 |
ORDER BY analytical_stem."(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org";
|
|
| 7467 | 7467 |
|
| 7468 | 7468 |
|
| 7469 | 7469 |
-- |
| ... | ... | |
| 8774 | 8774 |
-- |
| 8775 | 8775 |
|
| 8776 | 8776 |
CREATE VIEW range_modeling_input AS |
| 8777 |
SELECT analytical_stem.datasource AS source,
|
|
| 8778 |
analytical_stem."specimenHolderInstitutions" AS first_publisher,
|
|
| 8779 |
analytical_stem."decimalLatitude" AS latitude_deg, |
|
| 8780 |
analytical_stem."decimalLongitude" AS longitude_deg, |
|
| 8777 |
SELECT analytical_stem."datasource__@Brad__.identifier_examples@vegpath.org" AS source,
|
|
| 8778 |
analytical_stem."[custodial_]institutionCode[s]__@DwC__@vegpath.org" AS first_publisher,
|
|
| 8779 |
analytical_stem."decimalLatitude__@DwC__@vegpath.org" AS latitude_deg,
|
|
| 8780 |
analytical_stem."decimalLongitude__@DwC__@vegpath.org" AS longitude_deg,
|
|
| 8781 | 8781 |
analytical_stem.geovalid_bien AS geovalid, |
| 8782 |
analytical_stem.scrubbed_morphospecies_binomial AS species_taxon_name,
|
|
| 8783 |
analytical_stem."higherPlantGroup_bien" AS higher_plant_group
|
|
| 8782 |
analytical_stem."speciesBinomialWithMorphospecies__@VegCore__@vegpath.org" AS species_taxon_name,
|
|
| 8783 |
analytical_stem."[higher_plant_group~]higherClassification__@DwC__@vegpath.org" AS higher_plant_group
|
|
| 8784 | 8784 |
FROM analytical_stem |
| 8785 |
WHERE (analytical_stem."higherPlantGroup_bien" IS NOT NULL);
|
|
| 8785 |
WHERE (analytical_stem."[higher_plant_group~]higherClassification__@DwC__@vegpath.org" IS NOT NULL);
|
|
| 8786 | 8786 |
|
| 8787 | 8787 |
|
| 8788 | 8788 |
-- |
| ... | ... | |
| 10441 | 10441 |
|
| 10442 | 10442 |
|
| 10443 | 10443 |
-- |
| 10444 |
-- Name: taxon_occurrence__pkey; Type: DEFAULT; Schema: public; Owner: -
|
|
| 10444 |
-- Name: TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org; Type: DEFAULT; Schema: public; Owner: -
|
|
| 10445 | 10445 |
-- |
| 10446 | 10446 |
|
| 10447 |
ALTER TABLE ONLY analytical_stem ALTER COLUMN taxon_occurrence__pkey SET DEFAULT nextval('analytical_stem_taxon_occurrence__pkey_seq'::regclass);
|
|
| 10447 |
ALTER TABLE ONLY analytical_stem ALTER COLUMN "TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org" SET DEFAULT nextval('analytical_stem_taxon_occurrence__pkey_seq'::regclass);
|
|
| 10448 | 10448 |
|
| 10449 | 10449 |
|
| 10450 | 10450 |
-- |
| ... | ... | |
| 13527 | 13527 |
-- |
| 13528 | 13528 |
|
| 13529 | 13529 |
ALTER TABLE ONLY analytical_stem |
| 13530 |
ADD CONSTRAINT analytical_stem_pkey PRIMARY KEY (taxon_occurrence__pkey);
|
|
| 13530 |
ADD CONSTRAINT analytical_stem_pkey PRIMARY KEY ("TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org");
|
|
| 13531 | 13531 |
|
| 13532 | 13532 |
|
| 13533 | 13533 |
-- |
| ... | ... | |
| 14221 | 14221 |
-- Name: analytical_stem_datasource_occurrenceID_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 14222 | 14222 |
-- |
| 14223 | 14223 |
|
| 14224 |
CREATE INDEX "analytical_stem_datasource_occurrenceID_idx" ON analytical_stem USING btree (datasource, "occurrenceID");
|
|
| 14224 |
CREATE INDEX "analytical_stem_datasource_occurrenceID_idx" ON analytical_stem USING btree ("datasource__@Brad__.identifier_examples@vegpath.org", "occurrenceID__@DwC__@vegpath.org");
|
|
| 14225 | 14225 |
|
| 14226 | 14226 |
|
| 14227 | 14227 |
-- |
| 14228 | 14228 |
-- Name: analytical_stem_datasource_projectID_locationID_eventDate_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 14229 | 14229 |
-- |
| 14230 | 14230 |
|
| 14231 |
CREATE INDEX "analytical_stem_datasource_projectID_locationID_eventDate_idx" ON analytical_stem USING btree (datasource, "projectID", "locationID", "eventDate");
|
|
| 14231 |
CREATE INDEX "analytical_stem_datasource_projectID_locationID_eventDate_idx" ON analytical_stem USING btree ("datasource__@Brad__.identifier_examples@vegpath.org", "projectID__@VegX__.plotObservation@vegpath.org", "locationID__@DwC__@vegpath.org", "eventDate__@DwC__@vegpath.org");
|
|
| 14232 | 14232 |
|
| 14233 | 14233 |
|
| 14234 | 14234 |
-- |
| 14235 | 14235 |
-- Name: analytical_stem_datasource_specimenHolderInstitutions_colle_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 14236 | 14236 |
-- |
| 14237 | 14237 |
|
| 14238 |
CREATE INDEX "analytical_stem_datasource_specimenHolderInstitutions_colle_idx" ON analytical_stem USING btree (datasource, "specimenHolderInstitutions", collection, "accessionNumber");
|
|
| 14238 |
CREATE INDEX "analytical_stem_datasource_specimenHolderInstitutions_colle_idx" ON analytical_stem USING btree ("datasource__@Brad__.identifier_examples@vegpath.org", "[custodial_]institutionCode[s]__@DwC__@vegpath.org", "collectionCode__@DwC__@vegpath.org", "catalogNumber__@DwC__@vegpath.org");
|
|
| 14239 | 14239 |
|
| 14240 | 14240 |
|
| 14241 | 14241 |
-- |
| 14242 | 14242 |
-- Name: analytical_stem_scrubbed_morphospecies_binomial_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 14243 | 14243 |
-- |
| 14244 | 14244 |
|
| 14245 |
CREATE INDEX analytical_stem_scrubbed_morphospecies_binomial_idx ON analytical_stem USING btree (scrubbed_morphospecies_binomial);
|
|
| 14245 |
CREATE INDEX analytical_stem_scrubbed_morphospecies_binomial_idx ON analytical_stem USING btree ("speciesBinomialWithMorphospecies__@VegCore__@vegpath.org");
|
|
| 14246 | 14246 |
|
| 14247 | 14247 |
|
| 14248 | 14248 |
-- |
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