Revision 4786
Added by Aaron Marcuse-Kubitza almost 12 years ago
| vegbien.sql | ||
|---|---|---|
| 418 | 418 |
sourceaccessioncode text, |
| 419 | 419 |
taxonoccurrence_id integer NOT NULL, |
| 420 | 420 |
collectiondate timestamp with time zone, |
| 421 |
cover_frac double precision, |
|
| 421 |
cover_fraction double precision,
|
|
| 422 | 422 |
linecover_m double precision, |
| 423 | 423 |
basalarea_m2 double precision, |
| 424 | 424 |
biomass_kg_m2 double precision, |
| ... | ... | |
| 504 | 504 |
slopeaspect_deg double precision, |
| 505 | 505 |
minslopeaspect_deg double precision, |
| 506 | 506 |
maxslopeaspect_deg double precision, |
| 507 |
slopegradient_frac double precision, |
|
| 508 |
minslopegradient_frac double precision, |
|
| 509 |
maxslopegradient_frac double precision, |
|
| 507 |
slopegradient_fraction double precision,
|
|
| 508 |
minslopegradient_fraction double precision,
|
|
| 509 |
maxslopegradient_fraction double precision,
|
|
| 510 | 510 |
topoposition text, |
| 511 | 511 |
landform text, |
| 512 | 512 |
surficialdeposits text, |
| ... | ... | |
| 1142 | 1142 |
-- |
| 1143 | 1143 |
|
| 1144 | 1144 |
CREATE VIEW analytical_db_view AS |
| 1145 |
SELECT datasource.organizationname AS "dataSourceName", taxonpath.family, taxonpath.genus, taxonpath.species, COALESCE(taxonpath.scientificnamewithauthor, taxonpath.scientificname) AS taxon, taxonpath.scientificnameauthor AS "taxonAuthor", taxonpath.variety AS "taxonMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", functions._m2_to_ha(location.area_m2) AS "plotAreaHa", method.name AS "plotMethod", locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", functions._frac_to_pct(aggregateoccurrence.cover_frac) AS "pctCover" FROM (((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonpath USING (taxonpath_id)) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)); |
|
| 1145 |
SELECT datasource.organizationname AS "dataSourceName", taxonpath.family, taxonpath.genus, taxonpath.species, COALESCE(taxonpath.scientificnamewithauthor, taxonpath.scientificname) AS taxon, taxonpath.scientificnameauthor AS "taxonAuthor", taxonpath.variety AS "taxonMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", functions._m2_to_ha(location.area_m2) AS "plotAreaHa", method.name AS "plotMethod", locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", functions._frac_to_pct(aggregateoccurrence.cover_fraction) AS "pctCover" FROM (((((((((((((location JOIN party datasource ON (((datasource.party_id = location.datasource_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonpath USING (taxonpath_id)) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));
|
|
| 1146 | 1146 |
|
| 1147 | 1147 |
|
| 1148 | 1148 |
-- |
| ... | ... | |
| 2565 | 2565 |
depthbottom_m double precision, |
| 2566 | 2566 |
color text, |
| 2567 | 2567 |
texture text, |
| 2568 |
organic_frac double precision, |
|
| 2569 |
sand_frac double precision, |
|
| 2570 |
silt_frac double precision, |
|
| 2571 |
clay_frac double precision, |
|
| 2572 |
coarse_frac double precision, |
|
| 2568 |
organic_fraction double precision,
|
|
| 2569 |
sand_fraction double precision,
|
|
| 2570 |
silt_fraction double precision,
|
|
| 2571 |
clay_fraction double precision,
|
|
| 2572 |
coarse_fraction double precision,
|
|
| 2573 | 2573 |
ph double precision, |
| 2574 |
acidity_frac double precision, |
|
| 2575 |
basesaturation_frac double precision, |
|
| 2574 |
acidity_fraction double precision,
|
|
| 2575 |
basesaturation_fraction double precision,
|
|
| 2576 | 2576 |
cationexchangecapacity_cmol_kg double precision, |
| 2577 | 2577 |
conductivity double precision, |
| 2578 |
carbon_frac double precision, |
|
| 2579 |
phosphorus_frac double precision, |
|
| 2580 |
potassium_frac double precision, |
|
| 2581 |
magnesium_frac double precision, |
|
| 2582 |
nitrogen_frac double precision, |
|
| 2583 |
calcium_frac double precision, |
|
| 2584 |
sodium_frac double precision |
|
| 2578 |
carbon_fraction double precision,
|
|
| 2579 |
phosphorus_fraction double precision,
|
|
| 2580 |
potassium_fraction double precision,
|
|
| 2581 |
magnesium_fraction double precision,
|
|
| 2582 |
nitrogen_fraction double precision,
|
|
| 2583 |
calcium_fraction double precision,
|
|
| 2584 |
sodium_fraction double precision
|
|
| 2585 | 2585 |
); |
| 2586 | 2586 |
|
| 2587 | 2587 |
|
| 2588 | 2588 |
-- |
| 2589 |
-- Name: COLUMN soilobs.organic_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2589 |
-- Name: COLUMN soilobs.organic_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2590 | 2590 |
-- |
| 2591 | 2591 |
|
| 2592 |
COMMENT ON COLUMN soilobs.organic_frac IS 'fraction'; |
|
| 2592 |
COMMENT ON COLUMN soilobs.organic_fraction IS 'fraction';
|
|
| 2593 | 2593 |
|
| 2594 | 2594 |
|
| 2595 | 2595 |
-- |
| 2596 |
-- Name: COLUMN soilobs.sand_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2596 |
-- Name: COLUMN soilobs.sand_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2597 | 2597 |
-- |
| 2598 | 2598 |
|
| 2599 |
COMMENT ON COLUMN soilobs.sand_frac IS 'fraction'; |
|
| 2599 |
COMMENT ON COLUMN soilobs.sand_fraction IS 'fraction';
|
|
| 2600 | 2600 |
|
| 2601 | 2601 |
|
| 2602 | 2602 |
-- |
| 2603 |
-- Name: COLUMN soilobs.silt_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2603 |
-- Name: COLUMN soilobs.silt_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2604 | 2604 |
-- |
| 2605 | 2605 |
|
| 2606 |
COMMENT ON COLUMN soilobs.silt_frac IS 'fraction'; |
|
| 2606 |
COMMENT ON COLUMN soilobs.silt_fraction IS 'fraction';
|
|
| 2607 | 2607 |
|
| 2608 | 2608 |
|
| 2609 | 2609 |
-- |
| 2610 |
-- Name: COLUMN soilobs.clay_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2610 |
-- Name: COLUMN soilobs.clay_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2611 | 2611 |
-- |
| 2612 | 2612 |
|
| 2613 |
COMMENT ON COLUMN soilobs.clay_frac IS 'fraction'; |
|
| 2613 |
COMMENT ON COLUMN soilobs.clay_fraction IS 'fraction';
|
|
| 2614 | 2614 |
|
| 2615 | 2615 |
|
| 2616 | 2616 |
-- |
| 2617 |
-- Name: COLUMN soilobs.coarse_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2617 |
-- Name: COLUMN soilobs.coarse_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2618 | 2618 |
-- |
| 2619 | 2619 |
|
| 2620 |
COMMENT ON COLUMN soilobs.coarse_frac IS 'fraction'; |
|
| 2620 |
COMMENT ON COLUMN soilobs.coarse_fraction IS 'fraction';
|
|
| 2621 | 2621 |
|
| 2622 | 2622 |
|
| 2623 | 2623 |
-- |
| 2624 |
-- Name: COLUMN soilobs.acidity_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2624 |
-- Name: COLUMN soilobs.acidity_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2625 | 2625 |
-- |
| 2626 | 2626 |
|
| 2627 |
COMMENT ON COLUMN soilobs.acidity_frac IS 'fraction'; |
|
| 2627 |
COMMENT ON COLUMN soilobs.acidity_fraction IS 'fraction';
|
|
| 2628 | 2628 |
|
| 2629 | 2629 |
|
| 2630 | 2630 |
-- |
| 2631 |
-- Name: COLUMN soilobs.basesaturation_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2631 |
-- Name: COLUMN soilobs.basesaturation_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2632 | 2632 |
-- |
| 2633 | 2633 |
|
| 2634 |
COMMENT ON COLUMN soilobs.basesaturation_frac IS 'fraction'; |
|
| 2634 |
COMMENT ON COLUMN soilobs.basesaturation_fraction IS 'fraction';
|
|
| 2635 | 2635 |
|
| 2636 | 2636 |
|
| 2637 | 2637 |
-- |
| 2638 |
-- Name: COLUMN soilobs.carbon_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2638 |
-- Name: COLUMN soilobs.carbon_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2639 | 2639 |
-- |
| 2640 | 2640 |
|
| 2641 |
COMMENT ON COLUMN soilobs.carbon_frac IS 'fraction'; |
|
| 2641 |
COMMENT ON COLUMN soilobs.carbon_fraction IS 'fraction';
|
|
| 2642 | 2642 |
|
| 2643 | 2643 |
|
| 2644 | 2644 |
-- |
| 2645 |
-- Name: COLUMN soilobs.phosphorus_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2645 |
-- Name: COLUMN soilobs.phosphorus_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2646 | 2646 |
-- |
| 2647 | 2647 |
|
| 2648 |
COMMENT ON COLUMN soilobs.phosphorus_frac IS 'fraction'; |
|
| 2648 |
COMMENT ON COLUMN soilobs.phosphorus_fraction IS 'fraction';
|
|
| 2649 | 2649 |
|
| 2650 | 2650 |
|
| 2651 | 2651 |
-- |
| 2652 |
-- Name: COLUMN soilobs.potassium_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2652 |
-- Name: COLUMN soilobs.potassium_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2653 | 2653 |
-- |
| 2654 | 2654 |
|
| 2655 |
COMMENT ON COLUMN soilobs.potassium_frac IS 'fraction'; |
|
| 2655 |
COMMENT ON COLUMN soilobs.potassium_fraction IS 'fraction';
|
|
| 2656 | 2656 |
|
| 2657 | 2657 |
|
| 2658 | 2658 |
-- |
| 2659 |
-- Name: COLUMN soilobs.magnesium_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2659 |
-- Name: COLUMN soilobs.magnesium_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2660 | 2660 |
-- |
| 2661 | 2661 |
|
| 2662 |
COMMENT ON COLUMN soilobs.magnesium_frac IS 'fraction'; |
|
| 2662 |
COMMENT ON COLUMN soilobs.magnesium_fraction IS 'fraction';
|
|
| 2663 | 2663 |
|
| 2664 | 2664 |
|
| 2665 | 2665 |
-- |
| 2666 |
-- Name: COLUMN soilobs.nitrogen_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2666 |
-- Name: COLUMN soilobs.nitrogen_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2667 | 2667 |
-- |
| 2668 | 2668 |
|
| 2669 |
COMMENT ON COLUMN soilobs.nitrogen_frac IS 'fraction'; |
|
| 2669 |
COMMENT ON COLUMN soilobs.nitrogen_fraction IS 'fraction';
|
|
| 2670 | 2670 |
|
| 2671 | 2671 |
|
| 2672 | 2672 |
-- |
| 2673 |
-- Name: COLUMN soilobs.calcium_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2673 |
-- Name: COLUMN soilobs.calcium_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2674 | 2674 |
-- |
| 2675 | 2675 |
|
| 2676 |
COMMENT ON COLUMN soilobs.calcium_frac IS 'fraction'; |
|
| 2676 |
COMMENT ON COLUMN soilobs.calcium_fraction IS 'fraction';
|
|
| 2677 | 2677 |
|
| 2678 | 2678 |
|
| 2679 | 2679 |
-- |
| 2680 |
-- Name: COLUMN soilobs.sodium_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2680 |
-- Name: COLUMN soilobs.sodium_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2681 | 2681 |
-- |
| 2682 | 2682 |
|
| 2683 |
COMMENT ON COLUMN soilobs.sodium_frac IS 'fraction'; |
|
| 2683 |
COMMENT ON COLUMN soilobs.sodium_fraction IS 'fraction';
|
|
| 2684 | 2684 |
|
| 2685 | 2685 |
|
| 2686 | 2686 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: Changed _frac units suffix to _fraction for clarity and for consistency with _percent (which is spelled out), as used by SALVIAS (http://salvias.net/Documents/salvias_data_dictionary.html) and elsewhere