· 10 years ago · Mar 08, 2016, 01:39 AM
1CREATE DATABASE geocoder;
2
3CREATE EXTENSION postgis;
4CREATE EXTENSION fuzzystrmatch;
5CREATE EXTENSION postgis_tiger_geocoder;
6
7GRANT USAGE ON SCHEMA tiger TO PUBLIC;
8GRANT USAGE ON SCHEMA tiger_data TO PUBLIC;
9GRANT SELECT, REFERENCES, TRIGGER
10ON ALL TABLES IN SCHEMA tiger TO PUBLIC;
11GRANT SELECT, REFERENCES, TRIGGER
12ON ALL TABLES IN SCHEMA tiger_data TO PUBLIC;
13GRANT EXECUTE
14ON ALL FUNCTIONS IN SCHEMA tiger TO PUBLIC;
15ALTER DEFAULT PRIVILEGES IN SCHEMA tiger_data
16GRANT SELECT, REFERENCES
17ON TABLES TO PUBLIC;
18
19SELECT declare_sect FROM tiger.loader_platform WHERE os='windows';
20 declare_sect
21------------------------------------------------
22 set TMPDIR=${staging_fold}temp
23 set UNZIPTOOL="C:Program Files7-Zip7z.exe"
24 set WGETTOOL="C:wget-1.11.4wget.exe"
25 set PGBIN=C:Program FilesPostgreSQL9.5bin
26 set PGPORT=5432
27 set PGHOST=localhost
28 set PGUSER=postgres
29 set PGPASSWORD=pass
30 set PGDATABASE=tiger
31 set PSQL="%PGBIN%psql"
32 set SHP2PGSQL="%PGBIN%shp2pgsql"
33 cd ${staging_fold}
34
35t
36a
37o /gisdata/nationscript.bat
38SELECT loader_generate_nation_script('windows');
39o
40
41geocoder=# o /gisdata/nationscript.bat
42/gisdata/nationscript.bat: No such file or directory
43geocoder=# SELECT loader_generate_nation_script('windows');
44
45 loader_generate_nation_script
46
47
48--------------------------------------------------------------------------------
49--------------------------------------------------------------------------------
50--------------------------------------------------------------------------------
51---------------------
52 set TMPDIR=gisdatatemp
53 set UNZIPTOOL="C:Program Files7-Zip7z.exe"
54 set WGETTOOL="C:wget-1.11.4"
55 set PGBIN=C:Program FilesPostgreSQL9.5bin
56 set PGPORT=5432
57 set PGHOST=localhost
58 set PGUSER=postgres
59 set PGPASSWORD=pass
60 set PGDATABASE=geocoder
61 set PSQL="%PGBIN%psql"
62 set SHP2PGSQL="%PGBIN%shp2pgsql"
63 cd gisdata
64
65 cd gisdata
66 %WGETTOOL% ftp://ftp2.census.gov/geo/tiger/TIGER2015/STATE/ --no-parent --relat
67ive --recursive --level=1 --accept=zip --mirror --reject=html
68 cd gisdata/ftp2.census.gov/geo/tiger/TIGER2015/STATE
69 del %TMPDIR%*.* /Q
70 %PSQL% -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
71 %PSQL% -c "CREATE SCHEMA tiger_staging;"
72 %PSQL% -c "DO language 'plpgsql' $$ BEGIN IF NOT EXISTS (SELECT * FROM informat
73ion_schema.schemata WHERE schema_name = 'tiger_data' ) THEN CREATE SCHEMA tiger_
74data; END IF; END $$"
75 for /r %%z in (tl_*state.zip ) do %UNZIPTOOL% e %%z -o%TMPDIR%
76 cd %TMPDIR%
77 %PSQL% -c "CREATE TABLE tiger_data.state_all(CONSTRAINT pk_state_all PRIMARY KE
78Y (statefp),CONSTRAINT uidx_state_all_stusps UNIQUE (stusps), CONSTRAINT uidx_s
79tate_all_gid UNIQUE (gid) ) INHERITS(tiger.state); "
80 %SHP2PGSQL% -D -c -s 4269 -g the_geom -W "latin1" tl_2015_us_state.dbf tiger_
81staging.state | %PSQL%
82 %PSQL% -c "SELECT loader_load_staged_data(lower('state'), lower('state_all'));
83"
84 %PSQL% -c "CREATE INDEX tiger_data_state_all_the_geom_gist ON tiger_dat
85a.state_all USING gist(the_geom);"
86 %PSQL% -c "VACUUM ANALYZE tiger_data.state_all"
87 cd gisdata
88 %WGETTOOL% ftp://ftp2.census.gov/geo/tiger/TIGER2015/COUNTY/ --no-parent --rela
89tive --recursive --level=1 --accept=zip --mirror --reject=html
90 cd gisdata/ftp2.census.gov/geo/tiger/TIGER2015/COUNTY
91 del %TMPDIR%*.* /Q
92 %PSQL% -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
93 %PSQL% -c "CREATE SCHEMA tiger_staging;"
94 %PSQL% -c "DO language 'plpgsql' $$ BEGIN IF NOT EXISTS (SELECT * FROM informat
95ion_schema.schemata WHERE schema_name = 'tiger_data' ) THEN CREATE SCHEMA tiger_
96data; END IF; END $$"
97 for /r %%z in (tl_*county.zip ) do %UNZIPTOOL% e %%z -o%TMPDIR%
98 cd %TMPDIR%
99 %PSQL% -c "CREATE TABLE tiger_data.county_all(CONSTRAINT pk_tiger_data_county_a
100ll PRIMARY KEY (cntyidfp),CONSTRAINT uidx_tiger_data_county_all_gid UNIQUE (gid)
101 ) INHERITS(tiger.county); "
102 %SHP2PGSQL% -D -c -s 4269 -g the_geom -W "latin1" tl_2015_us_county.dbf tiger
103_staging.county | %PSQL%
104 %PSQL% -c "ALTER TABLE tiger_staging.county RENAME geoid TO cntyidfp; SELECT l
105oader_load_staged_data(lower('county'), lower('county_all'));"
106 %PSQL% -c "CREATE INDEX tiger_data_county_the_geom_gist ON tiger_data.c
107ounty_all USING gist(the_geom);"
108 %PSQL% -c "CREATE UNIQUE INDEX uidx_tiger_data_county_all_statefp_count
109yfp ON tiger_data.county_all USING btree(statefp,countyfp);"
110 %PSQL% -c "CREATE TABLE tiger_data.county_all_lookup ( CONSTRAINT pk_co
111unty_all_lookup PRIMARY KEY (st_code, co_code)) INHERITS (tiger.county_lookup);"
112 %PSQL% -c "VACUUM ANALYZE tiger_data.county_all;"
113 %PSQL% -c "INSERT INTO tiger_data.county_all_lookup(st_code, state, co_
114code, name) SELECT CAST(s.statefp as integer), s.abbrev, CAST(c.countyfp as inte
115ger), c.name FROM tiger_data.county_all As c INNER JOIN state_lookup As s ON s.s
116tatefp = c.statefp;"
117 %PSQL% -c "VACUUM ANALYZE tiger_data.county_all_lookup;"
118(1 row)
119
120o c:/users/noah/gisdata/nationscript.bat