[SCM] PostGIS branch master updated. 3.6.0rc2-122-g115ee04ba
git at osgeo.org
git at osgeo.org
Tue Oct 7 15:38:26 PDT 2025
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, master has been updated
via 115ee04ba2263ff714bd4f66b77cc3be1d5c35e1 (commit)
via 1ee415c1a24238ca26f0608d80707c649349b713 (commit)
from 56c2cc85083f125e9d5f623b9de1b20370d558ef (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 115ee04ba2263ff714bd4f66b77cc3be1d5c35e1
Author: Regina Obe <lr at pcorp.us>
Date: Tue Oct 7 18:27:17 2025 -0400
TIGER EXTENSION SECURITY fix
- Ensure tables are created by tiger extension and bale if not.
Patch provided by Andrey Borodin (Yandex)
Closes #5998 for PostGIS 3.7.0
diff --git a/extras/tiger_geocoder/geocode_settings.sql b/extras/tiger_geocoder/geocode_settings.sql
index e126f825b..e5c09b8c2 100644
--- a/extras/tiger_geocoder/geocode_settings.sql
+++ b/extras/tiger_geocoder/geocode_settings.sql
@@ -22,10 +22,16 @@ BEGIN
IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'geocode_settings') THEN
CREATE TABLE geocode_settings(name text primary key, setting text, unit text, category text, short_desc text);
GRANT SELECT ON geocode_settings TO public;
+ ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS geocode_settings(name text primary key, setting text, unit text, category text, short_desc text);
END IF;
IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'geocode_settings_default') THEN
CREATE TABLE geocode_settings_default(name text primary key, setting text, unit text, category text, short_desc text);
GRANT SELECT ON geocode_settings_default TO public;
+ ELSE
+ -- Same as above.
+ CREATE TABLE IF NOT EXISTS geocode_settings_default(name text primary key, setting text, unit text, category text, short_desc text);
END IF;
--recreate defaults
TRUNCATE TABLE geocode_settings_default;
diff --git a/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql b/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql
index ff78fc8f8..da6fc91a9 100644
--- a/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql
+++ b/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql
@@ -11,14 +11,23 @@ BEGIN
IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'pagc_gaz') THEN
CREATE TABLE pagc_gaz (id serial NOT NULL primary key ,seq integer ,word text, stdword text, token integer,is_custom boolean NOT NULL default true);
GRANT SELECT ON pagc_gaz TO public;
+ ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS pagc_gaz (id serial NOT NULL primary key ,seq integer ,word text, stdword text, token integer,is_custom boolean NOT NULL default true);
END IF;
IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'pagc_lex') THEN
CREATE TABLE pagc_lex (id serial NOT NULL primary key,seq integer,word text,stdword text,token integer,is_custom boolean NOT NULL default true);
GRANT SELECT ON pagc_lex TO public;
+ ELSE
+ -- Same as above.
+ CREATE TABLE IF NOT EXISTS pagc_lex (id serial NOT NULL primary key,seq integer,word text,stdword text,token integer,is_custom boolean NOT NULL default true);
END IF;
IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'pagc_rules') THEN
CREATE TABLE pagc_rules (id serial NOT NULL primary key,rule text, is_custom boolean DEFAULT true);
GRANT SELECT ON pagc_rules TO public;
+ ELSE
+ -- Same as above.
+ CREATE TABLE IF NOT EXISTS pagc_rules (id serial NOT NULL primary key,rule text, is_custom boolean DEFAULT true);
END IF;
IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'pagc_gaz' AND data_type='text') THEN
-- its probably old table structure change type of lex and gaz columns
diff --git a/extras/tiger_geocoder/tiger_loader_2012.sql b/extras/tiger_geocoder/tiger_loader_2012.sql
index c4d6da1b7..00593ecf5 100644
--- a/extras/tiger_geocoder/tiger_loader_2012.sql
+++ b/extras/tiger_geocoder/tiger_loader_2012.sql
@@ -57,18 +57,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -125,9 +121,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -138,8 +132,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -149,7 +142,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2013.sql b/extras/tiger_geocoder/tiger_loader_2013.sql
index d42f60ea2..8b6e0d166 100644
--- a/extras/tiger_geocoder/tiger_loader_2013.sql
+++ b/extras/tiger_geocoder/tiger_loader_2013.sql
@@ -56,18 +56,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -124,9 +120,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -137,8 +131,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -148,7 +141,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2014.sql b/extras/tiger_geocoder/tiger_loader_2014.sql
index bedb84051..8726e5afe 100644
--- a/extras/tiger_geocoder/tiger_loader_2014.sql
+++ b/extras/tiger_geocoder/tiger_loader_2014.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
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
@@ -144,18 +210,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -212,9 +274,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -225,8 +285,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -236,7 +295,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2015.sql b/extras/tiger_geocoder/tiger_loader_2015.sql
index fe0bdf62f..3311be2e7 100644
--- a/extras/tiger_geocoder/tiger_loader_2015.sql
+++ b/extras/tiger_geocoder/tiger_loader_2015.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
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
@@ -144,18 +210,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -212,9 +274,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -225,8 +285,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -236,7 +295,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2016.sql b/extras/tiger_geocoder/tiger_loader_2016.sql
index 0cb93e442..39dd37f0a 100644
--- a/extras/tiger_geocoder/tiger_loader_2016.sql
+++ b/extras/tiger_geocoder/tiger_loader_2016.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
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
@@ -144,18 +210,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -211,9 +273,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -224,8 +284,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -235,7 +294,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2017.sql b/extras/tiger_geocoder/tiger_loader_2017.sql
index 9a7c6a726..5644a4d53 100644
--- a/extras/tiger_geocoder/tiger_loader_2017.sql
+++ b/extras/tiger_geocoder/tiger_loader_2017.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
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
@@ -144,18 +210,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -211,9 +273,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -224,8 +284,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -235,7 +294,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2018.sql b/extras/tiger_geocoder/tiger_loader_2018.sql
index 61e68797a..33697e785 100644
--- a/extras/tiger_geocoder/tiger_loader_2018.sql
+++ b/extras/tiger_geocoder/tiger_loader_2018.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
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
@@ -144,18 +210,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -211,9 +273,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -224,8 +284,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -235,7 +294,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2019.sql b/extras/tiger_geocoder/tiger_loader_2019.sql
index 12a395b60..9485011cf 100644
--- a/extras/tiger_geocoder/tiger_loader_2019.sql
+++ b/extras/tiger_geocoder/tiger_loader_2019.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
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
@@ -144,18 +210,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -211,9 +273,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -224,8 +284,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -235,7 +294,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2020.sql b/extras/tiger_geocoder/tiger_loader_2020.sql
index 6afce151a..01c81f26a 100644
--- a/extras/tiger_geocoder/tiger_loader_2020.sql
+++ b/extras/tiger_geocoder/tiger_loader_2020.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
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
@@ -165,18 +231,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -232,9 +294,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -245,8 +305,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -256,7 +315,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2021.sql b/extras/tiger_geocoder/tiger_loader_2021.sql
index 0cdd7ee5a..675bff9cd 100644
--- a/extras/tiger_geocoder/tiger_loader_2021.sql
+++ b/extras/tiger_geocoder/tiger_loader_2021.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
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
@@ -172,18 +238,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -239,9 +301,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -252,8 +312,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -263,7 +322,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2022.sql b/extras/tiger_geocoder/tiger_loader_2022.sql
index af4b9ee25..991ce2255 100644
--- a/extras/tiger_geocoder/tiger_loader_2022.sql
+++ b/extras/tiger_geocoder/tiger_loader_2022.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
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
@@ -175,18 +241,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -242,9 +304,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -255,8 +315,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -266,7 +325,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2023.sql b/extras/tiger_geocoder/tiger_loader_2023.sql
index 691176236..65c497ce2 100644
--- a/extras/tiger_geocoder/tiger_loader_2023.sql
+++ b/extras/tiger_geocoder/tiger_loader_2023.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
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
@@ -175,18 +241,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -242,9 +304,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -255,8 +315,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -266,7 +325,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2024.sql b/extras/tiger_geocoder/tiger_loader_2024.sql
index f6d7f0b85..1c9b87830 100644
--- a/extras/tiger_geocoder/tiger_loader_2024.sql
+++ b/extras/tiger_geocoder/tiger_loader_2024.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tiger.tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tiger.tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tiger.bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
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
@@ -175,18 +241,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- 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);
- END IF;
+ CREATE TABLE IF NOT EXISTS 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);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -242,9 +304,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -255,8 +315,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -266,7 +325,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
commit 1ee415c1a24238ca26f0608d80707c649349b713
Author: Regina Obe <lr at pcorp.us>
Date: Tue Oct 7 18:26:13 2025 -0400
Fix winnie scripts
diff --git a/ci/winnie/regress_postgis.sh b/ci/winnie/regress_postgis.sh
index 10aa48c0e..229a16ed1 100644
--- a/ci/winnie/regress_postgis.sh
+++ b/ci/winnie/regress_postgis.sh
@@ -68,11 +68,11 @@ if [ $INCLUDE_MINOR_LIB == "1" ]; then
EXTRA_CONFIGURE_ARGS="${EXTRA_CONFIGURE_ARGS} --with-library-minor-version"
fi
-if [ $REGRESS_WITHOUT_TOPOLOGY == "1" ]; then
+if [ $REGRESS_WITHOUT_TOPOLOGY == "1" ]; then
EXTRA_CONFIGURE_ARGS="${EXTRA_CONFIGURE_ARGS} --without-topology"
fi
-if [ $REGRESS_WITHOUT_RASTER == "1" ]; then
+if [ $REGRESS_WITHOUT_RASTER == "1" ]; then
EXTRA_CONFIGURE_ARGS="${EXTRA_CONFIGURE_ARGS} --without-raster"
fi
@@ -103,9 +103,9 @@ make -j 4
make install
# don't run tests twice. Only run regular if extension test is not asked for
-if [ "$MAKE_EXTENSION" == "" ]; then
+if [ "$MAKE_EXTENSION" == "0" ]; then
make check RUNTESTFLAGS=-v
-fi
+fi
if [ "$MAKE_EXTENSION" == "1" ]; then
@@ -117,13 +117,13 @@ if [ "$MAKE_EXTENSION" == "1" ]; then
#strip raster/rt_pg/postgis_raster-*.dll
#strip sfcgal/*.dll
- if [ $REGRESS_WITHOUT_TOPOLOGY == "" ]; then
+ if [ $REGRESS_WITHOUT_TOPOLOGY == "0" ]; then
cp -r topology/*.dll ${PGPATHEDB}/lib
fi
cp postgis/postgis*.dll ${PGPATHEDB}/lib
cp sfcgal/*.dll ${PGPATHEDB}/lib
- if [ $REGRESS_WITHOUT_RASTER == "" ]; then
+ if [ $REGRESS_WITHOUT_RASTER == "0" ]; then
cp raster/rt_pg/postgis_raster-*.dll ${PGPATHEDB}/lib
fi
@@ -141,18 +141,18 @@ export UPGRADEABLE_VERSIONS=$value
export WIN_RELEASED_VERSIONS="2.0.0 2.0.1 2.0.3 2.0.4 2.0.6 2.1.4 2.1.7 2.1.8 2.2.0 2.2.3 2.3.0 2.3.7 2.4.0 2.4.4"
export extensions_to_install="postgis postgis_sfcgal postgis_tiger_geocoder address_standardizer"
-if [ $REGRESS_WITHOUT_TOPOLOGY == "" ]; then
+if [ $REGRESS_WITHOUT_TOPOLOGY == "0" ]; then
extensions_to_install="${extensions_to_install} postgis_topology"
fi
-if [ $REGRESS_WITHOUT_RASTER == "" ]; then
+if [ $REGRESS_WITHOUT_RASTER == "0" ]; then
extensions_to_install="${extensions_to_install} postgis_raster"
fi
#echo "Versions are: $UPGRADEABLE_VERSIONS"
for EXTNAME in $extensions_to_install; do
-
+
cp extensions/$EXTNAME/sql/* ${PGPATHEDB}/share/extension
cp extensions/$EXTNAME/sql/$EXTNAME--TEMPLATED--TO--ANY.sql ${PGPATHEDB}/share/extension/$EXTNAME--$POSTGIS_MICRO_VER--${POSTGIS_MINOR_MAX_VER}.sql;
diff --git a/ci/winnie/winnie_common.sh b/ci/winnie/winnie_common.sh
index e81b946f0..d375cbec6 100644
--- a/ci/winnie/winnie_common.sh
+++ b/ci/winnie/winnie_common.sh
@@ -65,6 +65,18 @@ fi;
echo "LZ4_VER ${LZ4_VER}"
+if [[ "${REGRESS_WITHOUT_TOPOLOGY}" == '' ]] ; then
+ export REGRESS_WITHOUT_TOPOLOGY=0
+fi;
+
+if [[ "${REGRESS_WITHOUT_RASTER}" == '' ]] ; then
+ export REGRESS_WITHOUT_RASTER=0
+fi;
+
+if [[ "${MAKE_EXTENSION}" == '' ]] ; then
+ export MAKE_EXTENSION=0
+fi;
+
#set to something even if override is on but not set
if [[ "${ZLIB_VER}" == '' ]] ; then
-----------------------------------------------------------------------
Summary of changes:
ci/winnie/regress_postgis.sh | 18 ++---
ci/winnie/winnie_common.sh | 12 ++++
extras/tiger_geocoder/geocode_settings.sql | 6 ++
.../tiger_geocoder/pagc_normalize/pagc_tables.sql | 9 +++
extras/tiger_geocoder/tiger_loader_2012.sql | 16 ++---
extras/tiger_geocoder/tiger_loader_2013.sql | 16 ++---
extras/tiger_geocoder/tiger_loader_2014.sql | 82 ++++++++++++++++++----
extras/tiger_geocoder/tiger_loader_2015.sql | 82 ++++++++++++++++++----
extras/tiger_geocoder/tiger_loader_2016.sql | 82 ++++++++++++++++++----
extras/tiger_geocoder/tiger_loader_2017.sql | 82 ++++++++++++++++++----
extras/tiger_geocoder/tiger_loader_2018.sql | 82 ++++++++++++++++++----
extras/tiger_geocoder/tiger_loader_2019.sql | 82 ++++++++++++++++++----
extras/tiger_geocoder/tiger_loader_2020.sql | 82 ++++++++++++++++++----
extras/tiger_geocoder/tiger_loader_2021.sql | 82 ++++++++++++++++++----
extras/tiger_geocoder/tiger_loader_2022.sql | 82 ++++++++++++++++++----
extras/tiger_geocoder/tiger_loader_2023.sql | 82 ++++++++++++++++++----
extras/tiger_geocoder/tiger_loader_2024.sql | 82 ++++++++++++++++++----
17 files changed, 814 insertions(+), 165 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list