| 1294 |
1294 |
|
| 1295 |
1295 |
|
| 1296 |
1296 |
--
|
|
1297 |
-- Name: analytical_aggregate; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
1298 |
--
|
|
1299 |
|
|
1300 |
CREATE TABLE analytical_aggregate (
|
|
1301 |
"institutionCode" text NOT NULL,
|
|
1302 |
country text,
|
|
1303 |
"stateProvince" text,
|
|
1304 |
county text,
|
|
1305 |
"decimalLatitude" double precision,
|
|
1306 |
"decimalLongitude" double precision,
|
|
1307 |
"coordinateUncertaintyInMeters" double precision,
|
|
1308 |
geovalid boolean,
|
|
1309 |
"isNewWorld" boolean,
|
|
1310 |
"distanceToCountry_km" double precision,
|
|
1311 |
"distanceToStateProvince_km" double precision,
|
|
1312 |
"plotName" text NOT NULL,
|
|
1313 |
"elevationInMeters" double precision,
|
|
1314 |
"plotArea_ha" double precision,
|
|
1315 |
"samplingProtocol" text,
|
|
1316 |
"dateCollected" date NOT NULL,
|
|
1317 |
"higherPlantGroup" higher_plant_group,
|
|
1318 |
family text,
|
|
1319 |
genus text,
|
|
1320 |
"speciesBinomial" text,
|
|
1321 |
"scientificName" text,
|
|
1322 |
"scientificNameAuthorship" text,
|
|
1323 |
"scientificNameWithMorphospecies" text NOT NULL,
|
|
1324 |
threatened boolean,
|
|
1325 |
"identifiedBy" text,
|
|
1326 |
"growthForm" growthform,
|
|
1327 |
cultivated boolean,
|
|
1328 |
"cultivatedBasis" text,
|
|
1329 |
"coverPercent" double precision,
|
|
1330 |
"individualCount" bigint,
|
|
1331 |
"individualCount_1cm_or_more" bigint,
|
|
1332 |
"individualCount_2_5cm_or_more" bigint,
|
|
1333 |
"individualCount_10cm_or_more" bigint
|
|
1334 |
);
|
|
1335 |
|
|
1336 |
|
|
1337 |
--
|
|
1338 |
-- Name: analytical_stem; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
1339 |
--
|
|
1340 |
|
|
1341 |
CREATE TABLE analytical_stem (
|
|
1342 |
"institutionCode" text NOT NULL,
|
|
1343 |
country text,
|
|
1344 |
"stateProvince" text,
|
|
1345 |
county text,
|
|
1346 |
"decimalLatitude" double precision,
|
|
1347 |
"decimalLongitude" double precision,
|
|
1348 |
"coordinateUncertaintyInMeters" double precision,
|
|
1349 |
geovalid boolean,
|
|
1350 |
"isNewWorld" boolean,
|
|
1351 |
"distanceToCountry_km" double precision,
|
|
1352 |
"distanceToStateProvince_km" double precision,
|
|
1353 |
"plotName" text NOT NULL,
|
|
1354 |
"elevationInMeters" double precision,
|
|
1355 |
"plotArea_ha" double precision,
|
|
1356 |
"samplingProtocol" text,
|
|
1357 |
"dateCollected" date NOT NULL,
|
|
1358 |
"higherPlantGroup" higher_plant_group,
|
|
1359 |
family text,
|
|
1360 |
genus text,
|
|
1361 |
"speciesBinomial" text,
|
|
1362 |
"scientificName" text,
|
|
1363 |
"scientificNameAuthorship" text,
|
|
1364 |
"scientificNameWithMorphospecies" text NOT NULL,
|
|
1365 |
threatened boolean,
|
|
1366 |
"identifiedBy" text,
|
|
1367 |
"growthForm" growthform,
|
|
1368 |
cultivated boolean,
|
|
1369 |
"cultivatedBasis" text,
|
|
1370 |
"coverPercent" double precision,
|
|
1371 |
"diameterBreastHeight_cm" double precision,
|
|
1372 |
height_m double precision,
|
|
1373 |
tag text,
|
|
1374 |
"organismX_m" double precision,
|
|
1375 |
"organismY_m" double precision,
|
|
1376 |
"recordedBy" text,
|
|
1377 |
"recordNumber" text NOT NULL
|
|
1378 |
);
|
|
1379 |
|
|
1380 |
|
|
1381 |
--
|
| 1297 |
1382 |
-- Name: address; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
| 1298 |
1383 |
--
|
| 1299 |
1384 |
|
| ... | ... | |
| 1412 |
1497 |
|
| 1413 |
1498 |
|
| 1414 |
1499 |
--
|
| 1415 |
|
-- Name: analytical_aggregate; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
| 1416 |
|
--
|
| 1417 |
|
|
| 1418 |
|
CREATE TABLE analytical_aggregate (
|
| 1419 |
|
"institutionCode" text NOT NULL,
|
| 1420 |
|
country text,
|
| 1421 |
|
"stateProvince" text,
|
| 1422 |
|
county text,
|
| 1423 |
|
"decimalLatitude" double precision,
|
| 1424 |
|
"decimalLongitude" double precision,
|
| 1425 |
|
"coordinateUncertaintyInMeters" double precision,
|
| 1426 |
|
geovalid boolean,
|
| 1427 |
|
"isNewWorld" boolean,
|
| 1428 |
|
"distanceToCountry_km" double precision,
|
| 1429 |
|
"distanceToStateProvince_km" double precision,
|
| 1430 |
|
"plotName" text NOT NULL,
|
| 1431 |
|
"elevationInMeters" double precision,
|
| 1432 |
|
"plotArea_ha" double precision,
|
| 1433 |
|
"samplingProtocol" text,
|
| 1434 |
|
"dateCollected" date NOT NULL,
|
| 1435 |
|
"higherPlantGroup" higher_plant_group,
|
| 1436 |
|
family text,
|
| 1437 |
|
genus text,
|
| 1438 |
|
"speciesBinomial" text,
|
| 1439 |
|
"scientificName" text,
|
| 1440 |
|
"scientificNameAuthorship" text,
|
| 1441 |
|
"scientificNameWithMorphospecies" text NOT NULL,
|
| 1442 |
|
threatened boolean,
|
| 1443 |
|
"identifiedBy" text,
|
| 1444 |
|
"growthForm" growthform,
|
| 1445 |
|
cultivated boolean,
|
| 1446 |
|
"cultivatedBasis" text,
|
| 1447 |
|
"coverPercent" double precision,
|
| 1448 |
|
"individualCount" bigint,
|
| 1449 |
|
"individualCount_1cm_or_more" bigint,
|
| 1450 |
|
"individualCount_2_5cm_or_more" bigint,
|
| 1451 |
|
"individualCount_10cm_or_more" bigint
|
| 1452 |
|
);
|
| 1453 |
|
|
| 1454 |
|
|
| 1455 |
|
--
|
| 1456 |
|
-- Name: analytical_stem; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
| 1457 |
|
--
|
| 1458 |
|
|
| 1459 |
|
CREATE TABLE analytical_stem (
|
| 1460 |
|
"institutionCode" text NOT NULL,
|
| 1461 |
|
country text,
|
| 1462 |
|
"stateProvince" text,
|
| 1463 |
|
county text,
|
| 1464 |
|
"decimalLatitude" double precision,
|
| 1465 |
|
"decimalLongitude" double precision,
|
| 1466 |
|
"coordinateUncertaintyInMeters" double precision,
|
| 1467 |
|
geovalid boolean,
|
| 1468 |
|
"isNewWorld" boolean,
|
| 1469 |
|
"distanceToCountry_km" double precision,
|
| 1470 |
|
"distanceToStateProvince_km" double precision,
|
| 1471 |
|
"plotName" text NOT NULL,
|
| 1472 |
|
"elevationInMeters" double precision,
|
| 1473 |
|
"plotArea_ha" double precision,
|
| 1474 |
|
"samplingProtocol" text,
|
| 1475 |
|
"dateCollected" date NOT NULL,
|
| 1476 |
|
"higherPlantGroup" higher_plant_group,
|
| 1477 |
|
family text,
|
| 1478 |
|
genus text,
|
| 1479 |
|
"speciesBinomial" text,
|
| 1480 |
|
"scientificName" text,
|
| 1481 |
|
"scientificNameAuthorship" text,
|
| 1482 |
|
"scientificNameWithMorphospecies" text NOT NULL,
|
| 1483 |
|
threatened boolean,
|
| 1484 |
|
"identifiedBy" text,
|
| 1485 |
|
"growthForm" growthform,
|
| 1486 |
|
cultivated boolean,
|
| 1487 |
|
"cultivatedBasis" text,
|
| 1488 |
|
"coverPercent" double precision,
|
| 1489 |
|
"diameterBreastHeight_cm" double precision,
|
| 1490 |
|
height_m double precision,
|
| 1491 |
|
tag text,
|
| 1492 |
|
"organismX_m" double precision,
|
| 1493 |
|
"organismY_m" double precision,
|
| 1494 |
|
"recordedBy" text,
|
| 1495 |
|
"recordNumber" text NOT NULL
|
| 1496 |
|
);
|
| 1497 |
|
|
| 1498 |
|
|
| 1499 |
|
--
|
| 1500 |
1500 |
-- Name: analytical_aggregate_view; Type: VIEW; Schema: public; Owner: -
|
| 1501 |
1501 |
--
|
| 1502 |
1502 |
|
| ... | ... | |
| 2235 |
2235 |
--
|
| 2236 |
2236 |
|
| 2237 |
2237 |
CREATE VIEW analytical_stem_view AS
|
| 2238 |
|
SELECT source.shortname AS "institutionCode", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", coordinates.coordsaccuracy_m AS "coordinateUncertaintyInMeters", canon_place.geovalid, "newWorldCountries"."isNewWorld", _m_to_km(canon_place.distance_to_country_m) AS "distanceToCountry_km", _m_to_km(canon_place.distance_to_state_m) AS "distanceToStateProvince_km", COALESCE(location.sourceaccessioncode, location.authorlocationcode) AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", COALESCE(locationevent.obsstartdate, aggregateoccurrence.collectiondate) AS "dateCollected", family_higher_plant_group.higher_plant_group AS "higherPlantGroup", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.taxonname) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[COALESCE(accepted_taxonverbatim.genus, accepted_taxonverbatim.family), COALESCE(accepted_taxonverbatim.specific_epithet, parsed_taxonverbatim.morphospecies)], ' '::text), ''::text) AS "scientificNameWithMorphospecies", (threatened_taxonlabel.taxonlabel_id IS NOT NULL) AS threatened, NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", ((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, location.iscultivated)) AS cultivated, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis WHEN (location.iscultivated IS NOT NULL) THEN NULL::text ELSE NULL::text END AS "cultivatedBasis", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", collector.fullname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM (((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place datasource_place USING (place_id)) LEFT JOIN place canon_place ON ((canon_place.place_id = datasource_place.canon_place_id))) LEFT JOIN coordinates ON ((coordinates.coordinates_id = canon_place.coordinates_id))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."GADM country" = canon_place.country))) LEFT JOIN "newWorld"."newWorldCountries" ON ((("newWorldCountries"."isoCode")::text = iso_code_gadm."2-digit iso code"))) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) LEFT JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = accepted_taxonverbatim.family))) LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = accepted_taxonverbatim.family) AND (cultivated_family_locations.country = canon_place.country)))) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) WHERE (taxondetermination.iscurrent AND (COALESCE(locationevent.obsstartdate, aggregateoccurrence.collectiondate) IS NOT NULL));
|
|
2238 |
SELECT source.shortname AS "institutionCode", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", coordinates.coordsaccuracy_m AS "coordinateUncertaintyInMeters", canon_place.geovalid, "newWorldCountries"."isNewWorld", _m_to_km(canon_place.distance_to_country_m) AS "distanceToCountry_km", _m_to_km(canon_place.distance_to_state_m) AS "distanceToStateProvince_km", COALESCE(location.sourceaccessioncode, location.authorlocationcode) AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", COALESCE(locationevent.obsstartdate, aggregateoccurrence.collectiondate) AS "dateCollected", family_higher_plant_group.higher_plant_group AS "higherPlantGroup", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.taxonname) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[COALESCE(accepted_taxonverbatim.genus, accepted_taxonverbatim.family), COALESCE(accepted_taxonverbatim.specific_epithet, parsed_taxonverbatim.morphospecies)], ' '::text), ''::text) AS "scientificNameWithMorphospecies", (threatened_taxonlabel.taxonlabel_id IS NOT NULL) AS threatened, NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", ((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, location.iscultivated)) AS cultivated, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis WHEN (location.iscultivated IS NOT NULL) THEN NULL::text ELSE NULL::text END AS "cultivatedBasis", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", collector.fullname AS "recordedBy", NULLIF(array_to_string(ARRAY[taxonoccurrence.sourceaccessioncode, taxonoccurrence.authortaxoncode, plantobservation.sourceaccessioncode, plantobservation.collectionnumber, stemobservation.sourceaccessioncode, stemobservation.authorstemcode, stemobservation.tag], '; '::text), ''::text) AS "recordNumber" FROM (((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place datasource_place USING (place_id)) LEFT JOIN place canon_place ON ((canon_place.place_id = datasource_place.canon_place_id))) LEFT JOIN coordinates ON ((coordinates.coordinates_id = canon_place.coordinates_id))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."GADM country" = canon_place.country))) LEFT JOIN "newWorld"."newWorldCountries" ON ((("newWorldCountries"."isoCode")::text = iso_code_gadm."2-digit iso code"))) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) LEFT JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = accepted_taxonverbatim.family))) LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = accepted_taxonverbatim.family) AND (cultivated_family_locations.country = canon_place.country)))) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) WHERE (taxondetermination.iscurrent AND (COALESCE(locationevent.obsstartdate, aggregateoccurrence.collectiondate) IS NOT NULL));
|
| 2239 |
2239 |
|
| 2240 |
2240 |
|
| 2241 |
2241 |
--
|
| ... | ... | |
| 7528 |
7528 |
|
| 7529 |
7529 |
|
| 7530 |
7530 |
--
|
|
7531 |
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: -
|
|
7532 |
--
|
|
7533 |
|
|
7534 |
REVOKE ALL ON TABLE analytical_stem FROM PUBLIC;
|
|
7535 |
REVOKE ALL ON TABLE analytical_stem FROM bien;
|
|
7536 |
GRANT ALL ON TABLE analytical_stem TO bien;
|
|
7537 |
GRANT SELECT ON TABLE analytical_stem TO bien_read;
|
|
7538 |
|
|
7539 |
|
|
7540 |
--
|
| 7531 |
7541 |
-- Name: address; Type: ACL; Schema: public; Owner: -
|
| 7532 |
7542 |
--
|
| 7533 |
7543 |
|
| ... | ... | |
| 7548 |
7558 |
|
| 7549 |
7559 |
|
| 7550 |
7560 |
--
|
| 7551 |
|
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: -
|
| 7552 |
|
--
|
| 7553 |
|
|
| 7554 |
|
REVOKE ALL ON TABLE analytical_stem FROM PUBLIC;
|
| 7555 |
|
REVOKE ALL ON TABLE analytical_stem FROM bien;
|
| 7556 |
|
GRANT ALL ON TABLE analytical_stem TO bien;
|
| 7557 |
|
GRANT SELECT ON TABLE analytical_stem TO bien_read;
|
| 7558 |
|
|
| 7559 |
|
|
| 7560 |
|
--
|
| 7561 |
7561 |
-- Name: analytical_aggregate_view; Type: ACL; Schema: public; Owner: -
|
| 7562 |
7562 |
--
|
| 7563 |
7563 |
|
schemas/vegbien.sql: analytical_stem_view: recordNumber: Combine identifying fields in taxonoccurrence, plantobservation, and stemobservation to ensure that this field is unique within the plot and not NULL