Revision 10936
Added by Aaron Marcuse-Kubitza about 11 years ago
| inputs/VegBank/plot_/postprocess.sql | ||
|---|---|---|
| 1 | 1 |
SELECT util.search_path_append('util');
|
| 2 | 2 |
|
| 3 | 3 |
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY ("plot_id")$$); -- runtime: 0.2 s ("Time: 238.474 ms") @starscream
|
| 4 |
|
|
| 5 |
|
|
| 6 |
-- map_*() derived cols |
|
| 7 |
-- runtime: 2 min @starscream |
|
| 8 |
|
|
| 9 |
CREATE OR REPLACE FUNCTION map_access_rights(value integer) |
|
| 10 |
RETURNS text AS |
|
| 11 |
$BODY$ |
|
| 12 |
/* |
|
| 13 |
"closed List of Values: |
|
| 14 |
Value Description Sorting |
|
| 15 |
0 Public 1 |
|
| 16 |
1 1 km radius (nearest 0.01 degree) 2 |
|
| 17 |
2 10 km radius (nearest 0.1 degree) 3 |
|
| 18 |
3 100 km radius (nearest degree) 4 |
|
| 19 |
4 Location embargo 5 |
|
| 20 |
5 Public embargo on data 6 |
|
| 21 |
6 Full embargo on data 7 |
|
| 22 |
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription) |
|
| 23 |
*/ |
|
| 24 |
SELECT util._map('0=>public, 1=>public, 2=>public, 3=>public, 4=>public, 5=>"data by request", 6=>private', $1::text)
|
|
| 25 |
$BODY$ |
|
| 26 |
LANGUAGE sql IMMUTABLE |
|
| 27 |
COST 100; |
|
| 28 |
|
|
| 29 |
SELECT mk_derived_col((:table_str, 'accessRights'), $$map_access_rights("confidentialitystatus")$$); -- runtime: 1 s ("Time: 1270.464 ms") @starscream
|
|
| 30 |
|
|
| 31 |
CREATE OR REPLACE FUNCTION map_fuzzing(value integer) |
|
| 32 |
RETURNS double precision AS |
|
| 33 |
$BODY$ |
|
| 34 |
/* |
|
| 35 |
"closed List of Values: |
|
| 36 |
Value Description Sorting |
|
| 37 |
0 Public 1 |
|
| 38 |
1 1 km radius (nearest 0.01 degree) 2 |
|
| 39 |
2 10 km radius (nearest 0.1 degree) 3 |
|
| 40 |
3 100 km radius (nearest degree) 4 |
|
| 41 |
4 Location embargo 5 |
|
| 42 |
5 Public embargo on data 6 |
|
| 43 |
6 Full embargo on data 7 |
|
| 44 |
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription) |
|
| 45 |
*/ |
|
| 46 |
SELECT util._map('0=>NULL, 1=>1, 2=>10, 3=>100, 4=>NULL, 5=>NULL, 6=>NULL', $1::text)::double precision
|
|
| 47 |
$BODY$ |
|
| 48 |
LANGUAGE sql IMMUTABLE |
|
| 49 |
COST 100; |
|
| 50 |
|
|
| 51 |
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters__from_fuzzing'), $$map_fuzzing("confidentialitystatus")$$); -- runtime: 1 s ("Time: 1134.143 ms") @starscream
|
|
| inputs/VegBank/plot_/map.csv | ||
|---|---|---|
| 4 | 4 |
reference_id,*reference_id,, |
| 5 | 5 |
parent_id,parentLocationID,, |
| 6 | 6 |
locationaccuracy,coordinateUncertaintyInMeters,/_alt/2, |
| 7 |
confidentialitystatus,accessRights,"/_map:[./{0,1,2,3,4}=public,5=""data by request"",6=private]/value",Values defined at <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription¶ms=1581>
|
|
| 8 |
confidentialitystatus,coordinateUncertaintyInMeters,"/_alt/1/_km_to_m/value/_map:[1=1,2=10,3=100,./{0,4,5,6}=]/value",Values defined at <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription¶ms=1581>
|
|
| 7 |
confidentialitystatus,accessRights_verbatim,, |
|
| 9 | 8 |
confidentialityreason,*confidentialityreason,, |
| 10 | 9 |
latitude,decimalLatitude,, |
| 11 | 10 |
longitude,decimalLongitude,, |
| ... | ... | |
| 54 | 53 |
county,county,, |
| 55 | 54 |
quadrangle,*quadrangle,, |
| 56 | 55 |
Geographic Name,*Geographic Name,, |
| 56 |
accessRights,accessRights,, |
|
| 57 |
coordinateUncertaintyInMeters__from_fuzzing,coordinateUncertaintyInMeters,/_alt/1, |
|
| inputs/VegBank/plot_/VegBIEN.csv | ||
|---|---|---|
| 4 | 4 |
plot_id,"/location/_if[@name=""if subplot""]/else/sourceaccessioncode", |
| 5 | 5 |
authorplotcode,"/location/_if[@name=""if subplot""]/then/parent_id/location/authorlocationcode", |
| 6 | 6 |
plot_id,"/location/_if[@name=""if subplot""]/then/parent_id/location/sourceaccessioncode", |
| 7 |
confidentialitystatus,"/location/accesslevel/_map:[./{0,1,2,3,4}=public,5=""data by request"",6=private]/value",Values defined at <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription¶ms=1581>
|
|
| 7 |
accessRights,/location/accesslevel,
|
|
| 8 | 8 |
area,/location/area_m2,"""Total area of the plot in m2"" (area definition at <http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename>)" |
| 9 | 9 |
elevation,/location/elevation_m/_alt/1,"has only 5 decimal places of precision, with only 9s and random #s after that. ""The elevation of the plot origin in meters above sea level"" (elevation definition at <http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename>)" |
| 10 | 10 |
locationnarrative,/location/iscultivated/_or/2/_locationnarrative_is_cultivated/locationnarrative/_join/1, |
| ... | ... | |
| 14 | 14 |
authorplotcode,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/cond/_exists", |
| 15 | 15 |
plot_id,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/cond/_exists", |
| 16 | 16 |
locationnarrative,/location/locationnarrative/_merge/1, |
| 17 |
confidentialitystatus,"/location/locationplace/*_id/place/*_id/coordinates/coordsaccuracy_m/_noCV/value/_alt/1/_km_to_m/value/_map:[1=1,2=10,3=100,./{0,4,5,6}=]/value",Values defined at <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription¶ms=1581>
|
|
| 17 |
coordinateUncertaintyInMeters__from_fuzzing,/location/locationplace/*_id/place/*_id/coordinates/coordsaccuracy_m/_noCV/value/_alt/1,
|
|
| 18 | 18 |
locationaccuracy,/location/locationplace/*_id/place/*_id/coordinates/coordsaccuracy_m/_noCV/value/_alt/2, |
| 19 | 19 |
shape,/location/locationplace/*_id/place/*_id/coordinates/footprintgeometry_dwc, |
| 20 | 20 |
latitude,"/location/locationplace/*_id/place/*_id/coordinates/latitude_deg/_nullIf:[null=0,type=float]/value", |
| ... | ... | |
| 47 | 47 |
authorzone,,** No join mapping for *authorzone ** |
| 48 | 48 |
azimuth,,** No join mapping for *azimuth ** |
| 49 | 49 |
confidentialityreason,,** No join mapping for *confidentialityreason ** |
| 50 |
confidentialitystatus,,** No join mapping for accessRights_verbatim ** |
|
| 50 | 51 |
dateentered,,** No join mapping for *dateentered ** |
| 51 | 52 |
dsgpoly,,** No join mapping for *dsgpoly ** |
| 52 | 53 |
elevationaccuracy,,** No join mapping for *elevationaccuracy ** |
| inputs/VegBank/plot_/test.xml.ref | ||
|---|---|---|
| 9 | 9 |
<location> |
| 10 | 10 |
<authorlocationcode>$authorplotcode</authorlocationcode> |
| 11 | 11 |
<sourceaccessioncode>$plot_id</sourceaccessioncode> |
| 12 |
<accesslevel> |
|
| 13 |
<_map> |
|
| 14 |
<0>public</0> |
|
| 15 |
<1>public</1> |
|
| 16 |
<2>public</2> |
|
| 17 |
<3>public</3> |
|
| 18 |
<4>public</4> |
|
| 19 |
<5>data by request</5> |
|
| 20 |
<6>private</6> |
|
| 21 |
<value>$confidentialitystatus</value> |
|
| 22 |
</_map> |
|
| 23 |
</accesslevel> |
|
| 12 |
<accesslevel>$accessRights</accesslevel> |
|
| 24 | 13 |
<area_m2>$area</area_m2> |
| 25 | 14 |
<elevation_m>$elevation</elevation_m> |
| 26 | 15 |
<iscultivated><_locationnarrative_is_cultivated><locationnarrative>$locationnarrative</locationnarrative></_locationnarrative_is_cultivated></iscultivated> |
| ... | ... | |
| 35 | 24 |
<_noCV> |
| 36 | 25 |
<value> |
| 37 | 26 |
<_alt> |
| 38 |
<1> |
|
| 39 |
<_km_to_m> |
|
| 40 |
<value> |
|
| 41 |
<_map> |
|
| 42 |
<1>1</1> |
|
| 43 |
<2>10</2> |
|
| 44 |
<3>100</3> |
|
| 45 |
<0></0> |
|
| 46 |
<4></4> |
|
| 47 |
<5></5> |
|
| 48 |
<6></6> |
|
| 49 |
<value>$confidentialitystatus</value> |
|
| 50 |
</_map> |
|
| 51 |
</value> |
|
| 52 |
</_km_to_m> |
|
| 53 |
</1> |
|
| 27 |
<1>$coordinateUncertaintyInMeters__from_fuzzing</1> |
|
| 54 | 28 |
<2>$locationaccuracy</2> |
| 55 | 29 |
</_alt> |
| 56 | 30 |
</value> |
| inputs/VegBank/plot_/new_terms.csv | ||
|---|---|---|
| 1 | 1 |
parent_id,parentLocationID,, |
| 2 | 2 |
locationaccuracy,coordinateUncertaintyInMeters,/_alt/2, |
| 3 |
confidentialitystatus,accessRights,"/_map:[./{0,1,2,3,4}=public,5=""data by request"",6=private]/value",Values defined at <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription¶ms=1581>
|
|
| 4 |
confidentialitystatus,coordinateUncertaintyInMeters,"/_alt/1/_km_to_m/value/_map:[1=1,2=10,3=100,./{0,4,5,6}=]/value",Values defined at <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription¶ms=1581>
|
|
| 3 |
confidentialitystatus,accessRights_verbatim,, |
|
| 5 | 4 |
shape,footprintWKT,, |
| 6 | 5 |
area|country|territory,DUPLICATE#of:country#area|country|territory,, |
| 7 | 6 |
region|state|province,DUPLICATE#of:stateprovince#region|state|province,, |
| 7 |
coordinateUncertaintyInMeters__from_fuzzing,coordinateUncertaintyInMeters,/_alt/1, |
|
| inputs/VegBank/plot_/unmapped_terms.csv | ||
|---|---|---|
| 1 | 1 |
*reference_id |
| 2 |
accessRights_verbatim |
|
| 2 | 3 |
*confidentialityreason |
| 3 | 4 |
*authore |
| 4 | 5 |
*authorn |
Also available in: Unified diff
inputs/VegBank/plot_/: translated single-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns