Revision 4787
Added by Aaron Marcuse-Kubitza about 12 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 171 | 171 |
sourceaccessioncode text, |
| 172 | 172 |
taxonoccurrence_id int(11) NOT NULL, |
| 173 | 173 |
collectiondate timestamp NULL, |
| 174 |
cover_frac double precision, |
|
| 174 |
cover_fraction double precision,
|
|
| 175 | 175 |
linecover_m double precision, |
| 176 | 176 |
basalarea_m2 double precision, |
| 177 | 177 |
biomass_kg_m2 double precision, |
| ... | ... | |
| 252 | 252 |
slopeaspect_deg double precision, |
| 253 | 253 |
minslopeaspect_deg double precision, |
| 254 | 254 |
maxslopeaspect_deg double precision, |
| 255 |
slopegradient_frac double precision, |
|
| 256 |
minslopegradient_frac double precision, |
|
| 257 |
maxslopegradient_frac double precision, |
|
| 255 |
slopegradient_fraction double precision,
|
|
| 256 |
minslopegradient_fraction double precision,
|
|
| 257 |
maxslopegradient_fraction double precision,
|
|
| 258 | 258 |
topoposition text, |
| 259 | 259 |
landform text, |
| 260 | 260 |
surficialdeposits text, |
| ... | ... | |
| 862 | 862 |
-- |
| 863 | 863 |
|
| 864 | 864 |
CREATE VIEW analytical_db_view AS |
| 865 |
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], CAST(' ' AS 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));
|
|
| 865 |
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], CAST(' ' AS 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));
|
|
| 866 | 866 |
|
| 867 | 867 |
|
| 868 | 868 |
-- |
| ... | ... | |
| 2063 | 2063 |
depthbottom_m double precision, |
| 2064 | 2064 |
color text, |
| 2065 | 2065 |
texture text, |
| 2066 |
organic_frac double precision, |
|
| 2067 |
sand_frac double precision, |
|
| 2068 |
silt_frac double precision, |
|
| 2069 |
clay_frac double precision, |
|
| 2070 |
coarse_frac double precision, |
|
| 2066 |
organic_fraction double precision,
|
|
| 2067 |
sand_fraction double precision,
|
|
| 2068 |
silt_fraction double precision,
|
|
| 2069 |
clay_fraction double precision,
|
|
| 2070 |
coarse_fraction double precision,
|
|
| 2071 | 2071 |
ph double precision, |
| 2072 |
acidity_frac double precision, |
|
| 2073 |
basesaturation_frac double precision, |
|
| 2072 |
acidity_fraction double precision,
|
|
| 2073 |
basesaturation_fraction double precision,
|
|
| 2074 | 2074 |
cationexchangecapacity_cmol_kg double precision, |
| 2075 | 2075 |
conductivity double precision, |
| 2076 |
carbon_frac double precision, |
|
| 2077 |
phosphorus_frac double precision, |
|
| 2078 |
potassium_frac double precision, |
|
| 2079 |
magnesium_frac double precision, |
|
| 2080 |
nitrogen_frac double precision, |
|
| 2081 |
calcium_frac double precision, |
|
| 2082 |
sodium_frac double precision |
|
| 2076 |
carbon_fraction double precision,
|
|
| 2077 |
phosphorus_fraction double precision,
|
|
| 2078 |
potassium_fraction double precision,
|
|
| 2079 |
magnesium_fraction double precision,
|
|
| 2080 |
nitrogen_fraction double precision,
|
|
| 2081 |
calcium_fraction double precision,
|
|
| 2082 |
sodium_fraction double precision
|
|
| 2083 | 2083 |
); |
| 2084 | 2084 |
|
| 2085 | 2085 |
|
| 2086 | 2086 |
-- |
| 2087 |
-- Name: COLUMN soilobs.organic_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2087 |
-- Name: COLUMN soilobs.organic_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2088 | 2088 |
-- |
| 2089 | 2089 |
|
| 2090 | 2090 |
|
| 2091 | 2091 |
|
| 2092 | 2092 |
|
| 2093 | 2093 |
-- |
| 2094 |
-- Name: COLUMN soilobs.sand_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2094 |
-- Name: COLUMN soilobs.sand_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2095 | 2095 |
-- |
| 2096 | 2096 |
|
| 2097 | 2097 |
|
| 2098 | 2098 |
|
| 2099 | 2099 |
|
| 2100 | 2100 |
-- |
| 2101 |
-- Name: COLUMN soilobs.silt_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2101 |
-- Name: COLUMN soilobs.silt_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2102 | 2102 |
-- |
| 2103 | 2103 |
|
| 2104 | 2104 |
|
| 2105 | 2105 |
|
| 2106 | 2106 |
|
| 2107 | 2107 |
-- |
| 2108 |
-- Name: COLUMN soilobs.clay_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2108 |
-- Name: COLUMN soilobs.clay_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2109 | 2109 |
-- |
| 2110 | 2110 |
|
| 2111 | 2111 |
|
| 2112 | 2112 |
|
| 2113 | 2113 |
|
| 2114 | 2114 |
-- |
| 2115 |
-- Name: COLUMN soilobs.coarse_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2115 |
-- Name: COLUMN soilobs.coarse_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2116 | 2116 |
-- |
| 2117 | 2117 |
|
| 2118 | 2118 |
|
| 2119 | 2119 |
|
| 2120 | 2120 |
|
| 2121 | 2121 |
-- |
| 2122 |
-- Name: COLUMN soilobs.acidity_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2122 |
-- Name: COLUMN soilobs.acidity_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2123 | 2123 |
-- |
| 2124 | 2124 |
|
| 2125 | 2125 |
|
| 2126 | 2126 |
|
| 2127 | 2127 |
|
| 2128 | 2128 |
-- |
| 2129 |
-- Name: COLUMN soilobs.basesaturation_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2129 |
-- Name: COLUMN soilobs.basesaturation_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2130 | 2130 |
-- |
| 2131 | 2131 |
|
| 2132 | 2132 |
|
| 2133 | 2133 |
|
| 2134 | 2134 |
|
| 2135 | 2135 |
-- |
| 2136 |
-- Name: COLUMN soilobs.carbon_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2136 |
-- Name: COLUMN soilobs.carbon_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2137 | 2137 |
-- |
| 2138 | 2138 |
|
| 2139 | 2139 |
|
| 2140 | 2140 |
|
| 2141 | 2141 |
|
| 2142 | 2142 |
-- |
| 2143 |
-- Name: COLUMN soilobs.phosphorus_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2143 |
-- Name: COLUMN soilobs.phosphorus_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2144 | 2144 |
-- |
| 2145 | 2145 |
|
| 2146 | 2146 |
|
| 2147 | 2147 |
|
| 2148 | 2148 |
|
| 2149 | 2149 |
-- |
| 2150 |
-- Name: COLUMN soilobs.potassium_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2150 |
-- Name: COLUMN soilobs.potassium_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2151 | 2151 |
-- |
| 2152 | 2152 |
|
| 2153 | 2153 |
|
| 2154 | 2154 |
|
| 2155 | 2155 |
|
| 2156 | 2156 |
-- |
| 2157 |
-- Name: COLUMN soilobs.magnesium_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2157 |
-- Name: COLUMN soilobs.magnesium_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2158 | 2158 |
-- |
| 2159 | 2159 |
|
| 2160 | 2160 |
|
| 2161 | 2161 |
|
| 2162 | 2162 |
|
| 2163 | 2163 |
-- |
| 2164 |
-- Name: COLUMN soilobs.nitrogen_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2164 |
-- Name: COLUMN soilobs.nitrogen_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2165 | 2165 |
-- |
| 2166 | 2166 |
|
| 2167 | 2167 |
|
| 2168 | 2168 |
|
| 2169 | 2169 |
|
| 2170 | 2170 |
-- |
| 2171 |
-- Name: COLUMN soilobs.calcium_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2171 |
-- Name: COLUMN soilobs.calcium_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2172 | 2172 |
-- |
| 2173 | 2173 |
|
| 2174 | 2174 |
|
| 2175 | 2175 |
|
| 2176 | 2176 |
|
| 2177 | 2177 |
-- |
| 2178 |
-- Name: COLUMN soilobs.sodium_frac; Type: COMMENT; Schema: public; Owner: - |
|
| 2178 |
-- Name: COLUMN soilobs.sodium_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 2179 | 2179 |
-- |
| 2180 | 2180 |
|
| 2181 | 2181 |
|
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