[postgis-tickets] r15984 - Fix tiger 2017 edges load issue by doing following:

[Regina Obe]

Author: robe
Date: 2017-10-14 07:07:25 -0700 (Sat, 14 Oct 2017)
New Revision: 15984

Modified:
   trunk/extras/tiger_geocoder/tiger_loader_2017.sql
Log:
Fix tiger 2017 edges load issue by doing following:
 insert to be ordered by column name instead of ordinal_position.  
  Exclude divroad from edges load which now appears to be no longer present in 2017 edges data.

References #3815 for trunk PostGIS 2.5.0

Modified: trunk/extras/tiger_geocoder/tiger_loader_2017.sql
===================================================================
--- trunk/extras/tiger_geocoder/tiger_loader_2017.sql	2017-10-14 08:08:15 UTC (rev 15983)
+++ trunk/extras/tiger_geocoder/tiger_loader_2017.sql	2017-10-14 14:07:25 UTC (rev 15984)
@@ -352,9 +352,9 @@
 ${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');"
 ${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"', ARRAY['gid','statefp']);
 
-INSERT INTO loader_lookuptables(process_order, lookup_name, table_name, load, level_county, level_state, single_geom_mode, insert_mode, pre_load_process, post_load_process )
+INSERT INTO loader_lookuptables(process_order, lookup_name, table_name, load, level_county, level_state, single_geom_mode, insert_mode, pre_load_process, post_load_process, columns_exclude )
 VALUES(8, 'edges', 'edges', true, true, false,false, 'a',
-'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${table_name}(CONSTRAINT pk_${state_abbrev}_${table_name} PRIMARY KEY (gid)) INHERITS(tiger.${table_name});" ',
+'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${table_name}(CONSTRAINT pk_${state_abbrev}_${table_name} PRIMARY KEY (gid)) INHERITS(tiger.${table_name});"',
 '${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${table_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');"
 ${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_tlid ON ${data_schema}.${state_abbrev}_${table_name} USING btree (tlid);"
 ${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}tfidr ON ${data_schema}.${state_abbrev}_${table_name} USING btree (tfidr);"
@@ -371,7 +371,7 @@
 ${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_zip_lookup_base(CONSTRAINT pk_${state_abbrev}_zip_state_loc_city PRIMARY KEY(zip,state, county, city, statefp)) INHERITS(tiger.zip_lookup_base);"
 ${psql} -c "INSERT INTO ${data_schema}.${state_abbrev}_zip_lookup_base(zip,state,county,city, statefp) SELECT DISTINCT e.zipl, ''${state_abbrev}'', c.name,p.name,''${state_fips}''  FROM ${data_schema}.${state_abbrev}_edges AS e INNER JOIN tiger.county As c  ON (e.countyfp = c.countyfp AND e.statefp = c.statefp AND e.statefp = ''${state_fips}'') INNER JOIN ${data_schema}.${state_abbrev}_faces AS f ON (e.tfidl = f.tfid OR e.tfidr = f.tfid) INNER JOIN ${data_schema}.${state_abbrev}_place As p ON(f.statefp = p.statefp AND f.placefp = p.placefp ) WHERE e.zipl IS NOT NULL;"
 ${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_zip_lookup_base ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');"
-${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_zip_lookup_base_citysnd ON ${data_schema}.${state_abbrev}_zip_lookup_base USING btree(soundex(city));" ');
+${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_zip_lookup_base_citysnd ON ${data_schema}.${state_abbrev}_zip_lookup_base USING btree(soundex(city));"',  ARRAY['gid', 'geoid','divroad'] );
 
 INSERT INTO loader_lookuptables(process_order, lookup_name, table_name, load, level_county, level_state, single_geom_mode, insert_mode, pre_load_process, post_load_process,columns_exclude )
 VALUES(9, 'addr', 'addr', true, true, false,false, 'a',
@@ -506,13 +506,13 @@
 				 WHERE table_name = param_target_table
 					AND table_schema = var_data_schema
 					AND column_name <> ALL(param_columns_exclude)
-                    ORDER BY ordinal_position ), ',') || ') SELECT '
+                    ORDER BY column_name ), ',') || ') SELECT '
 					|| array_to_string(ARRAY(SELECT quote_ident(column_name::text)
 				FROM information_schema.columns
 				 WHERE table_name = param_staging_table
 					AND table_schema = var_staging_schema
 					AND column_name <> ALL( param_columns_exclude)
-                    ORDER BY ordinal_position ), ',') ||' FROM '
+                    ORDER BY column_name ), ',') ||' FROM '
 					|| var_staging_schema || '.' || param_staging_table || ';';
 	RAISE NOTICE '%', var_sql;
 	EXECUTE (var_sql);