1-- 2-- PostGIS - Spatial Types for PostgreSQL 3-- http://postgis.net 4-- 5-- Copyright (C) 2012-2015 Regina Obe and Leo Hsu 6-- Paragon Corporation 7-- 8-- This is free software; you can redistribute and/or modify it under 9-- the terms of the GNU General Public Licence. See the COPYING file. 10-- 11-- Author: Regina Obe and Leo Hsu <lr@pcorp.us> 12-- 13-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 14SELECT tiger.SetSearchPathForInstall('tiger'); 15BEGIN; 16CREATE OR REPLACE FUNCTION create_census_base_tables() 17 RETURNS text AS 18$$ 19DECLARE var_temp text; 20BEGIN 21var_temp := tiger.SetSearchPathForInstall('tiger'); 22IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tract_id' AND table_name = 'tract') THEN 23 -- census block group/tracts parent tables not created yet or an older version -- drop old if not in use, create new structure 24 DROP TABLE IF EXISTS tiger.tract; 25 CREATE TABLE tract 26 ( 27 gid serial NOT NULL, 28 statefp varchar(2), 29 countyfp varchar(3), 30 tractce varchar(6), 31 tract_id varchar(11) PRIMARY KEY, 32 name varchar(7), 33 namelsad varchar(20), 34 mtfcc varchar(5), 35 funcstat varchar(1), 36 aland double precision, 37 awater double precision, 38 intptlat varchar(11), 39 intptlon varchar(12), 40 the_geom geometry, 41 CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2), 42 CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), 43 CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269) 44 ); 45 46 DROP TABLE IF EXISTS tiger.tabblock; 47 CREATE TABLE tabblock 48 ( 49 gid serial NOT NULL, 50 statefp varchar(2), 51 countyfp varchar(3), 52 tractce varchar(6), 53 blockce varchar(4), 54 tabblock_id varchar(16) PRIMARY KEY, 55 name varchar(20), 56 mtfcc varchar(5), 57 ur varchar(1), 58 uace varchar(5), 59 funcstat varchar(1), 60 aland double precision, 61 awater double precision, 62 intptlat varchar(11), 63 intptlon varchar(12), 64 the_geom geometry, 65 CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2), 66 CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), 67 CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269) 68 ); 69 70 DROP TABLE IF EXISTS tiger.bg; 71 CREATE TABLE bg 72 ( 73 gid serial NOT NULL, 74 statefp varchar(2), 75 countyfp varchar(3), 76 tractce varchar(6), 77 blkgrpce varchar(1), 78 bg_id varchar(12) PRIMARY KEY, 79 namelsad varchar(13), 80 mtfcc varchar(5), 81 funcstat varchar(1), 82 aland double precision, 83 awater double precision, 84 intptlat varchar(11), 85 intptlon varchar(12), 86 the_geom geometry, 87 CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2), 88 CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), 89 CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269) 90 ); 91 COMMENT ON TABLE tiger.bg IS 'block groups'; 92END IF; 93 94IF EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tabblock_id' AND table_name = 'tabblock' AND character_maximum_length < 16) THEN -- size of name and tabblock_id fields need to be increased 95 ALTER TABLE tiger.tabblock ALTER COLUMN name TYPE varchar(20); 96 ALTER TABLE tiger.tabblock ALTER COLUMN tabblock_id TYPE varchar(16); 97 RAISE NOTICE 'Size of tabblock_id and name are being increased'; 98END IF; 99RETURN 'Tables already present'; 100END 101$$ 102language 'plpgsql'; 103 104CREATE OR REPLACE FUNCTION loader_macro_replace(param_input text, param_keys text[],param_values text[]) 105RETURNS text AS 106$$ 107 DECLARE var_result text = param_input; 108 DECLARE var_count integer = array_upper(param_keys,1); 109 BEGIN 110 FOR i IN 1..var_count LOOP 111 var_result := replace(var_result, '${' || param_keys[i] || '}', param_values[i]); 112 END LOOP; 113 return var_result; 114 END; 115$$ 116 LANGUAGE 'plpgsql' IMMUTABLE 117 COST 100; 118 119-- Helper function that generates script to drop all tables in a particular schema for a particular table 120-- This is useful in case you need to reload a state 121CREATE OR REPLACE FUNCTION drop_state_tables_generate_script(param_state text, param_schema text DEFAULT 'tiger_data') 122 RETURNS text AS 123$$ 124SELECT array_to_string(array_agg('DROP TABLE ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ';'),E'\n') 125 FROM (SELECT * FROM information_schema.tables 126 WHERE table_schema = $2 AND table_name like lower($1) || '_%' ORDER BY table_name) AS foo; 127; 128$$ 129 LANGUAGE sql VOLATILE; 130 131-- Helper function that generates script to drop all nation tables (county, state) in a particular schema 132-- This is useful for loading 2011 because state and county tables aren't broken out into separate state files 133DROP FUNCTION IF EXISTS drop_national_tables_generate_script(text); 134CREATE OR REPLACE FUNCTION drop_nation_tables_generate_script(param_schema text DEFAULT 'tiger_data') 135 RETURNS text AS 136$$ 137SELECT array_to_string(array_agg('DROP TABLE ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ';'),E'\n') 138 FROM (SELECT * FROM information_schema.tables 139 WHERE table_schema = $1 AND (table_name ~ E'^[a-z]{2}\_county' or table_name ~ E'^[a-z]{2}\_state' or table_name = 'state_all' or table_name LIKE 'county_all%') ORDER BY table_name) AS foo; 140; 141$$ 142 LANGUAGE sql VOLATILE; 143 144DO 145$$ 146BEGIN 147 IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN 148 CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text); 149 END IF; 150END 151$$ LANGUAGE 'plpgsql'; 152 153DO 154$$ 155BEGIN 156 IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN 157 CREATE SCHEMA tiger_data; 158 END IF; 159END 160$$ LANGUAGE 'plpgsql'; 161 162DELETE FROM loader_platform WHERE os IN ('sh', 'windows'); 163GRANT SELECT ON TABLE loader_platform TO public; 164INSERT INTO loader_platform(os, wget, pgbin, declare_sect, unzip_command, psql,path_sep,loader, environ_set_command, county_process_command) 165VALUES('windows', '%WGETTOOL%', '%PGBIN%', 166E'set TMPDIR=${staging_fold}\\temp\\ 167set UNZIPTOOL="C:\\Program Files\\7-Zip\\7z.exe" 168set WGETTOOL="C:\\wget\\wget.exe" 169set PGBIN=C:\\Program Files\\PostgreSQL\\9.4\\bin\\ 170set PGPORT=5432 171set PGHOST=localhost 172set PGUSER=postgres 173set PGPASSWORD=yourpasswordhere 174set PGDATABASE=geocoder 175set PSQL="%PGBIN%psql" 176set SHP2PGSQL="%PGBIN%shp2pgsql" 177cd ${staging_fold} 178', E'del %TMPDIR%\\*.* /Q 179%PSQL% -c "DROP SCHEMA IF EXISTS ${staging_schema} CASCADE;" 180%PSQL% -c "CREATE SCHEMA ${staging_schema};" 181%PSQL% -c "DO language ''plpgsql'' $$ BEGIN IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = ''${data_schema}'' ) THEN CREATE SCHEMA ${data_schema}; END IF; END $$" 182for /r %%z in (*.zip) do %UNZIPTOOL% e %%z -o%TMPDIR% 183cd %TMPDIR%', E'%PSQL%', E'\\', E'%SHP2PGSQL%', 'set ', 184'for /r %%z in (*${table_name}*.dbf) do (${loader} -D -s 4269 -g the_geom -W "latin1" %%z tiger_staging.${state_abbrev}_${table_name} | ${psql} & ${psql} -c "SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}''));")' 185); 186 187INSERT INTO loader_platform(os, wget, pgbin, declare_sect, unzip_command, psql, path_sep, loader, environ_set_command, county_process_command) 188VALUES('sh', 'wget', '', 189E'TMPDIR="${staging_fold}/temp/" 190UNZIPTOOL=unzip 191WGETTOOL="/usr/bin/wget" 192export PGBIN=/usr/lib/postgresql/9.4/bin 193export PGPORT=5432 194export PGHOST=localhost 195export PGUSER=postgres 196export PGPASSWORD=yourpasswordhere 197export PGDATABASE=geocoder 198PSQL=${PGBIN}/psql 199SHP2PGSQL=${PGBIN}/shp2pgsql 200cd ${staging_fold} 201', E'rm -f ${TMPDIR}/*.* 202${PSQL} -c "DROP SCHEMA IF EXISTS ${staging_schema} CASCADE;" 203${PSQL} -c "CREATE SCHEMA ${staging_schema};" 204for z in *.zip; do $UNZIPTOOL -o -d $TMPDIR $z; done 205for z in */*.zip; do $UNZIPTOOL -o -d $TMPDIR $z; done 206cd $TMPDIR;\n', '${PSQL}', '/', '${SHP2PGSQL}', 'export ', 207'for z in *${table_name}*.dbf; do 208${loader} -D -s 4269 -g the_geom -W "latin1" $z ${staging_schema}.${state_abbrev}_${table_name} | ${psql} 209${PSQL} -c "SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}''));" 210done'); 211 212-- variables table 213DO $$ 214BEGIN 215 IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN 216 CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text); 217 END IF; 218END 219$$ LANGUAGE 'plpgsql'; 220 221TRUNCATE TABLE loader_variables; 222INSERT INTO loader_variables(tiger_year, website_root , staging_fold, data_schema, staging_schema) 223 VALUES('2015', 'ftp://ftp2.census.gov/geo/tiger/TIGER2015', '/gisdata', 'tiger_data', 'tiger_staging'); 224GRANT SELECT ON TABLE loader_variables TO public; 225 226DO $$ 227BEGIN 228 IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN 229 CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000, 230 lookup_name text primary key, 231 table_name text, single_mode boolean NOT NULL DEFAULT true, 232 load boolean NOT NULL DEFAULT true, 233 level_county boolean NOT NULL DEFAULT false, 234 level_state boolean NOT NULL DEFAULT false, 235 level_nation boolean NOT NULL DEFAULT false, 236 post_load_process text, single_geom_mode boolean DEFAULT false, 237 insert_mode char(1) NOT NULL DEFAULT 'c', 238 pre_load_process text,columns_exclude text[], website_root_override text); 239 END IF; 240END 241$$ LANGUAGE 'plpgsql'; 242 243TRUNCATE TABLE loader_lookuptables; 244 245GRANT SELECT ON TABLE loader_lookuptables TO public; 246 247-- put in explanatory comments of what each column is for 248COMMENT ON COLUMN loader_lookuptables.lookup_name IS 'This is the table name to inherit from and suffix of resulting output table -- how the table will be named -- edges here would mean -- ma_edges , pa_edges etc. except in the case of national tables. national level tables have no prefix'; 249COMMENT ON COLUMN loader_lookuptables.level_nation IS 'These are tables that contain all data for the whole US so there is just a single file'; 250COMMENT ON COLUMN loader_lookuptables.table_name IS 'suffix of the tables to load e.g. edges would load all tables like *edges.dbf(shp) -- so tl_2010_42129_edges.dbf . '; 251COMMENT ON COLUMN loader_lookuptables.load IS 'Whether or not to load the table. For states and zcta5 (you may just want to download states10, zcta510 nationwide file manually) load your own into a single table that inherits from tiger.states, tiger.zcta5. You''ll get improved performance for some geocoding cases.'; 252COMMENT ON COLUMN loader_lookuptables.columns_exclude IS 'List of columns to exclude as an array. This is excluded from both input table and output table and rest of columns remaining are assumed to be in same order in both tables. gid, geoid,cpi,suffix1ce are excluded if no columns are specified.'; 253COMMENT ON COLUMN loader_lookuptables.website_root_override IS 'Path to use for wget instead of that specified in year table. Needed currently for zcta where they release that only for 2000 and 2010'; 254 255INSERT 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 ) 256VALUES(10, 'tract', 'tract', true, false, true,false, 'c', 257'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (tract_id) ) INHERITS(tiger.${lookup_name}); " ', 258 '${psql} -c "ALTER TABLE ${staging_schema}.${state_abbrev}_${table_name} RENAME geoid TO tract_id; SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}'')); " 259 ${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${lookup_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${lookup_name} USING gist(the_geom);" 260 ${psql} -c "VACUUM ANALYZE ${data_schema}.${state_abbrev}_${lookup_name};" 261 ${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');"', ARRAY['gid']); 262 263INSERT 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 ) 264VALUES(11, 'tabblock', 'tabblock', true, true, false,false, 'c', 265'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (tabblock_id)) INHERITS(tiger.${lookup_name});" ', 266'${psql} -c "ALTER TABLE ${staging_schema}.${state_abbrev}_${lookup_name} RENAME geoid10 TO tabblock_id;" 267${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');" 268${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${lookup_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${lookup_name} USING gist(the_geom);" 269${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"', '{gid, uatyp10, uatype, tractce10, blockce10,suffix1ce,blockce,tractce}'::text[]); 270 271INSERT 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 ) 272VALUES(12, 'bg', 'bg', true,false, true,false, 'c', 273'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (bg_id)) INHERITS(tiger.${lookup_name});" ', 274'${psql} -c "ALTER TABLE ${staging_schema}.${state_abbrev}_${table_name} RENAME geoid TO bg_id; SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}'')); " 275${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');" 276${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${lookup_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${lookup_name} USING gist(the_geom);" 277${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"', ARRAY['gid']); 278 279INSERT INTO loader_lookuptables(process_order, lookup_name, table_name, load, level_county, level_state, level_nation, single_geom_mode, pre_load_process, post_load_process) 280VALUES(2, 'county_all', 'county', true, false, false, true, 281 false, '${psql} -c "CREATE TABLE ${data_schema}.${lookup_name}(CONSTRAINT pk_${data_schema}_${lookup_name} PRIMARY KEY (cntyidfp),CONSTRAINT uidx_${data_schema}_${lookup_name}_gid UNIQUE (gid) ) INHERITS(tiger.county); " ', 282 '${psql} -c "ALTER TABLE ${staging_schema}.${table_name} RENAME geoid TO cntyidfp; SELECT loader_load_staged_data(lower(''${table_name}''), lower(''${lookup_name}''));" 283 ${psql} -c "CREATE INDEX ${data_schema}_${table_name}_the_geom_gist ON ${data_schema}.${lookup_name} USING gist(the_geom);" 284 ${psql} -c "CREATE UNIQUE INDEX uidx_${data_schema}_${lookup_name}_statefp_countyfp ON ${data_schema}.${lookup_name} USING btree(statefp,countyfp);" 285 ${psql} -c "CREATE TABLE ${data_schema}.${lookup_name}_lookup ( CONSTRAINT pk_${lookup_name}_lookup PRIMARY KEY (st_code, co_code)) INHERITS (tiger.county_lookup);" 286 ${psql} -c "VACUUM ANALYZE ${data_schema}.${lookup_name};" 287 ${psql} -c "INSERT INTO ${data_schema}.${lookup_name}_lookup(st_code, state, co_code, name) SELECT CAST(s.statefp as integer), s.abbrev, CAST(c.countyfp as integer), c.name FROM ${data_schema}.${lookup_name} As c INNER JOIN state_lookup As s ON s.statefp = c.statefp;" 288 ${psql} -c "VACUUM ANALYZE ${data_schema}.${lookup_name}_lookup;" '); 289 290INSERT 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 ) 291VALUES(1, 'state_all', 'state', true, false, false,true,false, 'c', 292 '${psql} -c "CREATE TABLE ${data_schema}.${lookup_name}(CONSTRAINT pk_${lookup_name} PRIMARY KEY (statefp),CONSTRAINT uidx_${lookup_name}_stusps UNIQUE (stusps), CONSTRAINT uidx_${lookup_name}_gid UNIQUE (gid) ) INHERITS(tiger.state); "', 293 '${psql} -c "SELECT loader_load_staged_data(lower(''${table_name}''), lower(''${lookup_name}'')); " 294 ${psql} -c "CREATE INDEX ${data_schema}_${lookup_name}_the_geom_gist ON ${data_schema}.${lookup_name} USING gist(the_geom);" 295 ${psql} -c "VACUUM ANALYZE ${data_schema}.${lookup_name}"' ); 296 297INSERT 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 ) 298VALUES(3, 'place', 'place', true, false, true,false, 'c', 299 '${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${table_name} PRIMARY KEY (plcidfp) ) INHERITS(tiger.place);" ', 300 '${psql} -c "ALTER TABLE ${staging_schema}.${state_abbrev}_${table_name} RENAME geoid TO plcidfp;SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}'')); ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT uidx_${state_abbrev}_${lookup_name}_gid UNIQUE (gid);" 301${psql} -c "CREATE INDEX idx_${state_abbrev}_${lookup_name}_soundex_name ON ${data_schema}.${state_abbrev}_${lookup_name} USING btree (soundex(name));" 302${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${lookup_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${lookup_name} USING gist(the_geom);" 303${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');"' 304 ); 305 306INSERT 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 ) 307VALUES(4, 'cousub', 'cousub', true, false, true,false, 'c', 308 '${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (cosbidfp), CONSTRAINT uidx_${state_abbrev}_${lookup_name}_gid UNIQUE (gid)) INHERITS(tiger.${lookup_name});" ', 309 '${psql} -c "ALTER TABLE ${staging_schema}.${state_abbrev}_${table_name} RENAME geoid TO cosbidfp;SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}'')); ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');" 310${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${lookup_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${lookup_name} USING gist(the_geom);" 311${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_countyfp ON ${data_schema}.${state_abbrev}_${lookup_name} USING btree(countyfp);"'); 312 313INSERT 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, website_root_override ) 314-- this is a bit of a lie that its county. It's really state but works better with column routine 315VALUES(4, 'zcta5', 'zcta510', true,true, false,false, false, 'a', 316 '${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (zcta5ce,statefp), CONSTRAINT uidx_${state_abbrev}_${lookup_name}_gid UNIQUE (gid)) INHERITS(tiger.${lookup_name});" ', 317 '${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');" 318${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${lookup_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${lookup_name} USING gist(the_geom);"' 319, ARRAY['gid','geoid','geoid10'], 'ftp://ftp2.census.gov/geo/tiger/TIGER2010/ZCTA5/2010'); 320 321INSERT 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 ) 322VALUES(6, 'faces', 'faces', true, true, false,false, 'c', 323 '${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${table_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (gid)) INHERITS(tiger.${lookup_name});" ', 324 '${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${table_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${lookup_name} USING gist(the_geom);" 325 ${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_tfid ON ${data_schema}.${state_abbrev}_${lookup_name} USING btree (tfid);" 326 ${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${table_name}_countyfp ON ${data_schema}.${state_abbrev}_${table_name} USING btree (countyfp);" 327 ${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');" 328 ${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"'); 329 330INSERT 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 ) 331VALUES(7, 'featnames', 'featnames', true, true, false,false, 'a', 332'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${table_name}(CONSTRAINT pk_${state_abbrev}_${table_name} PRIMARY KEY (gid)) INHERITS(tiger.${table_name});ALTER TABLE ${data_schema}.${state_abbrev}_${table_name} ALTER COLUMN statefp SET DEFAULT ''${state_fips}'';" ', 333'${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_snd_name ON ${data_schema}.${state_abbrev}_${table_name} USING btree (soundex(name));" 334${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_lname ON ${data_schema}.${state_abbrev}_${table_name} USING btree (lower(name));" 335${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_tlid_statefp ON ${data_schema}.${state_abbrev}_${table_name} USING btree (tlid,statefp);" 336${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');" 337${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"', ARRAY['gid','statefp']); 338 339INSERT 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 ) 340VALUES(8, 'edges', 'edges', true, true, false,false, 'a', 341'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${table_name}(CONSTRAINT pk_${state_abbrev}_${table_name} PRIMARY KEY (gid)) INHERITS(tiger.${table_name});" ', 342'${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${table_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');" 343${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_tlid ON ${data_schema}.${state_abbrev}_${table_name} USING btree (tlid);" 344${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}tfidr ON ${data_schema}.${state_abbrev}_${table_name} USING btree (tfidr);" 345${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_tfidl ON ${data_schema}.${state_abbrev}_${table_name} USING btree (tfidl);" 346${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_countyfp ON ${data_schema}.${state_abbrev}_${table_name} USING btree (countyfp);" 347${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${table_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${table_name} USING gist(the_geom);" 348${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_zipl ON ${data_schema}.${state_abbrev}_${lookup_name} USING btree (zipl);" 349${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_zip_state_loc(CONSTRAINT pk_${state_abbrev}_zip_state_loc PRIMARY KEY(zip,stusps,place)) INHERITS(tiger.zip_state_loc);" 350${psql} -c "INSERT INTO ${data_schema}.${state_abbrev}_zip_state_loc(zip,stusps,statefp,place) SELECT DISTINCT e.zipl, ''${state_abbrev}'', ''${state_fips}'', p.name FROM ${data_schema}.${state_abbrev}_edges AS e 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;" 351${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_zip_state_loc_place ON ${data_schema}.${state_abbrev}_zip_state_loc USING btree(soundex(place));" 352${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_zip_state_loc ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');" 353${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};" 354${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_zip_state_loc;" 355${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);" 356${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;" 357${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_zip_lookup_base ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');" 358${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));" '); 359 360INSERT 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 ) 361VALUES(9, 'addr', 'addr', true, true, false,false, 'a', 362 '${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${table_name} PRIMARY KEY (gid)) INHERITS(tiger.${table_name});ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ALTER COLUMN statefp SET DEFAULT ''${state_fips}'';" ', 363 '${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');" 364 ${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_least_address ON tiger_data.${state_abbrev}_addr USING btree (least_hn(fromhn,tohn) );" 365 ${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${table_name}_tlid_statefp ON ${data_schema}.${state_abbrev}_${table_name} USING btree (tlid, statefp);" 366 ${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${table_name}_zip ON ${data_schema}.${state_abbrev}_${table_name} USING btree (zip);" 367 ${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_zip_state(CONSTRAINT pk_${state_abbrev}_zip_state PRIMARY KEY(zip,stusps)) INHERITS(tiger.zip_state); " 368 ${psql} -c "INSERT INTO ${data_schema}.${state_abbrev}_zip_state(zip,stusps,statefp) SELECT DISTINCT zip, ''${state_abbrev}'', ''${state_fips}'' FROM ${data_schema}.${state_abbrev}_${lookup_name} WHERE zip is not null;" 369 ${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_zip_state ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');" 370 ${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"', ARRAY['gid','statefp','fromarmid', 'toarmid']); 371 372INSERT 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 ) 373VALUES(9, 'addrfeat', 'addrfeat', false, true, false,true, 'a', 374 '${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${table_name} PRIMARY KEY (gid)) INHERITS(tiger.${table_name});ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ALTER COLUMN statefp SET DEFAULT ''${state_fips}'';" ', 375 '${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');" 376 ${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"', ARRAY['gid','statefp','fromarmid', 'toarmid']); 377 378CREATE OR REPLACE FUNCTION loader_generate_nation_script(os text) 379 RETURNS SETOF text AS 380$BODY$ 381WITH lu AS (SELECT lookup_name, table_name, pre_load_process,post_load_process, process_order, insert_mode, single_geom_mode, level_nation, level_county, level_state 382 FROM loader_lookuptables 383 WHERE level_nation = true AND load = true) 384SELECT 385 loader_macro_replace( 386 replace( 387 loader_macro_replace(declare_sect 388 , ARRAY['staging_fold', 'website_root', 'psql', 'data_schema', 'staging_schema'], 389 ARRAY[variables.staging_fold, variables.website_root, platform.psql, variables.data_schema, variables.staging_schema] 390 ), '/', platform.path_sep) || ' 391' || 392 -- Nation level files 393 array_to_string( ARRAY(SELECT loader_macro_replace('cd ' || replace(variables.staging_fold,'/', platform.path_sep) || ' 394' || platform.wget || ' ' || variables.website_root || '/' || upper(table_name) || '/ --no-parent --relative --recursive --level=1 --accept=zip --mirror --reject=html 395' 396|| 'cd ' || replace(variables.staging_fold,'/', platform.path_sep) || '/' || replace(replace(variables.website_root, 'http://', ''),'ftp://','') || '/' || upper(table_name) || ' 397' || replace(platform.unzip_command, '*.zip', 'tl_*' || table_name || '.zip ') || ' 398' || COALESCE(lu.pre_load_process || E'\n', '') || platform.loader || ' -D -' || lu.insert_mode || ' -s 4269 -g the_geom ' 399 || CASE WHEN lu.single_geom_mode THEN ' -S ' ELSE ' ' END::text || ' -W "latin1" tl_' || variables.tiger_year 400 || '_us_' || lu.table_name || '.dbf tiger_staging.' || lu.table_name || ' | '::text || platform.psql 401 || COALESCE(E'\n' || 402 lu.post_load_process , '') , ARRAY['loader','table_name', 'lookup_name'], ARRAY[platform.loader, lu.table_name, lu.lookup_name ] 403 ) 404 FROM lu 405 ORDER BY process_order, lookup_name), E'\n') ::text 406 , ARRAY['psql', 'data_schema','staging_schema', 'staging_fold', 'website_root'], 407 ARRAY[platform.psql, variables.data_schema, variables.staging_schema, variables.staging_fold, variables.website_root]) 408 AS shell_code 409FROM loader_variables As variables 410 CROSS JOIN loader_platform As platform 411WHERE platform.os = $1 -- generate script for selected platform 412; 413$BODY$ 414 LANGUAGE sql VOLATILE; 415 416CREATE OR REPLACE FUNCTION loader_generate_script(param_states text[], os text) 417 RETURNS SETOF text AS 418$BODY$ 419SELECT 420 loader_macro_replace( 421 replace( 422 loader_macro_replace(declare_sect 423 , ARRAY['staging_fold', 'state_fold','website_root', 'psql', 'state_abbrev', 'data_schema', 'staging_schema', 'state_fips'], 424 ARRAY[variables.staging_fold, s.state_fold, variables.website_root, platform.psql, s.state_abbrev, variables.data_schema, variables.staging_schema, s.state_fips::text] 425 ), '/', platform.path_sep) || ' 426' || 427 -- State level files - if an override website is specified we use that instead of variable one 428 array_to_string( ARRAY(SELECT 'cd ' || replace(variables.staging_fold,'/', platform.path_sep) || ' 429' || platform.wget || ' ' || COALESCE(lu.website_root_override,variables.website_root || '/' || upper(table_name) ) || '/tl_*_' || s.state_fips || '_* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 430' 431|| 'cd ' || replace(variables.staging_fold,'/', platform.path_sep) || '/' || replace(replace(COALESCE(lu.website_root_override,variables.website_root || '/' || upper(table_name) ), 'http://', ''),'ftp://','') || ' 432' || replace(platform.unzip_command, '*.zip', 'tl_*_' || s.state_fips || '*_' || table_name || '.zip ') || ' 433' ||loader_macro_replace(COALESCE(lu.pre_load_process || E'\n', '') || platform.loader || ' -D -' || lu.insert_mode || ' -s 4269 -g the_geom ' 434 || CASE WHEN lu.single_geom_mode THEN ' -S ' ELSE ' ' END::text || ' -W "latin1" tl_' || variables.tiger_year || '_' || s.state_fips 435 || '_' || lu.table_name || '.dbf tiger_staging.' || lower(s.state_abbrev) || '_' || lu.table_name || ' | '::text || platform.psql 436 || COALESCE(E'\n' || 437 lu.post_load_process , '') , ARRAY['loader','table_name', 'lookup_name'], ARRAY[platform.loader, lu.table_name, lu.lookup_name ]) 438 FROM loader_lookuptables AS lu 439 WHERE level_state = true AND load = true 440 ORDER BY process_order, lookup_name), E'\n') ::text 441 -- County Level files 442 || E'\n' || 443 array_to_string( ARRAY(SELECT 'cd ' || replace(variables.staging_fold,'/', platform.path_sep) || ' 444' || platform.wget || ' ' || COALESCE(lu.website_root_override,variables.website_root || '/' || upper(table_name) ) || '/tl_*_' || s.state_fips || '* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 445' 446|| 'cd ' || replace(variables.staging_fold,'/', platform.path_sep) || '/' || replace(replace(COALESCE(lu.website_root_override,variables.website_root || '/' || upper(table_name) || '/'), 'http://', ''),'ftp://','') || ' 447' || replace(platform.unzip_command, '*.zip', 'tl_*_' || s.state_fips || '*_' || table_name || '*.zip ') || ' 448' || loader_macro_replace(COALESCE(lu.pre_load_process || E'\n', '') || COALESCE(county_process_command || E'\n','') 449 || COALESCE(E'\n' ||lu.post_load_process , '') , ARRAY['loader','table_name','lookup_name'], ARRAY[platform.loader || ' -D ' || CASE WHEN lu.single_geom_mode THEN ' -S' ELSE ' ' END::text, lu.table_name, lu.lookup_name ]) 450 FROM loader_lookuptables AS lu 451 WHERE level_county = true AND load = true 452 ORDER BY process_order, lookup_name), E'\n') ::text 453 , ARRAY['psql', 'data_schema','staging_schema', 'staging_fold', 'state_fold', 'website_root', 'state_abbrev','state_fips'], 454 ARRAY[platform.psql, variables.data_schema, variables.staging_schema, variables.staging_fold, s.state_fold,variables.website_root, s.state_abbrev, s.state_fips::text]) 455 AS shell_code 456FROM loader_variables As variables 457 CROSS JOIN (SELECT name As state, abbrev As state_abbrev, lpad(st_code::text,2,'0') As state_fips, 458 lpad(st_code::text,2,'0') || '_' 459 || replace(name, ' ', '_') As state_fold 460FROM state_lookup) As s CROSS JOIN loader_platform As platform 461WHERE $1 @> ARRAY[state_abbrev::text] -- If state is contained in list of states input generate script for it 462AND platform.os = $2 -- generate script for selected platform 463; 464$BODY$ 465 LANGUAGE sql VOLATILE; 466 467CREATE OR REPLACE FUNCTION loader_load_staged_data(param_staging_table text, param_target_table text, param_columns_exclude text[]) RETURNS integer 468AS 469$$ 470DECLARE 471 var_sql text; 472 var_staging_schema text; var_data_schema text; 473 var_temp text; 474 var_num_records bigint; 475BEGIN 476-- Add all the fields except geoid and gid 477-- Assume all the columns are in same order as target 478 SELECT staging_schema, data_schema INTO var_staging_schema, var_data_schema FROM loader_variables; 479 var_sql := 'INSERT INTO ' || var_data_schema || '.' || quote_ident(param_target_table) || '(' || 480 array_to_string(ARRAY(SELECT quote_ident(column_name::text) 481 FROM information_schema.columns 482 WHERE table_name = param_target_table 483 AND table_schema = var_data_schema 484 AND column_name <> ALL(param_columns_exclude) 485 ORDER BY ordinal_position ), ',') || ') SELECT ' 486 || array_to_string(ARRAY(SELECT quote_ident(column_name::text) 487 FROM information_schema.columns 488 WHERE table_name = param_staging_table 489 AND table_schema = var_staging_schema 490 AND column_name <> ALL( param_columns_exclude) 491 ORDER BY ordinal_position ), ',') ||' FROM ' 492 || var_staging_schema || '.' || param_staging_table || ';'; 493 RAISE NOTICE '%', var_sql; 494 EXECUTE (var_sql); 495 GET DIAGNOSTICS var_num_records = ROW_COUNT; 496 SELECT DropGeometryTable(var_staging_schema,param_staging_table) INTO var_temp; 497 RETURN var_num_records; 498END; 499$$ 500LANGUAGE 'plpgsql' VOLATILE; 501 502CREATE OR REPLACE FUNCTION loader_load_staged_data(param_staging_table text, param_target_table text) 503RETURNS integer AS 504$$ 505-- exclude this set list of columns if no exclusion list is specified 506 507 SELECT loader_load_staged_data($1, $2,(SELECT COALESCE(columns_exclude,ARRAY['gid', 'geoid','cpi','suffix1ce', 'statefp00', 'statefp10', 'countyfp00','countyfp10' 508 ,'tractce00','tractce10', 'blkgrpce00', 'blkgrpce10', 'blockce00', 'blockce10' 509 , 'cousubfp00', 'submcdfp00', 'conctyfp00', 'placefp00', 'aiannhfp00', 'aiannhce00', 510 'comptyp00', 'trsubfp00', 'trsubce00', 'anrcfp00', 'elsdlea00', 'scsdlea00', 511 'unsdlea00', 'uace00', 'cd108fp', 'sldust00', 'sldlst00', 'vtdst00', 'zcta5ce00', 512 'tazce00', 'ugace00', 'puma5ce00','vtdst10','tazce10','uace10','puma5ce10','tazce', 'uace', 'vtdst', 'zcta5ce', 'zcta5ce10', 'puma5ce', 'ugace10','pumace10', 'estatefp', 'ugace', 'blockce']) FROM loader_lookuptables WHERE $2 LIKE '%' || lookup_name)) 513$$ 514language 'sql' VOLATILE; 515 516CREATE OR REPLACE FUNCTION loader_generate_census_script(param_states text[], os text) 517 RETURNS SETOF text AS 518$$ 519SELECT create_census_base_tables(); 520SELECT 521 loader_macro_replace( 522 replace( 523 loader_macro_replace(declare_sect 524 , ARRAY['staging_fold', 'state_fold','website_root', 'psql', 'state_abbrev', 'data_schema', 'staging_schema', 'state_fips'], 525 ARRAY[variables.staging_fold, s.state_fold, variables.website_root, platform.psql, s.state_abbrev, variables.data_schema, variables.staging_schema, s.state_fips::text] 526 ), '/', platform.path_sep) || ' 527' || 528 -- State level files - if an override website is specified we use that instead of variable one 529 array_to_string( ARRAY(SELECT 'cd ' || replace(variables.staging_fold,'/', platform.path_sep) || ' 530' || platform.wget || ' ' || COALESCE(lu.website_root_override,variables.website_root || '/' || upper(table_name) ) || '/*_' || s.state_fips || '* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 531' 532|| 'cd ' || replace(variables.staging_fold,'/', platform.path_sep) || '/' || replace(replace(COALESCE(lu.website_root_override,variables.website_root || '/' || upper(table_name) ), 'http://', ''),'ftp://','') || ' 533' || replace(platform.unzip_command, '*.zip', 'tl_*_' || s.state_fips || '*_' || table_name || '.zip ') || ' 534' ||loader_macro_replace(COALESCE(lu.pre_load_process || E'\n', '') || platform.loader || ' -' || lu.insert_mode || ' -s 4269 -g the_geom ' 535 || CASE WHEN lu.single_geom_mode THEN ' -S ' ELSE ' ' END::text || ' -W "latin1" tl_' || variables.tiger_year || '_' || s.state_fips 536 || '_' || lu.table_name || '.dbf tiger_staging.' || lower(s.state_abbrev) || '_' || lu.table_name || ' | '::text || platform.psql 537 || COALESCE(E'\n' || 538 lu.post_load_process , '') , ARRAY['loader','table_name', 'lookup_name'], ARRAY[platform.loader, lu.table_name, lu.lookup_name ]) 539 FROM loader_lookuptables AS lu 540 WHERE level_state = true AND load = true AND lookup_name IN('tract','bg','tabblock') 541 ORDER BY process_order, lookup_name), E'\n') ::text 542 -- County Level files 543 || E'\n' || 544 array_to_string( ARRAY(SELECT 'cd ' || replace(variables.staging_fold,'/', platform.path_sep) || ' 545' || platform.wget || ' ' || COALESCE(lu.website_root_override,variables.website_root || '/' || upper(table_name) ) || '/*_' || s.state_fips || '* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 546' 547|| 'cd ' || replace(variables.staging_fold,'/', platform.path_sep) || '/' || replace(replace(COALESCE(lu.website_root_override,variables.website_root || '/' || upper(table_name) || '/'), 'http://', ''),'ftp://','') || ' 548' || replace(platform.unzip_command, '*.zip', 'tl_*_' || s.state_fips || '*_' || table_name || '*.zip ') || ' 549' || loader_macro_replace(COALESCE(lu.pre_load_process || E'\n', '') || COALESCE(county_process_command || E'\n','') 550 || COALESCE(E'\n' ||lu.post_load_process , '') , ARRAY['loader','table_name','lookup_name'], ARRAY[platform.loader || CASE WHEN lu.single_geom_mode THEN ' -S' ELSE ' ' END::text, lu.table_name, lu.lookup_name ]) 551 FROM loader_lookuptables AS lu 552 WHERE level_county = true AND load = true AND lookup_name IN('tract','bg','tabblock') 553 ORDER BY process_order, lookup_name), E'\n') ::text 554 , ARRAY['psql', 'data_schema','staging_schema', 'staging_fold', 'state_fold', 'website_root', 'state_abbrev','state_fips'], 555 ARRAY[platform.psql, variables.data_schema, variables.staging_schema, variables.staging_fold, s.state_fold,variables.website_root, s.state_abbrev, s.state_fips::text]) 556 AS shell_code 557FROM loader_variables As variables 558 CROSS JOIN (SELECT name As state, abbrev As state_abbrev, lpad(st_code::text,2,'0') As state_fips, 559 lpad(st_code::text,2,'0') || '_' 560 || replace(name, ' ', '_') As state_fold 561FROM state_lookup) As s CROSS JOIN loader_platform As platform 562WHERE $1 @> ARRAY[state_abbrev::text] -- If state is contained in list of states input generate script for it 563AND platform.os = $2 -- generate script for selected platform 564; 565$$ 566 LANGUAGE sql VOLATILE; 567 568SELECT create_census_base_tables(); 569COMMIT;