Revision 1099
Added by Aaron Marcuse-Kubitza almost 13 years ago
| inputs/SALVIAS/test/VegBIEN.plots.xml.ref | ||
|---|---|---|
| 29 | 29 |
<centerlongitude>-78.3667</centerlongitude> |
| 30 | 30 |
</location> |
| 31 | 31 |
</location_id> |
| 32 |
<plotmethod_id><plotmethod><name>0.1 ha transect, stems >= 2.5 cm dbh</name></plotmethod></plotmethod_id>
|
|
| 32 |
<method_id><method><name>0.1 ha transect, stems >= 2.5 cm dbh</name></method></method_id>
|
|
| 33 | 33 |
<project_id> |
| 34 | 34 |
<project> |
| 35 | 35 |
<datasource_id><party><organizationname>SALVIAS</organizationname></party></datasource_id> |
| ... | ... | |
| 88 | 88 |
<centerlongitude>11.5</centerlongitude> |
| 89 | 89 |
</location> |
| 90 | 90 |
</location_id> |
| 91 |
<plotmethod_id><plotmethod><name>0.1 ha transect, stems >= 2.5 cm dbh</name></plotmethod></plotmethod_id>
|
|
| 91 |
<method_id><method><name>0.1 ha transect, stems >= 2.5 cm dbh</name></method></method_id>
|
|
| 92 | 92 |
<project_id> |
| 93 | 93 |
<project> |
| 94 | 94 |
<datasource_id><party><organizationname>SALVIAS</organizationname></party></datasource_id> |
| inputs/SALVIAS/maps/VegBIEN.plots.csv | ||
|---|---|---|
| 12 | 12 |
elev_min_m,"/*_id/location/{elevation/_alt/2/_avg/min,elevationrange/_range/from}",
|
| 13 | 13 |
LatDec,"/*_id/location/{locationdetermination[!namedplace_id]/latitude,centerlatitude}",
|
| 14 | 14 |
LongDec,"/*_id/location/{locationdetermination[!namedplace_id]/longitude,centerlongitude}",
|
| 15 |
PlotMethod,/*_id/plotmethod/name,
|
|
| 15 |
PlotMethod,/*_id/method/name, |
|
| 16 | 16 |
project_id,/*_id/project:[datasource_id/party/organizationname/_alt/2=$/_ignore/inLabel]/projectname, |
| 17 | 17 |
life_zone_code,/commclass/commdetermination/*_id/commconcept/*_id/*/commname, |
| 18 | 18 |
life_zone,/commclass/commdetermination/*_id/commconcept/commdescription, |
| inputs/SALVIAS-CSV/test/VegBIEN.plots.xml.ref | ||
|---|---|---|
| 38 | 38 |
<centerlongitude>-73.4333</centerlongitude> |
| 39 | 39 |
</location> |
| 40 | 40 |
</location_id> |
| 41 |
<plotmethod_id><plotmethod><name>1 ha, stems >= 10 cm dbh</name></plotmethod></plotmethod_id>
|
|
| 41 |
<method_id><method><name>1 ha, stems >= 10 cm dbh</name></method></method_id>
|
|
| 42 | 42 |
<project_id> |
| 43 | 43 |
<project> |
| 44 | 44 |
<datasource_id><party><organizationname>SALVIAS</organizationname></party></datasource_id> |
| ... | ... | |
| 88 | 88 |
<centerlongitude>-73.4333</centerlongitude> |
| 89 | 89 |
</location> |
| 90 | 90 |
</location_id> |
| 91 |
<plotmethod_id><plotmethod><name>1 ha, stems >= 10 cm dbh</name></plotmethod></plotmethod_id>
|
|
| 91 |
<method_id><method><name>1 ha, stems >= 10 cm dbh</name></method></method_id>
|
|
| 92 | 92 |
<project_id> |
| 93 | 93 |
<project> |
| 94 | 94 |
<datasource_id><party><organizationname>SALVIAS</organizationname></party></datasource_id> |
| inputs/SALVIAS-CSV/maps/VegBIEN.plots.csv | ||
|---|---|---|
| 12 | 12 |
elev_min_m,"/*_id/location/{elevation/_alt/2/_avg/min,elevationrange/_range/from}",
|
| 13 | 13 |
lat_decimal,"/*_id/location/{locationdetermination[!namedplace_id]/latitude,centerlatitude}",
|
| 14 | 14 |
long_decimal,"/*_id/location/{locationdetermination[!namedplace_id]/longitude,centerlongitude}",
|
| 15 |
plot_methodology,/*_id/plotmethod/name,
|
|
| 15 |
plot_methodology,/*_id/method/name, |
|
| 16 | 16 |
project,/*_id/project:[datasource_id/party/organizationname/_alt/2=$/_ignore/inLabel]/projectname, |
| 17 | 17 |
holdridge_life_zone,/commclass/commdetermination/*_id/commconcept/*_id/*/commname, |
| 18 | 18 |
life_zone_code,/commclass/commdetermination/*_id/commconcept/commdescription, |
| schemas/vegbien.my.sql | ||
|---|---|---|
| 730 | 730 |
sourceaccessioncode text, |
| 731 | 731 |
previous_id int(11), |
| 732 | 732 |
dateaccuracy text, |
| 733 |
plotmethod_id int(11),
|
|
| 733 |
method_id int(11), |
|
| 734 | 734 |
autotaxoncover int(1), |
| 735 | 735 |
originaldata text, |
| 736 | 736 |
effortlevel text, |
| ... | ... | |
| 900 | 900 |
covermethod_id int(11), |
| 901 | 901 |
samplingfactor double precision DEFAULT 1 NOT NULL, |
| 902 | 902 |
coverbasis text, |
| 903 |
stemsamplemethod text |
|
| 903 |
stemsamplemethod text, |
|
| 904 |
shape text, |
|
| 905 |
length double precision, |
|
| 906 |
width double precision, |
|
| 907 |
radius double precision, |
|
| 908 |
area double precision, |
|
| 909 |
samplearea double precision |
|
| 904 | 910 |
); |
| 905 | 911 |
|
| 906 | 912 |
|
| ... | ... | |
| 1017 | 1023 |
|
| 1018 | 1024 |
|
| 1019 | 1025 |
-- |
| 1026 |
-- Name: COLUMN method.shape; Type: COMMENT; Schema: public; Owner: - |
|
| 1027 |
-- |
|
| 1028 |
|
|
| 1029 |
|
|
| 1030 |
|
|
| 1031 |
|
|
| 1032 |
-- |
|
| 1033 |
-- Name: COLUMN method.length; Type: COMMENT; Schema: public; Owner: - |
|
| 1034 |
-- |
|
| 1035 |
|
|
| 1036 |
|
|
| 1037 |
|
|
| 1038 |
|
|
| 1039 |
-- |
|
| 1040 |
-- Name: COLUMN method.width; Type: COMMENT; Schema: public; Owner: - |
|
| 1041 |
-- |
|
| 1042 |
|
|
| 1043 |
|
|
| 1044 |
|
|
| 1045 |
|
|
| 1046 |
-- |
|
| 1047 |
-- Name: COLUMN method.radius; Type: COMMENT; Schema: public; Owner: - |
|
| 1048 |
-- |
|
| 1049 |
|
|
| 1050 |
|
|
| 1051 |
|
|
| 1052 |
|
|
| 1053 |
-- |
|
| 1054 |
-- Name: COLUMN method.area; Type: COMMENT; Schema: public; Owner: - |
|
| 1055 |
-- |
|
| 1056 |
|
|
| 1057 |
|
|
| 1058 |
|
|
| 1059 |
|
|
| 1060 |
-- |
|
| 1061 |
-- Name: COLUMN method.samplearea; Type: COMMENT; Schema: public; Owner: - |
|
| 1062 |
-- |
|
| 1063 |
|
|
| 1064 |
|
|
| 1065 |
|
|
| 1066 |
|
|
| 1067 |
-- |
|
| 1020 | 1068 |
-- Name: method_method_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| 1021 | 1069 |
-- |
| 1022 | 1070 |
|
| ... | ... | |
| 1486 | 1534 |
|
| 1487 | 1535 |
|
| 1488 | 1536 |
-- |
| 1489 |
-- Name: plotmethod; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1490 |
-- |
|
| 1491 |
|
|
| 1492 |
CREATE TABLE plotmethod ( |
|
| 1493 |
plotmethod_id int(11) NOT NULL, |
|
| 1494 |
reference_id int(11), |
|
| 1495 |
name text NOT NULL, |
|
| 1496 |
description text, |
|
| 1497 |
accessioncode text, |
|
| 1498 |
method_id int(11), |
|
| 1499 |
shape text, |
|
| 1500 |
length double precision, |
|
| 1501 |
width double precision, |
|
| 1502 |
radius double precision, |
|
| 1503 |
area double precision, |
|
| 1504 |
samplearea double precision |
|
| 1505 |
); |
|
| 1506 |
|
|
| 1507 |
|
|
| 1508 |
-- |
|
| 1509 |
-- Name: TABLE plotmethod; Type: COMMENT; Schema: public; Owner: - |
|
| 1510 |
-- |
|
| 1511 |
|
|
| 1512 |
|
|
| 1513 |
|
|
| 1514 |
|
|
| 1515 |
-- |
|
| 1516 |
-- Name: COLUMN plotmethod.name; Type: COMMENT; Schema: public; Owner: - |
|
| 1517 |
-- |
|
| 1518 |
|
|
| 1519 |
|
|
| 1520 |
|
|
| 1521 |
|
|
| 1522 |
-- |
|
| 1523 |
-- Name: COLUMN plotmethod.description; Type: COMMENT; Schema: public; Owner: - |
|
| 1524 |
-- |
|
| 1525 |
|
|
| 1526 |
|
|
| 1527 |
|
|
| 1528 |
|
|
| 1529 |
-- |
|
| 1530 |
-- Name: COLUMN plotmethod.shape; Type: COMMENT; Schema: public; Owner: - |
|
| 1531 |
-- |
|
| 1532 |
|
|
| 1533 |
|
|
| 1534 |
|
|
| 1535 |
|
|
| 1536 |
-- |
|
| 1537 |
-- Name: COLUMN plotmethod.length; Type: COMMENT; Schema: public; Owner: - |
|
| 1538 |
-- |
|
| 1539 |
|
|
| 1540 |
|
|
| 1541 |
|
|
| 1542 |
|
|
| 1543 |
-- |
|
| 1544 |
-- Name: COLUMN plotmethod.width; Type: COMMENT; Schema: public; Owner: - |
|
| 1545 |
-- |
|
| 1546 |
|
|
| 1547 |
|
|
| 1548 |
|
|
| 1549 |
|
|
| 1550 |
-- |
|
| 1551 |
-- Name: COLUMN plotmethod.radius; Type: COMMENT; Schema: public; Owner: - |
|
| 1552 |
-- |
|
| 1553 |
|
|
| 1554 |
|
|
| 1555 |
|
|
| 1556 |
|
|
| 1557 |
-- |
|
| 1558 |
-- Name: COLUMN plotmethod.area; Type: COMMENT; Schema: public; Owner: - |
|
| 1559 |
-- |
|
| 1560 |
|
|
| 1561 |
|
|
| 1562 |
|
|
| 1563 |
|
|
| 1564 |
-- |
|
| 1565 |
-- Name: COLUMN plotmethod.samplearea; Type: COMMENT; Schema: public; Owner: - |
|
| 1566 |
-- |
|
| 1567 |
|
|
| 1568 |
|
|
| 1569 |
|
|
| 1570 |
|
|
| 1571 |
-- |
|
| 1572 |
-- Name: plotmethod_plotmethod_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 1573 |
-- |
|
| 1574 |
|
|
| 1575 |
|
|
| 1576 |
|
|
| 1577 |
|
|
| 1578 |
-- |
|
| 1579 |
-- Name: plotmethod_plotmethod_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 1580 |
-- |
|
| 1581 |
|
|
| 1582 |
|
|
| 1583 |
|
|
| 1584 |
|
|
| 1585 |
-- |
|
| 1586 | 1537 |
-- Name: project; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1587 | 1538 |
-- |
| 1588 | 1539 |
|
| ... | ... | |
| 2612 | 2563 |
|
| 2613 | 2564 |
|
| 2614 | 2565 |
-- |
| 2615 |
-- Name: plotmethod_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 2616 |
-- |
|
| 2617 |
|
|
| 2618 |
|
|
| 2619 |
|
|
| 2620 |
|
|
| 2621 |
-- |
|
| 2622 | 2566 |
-- Name: project_id; Type: DEFAULT; Schema: public; Owner: - |
| 2623 | 2567 |
-- |
| 2624 | 2568 |
|
| ... | ... | |
| 3180 | 3124 |
|
| 3181 | 3125 |
|
| 3182 | 3126 |
-- |
| 3183 |
-- Name: plotmethod_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3184 |
-- |
|
| 3185 |
|
|
| 3186 |
ALTER TABLE plotmethod |
|
| 3187 |
ADD CONSTRAINT plotmethod_pkey PRIMARY KEY (plotmethod_id); |
|
| 3188 |
|
|
| 3189 |
|
|
| 3190 |
-- |
|
| 3191 | 3127 |
-- Name: project_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3192 | 3128 |
-- |
| 3193 | 3129 |
|
| ... | ... | |
| 3304 | 3240 |
-- |
| 3305 | 3241 |
|
| 3306 | 3242 |
ALTER TABLE specimenreplicate |
| 3307 |
ADD CONSTRAINT specimenreplicate_keys_accessioncode UNIQUE (datasource_id, collectioncode_dwc, sourceaccessioncode);
|
|
| 3243 |
ADD CONSTRAINT specimenreplicate_keys_accessioncode UNIQUE (datasource_id, sourceaccessioncode); |
|
| 3308 | 3244 |
|
| 3309 | 3245 |
|
| 3310 | 3246 |
-- |
| ... | ... | |
| 3320 | 3256 |
-- |
| 3321 | 3257 |
|
| 3322 | 3258 |
ALTER TABLE specimenreplicate |
| 3323 |
ADD CONSTRAINT specimenreplicate_keys_collectionnumber UNIQUE (datasource_id, collectioncode_dwc, collectionnumber);
|
|
| 3259 |
ADD CONSTRAINT specimenreplicate_keys_collectionnumber UNIQUE (datasource_id, collectionnumber); |
|
| 3324 | 3260 |
|
| 3325 | 3261 |
|
| 3326 | 3262 |
-- |
| ... | ... | |
| 3847 | 3783 |
-- Name: fki_locationevent_method_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3848 | 3784 |
-- |
| 3849 | 3785 |
|
| 3850 |
CREATE INDEX fki_locationevent_method_id ON locationevent (plotmethod_id);
|
|
| 3786 |
CREATE INDEX fki_locationevent_method_id ON locationevent (method_id); |
|
| 3851 | 3787 |
|
| 3852 | 3788 |
|
| 3853 | 3789 |
-- |
| ... | ... | |
| 3900 | 3836 |
|
| 3901 | 3837 |
|
| 3902 | 3838 |
-- |
| 3903 |
-- Name: fki_plotmethod_reference_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3904 |
-- |
|
| 3905 |
|
|
| 3906 |
CREATE INDEX fki_plotmethod_reference_id ON plotmethod (reference_id); |
|
| 3907 |
|
|
| 3908 |
|
|
| 3909 |
-- |
|
| 3910 | 3839 |
-- Name: fki_project_datasource_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3911 | 3840 |
-- |
| 3912 | 3841 |
|
| ... | ... | |
| 4397 | 4326 |
|
| 4398 | 4327 |
|
| 4399 | 4328 |
-- |
| 4400 |
-- Name: plotmethod_accessioncode; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4401 |
-- |
|
| 4402 |
|
|
| 4403 |
CREATE UNIQUE INDEX plotmethod_accessioncode ON plotmethod (accessioncode); |
|
| 4404 |
|
|
| 4405 |
|
|
| 4406 |
-- |
|
| 4407 |
-- Name: plotmethod_keys; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4408 |
-- |
|
| 4409 |
|
|
| 4410 |
|
|
| 4411 |
|
|
| 4412 |
|
|
| 4413 |
-- |
|
| 4414 | 4329 |
-- Name: project_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4415 | 4330 |
-- |
| 4416 | 4331 |
|
| ... | ... | |
| 4959 | 4874 |
|
| 4960 | 4875 |
|
| 4961 | 4876 |
-- |
| 4962 |
-- Name: locationevent_parent_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4877 |
-- Name: locationevent_method_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4963 | 4878 |
-- |
| 4964 | 4879 |
|
| 4965 | 4880 |
ALTER TABLE locationevent |
| 4966 |
ADD CONSTRAINT locationevent_parent_id FOREIGN KEY (parent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4881 |
ADD CONSTRAINT locationevent_method_id FOREIGN KEY (method_id) REFERENCES method(method_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4967 | 4882 |
|
| 4968 | 4883 |
|
| 4969 | 4884 |
-- |
| 4970 |
-- Name: locationevent_plotmethod_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4885 |
-- Name: locationevent_parent_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4971 | 4886 |
-- |
| 4972 | 4887 |
|
| 4973 | 4888 |
ALTER TABLE locationevent |
| 4974 |
ADD CONSTRAINT locationevent_plotmethod_id FOREIGN KEY (plotmethod_id) REFERENCES plotmethod(plotmethod_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4889 |
ADD CONSTRAINT locationevent_parent_id FOREIGN KEY (parent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4975 | 4890 |
|
| 4976 | 4891 |
|
| 4977 | 4892 |
-- |
| ... | ... | |
| 5301 | 5216 |
|
| 5302 | 5217 |
|
| 5303 | 5218 |
-- |
| 5304 |
-- Name: plotmethod_method_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5305 |
-- |
|
| 5306 |
|
|
| 5307 |
ALTER TABLE plotmethod |
|
| 5308 |
ADD CONSTRAINT plotmethod_method_id FOREIGN KEY (method_id) REFERENCES method(method_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5309 |
|
|
| 5310 |
|
|
| 5311 |
-- |
|
| 5312 |
-- Name: plotmethod_reference_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5313 |
-- |
|
| 5314 |
|
|
| 5315 |
|
|
| 5316 |
|
|
| 5317 |
|
|
| 5318 |
-- |
|
| 5319 | 5219 |
-- Name: project_datasource_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5320 | 5220 |
-- |
| 5321 | 5221 |
|
| schemas/vegbien_empty.sql | ||
|---|---|---|
| 36 | 36 |
TRUNCATE plantobservation CASCADE; |
| 37 | 37 |
TRUNCATE plantstatus CASCADE; |
| 38 | 38 |
TRUNCATE plantusage CASCADE; |
| 39 |
TRUNCATE plotmethod CASCADE; |
|
| 40 | 39 |
TRUNCATE project CASCADE; |
| 41 | 40 |
TRUNCATE projectcontributor CASCADE; |
| 42 | 41 |
TRUNCATE reference CASCADE; |
| schemas/vegbien.sql | ||
|---|---|---|
| 851 | 851 |
sourceaccessioncode text, |
| 852 | 852 |
previous_id integer, |
| 853 | 853 |
dateaccuracy text, |
| 854 |
plotmethod_id integer,
|
|
| 854 |
method_id integer, |
|
| 855 | 855 |
autotaxoncover boolean, |
| 856 | 856 |
originaldata text, |
| 857 | 857 |
effortlevel text, |
| ... | ... | |
| 1036 | 1036 |
covermethod_id integer, |
| 1037 | 1037 |
samplingfactor double precision DEFAULT 1 NOT NULL, |
| 1038 | 1038 |
coverbasis text, |
| 1039 |
stemsamplemethod text |
|
| 1039 |
stemsamplemethod text, |
|
| 1040 |
shape text, |
|
| 1041 |
length double precision, |
|
| 1042 |
width double precision, |
|
| 1043 |
radius double precision, |
|
| 1044 |
area double precision, |
|
| 1045 |
samplearea double precision |
|
| 1040 | 1046 |
); |
| 1041 | 1047 |
|
| 1042 | 1048 |
|
| ... | ... | |
| 1174 | 1180 |
|
| 1175 | 1181 |
|
| 1176 | 1182 |
-- |
| 1183 |
-- Name: COLUMN method.shape; Type: COMMENT; Schema: public; Owner: - |
|
| 1184 |
-- |
|
| 1185 |
|
|
| 1186 |
COMMENT ON COLUMN method.shape IS 'e.g. square, rectangle, circle, line, point, other'; |
|
| 1187 |
|
|
| 1188 |
|
|
| 1189 |
-- |
|
| 1190 |
-- Name: COLUMN method.length; Type: COMMENT; Schema: public; Owner: - |
|
| 1191 |
-- |
|
| 1192 |
|
|
| 1193 |
COMMENT ON COLUMN method.length IS 'Units: lengthunits'; |
|
| 1194 |
|
|
| 1195 |
|
|
| 1196 |
-- |
|
| 1197 |
-- Name: COLUMN method.width; Type: COMMENT; Schema: public; Owner: - |
|
| 1198 |
-- |
|
| 1199 |
|
|
| 1200 |
COMMENT ON COLUMN method.width IS 'Units: lengthunits'; |
|
| 1201 |
|
|
| 1202 |
|
|
| 1203 |
-- |
|
| 1204 |
-- Name: COLUMN method.radius; Type: COMMENT; Schema: public; Owner: - |
|
| 1205 |
-- |
|
| 1206 |
|
|
| 1207 |
COMMENT ON COLUMN method.radius IS 'Units: lengthunits'; |
|
| 1208 |
|
|
| 1209 |
|
|
| 1210 |
-- |
|
| 1211 |
-- Name: COLUMN method.area; Type: COMMENT; Schema: public; Owner: - |
|
| 1212 |
-- |
|
| 1213 |
|
|
| 1214 |
COMMENT ON COLUMN method.area IS 'Units: lengthunits^2'; |
|
| 1215 |
|
|
| 1216 |
|
|
| 1217 |
-- |
|
| 1218 |
-- Name: COLUMN method.samplearea; Type: COMMENT; Schema: public; Owner: - |
|
| 1219 |
-- |
|
| 1220 |
|
|
| 1221 |
COMMENT ON COLUMN method.samplearea IS 'The total surface area used for cover estimates and for which a complete species list is provided. If subplots were used, this would be the total area of the subplots without interstitial space. |
|
| 1222 |
|
|
| 1223 |
Units: lengthunits^2'; |
|
| 1224 |
|
|
| 1225 |
|
|
| 1226 |
-- |
|
| 1177 | 1227 |
-- Name: method_method_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| 1178 | 1228 |
-- |
| 1179 | 1229 |
|
| ... | ... | |
| 1723 | 1773 |
|
| 1724 | 1774 |
|
| 1725 | 1775 |
-- |
| 1726 |
-- Name: plotmethod; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1727 |
-- |
|
| 1728 |
|
|
| 1729 |
CREATE TABLE plotmethod ( |
|
| 1730 |
plotmethod_id integer NOT NULL, |
|
| 1731 |
reference_id integer, |
|
| 1732 |
name text NOT NULL, |
|
| 1733 |
description text, |
|
| 1734 |
accessioncode text, |
|
| 1735 |
method_id integer, |
|
| 1736 |
shape text, |
|
| 1737 |
length double precision, |
|
| 1738 |
width double precision, |
|
| 1739 |
radius double precision, |
|
| 1740 |
area double precision, |
|
| 1741 |
samplearea double precision |
|
| 1742 |
); |
|
| 1743 |
|
|
| 1744 |
|
|
| 1745 |
-- |
|
| 1746 |
-- Name: TABLE plotmethod; Type: COMMENT; Schema: public; Owner: - |
|
| 1747 |
-- |
|
| 1748 |
|
|
| 1749 |
COMMENT ON TABLE plotmethod IS 'A method for delineating and sampling plots.'; |
|
| 1750 |
|
|
| 1751 |
|
|
| 1752 |
-- |
|
| 1753 |
-- Name: COLUMN plotmethod.name; Type: COMMENT; Schema: public; Owner: - |
|
| 1754 |
-- |
|
| 1755 |
|
|
| 1756 |
COMMENT ON COLUMN plotmethod.name IS 'A short name for the set of methods used for the plot. Although there is no existing standard, many names are widely used, and could be useful for finding plots with similar methodology. |
|
| 1757 |
|
|
| 1758 |
e.g. "1 ha treeplot", "0.1 ha transect", "line-intercept plot", "point centered quarter"'; |
|
| 1759 |
|
|
| 1760 |
|
|
| 1761 |
-- |
|
| 1762 |
-- Name: COLUMN plotmethod.description; Type: COMMENT; Schema: public; Owner: - |
|
| 1763 |
-- |
|
| 1764 |
|
|
| 1765 |
COMMENT ON COLUMN plotmethod.description IS 'Additional metadata helpful for understanding how the data were collected during the observation event.'; |
|
| 1766 |
|
|
| 1767 |
|
|
| 1768 |
-- |
|
| 1769 |
-- Name: COLUMN plotmethod.shape; Type: COMMENT; Schema: public; Owner: - |
|
| 1770 |
-- |
|
| 1771 |
|
|
| 1772 |
COMMENT ON COLUMN plotmethod.shape IS 'e.g. square, rectangle, circle, line, point, other'; |
|
| 1773 |
|
|
| 1774 |
|
|
| 1775 |
-- |
|
| 1776 |
-- Name: COLUMN plotmethod.length; Type: COMMENT; Schema: public; Owner: - |
|
| 1777 |
-- |
|
| 1778 |
|
|
| 1779 |
COMMENT ON COLUMN plotmethod.length IS 'Units: lengthunits'; |
|
| 1780 |
|
|
| 1781 |
|
|
| 1782 |
-- |
|
| 1783 |
-- Name: COLUMN plotmethod.width; Type: COMMENT; Schema: public; Owner: - |
|
| 1784 |
-- |
|
| 1785 |
|
|
| 1786 |
COMMENT ON COLUMN plotmethod.width IS 'Units: lengthunits'; |
|
| 1787 |
|
|
| 1788 |
|
|
| 1789 |
-- |
|
| 1790 |
-- Name: COLUMN plotmethod.radius; Type: COMMENT; Schema: public; Owner: - |
|
| 1791 |
-- |
|
| 1792 |
|
|
| 1793 |
COMMENT ON COLUMN plotmethod.radius IS 'Units: lengthunits'; |
|
| 1794 |
|
|
| 1795 |
|
|
| 1796 |
-- |
|
| 1797 |
-- Name: COLUMN plotmethod.area; Type: COMMENT; Schema: public; Owner: - |
|
| 1798 |
-- |
|
| 1799 |
|
|
| 1800 |
COMMENT ON COLUMN plotmethod.area IS 'Units: lengthunits^2'; |
|
| 1801 |
|
|
| 1802 |
|
|
| 1803 |
-- |
|
| 1804 |
-- Name: COLUMN plotmethod.samplearea; Type: COMMENT; Schema: public; Owner: - |
|
| 1805 |
-- |
|
| 1806 |
|
|
| 1807 |
COMMENT ON COLUMN plotmethod.samplearea IS 'The total surface area used for cover estimates and for which a complete species list is provided. If subplots were used, this would be the total area of the subplots without interstitial space. |
|
| 1808 |
|
|
| 1809 |
Units: lengthunits^2'; |
|
| 1810 |
|
|
| 1811 |
|
|
| 1812 |
-- |
|
| 1813 |
-- Name: plotmethod_plotmethod_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 1814 |
-- |
|
| 1815 |
|
|
| 1816 |
CREATE SEQUENCE plotmethod_plotmethod_id_seq |
|
| 1817 |
START WITH 1 |
|
| 1818 |
INCREMENT BY 1 |
|
| 1819 |
NO MINVALUE |
|
| 1820 |
NO MAXVALUE |
|
| 1821 |
CACHE 1; |
|
| 1822 |
|
|
| 1823 |
|
|
| 1824 |
-- |
|
| 1825 |
-- Name: plotmethod_plotmethod_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 1826 |
-- |
|
| 1827 |
|
|
| 1828 |
ALTER SEQUENCE plotmethod_plotmethod_id_seq OWNED BY plotmethod.plotmethod_id; |
|
| 1829 |
|
|
| 1830 |
|
|
| 1831 |
-- |
|
| 1832 | 1776 |
-- Name: project; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1833 | 1777 |
-- |
| 1834 | 1778 |
|
| ... | ... | |
| 2973 | 2917 |
|
| 2974 | 2918 |
|
| 2975 | 2919 |
-- |
| 2976 |
-- Name: plotmethod_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 2977 |
-- |
|
| 2978 |
|
|
| 2979 |
ALTER TABLE plotmethod ALTER COLUMN plotmethod_id SET DEFAULT nextval('plotmethod_plotmethod_id_seq'::regclass);
|
|
| 2980 |
|
|
| 2981 |
|
|
| 2982 |
-- |
|
| 2983 | 2920 |
-- Name: project_id; Type: DEFAULT; Schema: public; Owner: - |
| 2984 | 2921 |
-- |
| 2985 | 2922 |
|
| ... | ... | |
| 3541 | 3478 |
|
| 3542 | 3479 |
|
| 3543 | 3480 |
-- |
| 3544 |
-- Name: plotmethod_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3545 |
-- |
|
| 3546 |
|
|
| 3547 |
ALTER TABLE ONLY plotmethod |
|
| 3548 |
ADD CONSTRAINT plotmethod_pkey PRIMARY KEY (plotmethod_id); |
|
| 3549 |
|
|
| 3550 |
|
|
| 3551 |
-- |
|
| 3552 | 3481 |
-- Name: project_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3553 | 3482 |
-- |
| 3554 | 3483 |
|
| ... | ... | |
| 3665 | 3594 |
-- |
| 3666 | 3595 |
|
| 3667 | 3596 |
ALTER TABLE ONLY specimenreplicate |
| 3668 |
ADD CONSTRAINT specimenreplicate_keys_accessioncode UNIQUE (datasource_id, collectioncode_dwc, sourceaccessioncode);
|
|
| 3597 |
ADD CONSTRAINT specimenreplicate_keys_accessioncode UNIQUE (datasource_id, sourceaccessioncode); |
|
| 3669 | 3598 |
|
| 3670 | 3599 |
|
| 3671 | 3600 |
-- |
| ... | ... | |
| 3681 | 3610 |
-- |
| 3682 | 3611 |
|
| 3683 | 3612 |
ALTER TABLE ONLY specimenreplicate |
| 3684 |
ADD CONSTRAINT specimenreplicate_keys_collectionnumber UNIQUE (datasource_id, collectioncode_dwc, collectionnumber);
|
|
| 3613 |
ADD CONSTRAINT specimenreplicate_keys_collectionnumber UNIQUE (datasource_id, collectionnumber); |
|
| 3685 | 3614 |
|
| 3686 | 3615 |
|
| 3687 | 3616 |
-- |
| ... | ... | |
| 4208 | 4137 |
-- Name: fki_locationevent_method_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4209 | 4138 |
-- |
| 4210 | 4139 |
|
| 4211 |
CREATE INDEX fki_locationevent_method_id ON locationevent USING btree (plotmethod_id);
|
|
| 4140 |
CREATE INDEX fki_locationevent_method_id ON locationevent USING btree (method_id); |
|
| 4212 | 4141 |
|
| 4213 | 4142 |
|
| 4214 | 4143 |
-- |
| ... | ... | |
| 4261 | 4190 |
|
| 4262 | 4191 |
|
| 4263 | 4192 |
-- |
| 4264 |
-- Name: fki_plotmethod_reference_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4265 |
-- |
|
| 4266 |
|
|
| 4267 |
CREATE INDEX fki_plotmethod_reference_id ON plotmethod USING btree (reference_id); |
|
| 4268 |
|
|
| 4269 |
|
|
| 4270 |
-- |
|
| 4271 | 4193 |
-- Name: fki_project_datasource_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4272 | 4194 |
-- |
| 4273 | 4195 |
|
| ... | ... | |
| 4758 | 4680 |
|
| 4759 | 4681 |
|
| 4760 | 4682 |
-- |
| 4761 |
-- Name: plotmethod_accessioncode; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4762 |
-- |
|
| 4763 |
|
|
| 4764 |
CREATE UNIQUE INDEX plotmethod_accessioncode ON plotmethod USING btree (accessioncode); |
|
| 4765 |
|
|
| 4766 |
|
|
| 4767 |
-- |
|
| 4768 |
-- Name: plotmethod_keys; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4769 |
-- |
|
| 4770 |
|
|
| 4771 |
CREATE UNIQUE INDEX plotmethod_keys ON plotmethod USING btree (name, (COALESCE(description, ''::text))); |
|
| 4772 |
|
|
| 4773 |
|
|
| 4774 |
-- |
|
| 4775 | 4683 |
-- Name: project_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4776 | 4684 |
-- |
| 4777 | 4685 |
|
| ... | ... | |
| 5331 | 5239 |
|
| 5332 | 5240 |
|
| 5333 | 5241 |
-- |
| 5334 |
-- Name: locationevent_parent_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5242 |
-- Name: locationevent_method_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5335 | 5243 |
-- |
| 5336 | 5244 |
|
| 5337 | 5245 |
ALTER TABLE ONLY locationevent |
| 5338 |
ADD CONSTRAINT locationevent_parent_id FOREIGN KEY (parent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5246 |
ADD CONSTRAINT locationevent_method_id FOREIGN KEY (method_id) REFERENCES method(method_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5339 | 5247 |
|
| 5340 | 5248 |
|
| 5341 | 5249 |
-- |
| 5342 |
-- Name: locationevent_plotmethod_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5250 |
-- Name: locationevent_parent_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5343 | 5251 |
-- |
| 5344 | 5252 |
|
| 5345 | 5253 |
ALTER TABLE ONLY locationevent |
| 5346 |
ADD CONSTRAINT locationevent_plotmethod_id FOREIGN KEY (plotmethod_id) REFERENCES plotmethod(plotmethod_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5254 |
ADD CONSTRAINT locationevent_parent_id FOREIGN KEY (parent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5347 | 5255 |
|
| 5348 | 5256 |
|
| 5349 | 5257 |
-- |
| ... | ... | |
| 5683 | 5591 |
|
| 5684 | 5592 |
|
| 5685 | 5593 |
-- |
| 5686 |
-- Name: plotmethod_method_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5687 |
-- |
|
| 5688 |
|
|
| 5689 |
ALTER TABLE ONLY plotmethod |
|
| 5690 |
ADD CONSTRAINT plotmethod_method_id FOREIGN KEY (method_id) REFERENCES method(method_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5691 |
|
|
| 5692 |
|
|
| 5693 |
-- |
|
| 5694 |
-- Name: plotmethod_reference_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5695 |
-- |
|
| 5696 |
|
|
| 5697 |
ALTER TABLE ONLY plotmethod |
|
| 5698 |
ADD CONSTRAINT plotmethod_reference_id FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5699 |
|
|
| 5700 |
|
|
| 5701 |
-- |
|
| 5702 | 5594 |
-- Name: project_datasource_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5703 | 5595 |
-- |
| 5704 | 5596 |
|
| mappings/VegX-VegBIEN.plots.csv | ||
|---|---|---|
| 14 | 14 |
/*UniqueIdentifierID->/*s/plot/geospatial/minimumElevationInMeters,"/*_id/location/{elevation/_alt/2/_avg/min,elevationrange/_range/from}",
|
| 15 | 15 |
/*UniqueIdentifierID->/*s/plot/geospatial/DecimalLatitude,"/*_id/location/{locationdetermination[!namedplace_id]/latitude,centerlatitude}",
|
| 16 | 16 |
/*UniqueIdentifierID->/*s/plot/geospatial/DecimalLongitude,"/*_id/location/{locationdetermination[!namedplace_id]/longitude,centerlongitude}",
|
| 17 |
/simpleUserdefined[name=plotMethodology]/value,/*_id/plotmethod/name,
|
|
| 17 |
/simpleUserdefined[name=plotMethodology]/value,/*_id/method/name, |
|
| 18 | 18 |
/*ID->/*s/project/personnel[role=metadataProvider]/organizationName,/*_id/project:[datasource_id/party/organizationname/_alt/2=$/_ignore/inLabel]/datasource_id/party/organizationname/_alt/1, |
| 19 | 19 |
/*ID->/*s/project:[personnel[role=metadataProvider]/organizationName=$/_ignore/inLabel]/title,/*_id/project:[datasource_id/party/organizationname/_alt/2=$/_ignore/inLabel]/projectname, |
| 20 | 20 |
->/*s/communityDetermination[*ID]/*s/communityConcept/commonName,/commclass/commdetermination/*_id/commconcept/*_id/*/commname, |
| mappings/VegX-VegBIEN.organisms.csv | ||
|---|---|---|
| 14 | 14 |
/*ID->/*s/plotObservation/*UniqueIdentifierID->/*s/plot/geospatial/minimumElevationInMeters,"/*_id/locationevent/*_id/location/{elevation/_alt/2/_avg/min,elevationrange/_range/from}",
|
| 15 | 15 |
/*ID->/*s/plotObservation/*UniqueIdentifierID->/*s/plot/geospatial/DecimalLatitude,"/*_id/locationevent/*_id/location/{locationdetermination[!namedplace_id]/latitude,centerlatitude}",
|
| 16 | 16 |
/*ID->/*s/plotObservation/*UniqueIdentifierID->/*s/plot/geospatial/DecimalLongitude,"/*_id/locationevent/*_id/location/{locationdetermination[!namedplace_id]/longitude,centerlongitude}",
|
| 17 |
/*ID->/*s/plotObservation/simpleUserdefined[name=plotMethodology]/value,/*_id/locationevent/*_id/plotmethod/name,
|
|
| 17 |
/*ID->/*s/plotObservation/simpleUserdefined[name=plotMethodology]/value,/*_id/locationevent/*_id/method/name, |
|
| 18 | 18 |
/*ID->/*s/plotObservation/*ID->/*s/project/personnel[role=metadataProvider]/organizationName,/*_id/locationevent/*_id/project:[datasource_id/party/organizationname/_alt/2=$/_ignore/inLabel]/datasource_id/party/organizationname/_alt/1, |
| 19 | 19 |
/*ID->/*s/plotObservation/*ID->/*s/project:[personnel[role=metadataProvider]/organizationName=$/_ignore/inLabel]/title,/*_id/locationevent/*_id/project:[datasource_id/party/organizationname/_alt/2=$/_ignore/inLabel]/projectname, |
| 20 | 20 |
/*ID->/*s/plotObservation->/*s/communityDetermination[*ID]/*s/communityConcept/commonName,/*_id/locationevent/commclass/commdetermination/*_id/commconcept/*_id/*/commname, |
| mappings/for_review/VegX-VegBIEN.plots.csv | ||
|---|---|---|
| 14 | 14 |
//geospatial/minimumElevationInMeters,"/*_id/location/{elevation/_alt/2/_avg/min,elevationrange/_range/from}",
|
| 15 | 15 |
//geospatial/DecimalLatitude,"/*_id/location/{locationdetermination[]/latitude,centerlatitude}",
|
| 16 | 16 |
//geospatial/DecimalLongitude,"/*_id/location/{locationdetermination[]/longitude,centerlongitude}",
|
| 17 |
/simpleUserdefined[name=plotMethodology]/value,//plotmethod/name,
|
|
| 17 |
/simpleUserdefined[name=plotMethodology]/value,//method/name, |
|
| 18 | 18 |
//project/personnel[role=metadataProvider]/organizationName,//party/organizationname/_alt/1, |
| 19 | 19 |
//*s/project:[personnel[role=metadataProvider]/organizationName=$/_ignore/inLabel]/title,//project/projectname, |
| 20 | 20 |
//*s/communityDetermination/*s/communityConcept/commonName,//*/commname, |
| mappings/for_review/VegX-VegBIEN.organisms.csv | ||
|---|---|---|
| 14 | 14 |
//geospatial/minimumElevationInMeters,"/*_id/locationevent/*_id/location/{elevation/_alt/2/_avg/min,elevationrange/_range/from}",
|
| 15 | 15 |
//geospatial/DecimalLatitude,"/*_id/locationevent/*_id/location/{locationdetermination[]/latitude,centerlatitude}",
|
| 16 | 16 |
//geospatial/DecimalLongitude,"/*_id/locationevent/*_id/location/{locationdetermination[]/longitude,centerlongitude}",
|
| 17 |
//plotObservation/simpleUserdefined[name=plotMethodology]/value,//plotmethod/name,
|
|
| 17 |
//plotObservation/simpleUserdefined[name=plotMethodology]/value,//method/name, |
|
| 18 | 18 |
//project/personnel[role=metadataProvider]/organizationName,//party/organizationname/_alt/1, |
| 19 | 19 |
//*s/project:[personnel[role=metadataProvider]/organizationName=$/_ignore/inLabel]/title,//project/projectname, |
| 20 | 20 |
//*s/communityDetermination/*s/communityConcept/commonName,//*/commname, |
Also available in: Unified diff
VegBIEN: Moved plotmethod fields to method because they can also apply to strata. Removed no longer used plotmethod table.