Project

General

Profile

« Previous | Next » 

Revision 11346

Added by Paul Sarando about 11 years ago

Added indexes to speed up geonames-to-gadm.sql.

Without these indexes, these queries could take hours to complete.
With them, the times more closely matched the times Jim noted in the sql
comments.

View differences:

derived/biengeo/geonames-to-gadm.sql
42 42 
-- UPDATE 248
43 43 
-- Time: 3562.601 ms
44 44 

  		




  
  45
  
    
CREATE INDEX "gadm_country_lookup_name_0_idx" ON gadm_country_lookup (name_0);


  		




  
  46
  
    

  		




  45
  47
  
    
------------------------------


  		




  46
  48
  
    
-- Level 1 (State/Province) --


  		




  47
  49
  
    
------------------------------


  		












  

    
      derived/biengeo/geonames.sh
    
  





  59
  59
  
    
psql -c "COPY geonames FROM '${DATADIR}/allCountries.txt' DELIMITER E'\t' NULL ''" geoscrub


  		




  60
  60
  
    
psql -c \ "SELECT setval('geonames_geonameid_seq', (SELECT max(geonameid) FROM geonames))" geoscrub


  		




  61
  61
  
    
psql -c \ "CREATE INDEX geonames_countrycode_idx ON geonames (countrycode)" geoscrub


  		




  
  62
  
    
psql -c "CREATE INDEX geonames_featurecode_idx ON geonames (featurecode)" geoscrub


  		




  
  63
  
    
psql -c "CREATE INDEX geonames_name_idx ON geonames (name)" geoscrub


  		




  62
  64
  
    

  		




  63
  65
  
    
#


  		




  64
  66
  
    
# load geonames.org alternate names


  		




  ......




  87
  89
  
    
# index alternatenames


  		




  88
  90
  
    
psql -c 'CREATE INDEX "alternatenames_alternatename_idx" ON alternateNames (alternateName)' geoscrub


  		




  89
  91
  
    
# 10m42.827s


  		




  
  92
  
    
psql -c 'CREATE INDEX "alternatenames_geonameid_idx" ON alternateNames (geonameid)' geoscrub


  		




  90
  93
  
    

  		




  91
  94
  
    
#


  		




  92
  95
  
    
# load geonames.org country info


  		




  ......




  135
  138
  
    

  		




  136
  139
  
    
# import geonames country table


  		




  137
  140
  
    
psql -c "COPY hierarchy FROM '${DATADIR}/hierarchy.txt' DELIMITER E'\t' NULL ''" geoscrub


  		




  
  141
  
    
psql -c "CREATE INDEX hierarchy_parentid_idx ON hierarchy (parentId)" geoscrub


  		




  
  142
  
    
psql -c "CREATE INDEX hierarchy_childid_idx ON hierarchy (childId)" geoscrub


  		




  138
  143
  
    

  		




  139
  144
  
    
#


  		




  140
  145
  
    
# insert additional custom name-scrub mappings


  		












Also available in:  Unified diff