Project

General

Profile

« Previous | Next » 

Revision 8165

Added by Aaron Marcuse-Kubitza almost 12 years ago

Added inputs/FIA/occurrence_all/, which combines all the core tables in a denormalized view. Note that it is not necessary to materialize this view into a (large) denormalized table, because the unique indexes and left/right joins allow the rows to be denormalized on the fly.

View differences:

inputs/FIA/import
11 11 
./TREE/import
12 12 
./REF_SPECIES/import
13 13 
./REF_PLANT_DICTIONARY/import
14 

  		




  
  15
  
    
./occurrence_all/import


  		












  

    
      inputs/FIA/occurrence_all/import
    
  





  
  1
  
    
#!/bin/bash


  		




  
  2
  
    
# :mode=transact-sql:


  		




  
  3
  
    
cd "$(dirname "$0")"


  		




  
  4
  
    
. ../../../lib/import.sh


  		




  
  5
  
    

  		




  
  6
  
    
psql <<'EOF'


  		




  
  7
  
    
DROP VIEW IF EXISTS occurrence_all;


  		




  
  8
  
    
CREATE OR REPLACE VIEW occurrence_all AS 


  		




  
  9
  
    
SELECT *


  		




  
  10
  
    
/* directional joins needed for PostgreSQL query planner to avoid slow sorts


  		




  
  11
  
    
(when LIMIT <= 1.3 million rows) */


  		




  
  12
  
    
FROM               ("REF_RESEARCH_STATION"


  		




  
  13
  
    
NATURAL RIGHT JOIN ("REF_UNIT"


  		




  
  14
  
    
NATURAL RIGHT JOIN ("COUNTY"


  		




  
  15
  
    
NATURAL RIGHT JOIN ("PLOT"


  		




  
  16
  
    
NATURAL RIGHT JOIN ("COND"


  		




  
  17
  
    
	NATURAL LEFT JOIN "REF_HABTYP_DESCRIPTION"


  		




  
  18
  
    
NATURAL RIGHT JOIN ("SUBPLOT"


  		




  
  19
  
    
NATURAL RIGHT JOIN ("TREE"


  		




  
  20
  
    
	NATURAL LEFT JOIN "REF_SPECIES"


  		




  
  21
  
    
	NATURAL LEFT JOIN "REF_PLANT_DICTIONARY"


  		




  
  22
  
    
)))))))


  		




  
  23
  
    
ORDER BY "*TREE.CN"


  		




  0
  24
  
    

  		












Also available in:  Unified diff