Project

General

Profile

« Previous | Next » 

Revision 11729

Added by Aaron Marcuse-Kubitza about 11 years ago

inputs/CVS/plot_/: translated column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#1-Translate-filters-to-postprocessing-derived-columns

View differences:

inputs/CVS/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 ("78.941 ms") @starscream


  		




  
  4
  
    

  		




  
  5
  
    

  		




  
  6
  
    
-- map_*() derived cols


  		




  
  7
  
    
-- runtime: 1 s @starscream


  		




  
  8
  
    

  		




  
  9
  
    
CREATE OR REPLACE FUNCTION map_access_rights(value integer)


  		




  
  10
  
    
  RETURNS text AS


  		




  
  11
  
    
$BODY$


  		




  
  12
  
    
/*


  		




  
  13
  
    
assuming values are the same as for VegBank:


  		




  
  14
  
    
"closed List of Values:


  		




  
  15
  
    
Value  Description                        Sorting


  		




  
  16
  
    
0      Public                             1


  		




  
  17
  
    
1      1 km radius (nearest 0.01 degree)  2


  		




  
  18
  
    
2      10 km radius (nearest 0.1 degree)  3


  		




  
  19
  
    
3      100 km radius (nearest degree)     4


  		




  
  20
  
    
4      Location embargo                   5


  		




  
  21
  
    
5      Public embargo on data             6


  		




  
  22
  
    
6      Full embargo on data               7


  		




  
  23
  
    
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription)


  		




  
  24
  
    
*/


  		




  
  25
  
    
SELECT util._map('0=>public, 1=>public, 2=>public, 3=>public, 4=>public, 5=>"data by request", 6=>private', $1::text)


  		




  
  26
  
    
$BODY$


  		




  
  27
  
    
  LANGUAGE sql IMMUTABLE


  		




  
  28
  
    
  COST 100;


  		




  
  29
  
    

  		




  
  30
  
    
SELECT mk_derived_col((:table_str, 'accessRights'), $$map_access_rights("confidentialityStatus")$$); -- runtime: 0.5 s ("292.078 ms") @starscream


  		




  
  31
  
    

  		




  
  32
  
    
CREATE OR REPLACE FUNCTION map_fuzzing(value integer)


  		




  
  33
  
    
  RETURNS double precision AS


  		




  
  34
  
    
$BODY$


  		




  
  35
  
    
/*


  		




  
  36
  
    
assuming values are the same as for VegBank:


  		




  
  37
  
    
"closed List of Values:


  		




  
  38
  
    
Value  Description                        Sorting


  		




  
  39
  
    
0      Public                             1


  		




  
  40
  
    
1      1 km radius (nearest 0.01 degree)  2


  		




  
  41
  
    
2      10 km radius (nearest 0.1 degree)  3


  		




  
  42
  
    
3      100 km radius (nearest degree)     4


  		




  
  43
  
    
4      Location embargo                   5


  		




  
  44
  
    
5      Public embargo on data             6


  		




  
  45
  
    
6      Full embargo on data               7


  		




  
  46
  
    
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription)


  		




  
  47
  
    
*/


  		




  
  48
  
    
SELECT public._km_to_m(util._map('0=>NULL, 1=>1, 2=>10, 3=>100, 4=>NULL, 5=>NULL, 6=>NULL', $1::text)::double precision)


  		




  
  49
  
    
$BODY$


  		




  
  50
  
    
  LANGUAGE sql IMMUTABLE


  		




  
  51
  
    
  COST 100;


  		




  
  52
  
    

  		




  
  53
  
    
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters__from_fuzzing'), $$map_fuzzing("confidentialityStatus")$$); -- runtime: 0.2 s ("199.359 ms") @starscream


  		




  
  54
  
    

  		




  
  55
  
    

  		




  
  56
  
    
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters'),


  		




  
  57
  
    
$$GREATEST("coordinateUncertaintyInMeters__from_fuzzing", "locationAccuracy")$$)


  		




  
  58
  
    
; -- runtime: 0.2 s ("172.425 ms") @starscream


  		












  

    
      inputs/CVS/plot_/map.csv
    
  





  7
  7
  
    
REPLACEMENTPLOT_ID,*REPLACEMENTPLOT_ID,,


  		




  8
  8
  
    
realLatitude,UNUSED#realLatitude,,


  		




  9
  9
  
    
realLongitude,UNUSED#realLongitude,,


  		




  10
  
  
    
locationAccuracy,coordinateUncertaintyInMeters,/_alt/2,"assuming units are the same as VegBank's corresponding field (""within this many meters of the reported location"", http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename&params=plot#locationAccuracy)"


  		




  11
  
  
    
confidentialityStatus,accessRights,"/_map:[./{0,1,2,3,4}=public,5=""data by request"",6=private]/value",assuming values are the same as VegBank's corresponding field (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription&params=1581)


  		




  12
  
  
    
confidentialityStatus,coordinateUncertaintyInMeters,"/_alt/1/_km_to_m/value/_map:[1=1,2=10,3=100,./{0,4,5,6}=]/value",assuming values are the same as VegBank's corresponding field (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription&params=1581)


  		




  
  10
  
    
locationAccuracy,coordinateUncertaintyInMeters__from_coords,,"assuming units are the same as VegBank's corresponding field (""within this many meters of the reported location"", http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename&params=plot#locationAccuracy)"


  		




  
  11
  
    
confidentialityStatus,accessRights_verbatim,,


  		




  13
  12
  
    
confidentialityFieldsBlocked,*confidentialityFieldsBlocked,,


  		




  14
  13
  
    
confidentialityReason,*confidentialityReason,,


  		




  15
  14
  
    
latitude,decimalLatitude,,


  		




  ......




  65
  64
  
    
quadrangle,*quadrangle,,


  		




  66
  65
  
    
Geographic Name,*Geographic Name,,


  		




  67
  66
  
    
River Basin,*River Basin,,


  		




  
  67
  
    
accessRights,accessRights,,


  		




  
  68
  
    
coordinateUncertaintyInMeters__from_fuzzing,coordinateUncertaintyInMeters__from_fuzzing,,


  		




  
  69
  
    
coordinateUncertaintyInMeters,coordinateUncertaintyInMeters,,


  		












  

    
      inputs/CVS/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",assuming values are the same as VegBank's corresponding field (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription&params=1581)


  		




  
  7
  
    
accessRights,/location/accesslevel,


  		




  8
  8
  
    
area,/location/area_m2,"assuming units are the same as VegBank's corresponding field (""Total area of the plot in m2"", http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename#area)"


  		




  9
  9
  
    
elevation,/location/elevation_m/_alt/1,"assuming units are the same as VegBank's corresponding field (""The elevation of the plot origin in meters above sea level"", http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename#elevation)"


  		




  10
  10
  
    
locationNarrative,/location/iscultivated/_or/2/_locationnarrative_is_cultivated/locationnarrative/_join/1,


  		




  ......




  14
  14
  
    
PLOT_ID,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/cond/_exists",


  		




  15
  15
  
    
authorPlotCode,"/location/locationevent/locationevent:[@fkey=parent_id]/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",assuming values are the same as VegBank's corresponding field (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription&params=1581)


  		




  18
  
  
    
locationAccuracy,/location/locationplace/*_id/place/*_id/coordinates/coordsaccuracy_m/_noCV/value/_alt/2,"assuming units are the same as VegBank's corresponding field (""within this many meters of the reported location"", http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename&params=plot#locationAccuracy)"


  		




  
  17
  
    
coordinateUncertaintyInMeters,/location/locationplace/*_id/place/*_id/coordinates/coordsaccuracy_m/_noCV/value,


  		




  19
  18
  
    
shape,/location/locationplace/*_id/place/*_id/coordinates/footprintgeometry_dwc,


  		




  20
  19
  
    
latitude,"/location/locationplace/*_id/place/*_id/coordinates/latitude_deg/_nullIf:[null=0,type=float]/value",


  		




  21
  20
  
    
longitude,"/location/locationplace/*_id/place/*_id/coordinates/longitude_deg/_nullIf:[null=0,type=float]/value",


  		




  ......




  57
  56
  
    
azimuth,,** No join mapping for *azimuth ** 


  		




  58
  57
  
    
confidentialityFieldsBlocked,,** No join mapping for *confidentialityFieldsBlocked ** 


  		




  59
  58
  
    
confidentialityReason,,** No join mapping for *confidentialityReason ** 


  		




  
  59
  
    
confidentialityStatus,,** No join mapping for accessRights_verbatim ** 


  		




  
  60
  
    
coordinateUncertaintyInMeters__from_fuzzing,,** No join mapping for coordinateUncertaintyInMeters__from_fuzzing ** 


  		




  60
  61
  
    
cust_cvs_province,,** No join mapping for *cust_cvs_province ** 


  		




  61
  62
  
    
cust_eep_reach,,** No join mapping for *cust_eep_reach ** 


  		




  62
  63
  
    
dateentered,,** No join mapping for *dateentered ** 


  		




  ......




  65
  66
  
    
elevationAccuracy,,** No join mapping for *elevationAccuracy ** 


  		




  66
  67
  
    
elevationRange,,** No non-empty join mapping for UNUSED#elevationRange ** 


  		




  67
  68
  
    
layoutNarrative,,** No join mapping for *layoutNarrative ** 


  		




  
  69
  
    
locationAccuracy,,"** No join mapping for coordinateUncertaintyInMeters__from_coords ** assuming units are the same as VegBank's corresponding field (""within this many meters of the reported location"", http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename&params=plot#locationAccuracy)"


  		




  68
  70
  
    
notesMgt,,** No join mapping for *notesMgt ** 


  		




  69
  71
  
    
notesPublic,,** No join mapping for *notesPublic ** 


  		




  70
  72
  
    
permanence,,** No join mapping for *permanence ** 


  		












  

    
      inputs/CVS/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>


  		




  ......




  31
  20
  
    
                                <place>


  		




  32
  21
  
    
                                    <coordinates_id>


  		




  33
  22
  
    
                                        <coordinates>


  		




  34
  
  
    
                                            <coordsaccuracy_m>


  		




  35
  
  
    
                                                <_noCV>


  		




  36
  
  
    
                                                    <value>


  		




  37
  
  
    
                                                        <_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>


  		




  54
  
  
    
                                                            <2>$locationAccuracy</2>


  		




  55
  
  
    
                                                        </_alt>


  		




  56
  
  
    
                                                    </value>


  		




  57
  
  
    
                                                </_noCV>


  		




  58
  
  
    
                                            </coordsaccuracy_m>


  		




  
  23
  
    
                                            <coordsaccuracy_m><_noCV><value>$coordinateUncertaintyInMeters</value></_noCV></coordsaccuracy_m>


  		




  59
  24
  
    
                                            <footprintgeometry_dwc>$shape</footprintgeometry_dwc>


  		




  60
  25
  
    
                                            <latitude_deg>


  		




  61
  26
  
    
                                                <_nullIf>


  		












  

    
      inputs/CVS/plot_/new_terms.csv
    
  





  1
  1
  
    
PARENT_ID,parentLocationID,,


  		




  2
  
  
    
locationAccuracy,coordinateUncertaintyInMeters,/_alt/2,"assuming units are the same as VegBank's corresponding field (""within this many meters of the reported location"", http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename&params=plot#locationAccuracy)"


  		




  3
  
  
    
confidentialityStatus,accessRights,"/_map:[./{0,1,2,3,4}=public,5=""data by request"",6=private]/value",assuming values are the same as VegBank's corresponding field (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription&params=1581)


  		




  4
  
  
    
confidentialityStatus,coordinateUncertaintyInMeters,"/_alt/1/_km_to_m/value/_map:[1=1,2=10,3=100,./{0,4,5,6}=]/value",assuming values are the same as VegBank's corresponding field (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription&params=1581)


  		




  
  2
  
    
locationAccuracy,coordinateUncertaintyInMeters__from_coords,,"assuming units are the same as VegBank's corresponding field (""within this many meters of the reported location"", http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=plot&entity=dba_tabledescription&where=where_tablename&params=plot#locationAccuracy)"


  		




  
  3
  
    
confidentialityStatus,accessRights_verbatim,,


  		




  5
  4
  
    
shape,footprintWKT,,


  		




  6
  5
  
    
area|country|territory,country,,


  		




  7
  6
  
    
region|state|province,stateProvince,,


  		












  

    
      inputs/CVS/plot_/unmapped_terms.csv
    
  





  1
  1
  
    
*reference_ID


  		




  2
  2
  
    
*plotObsolete


  		




  3
  3
  
    
*REPLACEMENTPLOT_ID


  		




  
  4
  
    
coordinateUncertaintyInMeters__from_coords


  		




  
  5
  
    
accessRights_verbatim


  		




  4
  6
  
    
*confidentialityFieldsBlocked


  		




  5
  7
  
    
*confidentialityReason


  		




  6
  8
  
    
*authorCoordinateType


  		




  ......




  35
  37
  
    
*quadrangle


  		




  36
  38
  
    
*Geographic Name


  		




  37
  39
  
    
*River Basin


  		




  
  40
  
    
coordinateUncertaintyInMeters__from_fuzzing


  		












Also available in:  Unified diff