[postgis-tickets] [SCM] PostGIS branch main updated. 3.1.0rc1-376-gd1d0d18
git at osgeo.org
git at osgeo.org
Thu Jul 29 15:07:11 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 d1d0d181bf07980893fd4873f748de35a9d138a3 (commit)
via 937f2600d782c900fbca123a18d7995eeab2396a (commit)
from f885c33548a5f58f2e1568765f87a93f6dc36333 (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 d1d0d181bf07980893fd4873f748de35a9d138a3
Author: Regina Obe <lr at pcorp.us>
Date: Thu Jul 29 18:07:01 2021 -0400
CREATE TABLE IF NOT EXISTS is now supported on all we support
diff --git a/extras/tiger_geocoder/upgrade_geocode.sql b/extras/tiger_geocoder/upgrade_geocode.sql
index 95e11bd..6a32af6 100644
--- a/extras/tiger_geocoder/upgrade_geocode.sql
+++ b/extras/tiger_geocoder/upgrade_geocode.sql
@@ -52,9 +52,9 @@ DROP FUNCTION IF EXISTS geocode_address(norm_addy,integer); /** changed to inclu
DROP FUNCTION IF EXISTS interpolate_from_address(integer, character varying, character varying, geometry); /** changed to use default args and added offset and side **/
DROP FUNCTION IF EXISTS interpolate_from_address(integer, integer, integer, geometry); /**don't need this since got collapes into varchar version **/
--- this will fail if already exists, that is fine. can't use IF NOT EXISTS until 9.1
+-- this will fail if already exists, that is fine.
SELECT tiger.SetSearchPathForInstall('tiger');
-CREATE TABLE addrfeat
+CREATE TABLE IF NOT EXISTS addrfeat
(
gid serial not null primary key,
tlid bigint,
@@ -305,4 +305,4 @@ SELECT install_missing_indexes();
--\i generate_drop_dupe_featnames.sql
\o
--\i drop_dup_feat_create_index.sql
-\echo 'Missing index Install completed'
\ No newline at end of file
+\echo 'Missing index Install completed'
commit 937f2600d782c900fbca123a18d7995eeab2396a
Author: Regina Obe <lr at pcorp.us>
Date: Thu Jul 29 18:04:38 2021 -0400
Fixes for faces. References #4956
diff --git a/extras/tiger_geocoder/tiger_loader_2020.sql b/extras/tiger_geocoder/tiger_loader_2020.sql
index edfe17d..ee290db 100644
--- a/extras/tiger_geocoder/tiger_loader_2020.sql
+++ b/extras/tiger_geocoder/tiger_loader_2020.sql
@@ -91,27 +91,6 @@ 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);
@@ -137,6 +116,27 @@ $$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100;
+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)
+);
+
-- Helper function that generates script to drop all tables in a particular schema for a particular table
-- This is useful in case you need to reload a state
CREATE OR REPLACE FUNCTION drop_state_tables_generate_script(param_state text, param_schema text DEFAULT 'tiger_data')
@@ -342,22 +342,22 @@ ${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${lookup_name}_the_geom_
${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_countyfp ON ${data_schema}.${state_abbrev}_${lookup_name} USING btree(countyfp);"');
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',
+VALUES(13, 'zcta5_raw', 'zcta520', 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), the_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']);
+, ARRAY['gid','geoid','geoid10', 'geoid20', '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 )
+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(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});" ',
'${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${table_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${lookup_name} USING gist(the_geom);"
${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${lookup_name}_tfid ON ${data_schema}.${state_abbrev}_${lookup_name} USING btree (tfid);"
${psql} -c "CREATE INDEX idx_${data_schema}_${state_abbrev}_${table_name}_countyfp ON ${data_schema}.${state_abbrev}_${table_name} USING btree (countyfp);"
${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};"');
+ ${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"', ARRAY['gid'] );
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(7, 'featnames', 'featnames', true, true, false,false, 'a',
-----------------------------------------------------------------------
Summary of changes:
extras/tiger_geocoder/tiger_loader_2020.sql | 50 ++++++++++++++---------------
extras/tiger_geocoder/upgrade_geocode.sql | 6 ++--
2 files changed, 28 insertions(+), 28 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list