From 84be3493c00ac4541c8fe5a37502728ca6daf19c Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Sat, 14 Oct 2017 14:07:25 +0000 Subject: [PATCH] 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 git-svn-id: http://svn.osgeo.org/postgis/trunk@15984 b70326c6-7e19-0410-871a-916f4a2858ee --- extras/tiger_geocoder/tiger_loader_2017.sql | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) diff --git a/extras/tiger_geocoder/tiger_loader_2017.sql b/extras/tiger_geocoder/tiger_loader_2017.sql index a4fef4c8f..abdf81ebe 100644 --- a/extras/tiger_geocoder/tiger_loader_2017.sql +++ b/extras/tiger_geocoder/tiger_loader_2017.sql @@ -352,9 +352,9 @@ ${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_tlid_ ${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 "vacuum analyze ${data_schema}.${state_abbrev}_zip_state_loc;" ${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 @@ BEGIN 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); -- 2.50.1