1 |
8291
|
aaronmk
|
#!/bin/bash -e
|
2 |
8287
|
aaronmk
|
. "$(dirname "${BASH_SOURCE[0]}")"/../table.run
|
3 |
8303
|
aaronmk
|
|
4 |
8705
|
aaronmk
|
if self_not_included; then
|
5 |
|
|
|
6 |
9461
|
aaronmk
|
herbaria_filter.ih.csv_/make()
|
7 |
9434
|
aaronmk
|
{
|
8 |
|
|
echo_func; set_make_vars; check_target_exists
|
9 |
|
|
|
10 |
|
|
use_local
|
11 |
9554
|
aaronmk
|
limit= query='SELECT DISTINCT acronym FROM herbaria.ih ORDER BY acronym' \
|
12 |
9434
|
aaronmk
|
to_target pg_export
|
13 |
|
|
}
|
14 |
|
|
|
15 |
9637
|
aaronmk
|
plant_fraction/make()
|
16 |
9483
|
aaronmk
|
{
|
17 |
|
|
echo_func; set_make_vars; mk_table_esc
|
18 |
|
|
|
19 |
|
|
use_local_remote
|
20 |
9627
|
aaronmk
|
if remaking; then table=plant_fraction table_esc= mk_drop|mysql_ANSI; fi
|
21 |
9626
|
aaronmk
|
(benign_error=1 mysql <<'EOF'
|
22 |
|
|
CREATE TABLE `plant_fraction` (
|
23 |
9592
|
aaronmk
|
`institution_code` varchar(255) NOT NULL PRIMARY KEY,
|
24 |
9629
|
aaronmk
|
`plant_fraction` float NOT NULL,
|
25 |
|
|
KEY `plant_fraction` (`plant_fraction`)
|
26 |
9628
|
aaronmk
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8
|
27 |
9592
|
aaronmk
|
EOF
|
28 |
9626
|
aaronmk
|
) || table=plant_fraction skip_table || return 0
|
29 |
9483
|
aaronmk
|
mysql_ANSI <<EOF
|
30 |
9592
|
aaronmk
|
INSERT INTO "plant_fraction"
|
31 |
9483
|
aaronmk
|
SELECT
|
32 |
|
|
institution_code
|
33 |
|
|
, COUNT(family LIKE '%aceae%' OR family IN ('Compositae','Gramineae','Palmae','Guttiferae','Cruciferae','Labiatae','Umbelliferae','Leguminosae'))/COUNT(*)
|
34 |
|
|
AS plant_fraction
|
35 |
|
|
FROM ${limit:+(SELECT * FROM }$table_esc ${limit:+$(prefix= limit)) s}
|
36 |
|
|
GROUP BY institution_code
|
37 |
|
|
ORDER BY institution_code
|
38 |
|
|
EOF
|
39 |
|
|
}
|
40 |
|
|
|
41 |
9637
|
aaronmk
|
plant_fraction/seal()
|
42 |
9633
|
aaronmk
|
{
|
43 |
|
|
echo_func; set_make_vars
|
44 |
|
|
|
45 |
|
|
use_local_remote
|
46 |
|
|
table=plant_fraction table_esc= mysql_seal_table
|
47 |
|
|
}
|
48 |
|
|
|
49 |
9639
|
aaronmk
|
plant_fraction_for_herbaria_filter/make()
|
50 |
|
|
{
|
51 |
|
|
echo_func; set_make_vars; mk_table_esc
|
52 |
|
|
|
53 |
|
|
use_local_remote
|
54 |
|
|
if remaking; then
|
55 |
|
|
table=plant_fraction_for_herbaria_filter table_esc= mk_drop|mysql_ANSI
|
56 |
|
|
fi
|
57 |
|
|
(benign_error=1 mysql <<'EOF'
|
58 |
|
|
CREATE VIEW `plant_fraction_for_herbaria_filter` AS
|
59 |
|
|
SELECT * FROM `plant_fraction`
|
60 |
|
|
WHERE plant_fraction >= 0.8
|
61 |
|
|
EOF
|
62 |
|
|
) || table=plant_fraction_for_herbaria_filter skip_table || return 0
|
63 |
|
|
}
|
64 |
|
|
|
65 |
9638
|
aaronmk
|
herbaria_filter/make()
|
66 |
9433
|
aaronmk
|
{
|
67 |
|
|
echo_func; set_make_vars
|
68 |
|
|
|
69 |
9659
|
aaronmk
|
use_local_remote
|
70 |
|
|
local table="$target_stem"
|
71 |
|
|
if remaking; then mk_drop|mysql_ANSI; remake=; fi
|
72 |
|
|
|
73 |
9661
|
aaronmk
|
herbaria_filter.ih.csv_/make
|
74 |
|
|
plant_fraction_for_herbaria_filter/make
|
75 |
9434
|
aaronmk
|
|
76 |
9643
|
aaronmk
|
(benign_error=1 mysql <<'EOF'
|
77 |
|
|
CREATE TABLE `herbaria_filter` (
|
78 |
9663
|
aaronmk
|
`institution_code` varchar(255) NOT NULL PRIMARY KEY
|
79 |
9664
|
aaronmk
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='contains all of IH.vegpath.org?&Index+Herbariorum'
|
80 |
9433
|
aaronmk
|
EOF
|
81 |
9643
|
aaronmk
|
) || skip_table || return 0
|
82 |
9661
|
aaronmk
|
mysql_ANSI <<EOF
|
83 |
|
|
INSERT INTO "herbaria_filter"
|
84 |
|
|
SELECT institution_code FROM plant_fraction_for_herbaria_filter
|
85 |
|
|
EOF
|
86 |
|
|
append=1 mysql_import <"$top_dir"/herbaria_filter.ih.csv_
|
87 |
9433
|
aaronmk
|
}
|
88 |
|
|
|
89 |
9665
|
aaronmk
|
herbaria_filter/seal()
|
90 |
|
|
{
|
91 |
|
|
echo_func; set_make_vars
|
92 |
|
|
|
93 |
|
|
use_local_remote
|
94 |
|
|
table=herbaria_filter table_esc= mysql_seal_table
|
95 |
|
|
}
|
96 |
|
|
|
97 |
9155
|
aaronmk
|
func_override table.tsv/make__src_table_run
|
98 |
9348
|
aaronmk
|
table.tsv/make() # usage: [remake=1] [limit=...] .../run table.tsv/make
|
99 |
8303
|
aaronmk
|
{
|
100 |
9357
|
aaronmk
|
echo_func; set_make_vars
|
101 |
|
|
|
102 |
9638
|
aaronmk
|
herbaria_filter/make
|
103 |
9356
|
aaronmk
|
filter="
|
104 |
9412
|
aaronmk
|
deleted IS NULL
|
105 |
9433
|
aaronmk
|
AND institution_code IN (SELECT * FROM herbaria_filter)" \
|
106 |
9356
|
aaronmk
|
table.tsv/make__src_table_run
|
107 |
8303
|
aaronmk
|
}
|
108 |
8705
|
aaronmk
|
|
109 |
|
|
fi
|