Revision 12516
Added by Aaron Marcuse-Kubitza almost 11 years ago
| trunk/inputs/GBIF/raw_occurrence_record_plants/postprocess.sql | ||
|---|---|---|
| 42 | 42 |
SELECT DISTINCT dataprovider |
| 43 | 43 |
FROM sourcelist |
| 44 | 44 |
JOIN provider_count ON provider_count.dataprovider = sourcelist.name |
| 45 |
WHERE source_id = source_by_shortname('GBIF')
|
|
| 45 |
WHERE source_id = (SELECT source_by_shortname('GBIF'))
|
|
| 46 | 46 |
ORDER BY dataprovider |
| 47 | 47 |
*/ |
| 48 | 48 |
; |
| trunk/inputs/SpeciesLink/Specimen/postprocess.sql | ||
|---|---|---|
| 12 | 12 |
SELECT DISTINCT dataprovider |
| 13 | 13 |
FROM sourcelist |
| 14 | 14 |
JOIN provider_count ON provider_count.dataprovider = sourcelist.name |
| 15 |
WHERE source_id = source_by_shortname('SpeciesLink')
|
|
| 15 |
WHERE source_id = (SELECT source_by_shortname('SpeciesLink'))
|
|
| 16 | 16 |
ORDER BY dataprovider |
| 17 | 17 |
*/ |
| 18 | 18 |
; |
| trunk/inputs/REMIB/Specimen/postprocess.sql | ||
|---|---|---|
| 25 | 25 |
SELECT DISTINCT dataprovider |
| 26 | 26 |
FROM sourcelist |
| 27 | 27 |
JOIN provider_count ON provider_count.dataprovider = sourcelist.name |
| 28 |
WHERE source_id = source_by_shortname('REMIB')
|
|
| 28 |
WHERE source_id = (SELECT source_by_shortname('REMIB'))
|
|
| 29 | 29 |
ORDER BY dataprovider |
| 30 | 30 |
*/ |
| 31 | 31 |
; |
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 1090 | 1090 |
|
| 1091 | 1091 |
|
| 1092 | 1092 |
|
| 1093 |
-- |
|
| 1094 |
-- Name: ~type._plots_01_count_of_projects; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 1095 |
-- |
|
| 1096 | 1093 |
|
| 1097 |
CREATE TABLE `~type._plots_01_count_of_projects` ( |
|
| 1098 |
projects varchar(255) |
|
| 1099 |
); |
|
| 1100 | 1094 |
|
| 1101 |
|
|
| 1102 | 1095 |
-- |
| 1103 |
-- Name: ~type._plots_02_list_of_project_names; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 1104 |
-- |
|
| 1105 |
|
|
| 1106 |
CREATE TABLE `~type._plots_02_list_of_project_names` ( |
|
| 1107 |
project_name varchar(255) |
|
| 1108 |
); |
|
| 1109 |
|
|
| 1110 |
|
|
| 1111 |
-- |
|
| 1112 |
-- Name: ~type._plots_03_count_of_all_plots_in_this_source; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 1113 |
-- |
|
| 1114 |
|
|
| 1115 |
CREATE TABLE `~type._plots_03_count_of_all_plots_in_this_source` ( |
|
| 1116 |
plots varchar(255) |
|
| 1117 |
); |
|
| 1118 |
|
|
| 1119 |
|
|
| 1120 |
-- |
|
| 1121 |
-- Name: ~type._plots_06_list_of_plots_with_stem_measurements; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 1122 |
-- |
|
| 1123 |
|
|
| 1124 |
CREATE TABLE `~type._plots_06_list_of_plots_with_stem_measurements` ( |
|
| 1125 |
project_name varchar(255), |
|
| 1126 |
`SiteCode` varchar(255) |
|
| 1127 |
); |
|
| 1128 |
|
|
| 1129 |
|
|
| 1130 |
-- |
|
| 1131 |
-- Name: ~type._plots_10_count_of_individuals_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 1132 |
-- |
|
| 1133 |
|
|
| 1134 |
CREATE TABLE `~type._plots_10_count_of_individuals_per_plot_in_each_project` ( |
|
| 1135 |
project_name varchar(255), |
|
| 1136 |
plotcode varchar(255), |
|
| 1137 |
individuals varchar(255) |
|
| 1138 |
); |
|
| 1139 |
|
|
| 1140 |
|
|
| 1141 |
-- |
|
| 1142 |
-- Name: ~type._plots_11_count_of_stems_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 1143 |
-- |
|
| 1144 |
|
|
| 1145 |
CREATE TABLE `~type._plots_11_count_of_stems_per_plot_in_each_project` ( |
|
| 1146 |
project_name varchar(255), |
|
| 1147 |
plotcode varchar(255), |
|
| 1148 |
stems varchar(255) |
|
| 1149 |
); |
|
| 1150 |
|
|
| 1151 |
|
|
| 1152 |
-- |
|
| 1153 |
-- Name: ~type._plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 1154 |
-- |
|
| 1155 |
|
|
| 1156 |
CREATE TABLE `~type._plots_12_count_of_verbatim_taxa_per_plot_in_each_project` ( |
|
| 1157 |
project_name varchar(255), |
|
| 1158 |
plotcode varchar(255), |
|
| 1159 |
taxa varchar(255) |
|
| 1160 |
); |
|
| 1161 |
|
|
| 1162 |
|
|
| 1163 |
-- |
|
| 1164 |
-- Name: ~type._plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 1165 |
-- |
|
| 1166 |
|
|
| 1167 |
CREATE TABLE `~type._plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_` ( |
|
| 1168 |
project_name varchar(255), |
|
| 1169 |
plotcode varchar(255), |
|
| 1170 |
taxon varchar(255) |
|
| 1171 |
); |
|
| 1172 |
|
|
| 1173 |
|
|
| 1174 |
-- |
|
| 1175 |
-- Name: ~type._plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_e; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 1176 |
-- |
|
| 1177 |
|
|
| 1178 |
CREATE TABLE `~type._plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_e` ( |
|
| 1179 |
project_name varchar(255), |
|
| 1180 |
plotcode varchar(255), |
|
| 1181 |
taxon varchar(255), |
|
| 1182 |
individuals varchar(255) |
|
| 1183 |
); |
|
| 1184 |
|
|
| 1185 |
|
|
| 1186 |
-- |
|
| 1187 |
-- Name: ~type._plots_17_count_of_subplots_per_plot_for_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 1188 |
-- |
|
| 1189 |
|
|
| 1190 |
CREATE TABLE `~type._plots_17_count_of_subplots_per_plot_for_each_project` ( |
|
| 1191 |
project_name varchar(255), |
|
| 1192 |
plotcode varchar(255), |
|
| 1193 |
subplots varchar(255) |
|
| 1194 |
); |
|
| 1195 |
|
|
| 1196 |
|
|
| 1197 |
|
|
| 1198 |
|
|
| 1199 |
-- |
|
| 1200 | 1096 |
-- Name: analytical_stem; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1201 | 1097 |
-- |
| 1202 | 1098 |
|
| ... | ... | |
| 4008 | 3904 |
|
| 4009 | 3905 |
|
| 4010 | 3906 |
-- |
| 3907 |
-- Name: ~type._plots_01_count_of_projects; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 3908 |
-- |
|
| 3909 |
|
|
| 3910 |
CREATE TABLE `~type._plots_01_count_of_projects` ( |
|
| 3911 |
projects varchar(255) |
|
| 3912 |
); |
|
| 3913 |
|
|
| 3914 |
|
|
| 3915 |
-- |
|
| 3916 |
-- Name: ~type._plots_02_list_of_project_names; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 3917 |
-- |
|
| 3918 |
|
|
| 3919 |
CREATE TABLE `~type._plots_02_list_of_project_names` ( |
|
| 3920 |
project_name varchar(255) |
|
| 3921 |
); |
|
| 3922 |
|
|
| 3923 |
|
|
| 3924 |
-- |
|
| 3925 |
-- Name: ~type._plots_03_count_of_all_plots_in_this_source; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 3926 |
-- |
|
| 3927 |
|
|
| 3928 |
CREATE TABLE `~type._plots_03_count_of_all_plots_in_this_source` ( |
|
| 3929 |
plots varchar(255) |
|
| 3930 |
); |
|
| 3931 |
|
|
| 3932 |
|
|
| 3933 |
-- |
|
| 3934 |
-- Name: ~type._plots_06_list_of_plots_with_stem_measurements; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 3935 |
-- |
|
| 3936 |
|
|
| 3937 |
CREATE TABLE `~type._plots_06_list_of_plots_with_stem_measurements` ( |
|
| 3938 |
project_name varchar(255), |
|
| 3939 |
`SiteCode` varchar(255) |
|
| 3940 |
); |
|
| 3941 |
|
|
| 3942 |
|
|
| 3943 |
-- |
|
| 3944 |
-- Name: ~type._plots_10_count_of_individuals_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 3945 |
-- |
|
| 3946 |
|
|
| 3947 |
CREATE TABLE `~type._plots_10_count_of_individuals_per_plot_in_each_project` ( |
|
| 3948 |
project_name varchar(255), |
|
| 3949 |
plotcode varchar(255), |
|
| 3950 |
individuals varchar(255) |
|
| 3951 |
); |
|
| 3952 |
|
|
| 3953 |
|
|
| 3954 |
-- |
|
| 3955 |
-- Name: ~type._plots_11_count_of_stems_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 3956 |
-- |
|
| 3957 |
|
|
| 3958 |
CREATE TABLE `~type._plots_11_count_of_stems_per_plot_in_each_project` ( |
|
| 3959 |
project_name varchar(255), |
|
| 3960 |
plotcode varchar(255), |
|
| 3961 |
stems varchar(255) |
|
| 3962 |
); |
|
| 3963 |
|
|
| 3964 |
|
|
| 3965 |
-- |
|
| 3966 |
-- Name: ~type._plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 3967 |
-- |
|
| 3968 |
|
|
| 3969 |
CREATE TABLE `~type._plots_12_count_of_verbatim_taxa_per_plot_in_each_project` ( |
|
| 3970 |
project_name varchar(255), |
|
| 3971 |
plotcode varchar(255), |
|
| 3972 |
taxa varchar(255) |
|
| 3973 |
); |
|
| 3974 |
|
|
| 3975 |
|
|
| 3976 |
-- |
|
| 3977 |
-- Name: ~type._plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 3978 |
-- |
|
| 3979 |
|
|
| 3980 |
CREATE TABLE `~type._plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_` ( |
|
| 3981 |
project_name varchar(255), |
|
| 3982 |
plotcode varchar(255), |
|
| 3983 |
taxon varchar(255) |
|
| 3984 |
); |
|
| 3985 |
|
|
| 3986 |
|
|
| 3987 |
-- |
|
| 3988 |
-- Name: ~type._plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_e; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 3989 |
-- |
|
| 3990 |
|
|
| 3991 |
CREATE TABLE `~type._plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_e` ( |
|
| 3992 |
project_name varchar(255), |
|
| 3993 |
plotcode varchar(255), |
|
| 3994 |
taxon varchar(255), |
|
| 3995 |
individuals varchar(255) |
|
| 3996 |
); |
|
| 3997 |
|
|
| 3998 |
|
|
| 3999 |
-- |
|
| 4000 |
-- Name: ~type._plots_17_count_of_subplots_per_plot_for_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 4001 |
-- |
|
| 4002 |
|
|
| 4003 |
CREATE TABLE `~type._plots_17_count_of_subplots_per_plot_for_each_project` ( |
|
| 4004 |
project_name varchar(255), |
|
| 4005 |
plotcode varchar(255), |
|
| 4006 |
subplots varchar(255) |
|
| 4007 |
); |
|
| 4008 |
|
|
| 4009 |
|
|
| 4010 |
-- |
|
| 4011 | 4011 |
-- Name: ~type._traits_01_count_records; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
| 4012 | 4012 |
-- |
| 4013 | 4013 |
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 862 | 862 |
DELETE FROM taxondetermination |
| 863 | 863 |
USING taxonoccurrence |
| 864 | 864 |
WHERE |
| 865 |
taxonoccurrence.source_id = source_by_shortname($1)
|
|
| 865 |
taxonoccurrence.source_id = (SELECT source_by_shortname($1))
|
|
| 866 | 866 |
AND taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id |
| 867 |
AND taxondetermination.source_id = source_by_shortname('TNRS')
|
|
| 867 |
AND taxondetermination.source_id = (SELECT source_by_shortname('TNRS'))
|
|
| 868 | 868 |
$_$; |
| 869 | 869 |
|
| 870 | 870 |
|
| ... | ... | |
| 2484 | 2484 |
$_$; |
| 2485 | 2485 |
|
| 2486 | 2486 |
|
| 2487 |
-- |
|
| 2488 |
-- Name: ~type._plots_01_count_of_projects; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2489 |
-- |
|
| 2490 |
|
|
| 2491 |
CREATE TABLE "~type._plots_01_count_of_projects" ( |
|
| 2492 |
projects bigint |
|
| 2493 |
); |
|
| 2494 |
|
|
| 2495 |
|
|
| 2496 |
-- |
|
| 2497 |
-- Name: ~type._plots_02_list_of_project_names; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2498 |
-- |
|
| 2499 |
|
|
| 2500 |
CREATE TABLE "~type._plots_02_list_of_project_names" ( |
|
| 2501 |
project_name text |
|
| 2502 |
); |
|
| 2503 |
|
|
| 2504 |
|
|
| 2505 |
-- |
|
| 2506 |
-- Name: ~type._plots_03_count_of_all_plots_in_this_source; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2507 |
-- |
|
| 2508 |
|
|
| 2509 |
CREATE TABLE "~type._plots_03_count_of_all_plots_in_this_source" ( |
|
| 2510 |
plots bigint |
|
| 2511 |
); |
|
| 2512 |
|
|
| 2513 |
|
|
| 2514 |
-- |
|
| 2515 |
-- Name: ~type._plots_06_list_of_plots_with_stem_measurements; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2516 |
-- |
|
| 2517 |
|
|
| 2518 |
CREATE TABLE "~type._plots_06_list_of_plots_with_stem_measurements" ( |
|
| 2519 |
project_name text, |
|
| 2520 |
"SiteCode" text |
|
| 2521 |
); |
|
| 2522 |
|
|
| 2523 |
|
|
| 2524 |
-- |
|
| 2525 |
-- Name: ~type._plots_10_count_of_individuals_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2526 |
-- |
|
| 2527 |
|
|
| 2528 |
CREATE TABLE "~type._plots_10_count_of_individuals_per_plot_in_each_project" ( |
|
| 2529 |
project_name text, |
|
| 2530 |
plotcode text, |
|
| 2531 |
individuals bigint |
|
| 2532 |
); |
|
| 2533 |
|
|
| 2534 |
|
|
| 2535 |
-- |
|
| 2536 |
-- Name: ~type._plots_11_count_of_stems_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2537 |
-- |
|
| 2538 |
|
|
| 2539 |
CREATE TABLE "~type._plots_11_count_of_stems_per_plot_in_each_project" ( |
|
| 2540 |
project_name text, |
|
| 2541 |
plotcode text, |
|
| 2542 |
stems bigint |
|
| 2543 |
); |
|
| 2544 |
|
|
| 2545 |
|
|
| 2546 |
-- |
|
| 2547 |
-- Name: ~type._plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2548 |
-- |
|
| 2549 |
|
|
| 2550 |
CREATE TABLE "~type._plots_12_count_of_verbatim_taxa_per_plot_in_each_project" ( |
|
| 2551 |
project_name text, |
|
| 2552 |
plotcode text, |
|
| 2553 |
taxa bigint |
|
| 2554 |
); |
|
| 2555 |
|
|
| 2556 |
|
|
| 2557 |
-- |
|
| 2558 |
-- Name: ~type._plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2559 |
-- |
|
| 2560 |
|
|
| 2561 |
CREATE TABLE "~type._plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_" ( |
|
| 2562 |
project_name text, |
|
| 2563 |
plotcode text, |
|
| 2564 |
taxon text |
|
| 2565 |
); |
|
| 2566 |
|
|
| 2567 |
|
|
| 2568 |
-- |
|
| 2569 |
-- Name: ~type._plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_e; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2570 |
-- |
|
| 2571 |
|
|
| 2572 |
CREATE TABLE "~type._plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_e" ( |
|
| 2573 |
project_name text, |
|
| 2574 |
plotcode text, |
|
| 2575 |
taxon text, |
|
| 2576 |
individuals bigint |
|
| 2577 |
); |
|
| 2578 |
|
|
| 2579 |
|
|
| 2580 |
-- |
|
| 2581 |
-- Name: ~type._plots_17_count_of_subplots_per_plot_for_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2582 |
-- |
|
| 2583 |
|
|
| 2584 |
CREATE TABLE "~type._plots_17_count_of_subplots_per_plot_for_each_project" ( |
|
| 2585 |
project_name text, |
|
| 2586 |
plotcode text, |
|
| 2587 |
subplots bigint |
|
| 2588 |
); |
|
| 2589 |
|
|
| 2590 |
|
|
| 2591 | 2487 |
SET search_path = public, pg_catalog; |
| 2592 | 2488 |
|
| 2593 | 2489 |
-- |
| ... | ... | |
| 5894 | 5790 |
FROM ((taxonlabel datasource_taxonlabel |
| 5895 | 5791 |
JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) |
| 5896 | 5792 |
JOIN taxonlabel_relationship accepted_taxonlabel_descendant ON ((accepted_taxonlabel_descendant.ancestor_id = accepted_taxonlabel.taxonlabel_id))) |
| 5897 |
WHERE (((datasource_taxonlabel.source_id = source_by_shortname('IUCN'::text)) AND (datasource_taxonlabel.taxonomicname IS NOT NULL)) AND (accepted_taxonlabel.rank >= 'species'::taxonrank));
|
|
| 5793 |
WHERE (((datasource_taxonlabel.source_id = ( SELECT source_by_shortname('IUCN'::text) AS source_by_shortname)) AND (datasource_taxonlabel.taxonomicname IS NOT NULL)) AND (accepted_taxonlabel.rank >= 'species'::taxonrank));
|
|
| 5898 | 5794 |
|
| 5899 | 5795 |
|
| 5900 | 5796 |
-- |
| ... | ... | |
| 6134 | 6030 |
FROM ((public.top_plot |
| 6135 | 6031 |
JOIN public.locationevent USING (location_id)) |
| 6136 | 6032 |
JOIN public.project USING (project_id)) |
| 6137 |
WHERE ((top_plot.source_id = public.source_by_shortname(("current_schema"())::text)) AND (EXISTS ( SELECT NULL::unknown AS unknown
|
|
| 6033 |
WHERE ((top_plot.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (EXISTS ( SELECT NULL::unknown AS unknown
|
|
| 6138 | 6034 |
FROM (((((public.location |
| 6139 | 6035 |
JOIN public.locationevent locationevent_1 USING (location_id)) |
| 6140 | 6036 |
JOIN public.taxonoccurrence USING (locationevent_id)) |
| ... | ... | |
| 6389 | 6285 |
SELECT count(*) AS "totalRecords" |
| 6390 | 6286 |
FROM (public.trait |
| 6391 | 6287 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| 6392 |
WHERE (taxonoccurrence.source_id = public.source_by_shortname(("current_schema"())::text));
|
|
| 6288 |
WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname));
|
|
| 6393 | 6289 |
|
| 6394 | 6290 |
|
| 6395 | 6291 |
-- |
| ... | ... | |
| 6407 | 6303 |
SELECT count(DISTINCT trait.name) AS traits |
| 6408 | 6304 |
FROM (public.trait |
| 6409 | 6305 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| 6410 |
WHERE (taxonoccurrence.source_id = public.source_by_shortname(("current_schema"())::text));
|
|
| 6306 |
WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname));
|
|
| 6411 | 6307 |
|
| 6412 | 6308 |
|
| 6413 | 6309 |
-- |
| ... | ... | |
| 6418 | 6314 |
SELECT DISTINCT trait.name AS trait |
| 6419 | 6315 |
FROM (public.trait |
| 6420 | 6316 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| 6421 |
WHERE (taxonoccurrence.source_id = public.source_by_shortname(("current_schema"())::text))
|
|
| 6317 |
WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
|
|
| 6422 | 6318 |
ORDER BY trait.name; |
| 6423 | 6319 |
|
| 6424 | 6320 |
|
| ... | ... | |
| 6431 | 6327 |
count(*) AS measurements |
| 6432 | 6328 |
FROM (public.trait |
| 6433 | 6329 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| 6434 |
WHERE (taxonoccurrence.source_id = public.source_by_shortname(("current_schema"())::text))
|
|
| 6330 |
WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
|
|
| 6435 | 6331 |
GROUP BY trait.name |
| 6436 | 6332 |
ORDER BY trait.name; |
| 6437 | 6333 |
|
| ... | ... | |
| 6443 | 6339 |
CREATE VIEW _traits_05_count_taxa AS |
| 6444 | 6340 |
SELECT count(DISTINCT ROW(taxonverbatim.taxonname, taxonverbatim.author)) AS taxa |
| 6445 | 6341 |
FROM public.taxonverbatim |
| 6446 |
WHERE (taxonverbatim.source_id = public.source_by_shortname(("current_schema"())::text));
|
|
| 6342 |
WHERE (taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname));
|
|
| 6447 | 6343 |
|
| 6448 | 6344 |
|
| 6449 | 6345 |
-- |
| ... | ... | |
| 6462 | 6358 |
CREATE VIEW _traits_06_list_distinct_taxa AS |
| 6463 | 6359 |
SELECT DISTINCT concat_ws(' '::text, taxonverbatim.taxonname, taxonverbatim.author) AS taxonwithauthor
|
| 6464 | 6360 |
FROM public.taxonverbatim |
| 6465 |
WHERE (taxonverbatim.source_id = public.source_by_shortname(("current_schema"())::text))
|
|
| 6361 |
WHERE (taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
|
|
| 6466 | 6362 |
ORDER BY concat_ws(' '::text, taxonverbatim.taxonname, taxonverbatim.author);
|
| 6467 | 6363 |
|
| 6468 | 6364 |
|
| ... | ... | |
| 6486 | 6382 |
trait.units |
| 6487 | 6383 |
FROM (public.trait |
| 6488 | 6384 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| 6489 |
WHERE (taxonoccurrence.source_id = public.source_by_shortname(("current_schema"())::text))
|
|
| 6385 |
WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
|
|
| 6490 | 6386 |
ORDER BY trait.name, trait.value, trait.units; |
| 6491 | 6387 |
|
| 6492 | 6388 |
|
| ... | ... | |
| 6501 | 6397 |
FROM ((public.taxonverbatim |
| 6502 | 6398 |
JOIN public.taxondetermination ON (((taxondetermination.taxonverbatim_id = taxonverbatim.taxonverbatim_id) AND taxondetermination.iscurrent))) |
| 6503 | 6399 |
JOIN public.trait USING (taxonoccurrence_id)) |
| 6504 |
WHERE (taxonverbatim.source_id = public.source_by_shortname(("current_schema"())::text))
|
|
| 6400 |
WHERE (taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
|
|
| 6505 | 6401 |
ORDER BY taxonverbatim.taxonname; |
| 6506 | 6402 |
|
| 6507 | 6403 |
|
| 6508 | 6404 |
-- |
| 6405 |
-- Name: ~type._plots_01_count_of_projects; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6406 |
-- |
|
| 6407 |
|
|
| 6408 |
CREATE TABLE "~type._plots_01_count_of_projects" ( |
|
| 6409 |
projects bigint |
|
| 6410 |
); |
|
| 6411 |
|
|
| 6412 |
|
|
| 6413 |
-- |
|
| 6414 |
-- Name: ~type._plots_02_list_of_project_names; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6415 |
-- |
|
| 6416 |
|
|
| 6417 |
CREATE TABLE "~type._plots_02_list_of_project_names" ( |
|
| 6418 |
project_name text |
|
| 6419 |
); |
|
| 6420 |
|
|
| 6421 |
|
|
| 6422 |
-- |
|
| 6423 |
-- Name: ~type._plots_03_count_of_all_plots_in_this_source; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6424 |
-- |
|
| 6425 |
|
|
| 6426 |
CREATE TABLE "~type._plots_03_count_of_all_plots_in_this_source" ( |
|
| 6427 |
plots bigint |
|
| 6428 |
); |
|
| 6429 |
|
|
| 6430 |
|
|
| 6431 |
-- |
|
| 6432 |
-- Name: ~type._plots_06_list_of_plots_with_stem_measurements; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6433 |
-- |
|
| 6434 |
|
|
| 6435 |
CREATE TABLE "~type._plots_06_list_of_plots_with_stem_measurements" ( |
|
| 6436 |
project_name text, |
|
| 6437 |
"SiteCode" text |
|
| 6438 |
); |
|
| 6439 |
|
|
| 6440 |
|
|
| 6441 |
-- |
|
| 6442 |
-- Name: ~type._plots_10_count_of_individuals_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6443 |
-- |
|
| 6444 |
|
|
| 6445 |
CREATE TABLE "~type._plots_10_count_of_individuals_per_plot_in_each_project" ( |
|
| 6446 |
project_name text, |
|
| 6447 |
plotcode text, |
|
| 6448 |
individuals bigint |
|
| 6449 |
); |
|
| 6450 |
|
|
| 6451 |
|
|
| 6452 |
-- |
|
| 6453 |
-- Name: ~type._plots_11_count_of_stems_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6454 |
-- |
|
| 6455 |
|
|
| 6456 |
CREATE TABLE "~type._plots_11_count_of_stems_per_plot_in_each_project" ( |
|
| 6457 |
project_name text, |
|
| 6458 |
plotcode text, |
|
| 6459 |
stems bigint |
|
| 6460 |
); |
|
| 6461 |
|
|
| 6462 |
|
|
| 6463 |
-- |
|
| 6464 |
-- Name: ~type._plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6465 |
-- |
|
| 6466 |
|
|
| 6467 |
CREATE TABLE "~type._plots_12_count_of_verbatim_taxa_per_plot_in_each_project" ( |
|
| 6468 |
project_name text, |
|
| 6469 |
plotcode text, |
|
| 6470 |
taxa bigint |
|
| 6471 |
); |
|
| 6472 |
|
|
| 6473 |
|
|
| 6474 |
-- |
|
| 6475 |
-- Name: ~type._plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6476 |
-- |
|
| 6477 |
|
|
| 6478 |
CREATE TABLE "~type._plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_" ( |
|
| 6479 |
project_name text, |
|
| 6480 |
plotcode text, |
|
| 6481 |
taxon text |
|
| 6482 |
); |
|
| 6483 |
|
|
| 6484 |
|
|
| 6485 |
-- |
|
| 6486 |
-- Name: ~type._plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_e; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6487 |
-- |
|
| 6488 |
|
|
| 6489 |
CREATE TABLE "~type._plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_e" ( |
|
| 6490 |
project_name text, |
|
| 6491 |
plotcode text, |
|
| 6492 |
taxon text, |
|
| 6493 |
individuals bigint |
|
| 6494 |
); |
|
| 6495 |
|
|
| 6496 |
|
|
| 6497 |
-- |
|
| 6498 |
-- Name: ~type._plots_17_count_of_subplots_per_plot_for_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6499 |
-- |
|
| 6500 |
|
|
| 6501 |
CREATE TABLE "~type._plots_17_count_of_subplots_per_plot_for_each_project" ( |
|
| 6502 |
project_name text, |
|
| 6503 |
plotcode text, |
|
| 6504 |
subplots bigint |
|
| 6505 |
); |
|
| 6506 |
|
|
| 6507 |
|
|
| 6508 |
-- |
|
| 6509 | 6509 |
-- Name: ~type._traits_01_count_records; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
| 6510 | 6510 |
-- |
| 6511 | 6511 |
|
| trunk/validation/aggregating/plots/bien3_validations_salvias_vegbien.sql | ||
|---|---|---|
| 89 | 89 |
|
| 90 | 90 |
SELECT authorlocationcode |
| 91 | 91 |
FROM top_plot |
| 92 |
WHERE source_id = source_by_shortname(current_schema)
|
|
| 92 |
WHERE source_id = (SELECT source_by_shortname(current_schema))
|
|
| 93 | 93 |
AND EXISTS( |
| 94 | 94 |
SELECT NULL |
| 95 | 95 |
FROM location |
| trunk/validation/aggregating/traits/bien3_validations_traits_bien3.sql | ||
|---|---|---|
| 14 | 14 |
SELECT COUNT(*) AS "totalRecords" |
| 15 | 15 |
FROM trait |
| 16 | 16 |
JOIN taxonoccurrence USING (taxonoccurrence_id) |
| 17 |
WHERE taxonoccurrence.source_id = source_by_shortname(current_schema)
|
|
| 17 |
WHERE taxonoccurrence.source_id = (SELECT source_by_shortname(current_schema))
|
|
| 18 | 18 |
; |
| 19 | 19 |
|
| 20 | 20 |
-- ------------------ |
| ... | ... | |
| 24 | 24 |
SELECT COUNT(DISTINCT name) AS traits |
| 25 | 25 |
FROM trait |
| 26 | 26 |
JOIN taxonoccurrence USING (taxonoccurrence_id) |
| 27 |
WHERE taxonoccurrence.source_id = source_by_shortname(current_schema);
|
|
| 27 |
WHERE taxonoccurrence.source_id = (SELECT source_by_shortname(current_schema));
|
|
| 28 | 28 |
|
| 29 | 29 |
-- ------------------ |
| 30 | 30 |
-- 3. List trait names |
| ... | ... | |
| 33 | 33 |
SELECT DISTINCT name AS trait |
| 34 | 34 |
FROM trait |
| 35 | 35 |
JOIN taxonoccurrence USING (taxonoccurrence_id) |
| 36 |
WHERE taxonoccurrence.source_id = source_by_shortname(current_schema)
|
|
| 36 |
WHERE taxonoccurrence.source_id = (SELECT source_by_shortname(current_schema))
|
|
| 37 | 37 |
ORDER BY name |
| 38 | 38 |
; |
| 39 | 39 |
|
| ... | ... | |
| 44 | 44 |
SELECT name AS trait, COUNT(*) AS measurements |
| 45 | 45 |
FROM trait |
| 46 | 46 |
JOIN taxonoccurrence USING (taxonoccurrence_id) |
| 47 |
WHERE taxonoccurrence.source_id = source_by_shortname(current_schema)
|
|
| 47 |
WHERE taxonoccurrence.source_id = (SELECT source_by_shortname(current_schema))
|
|
| 48 | 48 |
GROUP BY name |
| 49 | 49 |
ORDER BY name |
| 50 | 50 |
; |
| ... | ... | |
| 58 | 58 |
CREATE OR REPLACE VIEW _traits_05_count_taxa AS |
| 59 | 59 |
SELECT COUNT(DISTINCT (taxonname, author)) AS taxa |
| 60 | 60 |
FROM taxonverbatim |
| 61 |
WHERE source_id = source_by_shortname(current_schema)
|
|
| 61 |
WHERE source_id = (SELECT source_by_shortname(current_schema))
|
|
| 62 | 62 |
; |
| 63 | 63 |
|
| 64 | 64 |
-- ------------------ |
| ... | ... | |
| 71 | 71 |
CREATE OR REPLACE VIEW _traits_06_list_taxa AS |
| 72 | 72 |
SELECT DISTINCT concat_ws(' ', taxonname, author) AS taxonwithauthor
|
| 73 | 73 |
FROM taxonverbatim |
| 74 |
WHERE source_id = source_by_shortname(current_schema)
|
|
| 74 |
WHERE source_id = (SELECT source_by_shortname(current_schema))
|
|
| 75 | 75 |
ORDER BY concat_ws(' ', taxonname, author)
|
| 76 | 76 |
; |
| 77 | 77 |
|
| ... | ... | |
| 82 | 82 |
SELECT name AS trait, value, units |
| 83 | 83 |
FROM trait |
| 84 | 84 |
JOIN taxonoccurrence USING (taxonoccurrence_id) |
| 85 |
WHERE taxonoccurrence.source_id = source_by_shortname(current_schema)
|
|
| 85 |
WHERE taxonoccurrence.source_id = (SELECT source_by_shortname(current_schema))
|
|
| 86 | 86 |
ORDER BY name, value, units |
| 87 | 87 |
LIMIT 5000 |
| 88 | 88 |
; |
| ... | ... | |
| 95 | 95 |
FROM taxonverbatim |
| 96 | 96 |
JOIN taxondetermination ON taxondetermination.taxonverbatim_id = taxonverbatim.taxonverbatim_id AND taxondetermination.iscurrent |
| 97 | 97 |
JOIN trait USING (taxonoccurrence_id) |
| 98 |
WHERE taxonverbatim.source_id = source_by_shortname(current_schema)
|
|
| 98 |
WHERE taxonverbatim.source_id = (SELECT source_by_shortname(current_schema))
|
|
| 99 | 99 |
ORDER BY taxonname |
| 100 | 100 |
LIMIT 5000 |
| 101 | 101 |
; |
Also available in: Unified diff
bugfix: *.sql: public.source_by_shortname(): need to wrap it in a nested SELECT because Postgres incorrectly does not constant-fold (inline) it, leading to a slowdown when it is therefore run many times. this is done using the steps at wiki.vegpath.org/Postgres_queries#wrap-function-call-in-nested-SELECT .