Project

General

Profile

« Previous | Next » 

Revision 11738

Added by Aaron Marcuse-Kubitza about 11 years ago

inputs/CVS/stemCount_/: translated 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. note that the inserted row count changes, because there is now a primary key (which the table is auto-sorted by) where previously there was none.

View differences:

inputs/CVS/stemCount_/postprocess.sql
1 
SELECT util.search_path_append('util');
2 

  		




  
  3
  
    
SELECT create_if_not_exists($$CREATE INDEX $$||:table_str||%'__parent'||$$ ON $$||:table_str||$$ ("taxonObservation_ID")$$); -- runtime: 3 s ("3003.022 ms") @starscream


  		




  
  4
  
    

  		




  
  5
  
    

  		




  
  6
  
    
SELECT mk_derived_col((:table_str, 'aggregateOrganismObservationID'),


  		




  
  7
  
    
$$_join("taxonImportance_ID"::text, "STEMCOUNT_ID"::text)$$)


  		




  
  8
  
    
; -- runtime: 25 s ("26272.523 ms") @starscream


  		




  
  9
  
    

  		




  
  10
  
    

  		




  
  11
  
    
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY ("aggregateOrganismObservationID")$$); -- runtime: 30 s ("31202.934 ms") @starscream


  		












  

    
      inputs/CVS/stemCount_/map.csv
    
  





  1
  1
  
    
CVS,VegCore,Filter,Comments


  		




  2
  
  
    
taxonImportance_ID,aggregateOrganismObservationID,/_join/1,


  		




  
  2
  
    
taxonImportance_ID,aggregateOrganismObservationID__taxon_presence,,


  		




  3
  3
  
    
taxonObservation_ID,taxonOccurrenceID,,


  		




  4
  4
  
    
stratum_ID,*stratum_id,,


  		




  5
  5
  
    
cover,coverPercent,,


  		




  ......




  16
  16
  
    
cust_cvs_corner4Presence,*cust_cvs_corner4Presence,,


  		




  17
  17
  
    
cust_cvs_corner5Presence,*cust_cvs_corner5Presence,,


  		




  18
  18
  
    
cust_cvs_originalCoverCode,*cust_cvs_originalCoverCode,,


  		




  19
  
  
    
STEMCOUNT_ID,aggregateOrganismObservationID,/_join/2,


  		




  
  19
  
    
STEMCOUNT_ID,aggregateOrganismObservationID__individual_count,,


  		




  20
  20
  
    
stemDiameter,diameterBreastHeight_cm,,


  		




  21
  21
  
    
stemDiameterAccuracy,*stemdiameteraccuracy,,


  		




  22
  22
  
    
stemHeight,height_m,,


  		




  ......




  32
  32
  
    
stemCount_entry_treeLineID,*stemCount_entry_treeLineID,,


  		




  33
  33
  
    
entry_recordCount,*entry_recordCount,,


  		




  34
  34
  
    
entry_sortOrder,*entry_sortOrder,,


  		




  
  35
  
    
aggregateOrganismObservationID,aggregateOrganismObservationID,,


  		












  

    
      inputs/CVS/stemCount_/VegBIEN.csv
    
  





  4
  4
  
    
cover,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/aggregateoccurrence/cover_fraction/_percent_to_fraction/value,


  		




  5
  5
  
    
stemDiameter,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/aggregateoccurrence/plantobservation/stemobservation/diameterbreastheight_m/_cm_to_m/value,


  		




  6
  6
  
    
stemHeight,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/aggregateoccurrence/plantobservation/stemobservation/height_m,


  		




  7
  
  
    
taxonImportance_ID,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/aggregateoccurrence/sourceaccessioncode/_join/1,


  		




  8
  
  
    
STEMCOUNT_ID,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/aggregateoccurrence/sourceaccessioncode/_join/2,


  		




  
  7
  
    
aggregateOrganismObservationID,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/aggregateoccurrence/sourceaccessioncode,


  		




  9
  8
  
    
taxonObservation_ID,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/iscultivated/_alt/1/_if[@name=""if TaxonOccurrence""]/cond/_exists",


  		




  10
  9
  
    
taxonObservation_ID,/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/sourceaccessioncode/_first/1,


  		




  11
  
  
    
taxonImportance_ID,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/sourceaccessioncode/_first/2/_if[@name=""if organism in AggregateOccurrence""]/cond/_exists/_join/1",


  		




  12
  
  
    
STEMCOUNT_ID,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/sourceaccessioncode/_first/2/_if[@name=""if organism in AggregateOccurrence""]/cond/_exists/_join/2",


  		




  
  10
  
    
aggregateOrganismObservationID,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/sourceaccessioncode/_first/2/_if[@name=""if organism in AggregateOccurrence""]/cond/_exists",


  		




  13
  11
  
    
taxonObservation_ID,"/location/locationevent/locationevent:[@fkey=parent_id]/taxonoccurrence/taxondetermination[!isoriginal=true]/_if[@name=""if has taxondetermination""]/cond/_exists",


  		




  
  12
  
    
STEMCOUNT_ID,,** No join mapping for aggregateOrganismObservationID__individual_count ** 


  		




  14
  13
  
    
basalArea,,** No join mapping for *basalarea ** 


  		




  15
  14
  
    
biomass,,** No join mapping for *biomass ** 


  		




  16
  15
  
    
cust_cvs_StemFrequency,,** No join mapping for *cust_cvs_StemFrequency ** 


  		




  ......




  34
  33
  
    
stemHeightAccuracy,,** No join mapping for *stemheightaccuracy ** 


  		




  35
  34
  
    
stemTaxonArea,,** No join mapping for *stemtaxonarea ** 


  		




  36
  35
  
    
stratum_ID,,** No join mapping for *stratum_id ** 


  		




  
  36
  
    
taxonImportance_ID,,** No join mapping for aggregateOrganismObservationID__taxon_presence ** 


  		




  37
  37
  
    
taxonImportance_dba_src_ID,,** No join mapping for *taxonImportance_dba_src_ID ** 


  		




  38
  38
  
    
taxonImportance_entry_herbLineID,,** No join mapping for *taxonImportance_entry_herbLineID ** 


  		




  39
  39
  
    
taxonImportance_entry_treeLineID,,** No join mapping for *taxonImportance_entry_treeLineID ** 


  		












  

    
      inputs/CVS/stemCount_/test.xml.ref
    
  





  19
  19
  
    
                                                <height_m>$stemHeight</height_m>


  		




  20
  20
  
    
                                            </stemobservation>


  		




  21
  21
  
    
                                        </plantobservation>


  		




  22
  
  
    
                                        <sourceaccessioncode>


  		




  23
  
  
    
                                            <_join>


  		




  24
  
  
    
                                                <1>$taxonImportance_ID</1>


  		




  25
  
  
    
                                                <2>$STEMCOUNT_ID</2>


  		




  26
  
  
    
                                            </_join>


  		




  27
  
  
    
                                        </sourceaccessioncode>


  		




  
  22
  
    
                                        <sourceaccessioncode>$aggregateOrganismObservationID</sourceaccessioncode>


  		




  28
  23
  
    
                                    </aggregateoccurrence>


  		




  29
  24
  
    
                                    <sourceaccessioncode>$taxonObservation_ID</sourceaccessioncode>


  		




  30
  25
  
    
                                </taxonoccurrence>


  		




  ......




  36
  31
  
    
        </path>


  		




  37
  32
  
    
    </_setDefault>


  		




  38
  33
  
    
</VegBIEN>


  		




  39
  
  
    
Inserted 8 new rows into database


  		




  
  34
  
    
Inserted 7 new rows into database


  		












  

    
      inputs/CVS/stemCount_/new_terms.csv
    
  





  1
  
  
    
taxonImportance_ID,aggregateOrganismObservationID,/_join/1,


  		




  
  1
  
    
taxonImportance_ID,aggregateOrganismObservationID__taxon_presence,,


  		




  2
  2
  
    
cover,coverPercent,,


  		




  3
  
  
    
STEMCOUNT_ID,aggregateOrganismObservationID,/_join/2,


  		




  
  3
  
    
STEMCOUNT_ID,aggregateOrganismObservationID__individual_count,,


  		




  4
  4
  
    
stemDiameter,diameterBreastHeight_cm,,


  		












  

    
      inputs/CVS/stemCount_/unmapped_terms.csv
    
  





  
  1
  
    
aggregateOrganismObservationID__taxon_presence


  		




  1
  2
  
    
*stratum_id


  		




  2
  3
  
    
*basalarea


  		




  3
  4
  
    
*biomass


  		




  ......




  12
  13
  
    
*cust_cvs_corner4Presence


  		




  13
  14
  
    
*cust_cvs_corner5Presence


  		




  14
  15
  
    
*cust_cvs_originalCoverCode


  		




  
  16
  
    
aggregateOrganismObservationID__individual_count


  		




  15
  17
  
    
*stemdiameteraccuracy


  		




  16
  18
  
    
*stemheightaccuracy


  		




  17
  19
  
    
*stemtaxonarea


  		












Also available in:  Unified diff