[postgis-tickets] [SCM] PostGIS branch main updated. 3.1.0rc1-370-g095fecc
git at osgeo.org
git at osgeo.org
Wed Jul 28 19:54:06 PDT 2021
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, main has been updated
via 095fecc955e5c76535661564bc278b9d0a8b2ef2 (commit)
from d3f1c2b53deb2adc01c254d14f2c320e51fb6cc8 (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 095fecc955e5c76535661564bc278b9d0a8b2ef2
Author: Regina Obe <lr at pcorp.us>
Date: Wed Jul 28 22:54:00 2021 -0400
Add in logic to load tabblock20
diff --git a/extras/tiger_geocoder/tiger_loader_2020.sql b/extras/tiger_geocoder/tiger_loader_2020.sql
index 098a4ef..d3bd187 100644
--- a/extras/tiger_geocoder/tiger_loader_2020.sql
+++ b/extras/tiger_geocoder/tiger_loader_2020.sql
@@ -91,6 +91,27 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
COMMENT ON TABLE tiger.bg IS 'block groups';
END IF;
+CREATE TABLE IF NOT EXISTS tiger.tabblock20
+(
+ statefp character varying(2) ,
+ countyfp character varying(3) ,
+ tractce character varying(6) ,
+ blockce character varying(4) ,
+ geoid character varying(15) ,
+ name character varying(10) ,
+ mtfcc character varying(5) ,
+ ur character varying(1) ,
+ uace character varying(5) ,
+ uatype character varying(1) ,
+ funcstat character varying(1) ,
+ aland double precision,
+ awater double precision,
+ intptlat character varying(11) ,
+ intptlon character varying(12) ,
+ the_geom geometry(MultiPolygon,4269),
+ CONSTRAINT pk_tabblock20 PRIMARY KEY (geoid)
+);
+
IF 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
ALTER TABLE tiger.tabblock ALTER COLUMN name TYPE varchar(20);
ALTER TABLE tiger.tabblock ALTER COLUMN tabblock_id TYPE varchar(16);
@@ -254,7 +275,9 @@ COMMENT ON COLUMN loader_lookuptables.website_root_override IS 'Path to use for
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(10, 'tract', 'tract', true, false, true,false, 'c',
'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (tract_id) ) INHERITS(tiger.${lookup_name}); " ',
- '${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}'')); "
+ '${psql} -c "ALTER TABLE ${staging_schema}.${state_abbrev}_${table_name} RENAME geoid IF EXISTS TO tract_id;
+ ALTER TABLE ${staging_schema}.${state_abbrev}_${table_name} RENAME geoid10 IF EXISTS TO tract_id;
+ SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}'')); "
${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${lookup_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${lookup_name} USING gist(the_geom);"
${psql} -c "VACUUM ANALYZE ${data_schema}.${state_abbrev}_${lookup_name};"
${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');"', ARRAY['gid']);
@@ -262,12 +285,22 @@ VALUES(10, 'tract', 'tract', true, false, true,false, 'c',
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(11, 'tabblock', 'tabblock10', false, false, true,false, 'c',
'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (tabblock_id)) INHERITS(tiger.${lookup_name});" ',
-'${psql} -c "ALTER TABLE ${staging_schema}.${state_abbrev}_${table_name} RENAME geoid10 TO tabblock_id; SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}'')); "
+'${psql} -c "ALTER TABLE ${staging_schema}.${state_abbrev}_${table_name} RENAME geoid10 TO tabblock_id;
+SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}'')); "
${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');"
${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${lookup_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${lookup_name} USING gist(the_geom);"
${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"', '{gid, uatyp10, uatype, suffix1ce}'::text[]);
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(11, 'tabblock20', 'tabblock20', true, false, true,false, 'c',
+'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (geoid)) INHERITS(tiger.${lookup_name});" ',
+'${psql} -c "ALTER TABLE ${staging_schema}.${state_abbrev}_${table_name} RENAME geoid20 TO tabblock_id;
+SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}'')); "
+${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');"
+${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${lookup_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${lookup_name} USING gist(the_geom);"
+${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"', '{gid}'::text[]);
+
+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(12, 'bg', 'bg', false,false, true,false, 'c',
'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (bg_id)) INHERITS(tiger.${lookup_name});" ',
'${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}'')); "
-----------------------------------------------------------------------
Summary of changes:
extras/tiger_geocoder/tiger_loader_2020.sql | 37 +++++++++++++++++++++++++++--
1 file changed, 35 insertions(+), 2 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list