Revision 586
Added by Aaron Marcuse-Kubitza almost 13 years ago
test/accepted_output/SALVIAS.organisms.VegBIEN.xml | ||
---|---|---|
39 | 39 |
<surname>Vasquez</surname> |
40 | 40 |
</party> |
41 | 41 |
</collector_id> |
42 |
<sourceaccessionnumber>607771</sourceaccessionnumber>
|
|
42 |
<sourceaccessioncode>607771</sourceaccessioncode>
|
|
43 | 43 |
</specimen> |
44 | 44 |
<taxondetermination> |
45 | 45 |
<role_id> |
... | ... | |
308 | 308 |
<surname>Vasquez</surname> |
309 | 309 |
</party> |
310 | 310 |
</collector_id> |
311 |
<sourceaccessionnumber>607722</sourceaccessionnumber>
|
|
311 |
<sourceaccessioncode>607722</sourceaccessioncode>
|
|
312 | 312 |
</specimen> |
313 | 313 |
<taxondetermination> |
314 | 314 |
<role_id> |
test/accepted_output/SALVIAS_db.organisms.VegBIEN.xml | ||
---|---|---|
293 | 293 |
<reference_id> |
294 | 294 |
<reference/> |
295 | 295 |
</reference_id> |
296 |
<sourceaccessionnumber>1</sourceaccessionnumber>
|
|
296 |
<sourceaccessioncode>1</sourceaccessioncode>
|
|
297 | 297 |
</specimen> |
298 | 298 |
</taxonoccurrence> |
299 | 299 |
<taxonoccurrence id="1"> |
... | ... | |
589 | 589 |
<reference_id> |
590 | 590 |
<reference/> |
591 | 591 |
</reference_id> |
592 |
<sourceaccessionnumber>2</sourceaccessionnumber>
|
|
592 |
<sourceaccessioncode>2</sourceaccessioncode>
|
|
593 | 593 |
</specimen> |
594 | 594 |
</taxonoccurrence> |
595 | 595 |
</VegBIEN> |
test/accepted_output/NYBG.organisms.VegBIEN.xml | ||
---|---|---|
88 | 88 |
</party> |
89 | 89 |
</museum_id> |
90 | 90 |
<collectioncode_dwc>Herbarium</collectioncode_dwc> |
91 |
<sourceaccessionnumber>816590</sourceaccessionnumber>
|
|
91 |
<sourceaccessioncode>816590</sourceaccessioncode>
|
|
92 | 92 |
</specimen> |
93 | 93 |
<taxondetermination> |
94 | 94 |
<role_id> |
... | ... | |
373 | 373 |
</party> |
374 | 374 |
</museum_id> |
375 | 375 |
<collectioncode_dwc>Herbarium</collectioncode_dwc> |
376 |
<sourceaccessionnumber>220541</sourceaccessionnumber>
|
|
376 |
<sourceaccessioncode>220541</sourceaccessioncode>
|
|
377 | 377 |
</specimen> |
378 | 378 |
<taxondetermination> |
379 | 379 |
<role_id> |
inputs/SALVIAS/maps/VegBIEN.organisms.csv | ||
---|---|---|
28 | 28 |
infra_ep_1,"/taxondetermination[role_id/aux_role/rolecode=computer,*_id/plantconcept/plantstatus/plantlevel=Subspecies]:[determinationtype=""Computer (automated)"",originaldetermination=false,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/_name/last", |
29 | 29 |
OrigFamily,"/taxondetermination[role_id/aux_role/rolecode=identifier,*_id/plantconcept/plantstatus/plantlevel=Family]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/*_id/*/plantname",Brad: OMIT |
30 | 30 |
OrigSpecies,"/{taxondetermination[role_id/aux_role/rolecode=collector]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[*_id/reference]/*_id/*/plantname/_alt/2,taxondetermination[role_id/aux_role/rolecode=identifier,*_id/plantconcept/plantstatus/plantlevel=Species]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/*_id/*/plantname}",Brad: OMIT |
31 |
PlotObsID,":[aggregateoccurrence/count/_alt/2=1]/{aggregateoccurrence/individualplant/authorplantcode,specimen:[*_id/reference]/sourceaccessionnumber,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location:[confidentialitystatus=0]/authorlocationcode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later." |
|
31 |
PlotObsID,":[aggregateoccurrence/count/_alt/2=1]/{aggregateoccurrence/individualplant/authorplantcode,specimen:[*_id/reference]/sourceaccessioncode,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location:[confidentialitystatus=0]/authorlocationcode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later." |
schemas/vegbien.for_ERD.my.sql | ||
---|---|---|
151 | 151 |
authoreventcode character varying(30), |
152 | 152 |
`... (truncated) ...` int(11), |
153 | 153 |
accessioncode character varying(255), |
154 |
sourceaccessioncode character varying(100), |
|
154 | 155 |
CONSTRAINT locationevent_pkey PRIMARY KEY (locationevent_id ), |
155 | 156 |
CONSTRAINT locationevent_location_id FOREIGN KEY (location_id) |
156 | 157 |
REFERENCES location (location_id) MATCH SIMPLE |
... | ... | |
158 | 159 |
CONSTRAINT locationevent_project_id FOREIGN KEY (project_id) |
159 | 160 |
REFERENCES project (project_id) MATCH SIMPLE |
160 | 161 |
ON UPDATE CASCADE ON DELETE CASCADE, |
161 |
CONSTRAINT locationevent_keys UNIQUE (location_id , project_id , authoreventcode ) |
|
162 |
CONSTRAINT locationevent_keys_accessioncode UNIQUE (location_id , project_id , sourceaccessioncode ), |
|
163 |
CONSTRAINT locationevent_keys_code UNIQUE (location_id , project_id , authoreventcode ) |
|
162 | 164 |
); |
163 | 165 |
|
164 | 166 |
CREATE TABLE taxonoccurrence -- VegBank's taxonobservation table. |
... | ... | |
195 | 197 |
covercode character varying(10), |
196 | 198 |
count int(11) NOT NULL, |
197 | 199 |
accessioncode character varying(255), |
200 |
sourceaccessioncode character varying(100), |
|
198 | 201 |
CONSTRAINT aggregateoccurrence_pkey PRIMARY KEY (aggregateoccurrence_id ), |
199 | 202 |
CONSTRAINT aggregateoccurrence_taxonbinmethod_id FOREIGN KEY (taxonbinmethod_id) |
200 | 203 |
REFERENCES taxonbinmethod (taxonbinmethod_id) MATCH SIMPLE |
... | ... | |
202 | 205 |
CONSTRAINT aggregateoccurrence_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) |
203 | 206 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE |
204 | 207 |
ON UPDATE CASCADE ON DELETE CASCADE, |
205 |
CONSTRAINT aggregateoccurrence_keys UNIQUE (taxonoccurrence_id , taxonbinmethod_id ) |
|
208 |
CONSTRAINT aggregateoccurrence_keys_accessioncode UNIQUE (taxonoccurrence_id , sourceaccessioncode ), |
|
209 |
CONSTRAINT aggregateoccurrence_keys_method UNIQUE (taxonoccurrence_id , taxonbinmethod_id ) |
|
206 | 210 |
); |
207 | 211 |
|
208 | 212 |
CREATE TABLE individualplant -- VegBank's stemcount table. |
... | ... | |
215 | 219 |
authorplantcode character varying(20), |
216 | 220 |
accessioncode character varying(255), |
217 | 221 |
stemcount int(11), |
222 |
sourceaccessioncode character varying(100), |
|
218 | 223 |
CONSTRAINT individualplant_pkey PRIMARY KEY (individualplant_id ), |
219 | 224 |
CONSTRAINT individualplant_aggregateoccurrence_id FOREIGN KEY (aggregateoccurrence_id) |
220 | 225 |
REFERENCES aggregateoccurrence (aggregateoccurrence_id) MATCH SIMPLE |
221 | 226 |
ON UPDATE CASCADE ON DELETE CASCADE, |
222 |
CONSTRAINT individualplant_keys UNIQUE (aggregateoccurrence_id , authorplantcode ) |
|
227 |
CONSTRAINT individualplant_keys_accessioncode UNIQUE (aggregateoccurrence_id , sourceaccessioncode ), |
|
228 |
CONSTRAINT individualplant_keys_code UNIQUE (aggregateoccurrence_id , authorplantcode ) |
|
223 | 229 |
); |
224 | 230 |
|
225 | 231 |
CREATE TABLE stem -- VegBank's stemlocation table. |
... | ... | |
237 | 243 |
age double precision, |
238 | 244 |
accessioncode character varying(255), |
239 | 245 |
diameteraccuracy double precision, |
246 |
sourceaccessioncode character varying(100), |
|
240 | 247 |
CONSTRAINT stem_pkey PRIMARY KEY (stem_id ), |
241 | 248 |
CONSTRAINT stem_individualplant_id FOREIGN KEY (individualplant_id) |
242 | 249 |
REFERENCES individualplant (individualplant_id) MATCH SIMPLE |
243 | 250 |
ON UPDATE CASCADE ON DELETE CASCADE, |
244 |
CONSTRAINT stem_keys UNIQUE (individualplant_id , authorstemcode ) |
|
251 |
CONSTRAINT stem_keys_accessioncode UNIQUE (individualplant_id , sourceaccessioncode ), |
|
252 |
CONSTRAINT stem_keys_code UNIQUE (individualplant_id , authorstemcode ) |
|
245 | 253 |
); |
246 | 254 |
|
247 | 255 |
CREATE TABLE specimen -- A herbarium specimen. Contains Darwin Core specimen data. |
... | ... | |
253 | 261 |
collectiondate timestamp NULL, |
254 | 262 |
collector_id int(11), |
255 | 263 |
museum_id int(11), |
256 |
sourceaccessionnumber character varying(100),
|
|
264 |
sourceaccessioncode character varying(100),
|
|
257 | 265 |
accessioncode character varying(255), |
258 | 266 |
taxonoccurrence_id int(11) NOT NULL, |
259 | 267 |
collectornumber_dwc character varying(255), |
... | ... | |
270 | 278 |
CONSTRAINT specimen_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) |
271 | 279 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE |
272 | 280 |
ON UPDATE CASCADE ON DELETE CASCADE, |
273 |
CONSTRAINT specimen_keys UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc ) |
|
281 |
CONSTRAINT specimen_keys_accessioncode UNIQUE (reference_id , collectioncode_dwc , sourceaccessioncode ), |
|
282 |
CONSTRAINT specimen_keys_catalognumber UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc ) |
|
274 | 283 |
); |
275 | 284 |
|
276 | 285 |
CREATE TABLE voucher |
schemas/vegbien.for_wiki.sql | ||
---|---|---|
27 | 27 |
authoreventcode character varying(30), |
28 | 28 |
"... (truncated) ..." integer, |
29 | 29 |
accessioncode character varying(255), |
30 |
sourceaccessioncode character varying(100), |
|
30 | 31 |
); |
31 | 32 |
|
32 | 33 |
CREATE TABLE taxonoccurrence -- VegBank's taxonobservation table. |
... | ... | |
56 | 57 |
covercode character varying(10), |
57 | 58 |
count integer NOT NULL, |
58 | 59 |
accessioncode character varying(255), |
60 |
sourceaccessioncode character varying(100), |
|
59 | 61 |
); |
60 | 62 |
|
61 | 63 |
CREATE TABLE individualplant -- VegBank's stemcount table. |
... | ... | |
68 | 70 |
authorplantcode character varying(20), |
69 | 71 |
accessioncode character varying(255), |
70 | 72 |
stemcount integer, |
73 |
sourceaccessioncode character varying(100), |
|
71 | 74 |
); |
72 | 75 |
|
73 | 76 |
CREATE TABLE stem -- VegBank's stemlocation table. |
... | ... | |
85 | 88 |
age double precision, |
86 | 89 |
accessioncode character varying(255), |
87 | 90 |
diameteraccuracy double precision, |
91 |
sourceaccessioncode character varying(100), |
|
88 | 92 |
); |
89 | 93 |
|
90 | 94 |
CREATE TABLE specimen -- A herbarium specimen. Contains Darwin Core specimen data. |
... | ... | |
96 | 100 |
collectiondate timestamp with time zone, |
97 | 101 |
collector_id integer, |
98 | 102 |
museum_id integer, |
99 |
sourceaccessionnumber character varying(100),
|
|
103 |
sourceaccessioncode character varying(100),
|
|
100 | 104 |
accessioncode character varying(255), |
101 | 105 |
taxonoccurrence_id integer NOT NULL, |
102 | 106 |
collectornumber_dwc character varying(255), |
schemas/vegbien.for_ERD.sql | ||
---|---|---|
151 | 151 |
authoreventcode character varying(30), |
152 | 152 |
"... (truncated) ..." integer, |
153 | 153 |
accessioncode character varying(255), |
154 |
sourceaccessioncode character varying(100), |
|
154 | 155 |
CONSTRAINT locationevent_pkey PRIMARY KEY (locationevent_id ), |
155 | 156 |
CONSTRAINT locationevent_location_id FOREIGN KEY (location_id) |
156 | 157 |
REFERENCES location (location_id) MATCH SIMPLE |
... | ... | |
158 | 159 |
CONSTRAINT locationevent_project_id FOREIGN KEY (project_id) |
159 | 160 |
REFERENCES project (project_id) MATCH SIMPLE |
160 | 161 |
ON UPDATE CASCADE ON DELETE CASCADE, |
161 |
CONSTRAINT locationevent_keys UNIQUE (location_id , project_id , authoreventcode ) |
|
162 |
CONSTRAINT locationevent_keys_accessioncode UNIQUE (location_id , project_id , sourceaccessioncode ), |
|
163 |
CONSTRAINT locationevent_keys_code UNIQUE (location_id , project_id , authoreventcode ) |
|
162 | 164 |
); |
163 | 165 |
|
164 | 166 |
CREATE TABLE taxonoccurrence -- VegBank's taxonobservation table. |
... | ... | |
195 | 197 |
covercode character varying(10), |
196 | 198 |
count integer NOT NULL, |
197 | 199 |
accessioncode character varying(255), |
200 |
sourceaccessioncode character varying(100), |
|
198 | 201 |
CONSTRAINT aggregateoccurrence_pkey PRIMARY KEY (aggregateoccurrence_id ), |
199 | 202 |
CONSTRAINT aggregateoccurrence_taxonbinmethod_id FOREIGN KEY (taxonbinmethod_id) |
200 | 203 |
REFERENCES taxonbinmethod (taxonbinmethod_id) MATCH SIMPLE |
... | ... | |
202 | 205 |
CONSTRAINT aggregateoccurrence_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) |
203 | 206 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE |
204 | 207 |
ON UPDATE CASCADE ON DELETE CASCADE, |
205 |
CONSTRAINT aggregateoccurrence_keys UNIQUE (taxonoccurrence_id , taxonbinmethod_id ) |
|
208 |
CONSTRAINT aggregateoccurrence_keys_accessioncode UNIQUE (taxonoccurrence_id , sourceaccessioncode ), |
|
209 |
CONSTRAINT aggregateoccurrence_keys_method UNIQUE (taxonoccurrence_id , taxonbinmethod_id ) |
|
206 | 210 |
); |
207 | 211 |
|
208 | 212 |
CREATE TABLE individualplant -- VegBank's stemcount table. |
... | ... | |
215 | 219 |
authorplantcode character varying(20), |
216 | 220 |
accessioncode character varying(255), |
217 | 221 |
stemcount integer, |
222 |
sourceaccessioncode character varying(100), |
|
218 | 223 |
CONSTRAINT individualplant_pkey PRIMARY KEY (individualplant_id ), |
219 | 224 |
CONSTRAINT individualplant_aggregateoccurrence_id FOREIGN KEY (aggregateoccurrence_id) |
220 | 225 |
REFERENCES aggregateoccurrence (aggregateoccurrence_id) MATCH SIMPLE |
221 | 226 |
ON UPDATE CASCADE ON DELETE CASCADE, |
222 |
CONSTRAINT individualplant_keys UNIQUE (aggregateoccurrence_id , authorplantcode ) |
|
227 |
CONSTRAINT individualplant_keys_accessioncode UNIQUE (aggregateoccurrence_id , sourceaccessioncode ), |
|
228 |
CONSTRAINT individualplant_keys_code UNIQUE (aggregateoccurrence_id , authorplantcode ) |
|
223 | 229 |
); |
224 | 230 |
|
225 | 231 |
CREATE TABLE stem -- VegBank's stemlocation table. |
... | ... | |
237 | 243 |
age double precision, |
238 | 244 |
accessioncode character varying(255), |
239 | 245 |
diameteraccuracy double precision, |
246 |
sourceaccessioncode character varying(100), |
|
240 | 247 |
CONSTRAINT stem_pkey PRIMARY KEY (stem_id ), |
241 | 248 |
CONSTRAINT stem_individualplant_id FOREIGN KEY (individualplant_id) |
242 | 249 |
REFERENCES individualplant (individualplant_id) MATCH SIMPLE |
243 | 250 |
ON UPDATE CASCADE ON DELETE CASCADE, |
244 |
CONSTRAINT stem_keys UNIQUE (individualplant_id , authorstemcode ) |
|
251 |
CONSTRAINT stem_keys_accessioncode UNIQUE (individualplant_id , sourceaccessioncode ), |
|
252 |
CONSTRAINT stem_keys_code UNIQUE (individualplant_id , authorstemcode ) |
|
245 | 253 |
); |
246 | 254 |
|
247 | 255 |
CREATE TABLE specimen -- A herbarium specimen. Contains Darwin Core specimen data. |
... | ... | |
253 | 261 |
collectiondate timestamp with time zone, |
254 | 262 |
collector_id integer, |
255 | 263 |
museum_id integer, |
256 |
sourceaccessionnumber character varying(100),
|
|
264 |
sourceaccessioncode character varying(100),
|
|
257 | 265 |
accessioncode character varying(255), |
258 | 266 |
taxonoccurrence_id integer NOT NULL, |
259 | 267 |
collectornumber_dwc character varying(255), |
... | ... | |
270 | 278 |
CONSTRAINT specimen_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) |
271 | 279 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE |
272 | 280 |
ON UPDATE CASCADE ON DELETE CASCADE, |
273 |
CONSTRAINT specimen_keys UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc ) |
|
281 |
CONSTRAINT specimen_keys_accessioncode UNIQUE (reference_id , collectioncode_dwc , sourceaccessioncode ), |
|
282 |
CONSTRAINT specimen_keys_catalognumber UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc ) |
|
274 | 283 |
); |
275 | 284 |
|
276 | 285 |
CREATE TABLE voucher |
schemas/vegbien.sql | ||
---|---|---|
88 | 88 |
emb_aggregateoccurrence integer, |
89 | 89 |
covercode character varying(10), |
90 | 90 |
count integer NOT NULL, |
91 |
accessioncode character varying(255) |
|
91 |
accessioncode character varying(255), |
|
92 |
sourceaccessioncode character varying(100) |
|
92 | 93 |
); |
93 | 94 |
|
94 | 95 |
|
... | ... | |
1066 | 1067 |
emb_individualplant integer, |
1067 | 1068 |
authorplantcode character varying(20), |
1068 | 1069 |
accessioncode character varying(255), |
1069 |
stemcount integer |
|
1070 |
stemcount integer, |
|
1071 |
sourceaccessioncode character varying(100) |
|
1070 | 1072 |
); |
1071 | 1073 |
|
1072 | 1074 |
|
... | ... | |
1300 | 1302 |
toptaxon4name character varying(255), |
1301 | 1303 |
toptaxon5name character varying(255), |
1302 | 1304 |
numberoftaxa integer, |
1303 |
accessioncode character varying(255) |
|
1305 |
accessioncode character varying(255), |
|
1306 |
sourceaccessioncode character varying(100) |
|
1304 | 1307 |
); |
1305 | 1308 |
|
1306 | 1309 |
|
... | ... | |
2268 | 2271 |
collectiondate timestamp with time zone, |
2269 | 2272 |
collector_id integer, |
2270 | 2273 |
museum_id integer, |
2271 |
sourceaccessionnumber character varying(100),
|
|
2274 |
sourceaccessioncode character varying(100),
|
|
2272 | 2275 |
accessioncode character varying(255), |
2273 | 2276 |
taxonoccurrence_id integer NOT NULL, |
2274 | 2277 |
collectornumber_dwc character varying(255) |
... | ... | |
2320 | 2323 |
heightaccuracy double precision, |
2321 | 2324 |
age double precision, |
2322 | 2325 |
accessioncode character varying(255), |
2323 |
diameteraccuracy double precision |
|
2326 |
diameteraccuracy double precision, |
|
2327 |
sourceaccessioncode character varying(100) |
|
2324 | 2328 |
); |
2325 | 2329 |
|
2326 | 2330 |
|
... | ... | |
3770 | 3774 |
|
3771 | 3775 |
|
3772 | 3776 |
-- |
3773 |
-- Name: aggregateoccurrence_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
3777 |
-- Name: aggregateoccurrence_keys_accessioncode; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
3774 | 3778 |
-- |
3775 | 3779 |
|
3776 | 3780 |
ALTER TABLE ONLY aggregateoccurrence |
3777 |
ADD CONSTRAINT aggregateoccurrence_keys UNIQUE (taxonoccurrence_id, taxonbinmethod_id);
|
|
3781 |
ADD CONSTRAINT aggregateoccurrence_keys_accessioncode UNIQUE (taxonoccurrence_id, sourceaccessioncode);
|
|
3778 | 3782 |
|
3779 | 3783 |
|
3780 | 3784 |
-- |
3785 |
-- Name: aggregateoccurrence_keys_method; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
3786 |
-- |
|
3787 |
|
|
3788 |
ALTER TABLE ONLY aggregateoccurrence |
|
3789 |
ADD CONSTRAINT aggregateoccurrence_keys_method UNIQUE (taxonoccurrence_id, taxonbinmethod_id); |
|
3790 |
|
|
3791 |
|
|
3792 |
-- |
|
3781 | 3793 |
-- Name: aggregateoccurrence_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
3782 | 3794 |
-- |
3783 | 3795 |
|
... | ... | |
4034 | 4046 |
|
4035 | 4047 |
|
4036 | 4048 |
-- |
4037 |
-- Name: individualplant_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
4049 |
-- Name: individualplant_keys_accessioncode; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
4038 | 4050 |
-- |
4039 | 4051 |
|
4040 | 4052 |
ALTER TABLE ONLY individualplant |
4041 |
ADD CONSTRAINT individualplant_keys UNIQUE (aggregateoccurrence_id, authorplantcode);
|
|
4053 |
ADD CONSTRAINT individualplant_keys_accessioncode UNIQUE (aggregateoccurrence_id, sourceaccessioncode);
|
|
4042 | 4054 |
|
4043 | 4055 |
|
4044 | 4056 |
-- |
4057 |
-- Name: individualplant_keys_code; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
4058 |
-- |
|
4059 |
|
|
4060 |
ALTER TABLE ONLY individualplant |
|
4061 |
ADD CONSTRAINT individualplant_keys_code UNIQUE (aggregateoccurrence_id, authorplantcode); |
|
4062 |
|
|
4063 |
|
|
4064 |
-- |
|
4045 | 4065 |
-- Name: individualplant_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
4046 | 4066 |
-- |
4047 | 4067 |
|
... | ... | |
4090 | 4110 |
|
4091 | 4111 |
|
4092 | 4112 |
-- |
4093 |
-- Name: locationevent_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
4113 |
-- Name: locationevent_keys_accessioncode; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
4094 | 4114 |
-- |
4095 | 4115 |
|
4096 | 4116 |
ALTER TABLE ONLY locationevent |
4097 |
ADD CONSTRAINT locationevent_keys UNIQUE (location_id, project_id, authoreventcode);
|
|
4117 |
ADD CONSTRAINT locationevent_keys_accessioncode UNIQUE (location_id, project_id, sourceaccessioncode);
|
|
4098 | 4118 |
|
4099 | 4119 |
|
4100 | 4120 |
-- |
4121 |
-- Name: locationevent_keys_code; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
4122 |
-- |
|
4123 |
|
|
4124 |
ALTER TABLE ONLY locationevent |
|
4125 |
ADD CONSTRAINT locationevent_keys_code UNIQUE (location_id, project_id, authoreventcode); |
|
4126 |
|
|
4127 |
|
|
4128 |
-- |
|
4101 | 4129 |
-- Name: locationevent_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
4102 | 4130 |
-- |
4103 | 4131 |
|
... | ... | |
4354 | 4382 |
|
4355 | 4383 |
|
4356 | 4384 |
-- |
4357 |
-- Name: specimen_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
4385 |
-- Name: specimen_keys_accessioncode; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
4358 | 4386 |
-- |
4359 | 4387 |
|
4360 | 4388 |
ALTER TABLE ONLY specimen |
4361 |
ADD CONSTRAINT specimen_keys UNIQUE (reference_id, collectioncode_dwc, catalognumber_dwc);
|
|
4389 |
ADD CONSTRAINT specimen_keys_accessioncode UNIQUE (reference_id, collectioncode_dwc, sourceaccessioncode);
|
|
4362 | 4390 |
|
4363 | 4391 |
|
4364 | 4392 |
-- |
4393 |
-- Name: specimen_keys_catalognumber; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
4394 |
-- |
|
4395 |
|
|
4396 |
ALTER TABLE ONLY specimen |
|
4397 |
ADD CONSTRAINT specimen_keys_catalognumber UNIQUE (reference_id, collectioncode_dwc, catalognumber_dwc); |
|
4398 |
|
|
4399 |
|
|
4400 |
-- |
|
4365 | 4401 |
-- Name: specimen_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
4366 | 4402 |
-- |
4367 | 4403 |
|
... | ... | |
4370 | 4406 |
|
4371 | 4407 |
|
4372 | 4408 |
-- |
4373 |
-- Name: stem_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
4409 |
-- Name: stem_keys_accessioncode; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
4374 | 4410 |
-- |
4375 | 4411 |
|
4376 | 4412 |
ALTER TABLE ONLY stem |
4377 |
ADD CONSTRAINT stem_keys UNIQUE (individualplant_id, authorstemcode);
|
|
4413 |
ADD CONSTRAINT stem_keys_accessioncode UNIQUE (individualplant_id, sourceaccessioncode);
|
|
4378 | 4414 |
|
4379 | 4415 |
|
4380 | 4416 |
-- |
4417 |
-- Name: stem_keys_code; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
4418 |
-- |
|
4419 |
|
|
4420 |
ALTER TABLE ONLY stem |
|
4421 |
ADD CONSTRAINT stem_keys_code UNIQUE (individualplant_id, authorstemcode); |
|
4422 |
|
|
4423 |
|
|
4424 |
-- |
|
4381 | 4425 |
-- Name: stem_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
4382 | 4426 |
-- |
4383 | 4427 |
|
mappings/SALVIAS_db-VegBIEN.organisms.csv | ||
---|---|---|
28 | 28 |
infra_ep_1,"/taxondetermination[role_id/aux_role/rolecode=computer,*_id/plantconcept/plantstatus/plantlevel=Subspecies]:[determinationtype=""Computer (automated)"",originaldetermination=false,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/_name/last", |
29 | 29 |
OrigFamily,"/taxondetermination[role_id/aux_role/rolecode=identifier,*_id/plantconcept/plantstatus/plantlevel=Family]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/*_id/*/plantname",Brad: OMIT |
30 | 30 |
OrigSpecies,"/{taxondetermination[role_id/aux_role/rolecode=collector]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[*_id/reference]/*_id/*/plantname/_alt/2,taxondetermination[role_id/aux_role/rolecode=identifier,*_id/plantconcept/plantstatus/plantlevel=Species]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/*_id/*/plantname}",Brad: OMIT |
31 |
PlotObsID,":[aggregateoccurrence/count/_alt/2=1]/{aggregateoccurrence/individualplant/authorplantcode,specimen:[*_id/reference]/sourceaccessionnumber,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location:[confidentialitystatus=0]/authorlocationcode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later." |
|
31 |
PlotObsID,":[aggregateoccurrence/count/_alt/2=1]/{aggregateoccurrence/individualplant/authorplantcode,specimen:[*_id/reference]/sourceaccessioncode,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location:[confidentialitystatus=0]/authorlocationcode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later." |
mappings/VegX-VegBIEN.organisms.csv | ||
---|---|---|
122 | 122 |
"/*ID->/*s/individualOrganism/*sID->/*s/taxonNameUsageConcept->/*s/taxonDetermination[*ID,partyWithRole/role=identifier]/*s/taxonRelationshipAssertion(/assertion/*ID->/*s/taxonConcept[Rank/@code=phyl_div])/Name","/taxondetermination[role_id/aux_role/rolecode=identifier,*_id/plantconcept/plantstatus/plantlevel=Subkingdom]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/*_id/*/plantname" |
123 | 123 |
"/*ID->/*s/individualOrganism/*sID->/*s/taxonNameUsageConcept->/*s/taxonDetermination[*ID,partyWithRole/role=identifier]/*s/taxonRelationshipAssertion(/assertion/*ID->/*s/taxonConcept[Rank/@code=ssp])/Name","/taxondetermination[role_id/aux_role/rolecode=identifier,*_id/plantconcept/plantstatus/plantlevel=Subspecies]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/*_id/*/plantname" |
124 | 124 |
"/*ID->/*s/individualOrganism/*sID->/*s/taxonNameUsageConcept->/*s/taxonDetermination[*ID,partyWithRole/role=identifier]/*s/taxonRelationshipAssertion(/assertion/*ID->/*s/taxonConcept[Rank/@code=sp])/Name","/{taxondetermination[role_id/aux_role/rolecode=collector]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[*_id/reference]/*_id/*/plantname/_alt/2,taxondetermination[role_id/aux_role/rolecode=identifier,*_id/plantconcept/plantstatus/plantlevel=Species]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/*_id/*/plantname}" |
125 |
/*ID->/*s/individualOrganism/*sID->/*s/taxonNameUsageConcept/voucher,":[aggregateoccurrence/count/_alt/2=1]/{aggregateoccurrence/individualplant/authorplantcode,specimen:[*_id/reference]/sourceaccessionnumber,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location:[confidentialitystatus=0]/authorlocationcode/_alt/3}" |
|
125 |
/*ID->/*s/individualOrganism/*sID->/*s/taxonNameUsageConcept/voucher,":[aggregateoccurrence/count/_alt/2=1]/{aggregateoccurrence/individualplant/authorplantcode,specimen:[*_id/reference]/sourceaccessioncode,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location:[confidentialitystatus=0]/authorlocationcode/_alt/3}" |
mappings/NYBG-VegBIEN.organisms.csv | ||
---|---|---|
39 | 39 |
Phylum,"/taxondetermination[role_id/aux_role/rolecode=identifier,*_id/plantconcept/plantstatus/plantlevel=Subkingdom]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/*_id/*/plantname", |
40 | 40 |
Subspecies,"/taxondetermination[role_id/aux_role/rolecode=identifier,*_id/plantconcept/plantstatus/plantlevel=Subspecies]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/*_id/*/plantname", |
41 | 41 |
Species,"/{taxondetermination[role_id/aux_role/rolecode=collector]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[*_id/reference]/*_id/*/plantname/_alt/2,taxondetermination[role_id/aux_role/rolecode=identifier,*_id/plantconcept/plantstatus/plantlevel=Species]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/*_id/*/plantname}", |
42 |
UniqueNYInternalRecordNumber,":[aggregateoccurrence/count/_alt/2=1]/{aggregateoccurrence/individualplant/authorplantcode,specimen:[*_id/reference]/sourceaccessionnumber,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location:[confidentialitystatus=0]/authorlocationcode/_alt/3}","Brad: OMIT; Aaron: We need this field because it is the only ID column with an entry in every row; Brad: OK, use this as primary key" |
|
42 |
UniqueNYInternalRecordNumber,":[aggregateoccurrence/count/_alt/2=1]/{aggregateoccurrence/individualplant/authorplantcode,specimen:[*_id/reference]/sourceaccessioncode,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location:[confidentialitystatus=0]/authorlocationcode/_alt/3}","Brad: OMIT; Aaron: We need this field because it is the only ID column with an entry in every row; Brad: OK, use this as primary key" |
mappings/for_review/SALVIAS_db-VegBIEN.organisms.csv | ||
---|---|---|
28 | 28 |
infra_ep_1,"/taxondetermination[rolecode=computer,plantlevel=Subspecies]/*_id/plantconcept/_name/last", |
29 | 29 |
OrigFamily,"/taxondetermination[rolecode=identifier,plantlevel=Family]/*_id/plantconcept/*_id/*/plantname",Brad: OMIT |
30 | 30 |
OrigSpecies,"/{taxondetermination[rolecode=collector]/*_id/plantconcept/*_id/*/plantname/_alt/2,taxondetermination[rolecode=identifier,plantlevel=Species]/*_id/plantconcept/*_id/*/plantname}",Brad: OMIT |
31 |
PlotObsID,"/{aggregateoccurrence/individualplant/authorplantcode,specimen/sourceaccessionnumber,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location/authorlocationcode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later." |
|
31 |
PlotObsID,"/{aggregateoccurrence/individualplant/authorplantcode,specimen/sourceaccessioncode,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location/authorlocationcode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later." |
mappings/for_review/VegX-VegBIEN.organisms.csv | ||
---|---|---|
122 | 122 |
//*s/taxonConcept[@code=phyl_div]/Name,"/taxondetermination[rolecode=identifier,plantlevel=Subkingdom]/*_id/plantconcept/*_id/*/plantname" |
123 | 123 |
//*s/taxonConcept[@code=ssp]/Name,"/taxondetermination[rolecode=identifier,plantlevel=Subspecies]/*_id/plantconcept/*_id/*/plantname" |
124 | 124 |
//*s/taxonConcept[@code=sp]/Name,"/{taxondetermination[rolecode=collector]/*_id/plantconcept/*_id/*/plantname/_alt/2,taxondetermination[rolecode=identifier,plantlevel=Species]/*_id/plantconcept/*_id/*/plantname}" |
125 |
//taxonNameUsageConcept/voucher,"/{aggregateoccurrence/individualplant/authorplantcode,specimen/sourceaccessionnumber,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location/authorlocationcode/_alt/3}" |
|
125 |
//taxonNameUsageConcept/voucher,"/{aggregateoccurrence/individualplant/authorplantcode,specimen/sourceaccessioncode,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location/authorlocationcode/_alt/3}" |
mappings/for_review/NYBG-VegBIEN.organisms.csv | ||
---|---|---|
39 | 39 |
Phylum,"/taxondetermination[rolecode=identifier,plantlevel=Subkingdom]/*_id/plantconcept/*_id/*/plantname", |
40 | 40 |
Subspecies,"/taxondetermination[rolecode=identifier,plantlevel=Subspecies]/*_id/plantconcept/*_id/*/plantname", |
41 | 41 |
Species,"/{taxondetermination[rolecode=collector]/*_id/plantconcept/*_id/*/plantname/_alt/2,taxondetermination[rolecode=identifier,plantlevel=Species]/*_id/plantconcept/*_id/*/plantname}", |
42 |
UniqueNYInternalRecordNumber,"/{aggregateoccurrence/individualplant/authorplantcode,specimen/sourceaccessionnumber,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location/authorlocationcode/_alt/3}","Brad: OMIT; Aaron: We need this field because it is the only ID column with an entry in every row; Brad: OK, use this as primary key" |
|
42 |
UniqueNYInternalRecordNumber,"/{aggregateoccurrence/individualplant/authorplantcode,specimen/sourceaccessioncode,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location/authorlocationcode/_alt/3}","Brad: OMIT; Aaron: We need this field because it is the only ID column with an entry in every row; Brad: OK, use this as primary key" |
mappings/for_review/SALVIAS-VegBIEN.organisms.csv | ||
---|---|---|
37 | 37 |
infra_ep_1,"/taxondetermination[rolecode=computer,plantlevel=Subspecies]/*_id/plantconcept/_name/last", |
38 | 38 |
orig_family,"/taxondetermination[rolecode=identifier,plantlevel=Family]/*_id/plantconcept/*_id/*/plantname",Brad: OMIT |
39 | 39 |
orig_species,"/{taxondetermination[rolecode=collector]/*_id/plantconcept/*_id/*/plantname/_alt/2,taxondetermination[rolecode=identifier,plantlevel=Species]/*_id/plantconcept/*_id/*/plantname}",Brad: OMIT |
40 |
OBSERVATION_ID,"/{aggregateoccurrence/individualplant/authorplantcode,specimen/sourceaccessionnumber,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location/authorlocationcode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later." |
|
40 |
OBSERVATION_ID,"/{aggregateoccurrence/individualplant/authorplantcode,specimen/sourceaccessioncode,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location/authorlocationcode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later." |
mappings/SALVIAS-VegBIEN.organisms.csv | ||
---|---|---|
37 | 37 |
infra_ep_1,"/taxondetermination[role_id/aux_role/rolecode=computer,*_id/plantconcept/plantstatus/plantlevel=Subspecies]:[determinationtype=""Computer (automated)"",originaldetermination=false,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/_name/last", |
38 | 38 |
orig_family,"/taxondetermination[role_id/aux_role/rolecode=identifier,*_id/plantconcept/plantstatus/plantlevel=Family]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/*_id/*/plantname",Brad: OMIT |
39 | 39 |
orig_species,"/{taxondetermination[role_id/aux_role/rolecode=collector]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[*_id/reference]/*_id/*/plantname/_alt/2,taxondetermination[role_id/aux_role/rolecode=identifier,*_id/plantconcept/plantstatus/plantlevel=Species]:[determinationtype=Author,originaldetermination=true,currentdetermination=true,determinationdate=epoch,*_id/party]/*_id/plantconcept:[plantstatus:[plantconceptstatus=accepted,startdate=epoch,*_id/party],*_id/reference]/*_id/*/plantname}",Brad: OMIT |
40 |
OBSERVATION_ID,":[aggregateoccurrence/count/_alt/2=1]/{aggregateoccurrence/individualplant/authorplantcode,specimen:[*_id/reference]/sourceaccessionnumber,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location:[confidentialitystatus=0]/authorlocationcode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later." |
|
40 |
OBSERVATION_ID,":[aggregateoccurrence/count/_alt/2=1]/{aggregateoccurrence/individualplant/authorplantcode,specimen:[*_id/reference]/sourceaccessioncode,*_id/locationevent/authoreventcode/_alt/2,*_id/locationevent/*_id/location:[confidentialitystatus=0]/authorlocationcode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later." |
Also available in: Unified diff
VegBIEN: Renamed sourceaccessionnumber to sourceaccessioncode to show that they are the data source's analog of accessioncode. Added sourceaccessioncode to all applicable tables because this is the database pkey, which is distinct from any author*code applied by the collector.