[SCM] PostGIS branch master updated. 3.6.0beta1-15-gbc0854651
git at osgeo.org
git at osgeo.org
Mon Aug 4 16:13:35 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 bc08546510ebfbd458c42f22dd696f50bd81ac95 (commit)
from 8ec61f3b879a64cda6f2b1f82cca0acaf316cd87 (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 bc08546510ebfbd458c42f22dd696f50bd81ac95
Author: Regina Obe <lr at pcorp.us>
Date: Tue Aug 13 19:09:48 2024 -0400
SCHEMA qualify extension dependencies
for PG16 and above for postgis_tiger_geocoder
- schema qualify in scripts dependent extensions postgis, fuzzystrmatch
- add a no_relocate clause to the .control file to prevent relocation
of these for PG16 and above
- Use for new installs typmod for geometry instead of constraints
- Ignore regression.out files generated by installcheck
Closes #5359
Closes #5897
Closes https://git.osgeo.org/gitea/postgis/postgis/pulls/260
diff --git a/.gitignore b/.gitignore
index 1349b5322..e9e76c0a9 100644
--- a/.gitignore
+++ b/.gitignore
@@ -27,6 +27,7 @@ doc/html/postgis-*
doc/images
doc/html/images/generator
doc/html/images/Makefile
+regression.out
# PNG images under doc/html/images
# should be either symlinks or generated
diff --git a/NEWS b/NEWS
index 895ca9083..b9105175a 100644
--- a/NEWS
+++ b/NEWS
@@ -19,6 +19,9 @@ Dapeng Wang, Zuo Chenwei from HighGo (Chinese Team)
- GT-252, ST_NumGeometries/ST_GeometryN treat TIN and PolyhedralSurface as unitary geometries,
use ST_NumPatches/ST_PatchN for patch access (Loïc Bartoletti)
- #3110, GT-242 [topology] Support for bigint (Ayo Adesugba, U.S. Census Bureau)
+ - #5359, #5897, GT-260 [tiger_geocoder] Use @extschema:extension@ for PG >= 16
+ to schema qualify dependent extensions, switch to use typmod for tiger tables
+ (Regina Obe)
* Deprecated / Removed signatures *
diff --git a/extensions/postgis_tiger_geocoder/Makefile.in b/extensions/postgis_tiger_geocoder/Makefile.in
index 37193e0ae..2d8e80259 100644
--- a/extensions/postgis_tiger_geocoder/Makefile.in
+++ b/extensions/postgis_tiger_geocoder/Makefile.in
@@ -1,5 +1,6 @@
include @srcdir@/../upgradeable_versions.mk
+POSTGIS_PGSQL_VERSION=@POSTGIS_PGSQL_VERSION@
EXTENSION = postgis_tiger_geocoder
EXTVERSION = @POSTGIS_LIB_VERSION@
MINORVERSION = 2011. at POSTGIS_MAJOR_VERSION@. at POSTGIS_MINOR_VERSION@
@@ -50,8 +51,15 @@ expected:
sql:
mkdir -p $@
+
+## PostgreSQL < 16 doesn't understand new @extschema:extname@ syntax, so strip it
+ifeq ($(shell expr "$(POSTGIS_PGSQL_VERSION)" "<" 160),1)
sql/$(EXTENSION)--$(EXTVERSION).sql: sql/$(EXTENSION).sql | sql
+ $(PERL) -lpe "s/\@extschema:[a-z]+\@\./ /g" < $< > $@
+else
+ sql/$(EXTENSION)--$(EXTVERSION).sql: sql/$(EXTENSION).sql | sql
cp $< $@
+endif
expected/test-normalize_address.out: sql_bits/test_tuples_only_unaligned.sql.in ../../extras/tiger_geocoder/regress/normalize_address_regress | expected
cat $^ > $@
@@ -66,10 +74,15 @@ sql/test-normalize_address.sql: sql_bits/test_tuples_only_unaligned.sql.in ../..
sql/test-upgrade.sql: | sql
echo 'ALTER EXTENSION ${EXTENSION} UPDATE TO "ANY"; ALTER EXTENSION ${EXTENSION} UPDATE TO "$(EXTVERSION)"' > $@
+## no_relocate clause is only availabe in PostgreSQL 16 and above, so strip it for lower
+ifeq ($(shell expr "$(POSTGIS_PGSQL_VERSION)" "<" 160),1)
%.control: %.control.in Makefile
- cat $< \
- | $(PERL) -lpe "s'@EXTVERSION@'$(EXTVERSION)'g" \
- > $@
+ $(PERL) -lpe 's/^no_relocate.*//g; s/\@EXTVERSION\@/$(EXTVERSION)/g' < "$<" > "$@"
+else
+%.control: %.control.in Makefile
+ $(PERL) -lpe "s'@EXTVERSION@'$(EXTVERSION)'g" \
+ < "$<" > "$@"
+endif
expected/test-upgrade.out: sql/test-upgrade.sql | expected
cp $< $@
diff --git a/extensions/postgis_tiger_geocoder/postgis_tiger_geocoder.control.in b/extensions/postgis_tiger_geocoder/postgis_tiger_geocoder.control.in
index 25a963e47..647cba6b8 100644
--- a/extensions/postgis_tiger_geocoder/postgis_tiger_geocoder.control.in
+++ b/extensions/postgis_tiger_geocoder/postgis_tiger_geocoder.control.in
@@ -5,3 +5,4 @@ relocatable = false
schema = tiger
requires = 'postgis,fuzzystrmatch'
superuser= false
+no_relocate = 'postgis,fuzzystrmatch'
\ No newline at end of file
diff --git a/extras/tiger_geocoder/geocode/geocode_intersection.sql b/extras/tiger_geocoder/geocode/geocode_intersection.sql
index 12db663aa..3029e23c1 100644
--- a/extras/tiger_geocoder/geocode/geocode_intersection.sql
+++ b/extras/tiger_geocoder/geocode/geocode_intersection.sql
@@ -100,10 +100,10 @@ BEGIN
ST_StartPoint(ST_GeometryN(ST_Multi(e1.the_geom),1))
ELSE ST_EndPoint(ST_GeometryN(ST_Multi(e1.the_geom),1)) END AS geom ,
CASE WHEN lower(p.name) = $3 THEN 0 ELSE 1 END
- + levenshtein_ignore_case(p.name, $3)
- + levenshtein_ignore_case(e1.name || COALESCE('' '' || e1.sufqualabr, ''''),$2) +
- CASE WHEN e1.fullname = $6 THEN 0 ELSE levenshtein_ignore_case(e1.fullname, $6) END +
- + levenshtein_ignore_case(e2.name || COALESCE('' '' || e2.sufqualabr, ''''),$4)
+ + tiger.levenshtein_ignore_case(p.name, $3)
+ + tiger.levenshtein_ignore_case(e1.name || COALESCE('' '' || e1.sufqualabr, ''''),$2) +
+ CASE WHEN e1.fullname = $6 THEN 0 ELSE tiger.levenshtein_ignore_case(e1.fullname, $6) END +
+ + tiger.levenshtein_ignore_case(e2.name || COALESCE('' '' || e2.sufqualabr, ''''),$4)
AS a_rating
FROM e1
INNER JOIN e2 ON (
diff --git a/extras/tiger_geocoder/geocode/geocode_location.sql b/extras/tiger_geocoder/geocode/geocode_location.sql
index c99ee3d44..af062fcbf 100644
--- a/extras/tiger_geocoder/geocode/geocode_location.sql
+++ b/extras/tiger_geocoder/geocode/geocode_location.sql
@@ -24,7 +24,7 @@ BEGIN
zip.zip as zip,
ST_Centroid(zcta5.the_geom) as address_geom,
stusps as state,
- 100::integer + coalesce(levenshtein_ignore_case(coalesce(zip.city), parsed.location),0) as in_rating
+ 100::integer + coalesce( tiger.levenshtein_ignore_case(coalesce(zip.city), parsed.location),0) as in_rating
FROM
zip_lookup_base zip
JOIN zcta5 ON (zip.zip = zcta5.zcta5ce AND zip.statefp = zcta5.statefp)
@@ -32,7 +32,7 @@ BEGIN
WHERE
parsed.zip = zip.zip OR
(soundex(zip.city) = soundex(parsed.location) and zip.statefp = in_statefp)
- ORDER BY levenshtein_ignore_case(coalesce(zip.city), parsed.location), zip.zip
+ ORDER BY tiger.levenshtein_ignore_case(coalesce(zip.city), parsed.location), zip.zip
LOOP
ADDY.location := result.place;
ADDY.stateAbbrev := result.state;
@@ -58,11 +58,11 @@ BEGIN
|| ' pl.name as place, '
|| ' state.stusps as stateAbbrev, '
|| ' ST_Centroid(pl.the_geom) as address_geom, '
- || ' 100::integer + levenshtein_ignore_case(coalesce(pl.name), ' || quote_literal(coalesce(parsed.location,'')) || ') as in_rating '
+ || ' 100::integer + tiger.levenshtein_ignore_case(coalesce(pl.name), ' || quote_literal(coalesce(parsed.location,'')) || ') as in_rating '
|| ' FROM (SELECT * FROM place WHERE statefp = ' || quote_literal(coalesce(in_statefp,'')) || ' ' || COALESCE(' AND ST_Intersects(' || quote_literal(restrict_geom::text) || '::geometry, the_geom)', '') || ') AS pl '
|| ' INNER JOIN state ON(pl.statefp = state.statefp)'
|| ' WHERE soundex(pl.name) = soundex(' || quote_literal(coalesce(parsed.location,'')) || ') and pl.statefp = ' || quote_literal(COALESCE(in_statefp,''))
- || ' ORDER BY levenshtein_ignore_case(coalesce(pl.name), ' || quote_literal(coalesce(parsed.location,'')) || ');'
+ || ' ORDER BY tiger.levenshtein_ignore_case(coalesce(pl.name), ' || quote_literal(coalesce(parsed.location,'')) || ');'
;
IF var_debug THEN
diff --git a/extras/tiger_geocoder/geocode/rate_attributes.sql b/extras/tiger_geocoder/geocode/rate_attributes.sql
index 17b44216e..1949dcbde 100644
--- a/extras/tiger_geocoder/geocode/rate_attributes.sql
+++ b/extras/tiger_geocoder/geocode/rate_attributes.sql
@@ -14,7 +14,7 @@ DECLARE
var_verbose BOOLEAN := FALSE;
BEGIN
IF locationA IS NOT NULL AND locationB IS NOT NULL THEN
- result := levenshtein_ignore_case(locationA, locationB);
+ result := tiger.levenshtein_ignore_case(locationA, locationB);
ELSE
IF var_verbose THEN
RAISE NOTICE 'rate_attributes() - Location names cannot be null!';
@@ -41,7 +41,7 @@ DECLARE
typeWeight INTEGER := 5;
var_verbose BOOLEAN := false;
BEGIN
- result := result + levenshtein_ignore_case(cull_null($1), cull_null($2)) * directionWeight;
+ result := result + tiger.levenshtein_ignore_case(cull_null($1), cull_null($2)) * directionWeight;
IF var_verbose THEN
RAISE NOTICE 'streetNameA: %, streetNameB: %', streetNameA, streetNameB;
END IF;
@@ -52,15 +52,15 @@ BEGIN
IF prequalabr IS NOT NULL THEN
-- If the reference address (streetNameB) has a prequalabr streetNameA (prequalabr) - note: streetNameB usually comes thru without prequalabr
-- and the input street (streetNameA) is lacking the prequal -- only penalize a little
- result := (result + levenshtein_ignore_case( trim( trim( lower(streetNameA),lower(prequalabr) ) ), trim( trim( lower(streetNameB),lower(prequalabr) ) ) )*nameWeight*0.75 + levenshtein_ignore_case(trim(streetNameA),prequalabr || ' ' || streetNameB) * nameWeight*0.25)::integer;
+ result := (result + tiger.levenshtein_ignore_case( trim( trim( lower(streetNameA),lower(prequalabr) ) ), trim( trim( lower(streetNameB),lower(prequalabr) ) ) )*nameWeight*0.75 + tiger.levenshtein_ignore_case(trim(streetNameA),prequalabr || ' ' || streetNameB) * nameWeight*0.25)::integer;
ELSE
- result := result + levenshtein_ignore_case(streetNameA, streetNameB) * nameWeight;
+ result := result + tiger.levenshtein_ignore_case(streetNameA, streetNameB) * nameWeight;
END IF;
ELSE
-- Penalize for numeric streets if one is completely numeric and the other is not
-- This is to minimize on highways like 3A being matched with numbered streets since streets are usually number followed by 2 characters e.g nth ave and highways are just number with optional letter for name
IF (streetNameB ~ E'[a-zA-Z]{2,10}' AND NOT (streetNameA ~ E'[a-zA-Z]{2,10}') ) OR (streetNameA ~ E'[a-zA-Z]{2,10}' AND NOT (streetNameB ~ E'[a-zA-Z]{2,10}') ) THEN
- result := result + levenshtein_ignore_case(streetNameA, streetNameB) * nameWeight;
+ result := result + tiger.levenshtein_ignore_case(streetNameA, streetNameB) * nameWeight;
END IF;
END IF;
ELSE
@@ -69,9 +69,9 @@ BEGIN
END IF;
RETURN NULL;
END IF;
- result := result + levenshtein_ignore_case(cull_null(streetTypeA), cull_null(streetTypeB)) *
+ result := result + tiger.levenshtein_ignore_case(cull_null(streetTypeA), cull_null(streetTypeB)) *
typeWeight;
- result := result + levenshtein_ignore_case(cull_null(dirsA), cull_null(dirsB)) *
+ result := result + tiger.levenshtein_ignore_case(cull_null(dirsA), cull_null(dirsB)) *
directionWeight;
return result;
END;
diff --git a/extras/tiger_geocoder/normalize/end_soundex.sql b/extras/tiger_geocoder/normalize/end_soundex.sql
index 7b0163502..36a8bda56 100644
--- a/extras/tiger_geocoder/normalize/end_soundex.sql
+++ b/extras/tiger_geocoder/normalize/end_soundex.sql
@@ -8,9 +8,9 @@ DECLARE
BEGIN
tempString := substring($1, E'[ ,.\n\t\f]([a-zA-Z0-9]*)$');
IF tempString IS NOT NULL THEN
- tempString := soundex(tempString);
+ tempString := @extschema:fuzzystrmatch at .soundex(tempString);
ELSE
- tempString := soundex($1);
+ tempString := @extschema:fuzzystrmatch at .soundex($1);
END IF;
return tempString;
END;
diff --git a/extras/tiger_geocoder/normalize/location_extract.sql b/extras/tiger_geocoder/normalize/location_extract.sql
index be891e2bb..713a08030 100644
--- a/extras/tiger_geocoder/normalize/location_extract.sql
+++ b/extras/tiger_geocoder/normalize/location_extract.sql
@@ -47,21 +47,21 @@ BEGIN
stmt := ' SELECT'
|| ' 1,'
|| ' name,'
- || ' levenshtein_ignore_case(' || quote_literal(tempString) || ',name) as rating,'
+ || ' tiger.levenshtein_ignore_case(' || quote_literal(tempString) || ',name) as rating,'
|| ' length(name) as len'
|| ' FROM place'
|| ' WHERE ' || CASE WHEN stateAbbrev IS NOT NULL THEN 'statefp = ' || quote_literal(lstate) || ' AND ' ELSE '' END
- || ' soundex(' || quote_literal(tempString) || ') = soundex(name)'
- || ' AND levenshtein_ignore_case(' || quote_literal(tempString) || ',name) <= 2 '
+ || ' @extschema:fuzzystrmatch at .soundex(' || quote_literal(tempString) || ') = @extschema:fuzzystrmatch at .soundex(name)'
+ || ' AND tiger.levenshtein_ignore_case(' || quote_literal(tempString) || ',name) <= 2 '
|| ' UNION ALL SELECT'
|| ' 2,'
|| ' name,'
- || ' levenshtein_ignore_case(' || quote_literal(tempString) || ',name) as rating,'
+ || ' tiger.levenshtein_ignore_case(' || quote_literal(tempString) || ',name) as rating,'
|| ' length(name) as len'
|| ' FROM cousub'
|| ' WHERE ' || CASE WHEN stateAbbrev IS NOT NULL THEN 'statefp = ' || quote_literal(lstate) || ' AND ' ELSE '' END
- || ' soundex(' || quote_literal(tempString) || ') = soundex(name)'
- || ' AND levenshtein_ignore_case(' || quote_literal(tempString) || ',name) <= 2 '
+ || ' @extschema:fuzzystrmatch at .soundex(' || quote_literal(tempString) || ') = @extschema:fuzzystrmatch at .soundex(name)'
+ || ' AND tiger.levenshtein_ignore_case(' || quote_literal(tempString) || ',name) <= 2 '
|| ' ORDER BY '
|| ' 3 ASC, 1 ASC, 4 DESC'
|| ' LIMIT 1;'
diff --git a/extras/tiger_geocoder/normalize/location_extract_countysub_fuzzy.sql b/extras/tiger_geocoder/normalize/location_extract_countysub_fuzzy.sql
index a7c642a34..bf9bcc207 100644
--- a/extras/tiger_geocoder/normalize/location_extract_countysub_fuzzy.sql
+++ b/extras/tiger_geocoder/normalize/location_extract_countysub_fuzzy.sql
@@ -31,10 +31,10 @@ BEGIN
lstate := statefp FROM state WHERE stusps = stateAbbrev;
SELECT INTO tempInt count(*) FROM cousub
WHERE cousub.statefp = lstate
- AND soundex(tempString) = end_soundex(name);
+ AND @extschema:fuzzystrmatch at .soundex(tempString) = end_soundex(name);
ELSE
SELECT INTO tempInt count(*) FROM cousub
- WHERE soundex(tempString) = end_soundex(name);
+ WHERE @extschema:fuzzystrmatch at .soundex(tempString) = end_soundex(name);
END IF;
IF tempInt > 0 THEN
@@ -43,41 +43,41 @@ BEGIN
IF stateAbbrev IS NOT NULL THEN
FOR rec IN SELECT name FROM cousub
WHERE cousub.statefp = lstate
- AND soundex(tempString) = end_soundex(name) LOOP
+ AND @extschema:fuzzystrmatch at .soundex(tempString) = end_soundex(name) LOOP
word_count := count_words(rec.name);
test := TRUE;
tempString := get_last_words(fullStreet, word_count);
FOR i IN 1..word_count LOOP
- IF soundex(split_part(tempString, ' ', i)) !=
- soundex(split_part(rec.name, ' ', i)) THEN
+ IF @extschema:fuzzystrmatch at .soundex(split_part(tempString, ' ', i)) !=
+ @extschema:fuzzystrmatch at .soundex(split_part(rec.name, ' ', i)) THEN
test := FALSE;
END IF;
END LOOP;
IF test THEN
-- The soundex matched, determine if the distance is better.
- IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN
+ IF @extschema:fuzzystrmatch at .levenshtein_ignore_case(rec.name, tempString) < tempInt THEN
location := tempString;
- tempInt := levenshtein_ignore_case(rec.name, tempString);
+ tempInt := @extschema:fuzzystrmatch at .levenshtein_ignore_case(rec.name, tempString);
END IF;
END IF;
END LOOP;
ELSE
FOR rec IN SELECT name FROM cousub
- WHERE soundex(tempString) = end_soundex(name) LOOP
+ WHERE @extschema:fuzzystrmatch at .soundex(tempString) = end_soundex(name) LOOP
word_count := count_words(rec.name);
test := TRUE;
tempString := get_last_words(fullStreet, word_count);
FOR i IN 1..word_count LOOP
- IF soundex(split_part(tempString, ' ', i)) !=
- soundex(split_part(rec.name, ' ', i)) THEN
+ IF @extschema:fuzzystrmatch at .soundex(split_part(tempString, ' ', i)) !=
+ @extschema:fuzzystrmatch at .soundex(split_part(rec.name, ' ', i)) THEN
test := FALSE;
END IF;
END LOOP;
IF test THEN
-- The soundex matched, determine if the distance is better.
- IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN
+ IF @extschema:fuzzystrmatch at .levenshtein_ignore_case(rec.name, tempString) < tempInt THEN
location := tempString;
- tempInt := levenshtein_ignore_case(rec.name, tempString);
+ tempInt := @extschema:fuzzystrmatch at .levenshtein_ignore_case(rec.name, tempString);
END IF;
END IF;
END LOOP;
diff --git a/extras/tiger_geocoder/normalize/location_extract_place_fuzzy.sql b/extras/tiger_geocoder/normalize/location_extract_place_fuzzy.sql
index 30451b0da..fa8f4977b 100644
--- a/extras/tiger_geocoder/normalize/location_extract_place_fuzzy.sql
+++ b/extras/tiger_geocoder/normalize/location_extract_place_fuzzy.sql
@@ -54,9 +54,9 @@ BEGIN
END LOOP;
IF test THEN
-- The soundex matched, determine if the distance is better.
- IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN
+ IF tiger.levenshtein_ignore_case(rec.name, tempString) < tempInt THEN
location := tempString;
- tempInt := levenshtein_ignore_case(rec.name, tempString);
+ tempInt := tiger.levenshtein_ignore_case(rec.name, tempString);
END IF;
END IF;
END LOOP;
@@ -74,9 +74,9 @@ BEGIN
END LOOP;
IF test THEN
-- The soundex matched, determine if the distance is better.
- IF levenshtein_ignore_case(rec.name, tempString) < tempInt THEN
+ IF tiger.levenshtein_ignore_case(rec.name, tempString) < tempInt THEN
location := tempString;
- tempInt := levenshtein_ignore_case(rec.name, tempString);
+ tempInt := tiger.levenshtein_ignore_case(rec.name, tempString);
END IF;
END IF;
END LOOP;
diff --git a/extras/tiger_geocoder/tables/lookup_tables_2011.sql b/extras/tiger_geocoder/tables/lookup_tables_2011.sql
index 947993298..40ba3f57e 100644
--- a/extras/tiger_geocoder/tables/lookup_tables_2011.sql
+++ b/extras/tiger_geocoder/tables/lookup_tables_2011.sql
@@ -802,7 +802,7 @@ INSERT INTO place_lookup
JOIN state_lookup sl ON (pl.state = lpad(sl.st_code,2,'0'))
GROUP BY pl.state, sl.abbrev, pl.placefp, pl.name;
**/
-CREATE INDEX place_lookup_name_idx ON place_lookup (soundex(name));
+CREATE INDEX place_lookup_name_idx ON place_lookup ( @extschema:fuzzystrmatch at .soundex(name) );
CREATE INDEX place_lookup_state_idx ON place_lookup (state);
DROP TABLE IF EXISTS tiger.county_lookup;
@@ -826,7 +826,7 @@ INSERT INTO county_lookup
JOIN state_lookup sl ON (co.state = lpad(sl.st_code,2,'0'))
GROUP BY co.state, sl.abbrev, co.county, co.name;
**/
-CREATE INDEX county_lookup_name_idx ON county_lookup (soundex(name));
+CREATE INDEX county_lookup_name_idx ON county_lookup ( @extschema:fuzzystrmatch at .soundex(name) );
CREATE INDEX county_lookup_state_idx ON county_lookup (state);
DROP TABLE IF EXISTS tiger.countysub_lookup;
@@ -855,7 +855,7 @@ INSERT INTO countysub_lookup
JOIN county_lookup cl ON (cs.state = lpad(cl.st_code,2,'0') AND cs.county = cl.co_code)
GROUP BY cs.state, sl.abbrev, cs.county, cl.name, cs.cousubfp, cs.name;
**/
-CREATE INDEX countysub_lookup_name_idx ON countysub_lookup (soundex(name));
+CREATE INDEX countysub_lookup_name_idx ON countysub_lookup ( @extschema:fuzzystrmatch at .soundex(name) );
CREATE INDEX countysub_lookup_state_idx ON countysub_lookup (state);
DROP TABLE IF EXISTS tiger.zip_lookup_all;
@@ -982,12 +982,9 @@ CREATE TABLE county
awater double precision,
intptlat character varying(11),
intptlon character varying(12),
- the_geom geometry,
+ the_geom @extschema:postgis at .geometry(MULTIPOLYGON,4269),
CONSTRAINT uidx_county_gid UNIQUE (gid),
- CONSTRAINT pk_tiger_county PRIMARY KEY (cntyidfp),
- 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)
+ CONSTRAINT pk_tiger_county PRIMARY KEY (cntyidfp)
);
CREATE INDEX idx_tiger_county ON county USING btree (countyfp);
@@ -1008,13 +1005,10 @@ CREATE TABLE state
awater bigint,
intptlat character varying(11),
intptlon character varying(12),
- the_geom geometry,
+ the_geom @extschema:postgis at .geometry(MULTIPOLYGON,4269),
CONSTRAINT uidx_tiger_state_stusps UNIQUE (stusps),
CONSTRAINT uidx_tiger_state_gid UNIQUE (gid),
- CONSTRAINT pk_tiger_state PRIMARY KEY (statefp),
- CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
- CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
- CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269)
+ CONSTRAINT pk_tiger_state PRIMARY KEY (statefp)
);
CREATE INDEX idx_tiger_state_the_geom_gist ON state USING gist(the_geom);
@@ -1039,11 +1033,8 @@ CREATE TABLE place
awater bigint,
intptlat character varying(11),
intptlon character varying(12),
- the_geom geometry,
- CONSTRAINT uidx_tiger_place_gid UNIQUE (gid),
- CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
- CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
- CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269)
+ the_geom @extschema:postgis at .geometry(MULTIPOLYGON,4269),
+ CONSTRAINT uidx_tiger_place_gid UNIQUE (gid)
);
CREATE INDEX tiger_place_the_geom_gist ON place USING gist(the_geom);
@@ -1088,11 +1079,8 @@ CREATE TABLE cousub
awater numeric(14),
intptlat character varying(11),
intptlon character varying(12),
- the_geom geometry,
- CONSTRAINT uidx_cousub_gid UNIQUE (gid),
- CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
- CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
- CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269)
+ the_geom @extschema:postgis at .geometry(MULTIPOLYGON,4269),
+ CONSTRAINT uidx_cousub_gid UNIQUE (gid)
);
CREATE INDEX tige_cousub_the_geom_gist ON cousub USING gist(the_geom);
@@ -1132,10 +1120,7 @@ CREATE TABLE edges
offsetr character varying(1),
tnidf numeric(10),
tnidt numeric(10),
- the_geom geometry,
- CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
- CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL),
- CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269)
+ the_geom @extschema:postgis at .geometry(MULTILINESTRING,4269)
);
CREATE INDEX idx_edges_tlid ON edges USING btree(tlid);
CREATE INDEX idx_tiger_edges_countyfp ON edges USING btree(countyfp);
@@ -1168,10 +1153,7 @@ CREATE TABLE addrfeat
rtotyp character varying(1),
offsetl character varying(1),
offsetr character varying(1),
- the_geom geometry,
- CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
- CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'LINESTRING'::text OR the_geom IS NULL),
- CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269)
+ the_geom @extschema:postgis at .geometry(LINESTRING,4329)
);
CREATE INDEX idx_addrfeat_geom_gist ON addrfeat USING gist(the_geom );
CREATE INDEX idx_addrfeat_tlid ON addrfeat USING btree(tlid);
@@ -1250,10 +1232,7 @@ gid serial NOT NULL PRIMARY KEY,
atotal double precision,
intptlat varchar(11),
intptlon varchar(12),
- the_geom geometry,
- CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
- CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
- CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269)
+ the_geom @extschema:postgis at .geometry(MULTIPOLYGON,4269)
);
CREATE INDEX idx_tiger_faces_tfid ON faces USING btree (tfid);
CREATE INDEX idx_tiger_faces_countyfp ON faces USING btree(countyfp);
@@ -1284,7 +1263,7 @@ CREATE TABLE featnames
CONSTRAINT featnames_pkey PRIMARY KEY (gid)
);
ALTER TABLE featnames ADD COLUMN statefp character varying(2);
-CREATE INDEX idx_tiger_featnames_snd_name ON featnames USING btree (soundex(name));
+CREATE INDEX idx_tiger_featnames_snd_name ON featnames USING btree ( @extschema:fuzzystrmatch at .soundex(name) );
CREATE INDEX idx_tiger_featnames_lname ON featnames USING btree (lower(name));
CREATE INDEX idx_tiger_featnames_tlid_statefp ON featnames USING btree (tlid,statefp);
@@ -1324,10 +1303,6 @@ CREATE TABLE zcta5
intptlat character varying(11),
intptlon character varying(12),
partflg character varying(1),
- the_geom geometry,
- CONSTRAINT uidx_tiger_zcta5_gid UNIQUE (gid),
- CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
- CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
- CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269),
+ the_geom @extschema:postgis at .geometry(MULTIPOLYGON, 4269),
CONSTRAINT pk_tiger_zcta5_zcta5ce PRIMARY KEY (zcta5ce,statefp)
);
diff --git a/extras/tiger_geocoder/tiger_loader_2024.sql b/extras/tiger_geocoder/tiger_loader_2024.sql
index a2cf0178a..f6d7f0b85 100644
--- a/extras/tiger_geocoder/tiger_loader_2024.sql
+++ b/extras/tiger_geocoder/tiger_loader_2024.sql
@@ -22,7 +22,7 @@ var_temp := tiger.SetSearchPathForInstall('tiger');
IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tract_id' AND table_name = 'tract') THEN
-- census block group/tracts parent tables not created yet or an older version -- drop old if not in use, create new structure
DROP TABLE IF EXISTS tiger.tract;
- CREATE TABLE tract
+ CREATE TABLE tiger.tract
(
gid serial NOT NULL,
statefp varchar(2),
@@ -44,7 +44,7 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
);
DROP TABLE IF EXISTS tiger.tabblock;
- CREATE TABLE tabblock
+ CREATE TABLE tiger.tabblock
(
gid serial NOT NULL,
statefp varchar(2),
@@ -68,7 +68,7 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
);
DROP TABLE IF EXISTS tiger.bg;
- CREATE TABLE bg
+ CREATE TABLE tiger.bg
(
gid serial NOT NULL,
statefp varchar(2),
diff --git a/extras/tiger_geocoder/utility/levenshtein_ignore_case.sql b/extras/tiger_geocoder/utility/levenshtein_ignore_case.sql
index d0e832295..d7ab846e7 100644
--- a/extras/tiger_geocoder/utility/levenshtein_ignore_case.sql
+++ b/extras/tiger_geocoder/utility/levenshtein_ignore_case.sql
@@ -1,5 +1,5 @@
--- This function determines the levenshtein distance irespective of case.
+-- This function determines the levenshtein distance irrespective of case.
CREATE OR REPLACE FUNCTION levenshtein_ignore_case(VARCHAR, VARCHAR) RETURNS INTEGER
AS $_$
- SELECT levenshtein(COALESCE(upper($1),''), COALESCE(upper($2),''));
+ SELECT @extschema:fuzzystrmatch at .levenshtein(COALESCE(upper($1),''), COALESCE(upper($2),''));
$_$ LANGUAGE sql IMMUTABLE;
diff --git a/extras/tiger_geocoder/utility/nullable_levenshtein.sql b/extras/tiger_geocoder/utility/nullable_levenshtein.sql
index eab8ca7e7..e0ee3beca 100644
--- a/extras/tiger_geocoder/utility/nullable_levenshtein.sql
+++ b/extras/tiger_geocoder/utility/nullable_levenshtein.sql
@@ -20,7 +20,7 @@ BEGIN
END IF;
IF $2 IS NOT NULL AND $2 != '' THEN
- result := levenshtein_ignore_case(given_string, $2);
+ result := @extschema:fuzzystrmatch at .levenshtein_ignore_case_ignore_case(given_string, $2);
END IF;
RETURN result;
-----------------------------------------------------------------------
Summary of changes:
.gitignore | 1 +
NEWS | 3 ++
extensions/postgis_tiger_geocoder/Makefile.in | 19 ++++++--
.../postgis_tiger_geocoder.control.in | 1 +
.../geocode/geocode_intersection.sql | 8 +--
extras/tiger_geocoder/geocode/geocode_location.sql | 8 +--
extras/tiger_geocoder/geocode/rate_attributes.sql | 14 +++---
extras/tiger_geocoder/normalize/end_soundex.sql | 4 +-
.../tiger_geocoder/normalize/location_extract.sql | 12 ++---
.../normalize/location_extract_countysub_fuzzy.sql | 24 ++++-----
.../normalize/location_extract_place_fuzzy.sql | 8 +--
.../tiger_geocoder/tables/lookup_tables_2011.sql | 57 ++++++----------------
extras/tiger_geocoder/tiger_loader_2024.sql | 6 +--
.../utility/levenshtein_ignore_case.sql | 4 +-
.../utility/nullable_levenshtein.sql | 2 +-
15 files changed, 82 insertions(+), 89 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list