[postgis-tickets] r15985 - Fix tiger 2017 zcta5 load logic, but leave default as turned off (takes 30 minutes to process)

[Regina Obe]

Author: robe
Date: 2017-10-15 09:33:59 -0700 (Sun, 15 Oct 2017)
New Revision: 15985

Modified:
   trunk/extras/tiger_geocoder/tiger_loader_2017.sql
Log:
Fix tiger 2017 zcta5 load logic, but leave default as turned off (takes 30 minutes to process)

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 14:07:25 UTC (rev 15984)
+++ trunk/extras/tiger_geocoder/tiger_loader_2017.sql	2017-10-15 16:33:59 UTC (rev 15985)
@@ -315,25 +315,13 @@
 
 INSERT INTO loader_lookuptables(process_order, lookup_name, table_name, load, level_county, level_state, level_nation, single_geom_mode, insert_mode, pre_load_process, post_load_process, columns_exclude )
 VALUES(13, 'zcta5_raw', 'zcta510', false,false, false,true, false, 'c',
-	'${psql} -c "CREATE TABLE ${data_schema}.${lookup_name}( zcta5 character varying(5),
-  classfp character varying(2),
-  mtfcc character varying(5),
-  funcstat character varying(1),
-  aland double precision,
-  awater double precision,
-  intptlat character varying(11),
-  intptlon character varying(12),
-  geom geometry(MultiPolygon,4269) );',
-$post_load$${psql} -c "ALTER TABLE tiger.zcta5 DROP CONSTRAINT IF EXISTS enforce_geotype_the_geom; CREATE TABLE ${data_schema}.zcta5_all(CONSTRAINT pk_zcta5_all PRIMARY KEY (zcta5ce,statefp), CONSTRAINT uidx_${lookup_name}_all_gid UNIQUE (gid)) INHERITS(tiger.${lookup_name});
-INSERT INTO ${data_schema}.zcta5_all(statefp, zcta5ce, classfp, mtfcc, funcstat, aland, awater, intptlat, intptlon, partflg, the_geom)
-SELECT  s.statefp, z.zcta5ce10,  z.classfp10, z.mtfcc10, z.funcstat10, z.aland10,
-       z.awater10, z.intptlat10, z.intptlon10, CASE WHEN ST_Covers(s.the_geom, z.geom) THEN 'N' ELSE 'Y' END, CASE WHEN ST_Covers(s.the_geom, z.geom) THEN z.geom ELSE ST_Intersection(z.geom, s.the_geom)  END As geom
-FROM ${data_schema}.zcta5_raw AS z INNER JOIN tiger.state AS s ON (ST_Covers(s.the_geom, z.geom) or ST_Overlaps(s.the_geom, z.geom) );"
+	'${psql} -c "CREATE TABLE ${data_schema}.${lookup_name}( zcta5 character varying(5), classfp character varying(2),mtfcc character varying(5), funcstat character varying(1), aland double precision, awater double precision, intptlat character varying(11), intptlon character varying(12), geom geometry(MultiPolygon,4269) );"',
+$post_load$${psql} -c "ALTER TABLE tiger.zcta5 DROP CONSTRAINT IF EXISTS enforce_geotype_the_geom; CREATE TABLE ${data_schema}.zcta5_all(CONSTRAINT pk_zcta5_all PRIMARY KEY (zcta5ce,statefp), CONSTRAINT uidx_${lookup_name}_all_gid UNIQUE (gid)) INHERITS(tiger.zcta5);
+ ${psql} -c "SELECT loader_load_staged_data(lower('${table_name}'), lower('${lookup_name}'));"
+${psql} -c "INSERT INTO ${data_schema}.zcta5_all(statefp, zcta5ce, classfp, mtfcc, funcstat, aland, awater, intptlat, intptlon, partflg, the_geom) SELECT  s.statefp, z.zcta5,  z.classfp, z.mtfcc, z.funcstat, z.aland, z.awater, z.intptlat, z.intptlon, CASE WHEN ST_Covers(s.the_geom, z.the_geom) THEN 'N' ELSE 'Y' END, ST_SnapToGrid(ST_Transform(CASE WHEN ST_Covers(s.the_geom, z.the_geom) THEN ST_SimplifyPreserveTopology(ST_Transform(z.the_geom,2163),1000) ELSE ST_SimplifyPreserveTopology(ST_Intersection(ST_Transform(s.the_geom,2163), ST_Transform(z.the_geom,2163)),1000)  END,4269), 0.000001) As geom FROM ${data_schema}.zcta5_raw AS z INNER JOIN tiger.state AS s ON (ST_Covers(s.the_geom, z.the_geom) or ST_Overlaps(s.the_geom, z.the_geom) );"
 	${psql} -c "DROP TABLE ${data_schema}.zcta5_raw; CREATE INDEX idx_${data_schema}_zcta5_all_the_geom_gist ON ${data_schema}.zcta5_all USING gist(the_geom);"$post_load$
 , ARRAY['gid','geoid','geoid10', 'partflg']);
 
-
-
 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 )
 VALUES(6, 'faces', 'faces', true, true, false,false, 'c',
 	'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${table_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (gid)) INHERITS(tiger.${lookup_name});" ',