Revision 13926
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 3849 | 3849 |
|
| 3850 | 3850 |
|
| 3851 | 3851 |
-- |
| 3852 |
-- Name: iucn_red_list; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 3853 |
-- |
|
| 3854 |
|
|
| 3855 |
CREATE TABLE iucn_red_list ( |
|
| 3856 |
accepted_family varchar(255) NOT NULL, |
|
| 3857 |
accepted_species_binomial varchar(255) NOT NULL |
|
| 3858 |
); |
|
| 3859 |
|
|
| 3860 |
|
|
| 3861 |
-- |
|
| 3852 | 3862 |
-- Name: 2014-6-4.Iara_Lacher.reserve_prioritization; Type: VIEW; Schema: public; Owner: - |
| 3853 | 3863 |
-- |
| 3854 | 3864 |
|
| ... | ... | |
| 6743 | 6753 |
|
| 6744 | 6754 |
|
| 6745 | 6755 |
-- |
| 6746 |
-- Name: iucn_red_list; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 6747 |
-- |
|
| 6748 |
|
|
| 6749 |
CREATE TABLE iucn_red_list ( |
|
| 6750 |
accepted_family varchar(255) NOT NULL, |
|
| 6751 |
accepted_species_binomial varchar(255) NOT NULL |
|
| 6752 |
); |
|
| 6753 |
|
|
| 6754 |
|
|
| 6755 |
-- |
|
| 6756 | 6756 |
-- Name: iucn_red_list_view; Type: VIEW; Schema: public; Owner: - |
| 6757 | 6757 |
-- |
| 6758 | 6758 |
|
| trunk/schemas/public_.sql | ||
|---|---|---|
| 6146 | 6146 |
|
| 6147 | 6147 |
|
| 6148 | 6148 |
-- |
| 6149 |
-- Name: iucn_red_list; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 6150 |
-- |
|
| 6151 |
|
|
| 6152 |
CREATE TABLE iucn_red_list ( |
|
| 6153 |
accepted_family text NOT NULL, |
|
| 6154 |
accepted_species_binomial text NOT NULL |
|
| 6155 |
); |
|
| 6156 |
|
|
| 6157 |
|
|
| 6158 |
-- |
|
| 6149 | 6159 |
-- Name: 2014-6-4.Iara_Lacher.reserve_prioritization; Type: VIEW; Schema: public; Owner: - |
| 6150 | 6160 |
-- |
| 6151 | 6161 |
|
| ... | ... | |
| 6163 | 6173 |
analytical_stem."stem_dbh[_cm]__@SALVIAS__.Plot_data@vegpath.org" AS dbh_cm, |
| 6164 | 6174 |
analytical_stem."stemHeight[_m]__@VegBank__.stemCount@vegpath.org" AS height_m, |
| 6165 | 6175 |
analytical_stem."[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org" AS lifeform, |
| 6166 |
in_iucn_red_list(analytical_stem.scrubbed_family, analytical_stem.scrubbed_species_binomial) AS is_threatened_iucn |
|
| 6167 |
FROM analytical_stem |
|
| 6176 |
(iucn_red_list.accepted_family IS NOT NULL) AS is_threatened_iucn |
|
| 6177 |
FROM (analytical_stem |
|
| 6178 |
LEFT JOIN iucn_red_list ON (((iucn_red_list.accepted_family = analytical_stem.scrubbed_family) AND (iucn_red_list.accepted_species_binomial = analytical_stem.scrubbed_species_binomial)))) |
|
| 6168 | 6179 |
WHERE (((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem."decimalLatitude__@DwC__@vegpath.org" IS NOT NULL)) AND (analytical_stem."decimalLongitude__@DwC__@vegpath.org" IS NOT NULL)) AND in_south_america(analytical_stem."decimalLatitude__@DwC__@vegpath.org", analytical_stem."decimalLongitude__@DwC__@vegpath.org")) AND COALESCE((analytical_stem."taxonomicStatus__@DwC__@vegpath.org" = 'accepted'::text), false)); |
| 6169 | 6180 |
|
| 6170 | 6181 |
|
| ... | ... | |
| 9607 | 9618 |
|
| 9608 | 9619 |
|
| 9609 | 9620 |
-- |
| 9610 |
-- Name: iucn_red_list; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 9611 |
-- |
|
| 9612 |
|
|
| 9613 |
CREATE TABLE iucn_red_list ( |
|
| 9614 |
accepted_family text NOT NULL, |
|
| 9615 |
accepted_species_binomial text NOT NULL |
|
| 9616 |
); |
|
| 9617 |
|
|
| 9618 |
|
|
| 9619 |
-- |
|
| 9620 | 9621 |
-- Name: iucn_red_list_view; Type: VIEW; Schema: public; Owner: - |
| 9621 | 9622 |
-- |
| 9622 | 9623 |
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 6146 | 6146 |
|
| 6147 | 6147 |
|
| 6148 | 6148 |
-- |
| 6149 |
-- Name: iucn_red_list; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 6150 |
-- |
|
| 6151 |
|
|
| 6152 |
CREATE TABLE iucn_red_list ( |
|
| 6153 |
accepted_family text NOT NULL, |
|
| 6154 |
accepted_species_binomial text NOT NULL |
|
| 6155 |
); |
|
| 6156 |
|
|
| 6157 |
|
|
| 6158 |
-- |
|
| 6149 | 6159 |
-- Name: 2014-6-4.Iara_Lacher.reserve_prioritization; Type: VIEW; Schema: public; Owner: - |
| 6150 | 6160 |
-- |
| 6151 | 6161 |
|
| ... | ... | |
| 6163 | 6173 |
analytical_stem."stem_dbh[_cm]__@SALVIAS__.Plot_data@vegpath.org" AS dbh_cm, |
| 6164 | 6174 |
analytical_stem."stemHeight[_m]__@VegBank__.stemCount@vegpath.org" AS height_m, |
| 6165 | 6175 |
analytical_stem."[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org" AS lifeform, |
| 6166 |
in_iucn_red_list(analytical_stem.scrubbed_family, analytical_stem.scrubbed_species_binomial) AS is_threatened_iucn |
|
| 6167 |
FROM analytical_stem |
|
| 6176 |
(iucn_red_list.accepted_family IS NOT NULL) AS is_threatened_iucn |
|
| 6177 |
FROM (analytical_stem |
|
| 6178 |
LEFT JOIN iucn_red_list ON (((iucn_red_list.accepted_family = analytical_stem.scrubbed_family) AND (iucn_red_list.accepted_species_binomial = analytical_stem.scrubbed_species_binomial)))) |
|
| 6168 | 6179 |
WHERE (((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem."decimalLatitude__@DwC__@vegpath.org" IS NOT NULL)) AND (analytical_stem."decimalLongitude__@DwC__@vegpath.org" IS NOT NULL)) AND in_south_america(analytical_stem."decimalLatitude__@DwC__@vegpath.org", analytical_stem."decimalLongitude__@DwC__@vegpath.org")) AND COALESCE((analytical_stem."taxonomicStatus__@DwC__@vegpath.org" = 'accepted'::text), false)); |
| 6169 | 6180 |
|
| 6170 | 6181 |
|
| ... | ... | |
| 9607 | 9618 |
|
| 9608 | 9619 |
|
| 9609 | 9620 |
-- |
| 9610 |
-- Name: iucn_red_list; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 9611 |
-- |
|
| 9612 |
|
|
| 9613 |
CREATE TABLE iucn_red_list ( |
|
| 9614 |
accepted_family text NOT NULL, |
|
| 9615 |
accepted_species_binomial text NOT NULL |
|
| 9616 |
); |
|
| 9617 |
|
|
| 9618 |
|
|
| 9619 |
-- |
|
| 9620 | 9621 |
-- Name: iucn_red_list_view; Type: VIEW; Schema: public; Owner: - |
| 9621 | 9622 |
-- |
| 9622 | 9623 |
|
Also available in: Unified diff
fix: schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: is_threatened_iucn: generate this much faster by doing a LEFT JOIN, which can reuse the same in-memory hash index for every row