Revision 5855
Added by Aaron Marcuse-Kubitza almost 12 years ago
| inputs/GBIF/Specimen/VegBIEN.csv | ||
|---|---|---|
| 54 | 54 |
Collector,/location/locationevent/taxonoccurrence/verbatimcollectorname, |
| 55 | 55 |
CatalogNO,"/location/locationevent/taxonoccurrence/voucher/*_id/specimenreplicate/catalognumber_dwc/_if[@name=""if indirect voucher""]/then", |
| 56 | 56 |
Locality,/location/locationnarrative/_merge/1, |
| 57 |
Country,/location/locationplace/*_id/placepath/country,
|
|
| 58 |
County,/location/locationplace/*_id/placepath/county,
|
|
| 59 |
StateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 57 |
Country,/location/locationplace/*_id/place/country, |
|
| 58 |
County,/location/locationplace/*_id/place/county, |
|
| 59 |
StateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 60 | 60 |
0,,** No join mapping for *0 ** |
| 61 | 61 |
1,,** No join mapping for *1 ** |
| 62 | 62 |
25,,** No join mapping for *25 ** |
| inputs/NCU-NCSC/Specimen/VegBIEN.csv | ||
|---|---|---|
| 18 | 18 |
Full Taxon Name,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_join_words/2/_alt/2/_join_words/1/_alt/1, |
| 19 | 19 |
Full Taxon Name,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/binomial, |
| 20 | 20 |
Barcode,"/location/locationevent/taxonoccurrence/voucher/*_id/specimenreplicate/catalognumber_dwc/_if[@name=""if indirect voucher""]/then", |
| 21 |
Country,/location/locationplace/*_id/placepath/country,
|
|
| 22 |
County,/location/locationplace/*_id/placepath/county,
|
|
| 23 |
State,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 21 |
Country,/location/locationplace/*_id/place/country, |
|
| 22 |
County,/location/locationplace/*_id/place/county, |
|
| 23 |
State,/location/locationplace/*_id/place/stateprovince, |
|
| 24 | 24 |
Elevation,/location/verbatimelevation, |
| 25 | 25 |
Alt E/W,,** No join mapping for *Alt E/W ** |
| 26 | 26 |
Alt N/S,,** No join mapping for *Alt N/S ** |
| inputs/SpeciesLink/Specimen/VegBIEN.csv | ||
|---|---|---|
| 208 | 208 |
conceptual_darwin_2003_1_0_Locality,/location/locationnarrative/_merge/1, |
| 209 | 209 |
dwc_dwcore_Locality,/location/locationnarrative/_merge/1, |
| 210 | 210 |
dwc_terms_locality,/location/locationnarrative/_merge/1, |
| 211 |
conceptual_darwin_2003_1_0_ContinentOcean,/location/locationplace/*_id/placepath/continent,
|
|
| 212 |
dwc_dwcore_Continent,/location/locationplace/*_id/placepath/continent,
|
|
| 213 |
dwc_terms_continent,/location/locationplace/*_id/placepath/continent,
|
|
| 214 |
conceptual_darwin_2003_1_0_Country,/location/locationplace/*_id/placepath/country,
|
|
| 215 |
dwc_dwcore_Country,/location/locationplace/*_id/placepath/country,
|
|
| 216 |
dwc_terms_country,/location/locationplace/*_id/placepath/country,
|
|
| 217 |
conceptual_darwin_2003_1_0_County,/location/locationplace/*_id/placepath/county,
|
|
| 218 |
dwc_dwcore_County,/location/locationplace/*_id/placepath/county,
|
|
| 219 |
dwc_terms_county,/location/locationplace/*_id/placepath/county,
|
|
| 220 |
conceptual_darwin_2003_1_0_StateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 221 |
dwc_dwcore_StateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 222 |
dwc_terms_stateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 211 |
conceptual_darwin_2003_1_0_ContinentOcean,/location/locationplace/*_id/place/continent, |
|
| 212 |
dwc_dwcore_Continent,/location/locationplace/*_id/place/continent, |
|
| 213 |
dwc_terms_continent,/location/locationplace/*_id/place/continent, |
|
| 214 |
conceptual_darwin_2003_1_0_Country,/location/locationplace/*_id/place/country, |
|
| 215 |
dwc_dwcore_Country,/location/locationplace/*_id/place/country, |
|
| 216 |
dwc_terms_country,/location/locationplace/*_id/place/country, |
|
| 217 |
conceptual_darwin_2003_1_0_County,/location/locationplace/*_id/place/county, |
|
| 218 |
dwc_dwcore_County,/location/locationplace/*_id/place/county, |
|
| 219 |
dwc_terms_county,/location/locationplace/*_id/place/county, |
|
| 220 |
conceptual_darwin_2003_1_0_StateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 221 |
dwc_dwcore_StateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 222 |
dwc_terms_stateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 223 | 223 |
conceptual_darwin_2003_1_0_BasisOfRecord,,** No join mapping for basisOfRecord ** |
| 224 | 224 |
conceptual_darwin_2003_1_0_DateLastModified,,** No join mapping for dcterms:modified ** |
| 225 | 225 |
conceptual_darwin_2003_1_0_JulianDay,,** No join mapping for day/_alt/1 ** |
| inputs/bien_web/observation/VegBIEN.csv | ||
|---|---|---|
| 39 | 39 |
species,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/specific_epithet, |
| 40 | 40 |
taxon,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/taxonomicname, |
| 41 | 41 |
collector,/location/locationevent/taxonoccurrence/verbatimcollectorname, |
| 42 |
country,/location/locationplace/*_id/placepath/country,
|
|
| 43 |
countyParish,/location/locationplace/*_id/placepath/county,
|
|
| 44 |
stateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 42 |
country,/location/locationplace/*_id/place/country, |
|
| 43 |
countyParish,/location/locationplace/*_id/place/county, |
|
| 44 |
stateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 45 | 45 |
plotCode,"/location/sourceaccessioncode/_join/1/_if[@name=""if subplot and unique only within parent""]/then/_first/2", |
| 46 | 46 |
isCultivatedReason,,** No join mapping for cultivatedBasis ** |
| 47 | 47 |
taxonMorphospecies,,** No join mapping for scientificNameWithMorphospecies ** |
| inputs/MO/Specimen/VegBIEN.csv | ||
|---|---|---|
| 48 | 48 |
Collector,/location/locationevent/taxonoccurrence/verbatimcollectorname, |
| 49 | 49 |
CatalogNumber,"/location/locationevent/taxonoccurrence/voucher/*_id/specimenreplicate/catalognumber_dwc/_if[@name=""if indirect voucher""]/then", |
| 50 | 50 |
Locality,/location/locationnarrative/_merge/1, |
| 51 |
ContinentOcean,/location/locationplace/*_id/placepath/continent,
|
|
| 52 |
Country,/location/locationplace/*_id/placepath/country,
|
|
| 53 |
County,/location/locationplace/*_id/placepath/county,
|
|
| 54 |
StateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 51 |
ContinentOcean,/location/locationplace/*_id/place/continent, |
|
| 52 |
Country,/location/locationplace/*_id/place/country, |
|
| 53 |
County,/location/locationplace/*_id/place/county, |
|
| 54 |
StateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 55 | 55 |
BasisOfRecord,,** No join mapping for basisOfRecord ** |
| 56 | 56 |
DateLastModified,,** No join mapping for dcterms:modified ** |
| 57 | 57 |
TypeStatus,,** No join mapping for typeStatus ** |
| inputs/VegBank/plot_/test.xml.ref | ||
|---|---|---|
| 29 | 29 |
</locationcoords> |
| 30 | 30 |
<locationnarrative>$locationnarrative</locationnarrative> |
| 31 | 31 |
<locationplace> |
| 32 |
<placepath_id>
|
|
| 33 |
<placepath>
|
|
| 32 |
<place_id> |
|
| 33 |
<place> |
|
| 34 | 34 |
<continent>$continent</continent> |
| 35 | 35 |
<country> |
| 36 | 36 |
<_alt> |
| ... | ... | |
| 45 | 45 |
<1>$stateprovince</1> |
| 46 | 46 |
</_alt> |
| 47 | 47 |
</stateprovince> |
| 48 |
</placepath>
|
|
| 49 |
</placepath_id>
|
|
| 48 |
</place> |
|
| 49 |
</place_id> |
|
| 50 | 50 |
</locationplace> |
| 51 | 51 |
<parent_id><location><sourceaccessioncode>$parent_id</sourceaccessioncode></location></parent_id> |
| 52 | 52 |
<slopeaspect_deg> |
| schemas/vegbien.my.sql | ||
|---|---|---|
| 229 | 229 |
|
| 230 | 230 |
|
| 231 | 231 |
-- |
| 232 |
-- Name: placename_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 232 |
-- Name: place_matched_place_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 233 | 233 |
-- |
| 234 | 234 |
|
| 235 | 235 |
|
| 236 | 236 |
|
| 237 | 237 |
|
| 238 | 238 |
-- |
| 239 |
-- Name: placepath_matched_placepath_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 239 |
-- Name: placename_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 240 | 240 |
-- |
| 241 | 241 |
|
| 242 | 242 |
|
| ... | ... | |
| 794 | 794 |
CREATE TABLE locationplace ( |
| 795 | 795 |
locationplace_id int(11) NOT NULL, |
| 796 | 796 |
location_id int(11) NOT NULL, |
| 797 |
placepath_id int(11) NOT NULL,
|
|
| 797 |
place_id int(11) NOT NULL, |
|
| 798 | 798 |
identifier_id int(11) |
| 799 | 799 |
); |
| 800 | 800 |
|
| ... | ... | |
| 1003 | 1003 |
|
| 1004 | 1004 |
|
| 1005 | 1005 |
-- |
| 1006 |
-- Name: placepath; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 1006 |
-- Name: place; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1007 | 1007 |
-- |
| 1008 | 1008 |
|
| 1009 |
CREATE TABLE placepath (
|
|
| 1010 |
placepath_id int(11) NOT NULL,
|
|
| 1009 |
CREATE TABLE place ( |
|
| 1010 |
place_id int(11) NOT NULL, |
|
| 1011 | 1011 |
creator_id int(11) NOT NULL, |
| 1012 | 1012 |
placecode text, |
| 1013 |
matched_placepath_id int(11),
|
|
| 1013 |
matched_place_id int(11), |
|
| 1014 | 1014 |
placename_id int(11), |
| 1015 | 1015 |
continent text, |
| 1016 | 1016 |
country text, |
| ... | ... | |
| 1023 | 1023 |
|
| 1024 | 1024 |
|
| 1025 | 1025 |
-- |
| 1026 |
-- Name: TABLE placepath; Type: COMMENT; Schema: public; Owner: -
|
|
| 1026 |
-- Name: TABLE place; Type: COMMENT; Schema: public; Owner: - |
|
| 1027 | 1027 |
-- |
| 1028 | 1028 |
|
| 1029 | 1029 |
|
| 1030 | 1030 |
|
| 1031 | 1031 |
|
| 1032 | 1032 |
-- |
| 1033 |
-- Name: COLUMN placepath.matched_placepath_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 1033 |
-- Name: COLUMN place.matched_place_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 1034 | 1034 |
-- |
| 1035 | 1035 |
|
| 1036 | 1036 |
|
| 1037 | 1037 |
|
| 1038 | 1038 |
|
| 1039 | 1039 |
-- |
| 1040 |
-- Name: COLUMN placepath.otherranks; Type: COMMENT; Schema: public; Owner: -
|
|
| 1040 |
-- Name: COLUMN place.otherranks; Type: COMMENT; Schema: public; Owner: - |
|
| 1041 | 1041 |
-- |
| 1042 | 1042 |
|
| 1043 | 1043 |
|
| ... | ... | |
| 2011 | 2011 |
|
| 2012 | 2012 |
|
| 2013 | 2013 |
-- |
| 2014 |
-- Name: place_place_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 2015 |
-- |
|
| 2016 |
|
|
| 2017 |
|
|
| 2018 |
|
|
| 2019 |
|
|
| 2020 |
-- |
|
| 2021 |
-- Name: place_place_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 2022 |
-- |
|
| 2023 |
|
|
| 2024 |
|
|
| 2025 |
|
|
| 2026 |
|
|
| 2027 |
-- |
|
| 2014 | 2028 |
-- Name: placecorrelation; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2015 | 2029 |
-- |
| 2016 | 2030 |
|
| ... | ... | |
| 2090 | 2104 |
|
| 2091 | 2105 |
|
| 2092 | 2106 |
-- |
| 2093 |
-- Name: placepath_placepath_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 2094 |
-- |
|
| 2095 |
|
|
| 2096 |
|
|
| 2097 |
|
|
| 2098 |
|
|
| 2099 |
-- |
|
| 2100 |
-- Name: placepath_placepath_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 2101 |
-- |
|
| 2102 |
|
|
| 2103 |
|
|
| 2104 |
|
|
| 2105 |
|
|
| 2106 |
-- |
|
| 2107 | 2107 |
-- Name: plant; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2108 | 2108 |
-- |
| 2109 | 2109 |
|
| ... | ... | |
| 3102 | 3102 |
|
| 3103 | 3103 |
|
| 3104 | 3104 |
-- |
| 3105 |
-- Name: placecorrelation_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3105 |
-- Name: place_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3106 | 3106 |
-- |
| 3107 | 3107 |
|
| 3108 | 3108 |
|
| 3109 | 3109 |
|
| 3110 | 3110 |
|
| 3111 | 3111 |
-- |
| 3112 |
-- Name: placename_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3112 |
-- Name: placecorrelation_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3113 | 3113 |
-- |
| 3114 | 3114 |
|
| 3115 | 3115 |
|
| 3116 | 3116 |
|
| 3117 | 3117 |
|
| 3118 | 3118 |
-- |
| 3119 |
-- Name: placepath_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3119 |
-- Name: placename_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3120 | 3120 |
-- |
| 3121 | 3121 |
|
| 3122 | 3122 |
|
| ... | ... | |
| 3591 | 3591 |
|
| 3592 | 3592 |
|
| 3593 | 3593 |
-- |
| 3594 |
-- Name: place_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3595 |
-- |
|
| 3596 |
|
|
| 3597 |
ALTER TABLE place |
|
| 3598 |
ADD CONSTRAINT place_pkey PRIMARY KEY (place_id); |
|
| 3599 |
|
|
| 3600 |
|
|
| 3601 |
-- |
|
| 3594 | 3602 |
-- Name: placecorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3595 | 3603 |
-- |
| 3596 | 3604 |
|
| ... | ... | |
| 3615 | 3623 |
|
| 3616 | 3624 |
|
| 3617 | 3625 |
-- |
| 3618 |
-- Name: placepath_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3619 |
-- |
|
| 3620 |
|
|
| 3621 |
ALTER TABLE placepath |
|
| 3622 |
ADD CONSTRAINT placepath_pkey PRIMARY KEY (placepath_id); |
|
| 3623 |
|
|
| 3624 |
|
|
| 3625 |
-- |
|
| 3626 | 3626 |
-- Name: plant_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3627 | 3627 |
-- |
| 3628 | 3628 |
|
| ... | ... | |
| 4128 | 4128 |
|
| 4129 | 4129 |
|
| 4130 | 4130 |
-- |
| 4131 |
-- Name: placename_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4131 |
-- Name: place_unique_within_creator_by_code; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4132 | 4132 |
-- |
| 4133 | 4133 |
|
| 4134 |
CREATE UNIQUE INDEX placename_accessioncode_index ON placename (accessioncode); |
|
| 4135 | 4134 |
|
| 4136 | 4135 |
|
| 4136 |
|
|
| 4137 | 4137 |
-- |
| 4138 |
-- Name: placename_unique; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4138 |
-- Name: place_unique_within_creator_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4139 | 4139 |
-- |
| 4140 | 4140 |
|
| 4141 | 4141 |
|
| 4142 | 4142 |
|
| 4143 | 4143 |
|
| 4144 | 4144 |
-- |
| 4145 |
-- Name: placepath_unique_within_creator_by_code; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4145 |
-- Name: placename_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4146 | 4146 |
-- |
| 4147 | 4147 |
|
| 4148 |
CREATE UNIQUE INDEX placename_accessioncode_index ON placename (accessioncode); |
|
| 4148 | 4149 |
|
| 4149 | 4150 |
|
| 4150 |
|
|
| 4151 | 4151 |
-- |
| 4152 |
-- Name: placepath_unique_within_creator_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4152 |
-- Name: placename_unique; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4153 | 4153 |
-- |
| 4154 | 4154 |
|
| 4155 | 4155 |
|
| ... | ... | |
| 4380 | 4380 |
|
| 4381 | 4381 |
|
| 4382 | 4382 |
-- |
| 4383 |
-- Name: placename_update_ancestors; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4383 |
-- Name: place_matched_place_id_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4384 | 4384 |
-- |
| 4385 | 4385 |
|
| 4386 | 4386 |
|
| 4387 | 4387 |
|
| 4388 | 4388 |
|
| 4389 | 4389 |
-- |
| 4390 |
-- Name: placepath_matched_placepath_id_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4390 |
-- Name: placename_update_ancestors; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4391 | 4391 |
-- |
| 4392 | 4392 |
|
| 4393 | 4393 |
|
| ... | ... | |
| 4846 | 4846 |
|
| 4847 | 4847 |
|
| 4848 | 4848 |
-- |
| 4849 |
-- Name: locationplace_placepath_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4849 |
-- Name: locationplace_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4850 | 4850 |
-- |
| 4851 | 4851 |
|
| 4852 | 4852 |
ALTER TABLE locationplace |
| 4853 |
ADD CONSTRAINT locationplace_placepath_id_fkey FOREIGN KEY (placepath_id) REFERENCES placepath(placepath_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4853 |
ADD CONSTRAINT locationplace_place_id_fkey FOREIGN KEY (place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4854 | 4854 |
|
| 4855 | 4855 |
|
| 4856 | 4856 |
-- |
| ... | ... | |
| 4947 | 4947 |
|
| 4948 | 4948 |
|
| 4949 | 4949 |
-- |
| 4950 |
-- Name: place_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4951 |
-- |
|
| 4952 |
|
|
| 4953 |
|
|
| 4954 |
|
|
| 4955 |
|
|
| 4956 |
-- |
|
| 4957 |
-- Name: place_matched_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4958 |
-- |
|
| 4959 |
|
|
| 4960 |
ALTER TABLE place |
|
| 4961 |
ADD CONSTRAINT place_matched_place_id_fkey FOREIGN KEY (matched_place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 4962 |
|
|
| 4963 |
|
|
| 4964 |
-- |
|
| 4965 |
-- Name: place_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4966 |
-- |
|
| 4967 |
|
|
| 4968 |
ALTER TABLE place |
|
| 4969 |
ADD CONSTRAINT place_place_id_fkey FOREIGN KEY (placename_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 4970 |
|
|
| 4971 |
|
|
| 4972 |
-- |
|
| 4950 | 4973 |
-- Name: placecorrelation_childplace_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 4951 | 4974 |
-- |
| 4952 | 4975 |
|
| ... | ... | |
| 4987 | 5010 |
|
| 4988 | 5011 |
|
| 4989 | 5012 |
-- |
| 4990 |
-- Name: placepath_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4991 |
-- |
|
| 4992 |
|
|
| 4993 |
|
|
| 4994 |
|
|
| 4995 |
|
|
| 4996 |
-- |
|
| 4997 |
-- Name: placepath_matched_placepath_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4998 |
-- |
|
| 4999 |
|
|
| 5000 |
ALTER TABLE placepath |
|
| 5001 |
ADD CONSTRAINT placepath_matched_placepath_id_fkey FOREIGN KEY (matched_placepath_id) REFERENCES placepath(placepath_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5002 |
|
|
| 5003 |
|
|
| 5004 |
-- |
|
| 5005 |
-- Name: placepath_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5006 |
-- |
|
| 5007 |
|
|
| 5008 |
ALTER TABLE placepath |
|
| 5009 |
ADD CONSTRAINT placepath_place_id_fkey FOREIGN KEY (placename_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5010 |
|
|
| 5011 |
|
|
| 5012 |
-- |
|
| 5013 | 5013 |
-- Name: plantobservation_aggregateoccurrence_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5014 | 5014 |
-- |
| 5015 | 5015 |
|
| inputs/MT/Specimen/VegBIEN.csv | ||
|---|---|---|
| 22 | 22 |
family,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/family, |
| 23 | 23 |
genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/genus, |
| 24 | 24 |
locality,/location/locationnarrative/_merge/1, |
| 25 |
continent,/location/locationplace/*_id/placepath/continent,
|
|
| 25 |
continent,/location/locationplace/*_id/place/continent, |
|
| 26 | 26 |
basisOfRecord,,** No join mapping for basisOfRecord ** |
| 27 | 27 |
countryCode,,** No join mapping for countryCode ** |
| 28 | 28 |
datasetName,,** No join mapping for datasetName ** |
| inputs/MT/Specimen/test.xml.ref | ||
|---|---|---|
| 117 | 117 |
</taxonoccurrence> |
| 118 | 118 |
</locationevent> |
| 119 | 119 |
<locationnarrative>$locality</locationnarrative> |
| 120 |
<locationplace><placepath_id><placepath><continent>$continent</continent></placepath></placepath_id></locationplace>
|
|
| 120 |
<locationplace><place_id><place><continent>$continent</continent></place></place_id></locationplace>
|
|
| 121 | 121 |
</location> |
| 122 | 122 |
</path> |
| 123 | 123 |
</_simplifyPath> |
| inputs/XAL/Specimen/VegBIEN.csv | ||
|---|---|---|
| 60 | 60 |
darwin:MaximumDepth,/location/locationevent/waterdepth_m/_avg/max, |
| 61 | 61 |
darwin:MinimumDepth,/location/locationevent/waterdepth_m/_avg/min, |
| 62 | 62 |
darwin:Locality,/location/locationnarrative/_merge/1, |
| 63 |
darwin:ContinentOcean,/location/locationplace/*_id/placepath/continent,
|
|
| 64 |
darwin:Country,/location/locationplace/*_id/placepath/country,
|
|
| 65 |
darwin:County,/location/locationplace/*_id/placepath/county,
|
|
| 66 |
darwin:StateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 63 |
darwin:ContinentOcean,/location/locationplace/*_id/place/continent, |
|
| 64 |
darwin:Country,/location/locationplace/*_id/place/country, |
|
| 65 |
darwin:County,/location/locationplace/*_id/place/county, |
|
| 66 |
darwin:StateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 67 | 67 |
darwin:BasisOfRecord,,** No join mapping for basisOfRecord ** |
| 68 | 68 |
darwin:DateLastModified,,** No join mapping for dcterms:modified ** |
| 69 | 69 |
darwin:FieldNumber,,"** No non-empty join mapping for UNUSED ** `grep -F ""<darwin:FieldNumber>"" inputs/XAL/src/digir.specimens.xml` returns no lines. This historical DwC term (http://rs.tdwg.org/dwc/terms/history/index.htm#fieldNumber-2009-04-24) has close to the same meaning as recordNumber (http://rs.tdwg.org/dwc/terms/#recordNumber)." |
| inputs/NY/Specimen/VegBIEN.csv | ||
|---|---|---|
| 68 | 68 |
Substrate,"/location/locationnarrative/_merge/1/_join/2/_label[label=""substrate""]/value", |
| 69 | 69 |
Vegetation,"/location/locationnarrative/_merge/1/_join/3/_label[label=""vegetation""]/value", |
| 70 | 70 |
Habitat,"/location/locationnarrative/_merge/3/_label[label=""habitat""]/value","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Vegetation'. Bob, Nick: keep as user defined or create special element?" |
| 71 |
ContinentOcean,/location/locationplace/*_id/placepath/continent,
|
|
| 72 |
Country,/location/locationplace/*_id/placepath/country,
|
|
| 73 |
County,/location/locationplace/*_id/placepath/county,
|
|
| 74 |
StateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 71 |
ContinentOcean,/location/locationplace/*_id/place/continent, |
|
| 72 |
Country,/location/locationplace/*_id/place/country, |
|
| 73 |
County,/location/locationplace/*_id/place/county, |
|
| 74 |
StateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 75 | 75 |
BasisOfRecord,,** No join mapping for basisOfRecord ** |
| 76 | 76 |
CollectorNumber,,** No non-empty join mapping for UNUSED ** |
| 77 | 77 |
DateLastModified,,** No join mapping for dcterms:modified ** |
| inputs/NY/Specimen/test.xml.ref | ||
|---|---|---|
| 335 | 335 |
</_merge> |
| 336 | 336 |
</locationnarrative> |
| 337 | 337 |
<locationplace> |
| 338 |
<placepath_id>
|
|
| 339 |
<placepath>
|
|
| 338 |
<place_id> |
|
| 339 |
<place> |
|
| 340 | 340 |
<continent>$ContinentOcean</continent> |
| 341 | 341 |
<country>$Country</country> |
| 342 | 342 |
<county>$County</county> |
| 343 | 343 |
<stateprovince>$StateProvince</stateprovince> |
| 344 |
</placepath>
|
|
| 345 |
</placepath_id>
|
|
| 344 |
</place> |
|
| 345 |
</place_id> |
|
| 346 | 346 |
</locationplace> |
| 347 | 347 |
</location> |
| 348 | 348 |
</path> |
| inputs/UNCC/Specimen/VegBIEN.csv | ||
|---|---|---|
| 89 | 89 |
collector3,/location/locationevent/taxonoccurrence/verbatimcollectorname/_join/4, |
| 90 | 90 |
locality,/location/locationnarrative/_merge/1, |
| 91 | 91 |
habitat,"/location/locationnarrative/_merge/3/_label[label=""habitat""]/value","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Vegetation'. Bob, Nick: keep as user defined or create special element?" |
| 92 |
country,/location/locationplace/*_id/placepath/country,
|
|
| 93 |
county,/location/locationplace/*_id/placepath/county,
|
|
| 94 |
state,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 92 |
country,/location/locationplace/*_id/place/country, |
|
| 93 |
county,/location/locationplace/*_id/place/county, |
|
| 94 |
state,/location/locationplace/*_id/place/stateprovince, |
|
| 95 | 95 |
filler,,"** No join mapping for *filler ** Where used, same as family" |
| 96 | 96 |
flower,,"** No join mapping for reproductiveCondition/_map:[A=*,I=*,M=*]/value ** Main values are A, I, and M. What do they mean?" |
| 97 | 97 |
fruit,,"** No join mapping for reproductiveCondition/_map:[A=*,I=*,M=*]/value ** Main values are A, I, and M. What do they mean?" |
| inputs/UNCC/Specimen/test.xml.ref | ||
|---|---|---|
| 259 | 259 |
</_merge> |
| 260 | 260 |
</locationnarrative> |
| 261 | 261 |
<locationplace> |
| 262 |
<placepath_id>
|
|
| 263 |
<placepath>
|
|
| 262 |
<place_id> |
|
| 263 |
<place> |
|
| 264 | 264 |
<country>$country</country> |
| 265 | 265 |
<county>$county</county> |
| 266 | 266 |
<stateprovince>$state</stateprovince> |
| 267 |
</placepath>
|
|
| 268 |
</placepath_id>
|
|
| 267 |
</place> |
|
| 268 |
</place_id> |
|
| 269 | 269 |
</locationplace> |
| 270 | 270 |
</location> |
| 271 | 271 |
</path> |
| inputs/ACAD/Specimen/VegBIEN.csv | ||
|---|---|---|
| 64 | 64 |
catalogNumber,"/location/locationevent/taxonoccurrence/voucher/*_id/specimenreplicate/catalognumber_dwc/_if[@name=""if indirect voucher""]/then", |
| 65 | 65 |
locality,/location/locationnarrative/_merge/1, |
| 66 | 66 |
habitat,"/location/locationnarrative/_merge/3/_label[label=""habitat""]/value","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Vegetation'. Bob, Nick: keep as user defined or create special element?" |
| 67 |
continent,/location/locationplace/*_id/placepath/continent,
|
|
| 68 |
country,/location/locationplace/*_id/placepath/country,
|
|
| 69 |
county,/location/locationplace/*_id/placepath/county,
|
|
| 70 |
stateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 67 |
continent,/location/locationplace/*_id/place/continent, |
|
| 68 |
country,/location/locationplace/*_id/place/country, |
|
| 69 |
county,/location/locationplace/*_id/place/county, |
|
| 70 |
stateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 71 | 71 |
verbatimElevation,/location/verbatimelevation, |
| 72 | 72 |
basisOfRecord,,** No join mapping for basisOfRecord ** |
| 73 | 73 |
bibliographicCitation,,** No join mapping for dcterms:bibliographicCitation ** |
| inputs/ACAD/Specimen/test.xml.ref | ||
|---|---|---|
| 249 | 249 |
</_merge> |
| 250 | 250 |
</locationnarrative> |
| 251 | 251 |
<locationplace> |
| 252 |
<placepath_id>
|
|
| 253 |
<placepath>
|
|
| 252 |
<place_id> |
|
| 253 |
<place> |
|
| 254 | 254 |
<continent>$continent</continent> |
| 255 | 255 |
<country>$country</country> |
| 256 | 256 |
<county>$county</county> |
| 257 | 257 |
<stateprovince>$stateProvince</stateprovince> |
| 258 |
</placepath>
|
|
| 259 |
</placepath_id>
|
|
| 258 |
</place> |
|
| 259 |
</place_id> |
|
| 260 | 260 |
</locationplace> |
| 261 | 261 |
<verbatimelevation>$verbatimElevation</verbatimelevation> |
| 262 | 262 |
</location> |
| inputs/SALVIAS-CSV/Plot/VegBIEN.csv | ||
|---|---|---|
| 40 | 40 |
plot_code,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/cond/_exists",Brad: plotCode is as-assigned by data provider; guranteed to be unique only within dataset (=project) |
| 41 | 41 |
temp_c,/location/locationevent/temperature_c, |
| 42 | 42 |
locality_description,/location/locationnarrative/_merge/1,"Brad: No; this is free text description of locality where plot was situated, same as for DwC. Not a description of vegetation. Most likely='namedPlace.placeDescription' in VB; not sure about VX, again need to ask Nick where locality descriptions live in VX" |
| 43 |
major_geo,/location/locationplace/*_id/placepath/continent,
|
|
| 44 |
country,/location/locationplace/*_id/placepath/country,
|
|
| 45 |
pol2,/location/locationplace/*_id/placepath/county,Brad: No pol2=countyParish
|
|
| 46 |
pol1,/location/locationplace/*_id/placepath/stateprovince,Brad: No; pol1=stateProvince
|
|
| 43 |
major_geo,/location/locationplace/*_id/place/continent, |
|
| 44 |
country,/location/locationplace/*_id/place/country, |
|
| 45 |
pol2,/location/locationplace/*_id/place/county,Brad: No pol2=countyParish |
|
| 46 |
pol1,/location/locationplace/*_id/place/stateprovince,Brad: No; pol1=stateProvince |
|
| 47 | 47 |
slope_aspect,/location/slopeaspect_deg/_alt/1/_compass/value, |
| 48 | 48 |
slope_gradient,/location/slopegradient_fraction/_alt/1, |
| 49 | 49 |
PLOT_ID,"/location/sourceaccessioncode/_join/1/_if[@name=""if subplot and unique only within parent""]/then/_first/1","Brad: This is artificial internal database ID; a unique identifier within SALVIAS DB to each plot, within the table plotMetadata." |
| inputs/SALVIAS-CSV/Plot/test.xml.ref | ||
|---|---|---|
| 93 | 93 |
</locationevent> |
| 94 | 94 |
<locationnarrative>$locality_description</locationnarrative> |
| 95 | 95 |
<locationplace> |
| 96 |
<placepath_id>
|
|
| 97 |
<placepath>
|
|
| 96 |
<place_id> |
|
| 97 |
<place> |
|
| 98 | 98 |
<continent>$major_geo</continent> |
| 99 | 99 |
<country>$country</country> |
| 100 | 100 |
<county>$pol2</county> |
| 101 | 101 |
<stateprovince>$pol1</stateprovince> |
| 102 |
</placepath>
|
|
| 103 |
</placepath_id>
|
|
| 102 |
</place> |
|
| 103 |
</place_id> |
|
| 104 | 104 |
</locationplace> |
| 105 | 105 |
<slopeaspect_deg><_compass><value>$slope_aspect</value></_compass></slopeaspect_deg> |
| 106 | 106 |
<slopegradient_fraction>$slope_gradient</slopegradient_fraction> |
| inputs/CVS/Organism/VegBIEN.csv | ||
|---|---|---|
| 13 | 13 |
currentTaxonName sec Weakley 2006,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/_if[@name=""if has verbatim name""]/else/matched_label_id/taxonlabel:[creator_id/party:[creator_id=0]/organizationname=TNRS]/taxonomicname/_join_words/2/_alt/2/_join_words/1/_alt/1", |
| 14 | 14 |
currentTaxonName sec Weakley 2006,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_join_words/2/_alt/2/_join_words/1/_alt/1, |
| 15 | 15 |
currentTaxonName sec Weakley 2006,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/binomial, |
| 16 |
county,/location/locationplace/*_id/placepath/county,
|
|
| 17 |
state,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 16 |
county,/location/locationplace/*_id/place/county, |
|
| 17 |
state,/location/locationplace/*_id/place/stateprovince, |
|
| 18 | 18 |
confidentialityStatus,,** No join mapping for *confidentialityStatus ** |
| 19 | 19 |
realLatitude,,** No non-empty join mapping for PRIVATE ** |
| 20 | 20 |
realLongitude,,** No non-empty join mapping for PRIVATE ** |
| inputs/CVS/Organism/test.xml.ref | ||
|---|---|---|
| 54 | 54 |
</taxonoccurrence> |
| 55 | 55 |
</locationevent> |
| 56 | 56 |
<locationplace> |
| 57 |
<placepath_id>
|
|
| 58 |
<placepath>
|
|
| 57 |
<place_id> |
|
| 58 |
<place> |
|
| 59 | 59 |
<county>$county</county> |
| 60 | 60 |
<stateprovince>$state</stateprovince> |
| 61 |
</placepath>
|
|
| 62 |
</placepath_id>
|
|
| 61 |
</place> |
|
| 62 |
</place_id> |
|
| 63 | 63 |
</locationplace> |
| 64 | 64 |
</location> |
| 65 | 65 |
</path> |
| inputs/REMIB/Specimen/VegBIEN.csv | ||
|---|---|---|
| 59 | 59 |
-----" |
| 60 | 60 |
locality,/location/locationnarrative/_merge/1, |
| 61 | 61 |
habitat,"/location/locationnarrative/_merge/3/_label[label=""habitat""]/value","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Vegetation'. Bob, Nick: keep as user defined or create special element?" |
| 62 |
country,/location/locationplace/*_id/placepath/country,
|
|
| 63 |
county,/location/locationplace/*_id/placepath/county,
|
|
| 64 |
state,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 62 |
country,/location/locationplace/*_id/place/country, |
|
| 63 |
county,/location/locationplace/*_id/place/county, |
|
| 64 |
state,/location/locationplace/*_id/place/stateprovince, |
|
| 65 | 65 |
lat_min,,** No join mapping for *lat_min ** |
| 66 | 66 |
lat_sec,,** No join mapping for *lat_sec ** |
| 67 | 67 |
long_min,,** No join mapping for *long_min ** |
| inputs/REMIB/Specimen/test.xml.ref | ||
|---|---|---|
| 163 | 163 |
</_merge> |
| 164 | 164 |
</locationnarrative> |
| 165 | 165 |
<locationplace> |
| 166 |
<placepath_id>
|
|
| 167 |
<placepath>
|
|
| 166 |
<place_id> |
|
| 167 |
<place> |
|
| 168 | 168 |
<country>$country</country> |
| 169 | 169 |
<county>$county</county> |
| 170 | 170 |
<stateprovince>$state</stateprovince> |
| 171 |
</placepath>
|
|
| 172 |
</placepath_id>
|
|
| 171 |
</place> |
|
| 172 |
</place_id> |
|
| 173 | 173 |
</locationplace> |
| 174 | 174 |
</location> |
| 175 | 175 |
</path> |
| inputs/CTFS/Plot/VegBIEN.csv | ||
|---|---|---|
| 23 | 23 |
PlotName,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/cond/_exists", |
| 24 | 24 |
DescriptionOfSite,/location/locationnarrative/_merge/1, |
| 25 | 25 |
LocationName,/location/locationnarrative/_merge/1, |
| 26 |
CountryName,/location/locationplace/*_id/placepath/country,
|
|
| 26 |
CountryName,/location/locationplace/*_id/place/country, |
|
| 27 | 27 |
PlotID,"/location/sourceaccessioncode/_join/1/_if[@name=""if subplot and unique only within parent""]/then/_first/1", |
| 28 | 28 |
PlotName,"/location/sourceaccessioncode/_join/1/_if[@name=""if subplot and unique only within parent""]/then/_first/2", |
| 29 | 29 |
CountryID,,** No join mapping for *CountryID ** |
| inputs/CTFS/Plot/test.xml.ref | ||
|---|---|---|
| 31 | 31 |
<1>$LocationName</1> |
| 32 | 32 |
</_alt> |
| 33 | 33 |
</locationnarrative> |
| 34 |
<locationplace><placepath_id><placepath><country>$CountryName</country></placepath></placepath_id></locationplace>
|
|
| 34 |
<locationplace><place_id><place><country>$CountryName</country></place></place_id></locationplace>
|
|
| 35 | 35 |
</location> |
| 36 | 36 |
</path> |
| 37 | 37 |
</_simplifyPath> |
| inputs/FIA/Organism/VegBIEN.csv | ||
|---|---|---|
| 25 | 25 |
SpecificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/*_id/taxonlabel/taxonomicname/_join_words/2/_alt/2/_join_words/1/_alt/2/_join_words/3/_join_words/1, |
| 26 | 26 |
Genus,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/genus, |
| 27 | 27 |
SpecificEpithet,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonverbatim/specific_epithet, |
| 28 |
County,/location/locationplace/*_id/placepath/county,
|
|
| 29 |
StateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 28 |
County,/location/locationplace/*_id/place/county, |
|
| 29 |
StateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 30 | 30 |
PlotCD,"/location/sourceaccessioncode/_join/1/_if[@name=""if subplot and unique only within parent""]/then/_first/1", |
| 31 | 31 |
HOM,,** No join mapping for *HOM ** |
| inputs/FIA/Organism/test.xml.ref | ||
|---|---|---|
| 132 | 132 |
</taxonoccurrence> |
| 133 | 133 |
</locationevent> |
| 134 | 134 |
<locationplace> |
| 135 |
<placepath_id>
|
|
| 136 |
<placepath>
|
|
| 135 |
<place_id> |
|
| 136 |
<place> |
|
| 137 | 137 |
<county>$County</county> |
| 138 | 138 |
<stateprovince>$StateProvince</stateprovince> |
| 139 |
</placepath>
|
|
| 140 |
</placepath_id>
|
|
| 139 |
</place> |
|
| 140 |
</place_id> |
|
| 141 | 141 |
</locationplace> |
| 142 | 142 |
</location> |
| 143 | 143 |
</path> |
| inputs/SALVIAS/plotMetadata/VegBIEN.csv | ||
|---|---|---|
| 41 | 41 |
Temp,/location/locationevent/temperature_c, |
| 42 | 42 |
Locality_Description,/location/locationnarrative/_merge/1,"Brad: No; this is free text description of locality where plot was situated, same as for DwC. Not a description of vegetation. Most likely='namedPlace.placeDescription' in VB; not sure about VX, again need to ask Nick where locality descriptions live in VX" |
| 43 | 43 |
Habitat,"/location/locationnarrative/_merge/3/_label[label=""habitat""]/value","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Vegetation'. Bob, Nick: keep as user defined or create special element?" |
| 44 |
MajorGeo,/location/locationplace/*_id/placepath/continent,
|
|
| 45 |
Country,/location/locationplace/*_id/placepath/country,
|
|
| 46 |
pol2,/location/locationplace/*_id/placepath/county,Brad: No pol2=countyParish
|
|
| 47 |
PolDiv1,/location/locationplace/*_id/placepath/stateprovince,Brad: No; pol1=stateProvince
|
|
| 44 |
MajorGeo,/location/locationplace/*_id/place/continent, |
|
| 45 |
Country,/location/locationplace/*_id/place/country, |
|
| 46 |
pol2,/location/locationplace/*_id/place/county,Brad: No pol2=countyParish |
|
| 47 |
PolDiv1,/location/locationplace/*_id/place/stateprovince,Brad: No; pol1=stateProvince |
|
| 48 | 48 |
plot_notes,/location/notespublic, |
| 49 | 49 |
slope_aspect,/location/slopeaspect_deg/_alt/1/_compass/value, |
| 50 | 50 |
slope_gradient,/location/slopegradient_fraction/_alt/1, |
| inputs/SALVIAS/plotMetadata/test.xml.ref | ||
|---|---|---|
| 103 | 103 |
</_merge> |
| 104 | 104 |
</locationnarrative> |
| 105 | 105 |
<locationplace> |
| 106 |
<placepath_id>
|
|
| 107 |
<placepath>
|
|
| 106 |
<place_id> |
|
| 107 |
<place> |
|
| 108 | 108 |
<continent>$MajorGeo</continent> |
| 109 | 109 |
<country>$Country</country> |
| 110 | 110 |
<county>$pol2</county> |
| 111 | 111 |
<stateprovince>$PolDiv1</stateprovince> |
| 112 |
</placepath>
|
|
| 113 |
</placepath_id>
|
|
| 112 |
</place> |
|
| 113 |
</place_id> |
|
| 114 | 114 |
</locationplace> |
| 115 | 115 |
<notespublic>$plot_notes</notespublic> |
| 116 | 116 |
<slopeaspect_deg><_compass><value>$slope_aspect</value></_compass></slopeaspect_deg> |
| inputs/U/Specimen/VegBIEN.csv | ||
|---|---|---|
| 35 | 35 |
Newgazett,"/location/locationnarrative/_merge/2/_join/1/_label[label=""gazetteer""]/value/_join/2",Only used in 1 row |
| 36 | 36 |
Locnotes,/location/locationnarrative/_merge/2/_join/2, |
| 37 | 37 |
Ecology,"/location/locationnarrative/_merge/3/_label[label=""habitat""]/value","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Vegetation'. Bob, Nick: keep as user defined or create special element?" |
| 38 |
Country,/location/locationplace/*_id/placepath/country,
|
|
| 38 |
Country,/location/locationplace/*_id/place/country, |
|
| 39 | 39 |
Alt,,** No join mapping for *Alt ** What is this? |
| 40 | 40 |
Alt2,,** No join mapping for *Alt2 ** What is this? |
| 41 | 41 |
Coorsyst,,** No join mapping for *Coorsyst ** |
| inputs/U/Specimen/test.xml.ref | ||
|---|---|---|
| 203 | 203 |
</3> |
| 204 | 204 |
</_merge> |
| 205 | 205 |
</locationnarrative> |
| 206 |
<locationplace><placepath_id><placepath><country>$Country</country></placepath></placepath_id></locationplace>
|
|
| 206 |
<locationplace><place_id><place><country>$Country</country></place></place_id></locationplace>
|
|
| 207 | 207 |
</location> |
| 208 | 208 |
</path> |
| 209 | 209 |
</_simplifyPath> |
| inputs/ARIZ/Specimen/VegBIEN.csv | ||
|---|---|---|
| 72 | 72 |
MaximumDepthInMeters,/location/locationevent/waterdepth_m/_avg/max, |
| 73 | 73 |
MinimumDepthInMeters,/location/locationevent/waterdepth_m/_avg/min, |
| 74 | 74 |
Locality,/location/locationnarrative/_merge/1, |
| 75 |
ContinentOcean,/location/locationplace/*_id/placepath/continent,
|
|
| 76 |
Country,/location/locationplace/*_id/placepath/country,
|
|
| 77 |
County,/location/locationplace/*_id/placepath/county,
|
|
| 78 |
StateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 75 |
ContinentOcean,/location/locationplace/*_id/place/continent, |
|
| 76 |
Country,/location/locationplace/*_id/place/country, |
|
| 77 |
County,/location/locationplace/*_id/place/county, |
|
| 78 |
StateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 79 | 79 |
AgeClass,,** No join mapping for lifeStage ** |
| 80 | 80 |
BasisOfRecord,,** No join mapping for basisOfRecord ** |
| 81 | 81 |
DateLastModified,,** No join mapping for dcterms:modified ** |
| inputs/ARIZ/Specimen/test.xml.ref | ||
|---|---|---|
| 311 | 311 |
</locationevent> |
| 312 | 312 |
<locationnarrative>$Locality</locationnarrative> |
| 313 | 313 |
<locationplace> |
| 314 |
<placepath_id>
|
|
| 315 |
<placepath>
|
|
| 314 |
<place_id> |
|
| 315 |
<place> |
|
| 316 | 316 |
<continent>$ContinentOcean</continent> |
| 317 | 317 |
<country>$Country</country> |
| 318 | 318 |
<county>$County</county> |
| 319 | 319 |
<stateprovince>$StateProvince</stateprovince> |
| 320 |
</placepath>
|
|
| 321 |
</placepath_id>
|
|
| 320 |
</place> |
|
| 321 |
</place_id> |
|
| 322 | 322 |
</locationplace> |
| 323 | 323 |
</location> |
| 324 | 324 |
</path> |
| inputs/GBIF/Specimen/test.xml.ref | ||
|---|---|---|
| 256 | 256 |
</locationevent> |
| 257 | 257 |
<locationnarrative>$Locality</locationnarrative> |
| 258 | 258 |
<locationplace> |
| 259 |
<placepath_id>
|
|
| 260 |
<placepath>
|
|
| 259 |
<place_id> |
|
| 260 |
<place> |
|
| 261 | 261 |
<country>$Country</country> |
| 262 | 262 |
<county>$County</county> |
| 263 | 263 |
<stateprovince>$StateProvince</stateprovince> |
| 264 |
</placepath>
|
|
| 265 |
</placepath_id>
|
|
| 264 |
</place> |
|
| 265 |
</place_id> |
|
| 266 | 266 |
</locationplace> |
| 267 | 267 |
</location> |
| 268 | 268 |
</path> |
| inputs/NCU-NCSC/Specimen/test.xml.ref | ||
|---|---|---|
| 89 | 89 |
</taxonoccurrence> |
| 90 | 90 |
</locationevent> |
| 91 | 91 |
<locationplace> |
| 92 |
<placepath_id>
|
|
| 93 |
<placepath>
|
|
| 92 |
<place_id> |
|
| 93 |
<place> |
|
| 94 | 94 |
<country>$Country</country> |
| 95 | 95 |
<county>$County</county> |
| 96 | 96 |
<stateprovince>$State</stateprovince> |
| 97 |
</placepath>
|
|
| 98 |
</placepath_id>
|
|
| 97 |
</place> |
|
| 98 |
</place_id> |
|
| 99 | 99 |
</locationplace> |
| 100 | 100 |
<verbatimelevation>$Elevation</verbatimelevation> |
| 101 | 101 |
</location> |
| inputs/SpeciesLink/Specimen/test.xml.ref | ||
|---|---|---|
| 631 | 631 |
</_alt> |
| 632 | 632 |
</locationnarrative> |
| 633 | 633 |
<locationplace> |
| 634 |
<placepath_id>
|
|
| 635 |
<placepath>
|
|
| 634 |
<place_id> |
|
| 635 |
<place> |
|
| 636 | 636 |
<continent> |
| 637 | 637 |
<_alt> |
| 638 | 638 |
<0>$conceptual_darwin_2003_1_0_ContinentOcean</0> |
| ... | ... | |
| 661 | 661 |
<2>$dwc_terms_stateProvince</2> |
| 662 | 662 |
</_alt> |
| 663 | 663 |
</stateprovince> |
| 664 |
</placepath>
|
|
| 665 |
</placepath_id>
|
|
| 664 |
</place> |
|
| 665 |
</place_id> |
|
| 666 | 666 |
</locationplace> |
| 667 | 667 |
</location> |
| 668 | 668 |
</path> |
| inputs/bien_web/observation/test.xml.ref | ||
|---|---|---|
| 156 | 156 |
</taxonoccurrence> |
| 157 | 157 |
</locationevent> |
| 158 | 158 |
<locationplace> |
| 159 |
<placepath_id>
|
|
| 160 |
<placepath>
|
|
| 159 |
<place_id> |
|
| 160 |
<place> |
|
| 161 | 161 |
<country>$country</country> |
| 162 | 162 |
<county>$countyParish</county> |
| 163 | 163 |
<stateprovince>$stateProvince</stateprovince> |
| 164 |
</placepath>
|
|
| 165 |
</placepath_id>
|
|
| 164 |
</place> |
|
| 165 |
</place_id> |
|
| 166 | 166 |
</locationplace> |
| 167 | 167 |
</location> |
| 168 | 168 |
</path> |
| inputs/VegBank/plot_/VegBIEN.csv | ||
|---|---|---|
| 16 | 16 |
authorplotcode,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/cond/_exists", |
| 17 | 17 |
plot_id,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/cond/_exists", |
| 18 | 18 |
locationnarrative,/location/locationnarrative/_merge/1, |
| 19 |
continent,/location/locationplace/*_id/placepath/continent,
|
|
| 20 |
area|country|territory,/location/locationplace/*_id/placepath/country,
|
|
| 21 |
country,/location/locationplace/*_id/placepath/country,
|
|
| 22 |
county,/location/locationplace/*_id/placepath/county,
|
|
| 23 |
region|state|province,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 24 |
stateprovince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 19 |
continent,/location/locationplace/*_id/place/continent, |
|
| 20 |
area|country|territory,/location/locationplace/*_id/place/country, |
|
| 21 |
country,/location/locationplace/*_id/place/country, |
|
| 22 |
county,/location/locationplace/*_id/place/county, |
|
| 23 |
region|state|province,/location/locationplace/*_id/place/stateprovince, |
|
| 24 |
stateprovince,/location/locationplace/*_id/place/stateprovince, |
|
| 25 | 25 |
parent_id,/location/parent_id/location/sourceaccessioncode, |
| 26 | 26 |
slopeaspect,/location/slopeaspect_deg/_alt/1/_compass/value, |
| 27 | 27 |
maxslopeaspect,/location/slopeaspect_deg/_alt/2/_avg/max, |
| inputs/MO/Specimen/test.xml.ref | ||
|---|---|---|
| 222 | 222 |
</locationevent> |
| 223 | 223 |
<locationnarrative>$Locality</locationnarrative> |
| 224 | 224 |
<locationplace> |
| 225 |
<placepath_id>
|
|
| 226 |
<placepath>
|
|
| 225 |
<place_id> |
|
| 226 |
<place> |
|
| 227 | 227 |
<continent>$ContinentOcean</continent> |
| 228 | 228 |
<country>$Country</country> |
| 229 | 229 |
<county>$County</county> |
| 230 | 230 |
<stateprovince>$StateProvince</stateprovince> |
| 231 |
</placepath>
|
|
| 232 |
</placepath_id>
|
|
| 231 |
</place> |
|
| 232 |
</place_id> |
|
| 233 | 233 |
</locationplace> |
| 234 | 234 |
</location> |
| 235 | 235 |
</path> |
| inputs/QMOR/Specimen/VegBIEN.csv | ||
|---|---|---|
| 72 | 72 |
catalogNumber,"/location/locationevent/taxonoccurrence/voucher/*_id/specimenreplicate/catalognumber_dwc/_if[@name=""if indirect voucher""]/then", |
| 73 | 73 |
locality,/location/locationnarrative/_merge/1, |
| 74 | 74 |
habitat,"/location/locationnarrative/_merge/3/_label[label=""habitat""]/value","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Vegetation'. Bob, Nick: keep as user defined or create special element?" |
| 75 |
continent,/location/locationplace/*_id/placepath/continent,
|
|
| 76 |
country,/location/locationplace/*_id/placepath/country,
|
|
| 77 |
county,/location/locationplace/*_id/placepath/county,
|
|
| 78 |
stateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 75 |
continent,/location/locationplace/*_id/place/continent, |
|
| 76 |
country,/location/locationplace/*_id/place/country, |
|
| 77 |
county,/location/locationplace/*_id/place/county, |
|
| 78 |
stateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 79 | 79 |
verbatimElevation,/location/verbatimelevation, |
| 80 | 80 |
acceptedNameUsage,,** No join mapping for acceptedNameUsage ** |
| 81 | 81 |
basisOfRecord,,** No join mapping for basisOfRecord ** |
| mappings/for_review/VegCore-VegBIEN.csv | ||
|---|---|---|
| 323 | 323 |
locality,//location/locationnarrative, |
| 324 | 324 |
verbatimLocality,//location/locationnarrative, |
| 325 | 325 |
habitat,"//location/locationnarrative/_label[label=""habitat""]/value","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Vegetation'. Bob, Nick: keep as user defined or create special element?" |
| 326 |
continent,//placepath/continent,
|
|
| 327 |
country,//placepath/country,
|
|
| 328 |
county,//placepath/county,
|
|
| 329 |
stateProvince,//placepath/stateprovince,
|
|
| 326 |
continent,//place/continent, |
|
| 327 |
country,//place/country, |
|
| 328 |
county,//place/county, |
|
| 329 |
stateProvince,//place/stateprovince, |
|
| 330 | 330 |
plotNotes,//location/notespublic, |
| 331 | 331 |
parentPlotName,//location/authorlocationcode, |
| 332 | 332 |
parentLocationID,//location/sourceaccessioncode, |
| mappings/VegCore-VegBIEN.csv | ||
|---|---|---|
| 323 | 323 |
locality,/location/locationnarrative/_merge/1, |
| 324 | 324 |
verbatimLocality,/location/locationnarrative/_merge/2, |
| 325 | 325 |
habitat,"/location/locationnarrative/_merge/3/_label[label=""habitat""]/value","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Vegetation'. Bob, Nick: keep as user defined or create special element?" |
| 326 |
continent,/location/locationplace/*_id/placepath/continent,
|
|
| 327 |
country,/location/locationplace/*_id/placepath/country,
|
|
| 328 |
county,/location/locationplace/*_id/placepath/county,
|
|
| 329 |
stateProvince,/location/locationplace/*_id/placepath/stateprovince,
|
|
| 326 |
continent,/location/locationplace/*_id/place/continent, |
|
| 327 |
country,/location/locationplace/*_id/place/country, |
|
| 328 |
county,/location/locationplace/*_id/place/county, |
|
| 329 |
stateProvince,/location/locationplace/*_id/place/stateprovince, |
|
| 330 | 330 |
plotNotes,/location/notespublic, |
| 331 | 331 |
parentPlotName,/location/parent_id/location/authorlocationcode, |
| 332 | 332 |
parentLocationID,/location/parent_id/location/sourceaccessioncode, |
| inputs/QMOR/Specimen/test.xml.ref | ||
|---|---|---|
| 274 | 274 |
</_merge> |
| 275 | 275 |
</locationnarrative> |
| 276 | 276 |
<locationplace> |
| 277 |
<placepath_id>
|
|
| 278 |
<placepath>
|
|
| 277 |
<place_id> |
|
| 278 |
<place> |
|
| 279 | 279 |
<continent>$continent</continent> |
| 280 | 280 |
<country>$country</country> |
| 281 | 281 |
<county>$county</county> |
| 282 | 282 |
<stateprovince>$stateProvince</stateprovince> |
| 283 |
</placepath>
|
|
| 284 |
</placepath_id>
|
|
| 283 |
</place> |
|
| 284 |
</place_id> |
|
| 285 | 285 |
</locationplace> |
| 286 | 286 |
<verbatimelevation>$verbatimElevation</verbatimelevation> |
| 287 | 287 |
</location> |
| schemas/vegbien.sql | ||
|---|---|---|
| 469 | 469 |
|
| 470 | 470 |
|
| 471 | 471 |
-- |
| 472 |
-- Name: place_matched_place_id_self_ref(); Type: FUNCTION; Schema: public; Owner: - |
|
| 473 |
-- |
|
| 474 |
|
|
| 475 |
CREATE FUNCTION place_matched_place_id_self_ref() RETURNS trigger |
|
| 476 |
LANGUAGE plpgsql |
|
| 477 |
AS $$ |
|
| 478 |
BEGIN |
|
| 479 |
IF new.place_id IS NULL THEN -- prepopulate place_id |
|
| 480 |
new.place_id = nextval('place_place_id_seq'::regclass);
|
|
| 481 |
END IF; |
|
| 482 |
IF new.matched_place_id = 0 THEN -- make self-reference |
|
| 483 |
new.matched_place_id = new.place_id; |
|
| 484 |
END IF; |
|
| 485 |
RETURN new; |
|
| 486 |
END; |
|
| 487 |
$$; |
|
| 488 |
|
|
| 489 |
|
|
| 490 |
-- |
|
| 472 | 491 |
-- Name: placename_update_ancestors(); Type: FUNCTION; Schema: public; Owner: - |
| 473 | 492 |
-- |
| 474 | 493 |
|
| ... | ... | |
| 521 | 540 |
|
| 522 | 541 |
|
| 523 | 542 |
-- |
| 524 |
-- Name: placepath_matched_placepath_id_self_ref(); Type: FUNCTION; Schema: public; Owner: - |
|
| 525 |
-- |
|
| 526 |
|
|
| 527 |
CREATE FUNCTION placepath_matched_placepath_id_self_ref() RETURNS trigger |
|
| 528 |
LANGUAGE plpgsql |
|
| 529 |
AS $$ |
|
| 530 |
BEGIN |
|
| 531 |
IF new.placepath_id IS NULL THEN -- prepopulate placepath_id |
|
| 532 |
new.placepath_id = nextval('placepath_placepath_id_seq'::regclass);
|
|
| 533 |
END IF; |
|
| 534 |
IF new.matched_placepath_id = 0 THEN -- make self-reference |
|
| 535 |
new.matched_placepath_id = new.placepath_id; |
|
| 536 |
END IF; |
|
| 537 |
RETURN new; |
|
| 538 |
END; |
|
| 539 |
$$; |
|
| 540 |
|
|
| 541 |
|
|
| 542 |
-- |
|
| 543 | 543 |
-- Name: plantobservation_aggregateoccurrence_count_1(); Type: FUNCTION; Schema: public; Owner: - |
| 544 | 544 |
-- |
| 545 | 545 |
|
| ... | ... | |
| 1301 | 1301 |
CREATE TABLE locationplace ( |
| 1302 | 1302 |
locationplace_id integer NOT NULL, |
| 1303 | 1303 |
location_id integer NOT NULL, |
| 1304 |
placepath_id integer NOT NULL,
|
|
| 1304 |
place_id integer NOT NULL, |
|
| 1305 | 1305 |
identifier_id integer |
| 1306 | 1306 |
); |
| 1307 | 1307 |
|
| ... | ... | |
| 1534 | 1534 |
|
| 1535 | 1535 |
|
| 1536 | 1536 |
-- |
| 1537 |
-- Name: placepath; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 1537 |
-- Name: place; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1538 | 1538 |
-- |
| 1539 | 1539 |
|
| 1540 |
CREATE TABLE placepath (
|
|
| 1541 |
placepath_id integer NOT NULL,
|
|
| 1540 |
CREATE TABLE place ( |
|
| 1541 |
place_id integer NOT NULL, |
|
| 1542 | 1542 |
creator_id integer NOT NULL, |
| 1543 | 1543 |
placecode text, |
| 1544 |
matched_placepath_id integer,
|
|
| 1544 |
matched_place_id integer, |
|
| 1545 | 1545 |
placename_id integer, |
| 1546 | 1546 |
continent text, |
| 1547 | 1547 |
country text, |
| ... | ... | |
| 1550 | 1550 |
municipality text, |
| 1551 | 1551 |
site text, |
| 1552 | 1552 |
otherranks rankedplacename[], |
| 1553 |
CONSTRAINT placepath_required_key CHECK (((((((placecode IS NOT NULL) OR (continent IS NOT NULL)) OR (country IS NOT NULL)) OR (stateprovince IS NOT NULL)) OR (county IS NOT NULL)) OR (otherranks IS NOT NULL)))
|
|
| 1553 |
CONSTRAINT place_required_key CHECK (((((((placecode IS NOT NULL) OR (continent IS NOT NULL)) OR (country IS NOT NULL)) OR (stateprovince IS NOT NULL)) OR (county IS NOT NULL)) OR (otherranks IS NOT NULL))) |
|
| 1554 | 1554 |
); |
| 1555 | 1555 |
|
| 1556 | 1556 |
|
| 1557 | 1557 |
-- |
| 1558 |
-- Name: TABLE placepath; Type: COMMENT; Schema: public; Owner: -
|
|
| 1558 |
-- Name: TABLE place; Type: COMMENT; Schema: public; Owner: - |
|
| 1559 | 1559 |
-- |
| 1560 | 1560 |
|
| 1561 |
COMMENT ON TABLE placepath IS 'The full path to a place. Can be either verbatim or accepted. For accepted names, points to the identified place.
|
|
| 1561 |
COMMENT ON TABLE place IS 'The full path to a place. Can be either verbatim or accepted. For accepted names, points to the identified place. |
|
| 1562 | 1562 |
|
| 1563 | 1563 |
To include a placename at a rank with no explicit column, add it to the otherranks array.'; |
| 1564 | 1564 |
|
| 1565 | 1565 |
|
| 1566 | 1566 |
-- |
| 1567 |
-- Name: COLUMN placepath.matched_placepath_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 1567 |
-- Name: COLUMN place.matched_place_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 1568 | 1568 |
-- |
| 1569 | 1569 |
|
| 1570 |
COMMENT ON COLUMN placepath.matched_placepath_id IS 'The placepath containing the accepted name of this verbatim place path. placepaths should be linked in a two-level hierarchy of datasource name -> accepted name.
|
|
| 1570 |
COMMENT ON COLUMN place.matched_place_id IS 'The canonical place for this verbatim place. Places should be linked in a two-level hierarchy of verbatim place -> accepted place.
|
|
| 1571 | 1571 |
|
| 1572 |
A accepted name should point to itself in this field. This will happen automatically by setting it to the special value 0.';
|
|
| 1572 |
An accepted place should point to itself in this field. This will happen automatically by setting it to the special value 0.';
|
|
| 1573 | 1573 |
|
| 1574 | 1574 |
|
| 1575 | 1575 |
-- |
| 1576 |
-- Name: COLUMN placepath.otherranks; Type: COMMENT; Schema: public; Owner: -
|
|
| 1576 |
-- Name: COLUMN place.otherranks; Type: COMMENT; Schema: public; Owner: - |
|
| 1577 | 1577 |
-- |
| 1578 | 1578 |
|
| 1579 |
COMMENT ON COLUMN placepath.otherranks IS 'Additional ranks which do not have a named column. Put ranks in path order, so that lower-level places come after higher-level places.';
|
|
| 1579 |
COMMENT ON COLUMN place.otherranks IS 'Additional ranks which do not have a named column. Put ranks in path order, so that lower-level places come after higher-level places.'; |
|
| 1580 | 1580 |
|
| 1581 | 1581 |
|
| 1582 | 1582 |
-- |
| ... | ... | |
| 1864 | 1864 |
-- |
| 1865 | 1865 |
|
| 1866 | 1866 |
CREATE VIEW analytical_db_view AS |
| 1867 |
SELECT datasource.organizationname AS "institutionCode", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county, locationcoords.latitude_deg AS "decimalLatitude", locationcoords.longitude_deg AS "decimalLongitude", location.sourceaccessioncode AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", aggregateoccurrence.collectiondate AS "dateCollected", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.binomial) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", taxonoccurrence.iscultivated AS cultivated, taxonoccurrence.cultivatedbasis AS "cultivatedBasis", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", taxonoccurrence.verbatimcollectorname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM (((((((((((((((((((party datasource JOIN location ON ((location.creator_id = datasource.party_id))) 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 taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) WHERE ((datasource.creator_id = datasource.party_id) AND (NOT taxondetermination.isoriginal));
|
|
| 1867 |
SELECT datasource.organizationname AS "institutionCode", place.country, place.stateprovince AS "stateProvince", place.county, locationcoords.latitude_deg AS "decimalLatitude", locationcoords.longitude_deg AS "decimalLongitude", location.sourceaccessioncode AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", aggregateoccurrence.collectiondate AS "dateCollected", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.binomial) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", taxonoccurrence.iscultivated AS cultivated, taxonoccurrence.cultivatedbasis AS "cultivatedBasis", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", taxonoccurrence.verbatimcollectorname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM (((((((((((((((((((party datasource JOIN location ON ((location.creator_id = datasource.party_id))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place USING (place_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 taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) WHERE ((datasource.creator_id = datasource.party_id) AND (NOT taxondetermination.isoriginal));
|
|
| 1868 | 1868 |
|
| 1869 | 1869 |
|
| 1870 | 1870 |
-- |
| ... | ... | |
| 2691 | 2691 |
|
| 2692 | 2692 |
|
| 2693 | 2693 |
-- |
| 2694 |
-- Name: place_place_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 2695 |
-- |
|
| 2696 |
|
|
| 2697 |
CREATE SEQUENCE place_place_id_seq |
|
| 2698 |
START WITH 1 |
|
| 2699 |
INCREMENT BY 1 |
|
| 2700 |
NO MINVALUE |
|
| 2701 |
NO MAXVALUE |
|
| 2702 |
CACHE 1; |
|
| 2703 |
|
|
| 2704 |
|
|
| 2705 |
-- |
|
| 2706 |
-- Name: place_place_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 2707 |
-- |
|
| 2708 |
|
|
| 2709 |
ALTER SEQUENCE place_place_id_seq OWNED BY place.place_id; |
|
| 2710 |
|
|
| 2711 |
|
|
| 2712 |
-- |
|
| 2694 | 2713 |
-- Name: placecorrelation; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2695 | 2714 |
-- |
| 2696 | 2715 |
|
| ... | ... | |
| 2782 | 2801 |
|
| 2783 | 2802 |
|
| 2784 | 2803 |
-- |
| 2785 |
-- Name: placepath_placepath_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 2786 |
-- |
|
| 2787 |
|
|
| 2788 |
CREATE SEQUENCE placepath_placepath_id_seq |
|
| 2789 |
START WITH 1 |
|
| 2790 |
INCREMENT BY 1 |
|
| 2791 |
NO MINVALUE |
|
| 2792 |
NO MAXVALUE |
|
| 2793 |
CACHE 1; |
|
| 2794 |
|
|
| 2795 |
|
|
| 2796 |
-- |
|
| 2797 |
-- Name: placepath_placepath_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 2798 |
-- |
|
| 2799 |
|
|
| 2800 |
ALTER SEQUENCE placepath_placepath_id_seq OWNED BY placepath.placepath_id; |
|
| 2801 |
|
|
| 2802 |
|
|
| 2803 |
-- |
|
| 2804 | 2804 |
-- Name: plant; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2805 | 2805 |
-- |
| 2806 | 2806 |
|
| ... | ... | |
| 3948 | 3948 |
|
| 3949 | 3949 |
|
| 3950 | 3950 |
-- |
| 3951 |
-- Name: placecorrelation_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3951 |
-- Name: place_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3952 | 3952 |
-- |
| 3953 | 3953 |
|
| 3954 |
ALTER TABLE placecorrelation ALTER COLUMN placecorrelation_id SET DEFAULT nextval('placecorrelation_placecorrelation_id_seq'::regclass);
|
|
| 3954 |
ALTER TABLE place ALTER COLUMN place_id SET DEFAULT nextval('place_place_id_seq'::regclass);
|
|
| 3955 | 3955 |
|
| 3956 | 3956 |
|
| 3957 | 3957 |
-- |
| 3958 |
-- Name: placename_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3958 |
-- Name: placecorrelation_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3959 | 3959 |
-- |
| 3960 | 3960 |
|
| 3961 |
ALTER TABLE placename ALTER COLUMN placename_id SET DEFAULT nextval('placename_placename_id_seq'::regclass);
|
|
| 3961 |
ALTER TABLE placecorrelation ALTER COLUMN placecorrelation_id SET DEFAULT nextval('placecorrelation_placecorrelation_id_seq'::regclass);
|
|
| 3962 | 3962 |
|
| 3963 | 3963 |
|
| 3964 | 3964 |
-- |
| 3965 |
-- Name: placepath_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3965 |
-- Name: placename_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3966 | 3966 |
-- |
| 3967 | 3967 |
|
| 3968 |
ALTER TABLE placepath ALTER COLUMN placepath_id SET DEFAULT nextval('placepath_placepath_id_seq'::regclass);
|
|
| 3968 |
ALTER TABLE placename ALTER COLUMN placename_id SET DEFAULT nextval('placename_placename_id_seq'::regclass);
|
|
| 3969 | 3969 |
|
| 3970 | 3970 |
|
| 3971 | 3971 |
-- |
| ... | ... | |
| 4437 | 4437 |
|
| 4438 | 4438 |
|
| 4439 | 4439 |
-- |
| 4440 |
-- Name: place_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4441 |
-- |
|
| 4442 |
|
|
| 4443 |
ALTER TABLE ONLY place |
|
| 4444 |
ADD CONSTRAINT place_pkey PRIMARY KEY (place_id); |
|
| 4445 |
|
|
| 4446 |
|
|
| 4447 |
-- |
|
| 4440 | 4448 |
-- Name: placecorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 4441 | 4449 |
-- |
| 4442 | 4450 |
|
| ... | ... | |
| 4461 | 4469 |
|
| 4462 | 4470 |
|
| 4463 | 4471 |
-- |
| 4464 |
-- Name: placepath_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4465 |
-- |
|
| 4466 |
|
|
| 4467 |
ALTER TABLE ONLY placepath |
|
| 4468 |
ADD CONSTRAINT placepath_pkey PRIMARY KEY (placepath_id); |
|
| 4469 |
|
|
| 4470 |
|
|
| 4471 |
-- |
|
| 4472 | 4472 |
-- Name: plant_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 4473 | 4473 |
-- |
| 4474 | 4474 |
|
| ... | ... | |
| 4928 | 4928 |
-- Name: locationplace_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4929 | 4929 |
-- |
| 4930 | 4930 |
|
| 4931 |
CREATE UNIQUE INDEX locationplace_unique ON locationplace USING btree (location_id, placepath_id, (COALESCE(identifier_id, 2147483647)));
|
|
| 4931 |
CREATE UNIQUE INDEX locationplace_unique ON locationplace USING btree (location_id, place_id, (COALESCE(identifier_id, 2147483647))); |
|
| 4932 | 4932 |
|
| 4933 | 4933 |
|
| 4934 | 4934 |
-- |
| ... | ... | |
| 4974 | 4974 |
|
| 4975 | 4975 |
|
| 4976 | 4976 |
-- |
| 4977 |
-- Name: placename_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4977 |
-- Name: place_unique_within_creator_by_code; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4978 | 4978 |
-- |
| 4979 | 4979 |
|
| 4980 |
CREATE UNIQUE INDEX placename_accessioncode_index ON placename USING btree (accessioncode);
|
|
| 4980 |
CREATE UNIQUE INDEX place_unique_within_creator_by_code ON place USING btree (creator_id, (COALESCE(placecode, '\\N'::text))) WHERE (placecode IS NOT NULL);
|
|
| 4981 | 4981 |
|
| 4982 | 4982 |
|
| 4983 | 4983 |
-- |
| 4984 |
-- Name: placename_unique; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4984 |
-- Name: place_unique_within_creator_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4985 | 4985 |
-- |
| 4986 | 4986 |
|
| 4987 |
CREATE UNIQUE INDEX placename_unique ON placename USING btree ((COALESCE(parent_id, 2147483647)), placename, rank);
|
|
| 4987 |
CREATE UNIQUE INDEX place_unique_within_creator_by_name ON place USING btree (creator_id, (COALESCE(continent, '\\N'::text)), (COALESCE(country, '\\N'::text)), (COALESCE(stateprovince, '\\N'::text)), (COALESCE(county, '\\N'::text)), (COALESCE(municipality, '\\N'::text)), (COALESCE(site, '\\N'::text)), (COALESCE(otherranks, ARRAY[]::rankedplacename[]))) WHERE (placecode IS NULL);
|
|
| 4988 | 4988 |
|
| 4989 | 4989 |
|
| 4990 | 4990 |
-- |
| 4991 |
-- Name: placepath_unique_within_creator_by_code; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4991 |
-- Name: placename_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4992 | 4992 |
-- |
| 4993 | 4993 |
|
| 4994 |
CREATE UNIQUE INDEX placepath_unique_within_creator_by_code ON placepath USING btree (creator_id, (COALESCE(placecode, '\\N'::text))) WHERE (placecode IS NOT NULL);
|
|
| 4994 |
CREATE UNIQUE INDEX placename_accessioncode_index ON placename USING btree (accessioncode);
|
|
| 4995 | 4995 |
|
| 4996 | 4996 |
|
| 4997 | 4997 |
-- |
| 4998 |
-- Name: placepath_unique_within_creator_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4998 |
-- Name: placename_unique; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4999 | 4999 |
-- |
| 5000 | 5000 |
|
| 5001 |
CREATE UNIQUE INDEX placepath_unique_within_creator_by_name ON placepath USING btree (creator_id, (COALESCE(continent, '\\N'::text)), (COALESCE(country, '\\N'::text)), (COALESCE(stateprovince, '\\N'::text)), (COALESCE(county, '\\N'::text)), (COALESCE(municipality, '\\N'::text)), (COALESCE(site, '\\N'::text)), (COALESCE(otherranks, ARRAY[]::rankedplacename[]))) WHERE (placecode IS NULL);
|
|
| 5001 |
CREATE UNIQUE INDEX placename_unique ON placename USING btree ((COALESCE(parent_id, 2147483647)), placename, rank);
|
|
| 5002 | 5002 |
|
| 5003 | 5003 |
|
| 5004 | 5004 |
-- |
| ... | ... | |
| 5226 | 5226 |
|
| 5227 | 5227 |
|
| 5228 | 5228 |
-- |
| 5229 |
-- Name: placename_update_ancestors; Type: TRIGGER; Schema: public; Owner: -
|
|
| 5229 |
-- Name: place_matched_place_id_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
|
| 5230 | 5230 |
-- |
| 5231 | 5231 |
|
| 5232 |
CREATE CONSTRAINT TRIGGER placename_update_ancestors AFTER INSERT OR UPDATE ON placename DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE placename_update_ancestors();
|
|
| 5232 |
CREATE TRIGGER place_matched_place_id_self_ref BEFORE INSERT OR UPDATE ON place FOR EACH ROW EXECUTE PROCEDURE place_matched_place_id_self_ref();
|
|
| 5233 | 5233 |
|
| 5234 | 5234 |
|
| 5235 | 5235 |
-- |
| 5236 |
-- Name: placepath_matched_placepath_id_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
|
| 5236 |
-- Name: placename_update_ancestors; Type: TRIGGER; Schema: public; Owner: -
|
|
| 5237 | 5237 |
-- |
| 5238 | 5238 |
|
| 5239 |
CREATE TRIGGER placepath_matched_placepath_id_self_ref BEFORE INSERT OR UPDATE ON placepath FOR EACH ROW EXECUTE PROCEDURE placepath_matched_placepath_id_self_ref();
|
|
| 5239 |
CREATE CONSTRAINT TRIGGER placename_update_ancestors AFTER INSERT OR UPDATE ON placename DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE placename_update_ancestors();
|
|
| 5240 | 5240 |
|
| 5241 | 5241 |
|
| 5242 | 5242 |
-- |
| ... | ... | |
| 5712 | 5712 |
|
| 5713 | 5713 |
|
| 5714 | 5714 |
-- |
| 5715 |
-- Name: locationplace_placepath_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5715 |
-- Name: locationplace_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5716 | 5716 |
-- |
| 5717 | 5717 |
|
| 5718 | 5718 |
ALTER TABLE ONLY locationplace |
| 5719 |
ADD CONSTRAINT locationplace_placepath_id_fkey FOREIGN KEY (placepath_id) REFERENCES placepath(placepath_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5719 |
ADD CONSTRAINT locationplace_place_id_fkey FOREIGN KEY (place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5720 | 5720 |
|
| 5721 | 5721 |
|
| 5722 | 5722 |
-- |
| ... | ... | |
| 5816 | 5816 |
|
| 5817 | 5817 |
|
| 5818 | 5818 |
-- |
| 5819 |
-- Name: place_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5820 |
-- |
|
| 5821 |
|
|
| 5822 |
ALTER TABLE ONLY place |
|
| 5823 |
ADD CONSTRAINT place_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5824 |
|
|
| 5825 |
|
|
| 5826 |
-- |
|
| 5827 |
-- Name: place_matched_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5828 |
-- |
|
| 5829 |
|
|
| 5830 |
ALTER TABLE ONLY place |
|
| 5831 |
ADD CONSTRAINT place_matched_place_id_fkey FOREIGN KEY (matched_place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5832 |
|
|
| 5833 |
|
|
| 5834 |
-- |
|
| 5835 |
-- Name: place_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5836 |
-- |
|
| 5837 |
|
|
| 5838 |
ALTER TABLE ONLY place |
|
| 5839 |
ADD CONSTRAINT place_place_id_fkey FOREIGN KEY (placename_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5840 |
|
|
| 5841 |
|
|
| 5842 |
-- |
|
| 5819 | 5843 |
-- Name: placecorrelation_childplace_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5820 | 5844 |
-- |
| 5821 | 5845 |
|
| ... | ... | |
| 5856 | 5880 |
|
| 5857 | 5881 |
|
| 5858 | 5882 |
-- |
| 5859 |
-- Name: placepath_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5860 |
-- |
|
| 5861 |
|
|
| 5862 |
ALTER TABLE ONLY placepath |
|
| 5863 |
ADD CONSTRAINT placepath_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5864 |
|
|
| 5865 |
|
|
| 5866 |
-- |
|
| 5867 |
-- Name: placepath_matched_placepath_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5868 |
-- |
|
| 5869 |
|
|
| 5870 |
ALTER TABLE ONLY placepath |
|
| 5871 |
ADD CONSTRAINT placepath_matched_placepath_id_fkey FOREIGN KEY (matched_placepath_id) REFERENCES placepath(placepath_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5872 |
|
|
| 5873 |
|
|
| 5874 |
-- |
|
| 5875 |
-- Name: placepath_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5876 |
-- |
|
| 5877 |
|
|
| 5878 |
ALTER TABLE ONLY placepath |
|
Also available in: Unified diff
schemas/vegbien.sql: Renamed placepath to place since this contains primary information about the place, including the reference to the canonical place