[postgis-tickets] r15508 - Geocoder enhancements, add zip4 and address_alphanumeric, adjustments to pagc matching
Regina Obe
lr at pcorp.us
Thu Jul 27 09:19:13 PDT 2017
Author: robe
Date: 2017-07-27 09:19:13 -0700 (Thu, 27 Jul 2017)
New Revision: 15508
Added:
trunk/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_upgrade.sql.in
Modified:
trunk/NEWS
trunk/doc/extras_tigergeocoder.xml
trunk/extensions/postgis_tiger_geocoder/Makefile.in
trunk/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_create.sql.in
trunk/extras/tiger_geocoder/create_geocode.sql
trunk/extras/tiger_geocoder/normalize/normalize_address.sql
trunk/extras/tiger_geocoder/normalize/pprint_addy.sql
trunk/extras/tiger_geocoder/pagc_normalize/pagc_normalize_address.sql
trunk/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql
trunk/extras/tiger_geocoder/regress/normalize_address_regress
trunk/extras/tiger_geocoder/regress/pagc_normalize_address_regress
trunk/extras/tiger_geocoder/regress/regress.sql
trunk/extras/tiger_geocoder/upgrade_geocode.sql
trunk/extras/tiger_geocoder/upgrade_geocoder.bat
Log:
Geocoder enhancements, add zip4 and address_alphanumeric, adjustments to pagc matching
Closes #3747
Closes #3748
Modified: trunk/NEWS
===================================================================
--- trunk/NEWS 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/NEWS 2017-07-27 16:19:13 UTC (rev 15508)
@@ -14,6 +14,13 @@
(Darafei Praliaskouski)
- #3677, ST_FrechetDistance (Shinichi Sugiyama)
+* Enhancements *
+ - #3747, Add zip4 and address_alphanumeric as attributes
+ to norm_addy tiger_geocoder type.
+ - #3748, address_standardizer lookup tables update
+ so pagc_normalize_address better standardizes abbreviations
+
+
PostGIS 2.3.3
2017/07/01
Modified: trunk/doc/extras_tigergeocoder.xml
===================================================================
--- trunk/doc/extras_tigergeocoder.xml 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/doc/extras_tigergeocoder.xml 2017-07-27 16:19:13 UTC (rev 15508)
@@ -732,7 +732,7 @@
</listitem>
</orderedlist>
<para>Availability: 2.0.0 to support Tiger 2010 structured data and load census tract (tract), block groups (bg), and blocks (tabblocks) tables .</para>
- <note><para>If you are using pgAdmin 3, be warned that by default pgAdmin 3 truncates long text. To fix, change
+ <note><para>If you are using pgAdmin 3, be warned that by default pgAdmin 3 truncates long text. To fix, change
<emphasis>File -> Options -> Query Tool -> Query Editor - > Max. characters per column</emphasis> to larger than 50000 characters.</para></note>
@@ -934,7 +934,8 @@
You may find the need to add more abbreviations or alternative namings to the various lookup tables in the <varname>tiger</varname> schema.</para>
<para>It uses various control lookup tables located in <varname>tiger</varname> schema to normalize the input address.</para>
<para>Fields in the <varname>norm_addy</varname> type object returned by this function in this order where () indicates a field required by the geocoder, [] indicates an optional field:</para>
- <para>(address) [predirAbbrev] (streetName) [streetTypeAbbrev] [postdirAbbrev] [internal] [location] [stateAbbrev] [zip]</para>
+ <para>(address) [predirAbbrev] (streetName) [streetTypeAbbrev] [postdirAbbrev] [internal] [location] [stateAbbrev] [zip] [parsed] [zip4] [address_alphanumeric]</para>
+ <para>Enhanced: 2.4.0 norm_addy object includes additional fields zip4 and address_alphanumeric.</para>
<orderedlist>
<listitem>
<para><varname>address</varname> is an integer: The street number</para>
@@ -966,6 +967,12 @@
<listitem>
<para><varname>parsed</varname> boolean - denotes if addess was formed from normalize process. The normalize_address function sets this to true before returning the address.</para>
</listitem>
+ <listitem>
+ <para><varname>zip4</varname> last 4 digits of a 9 digit zip code. Availability: PostGIS 2.4.0.</para>
+ </listitem>
+ <listitem>
+ <para><varname>address_alphanumeric</varname> Full street number even if it has alpha characters like 17R. Parsing of this is better using <xref linkend="Pagc_Normalize_Address" /> function. Availability: PostGIS 2.4.0.</para>
+ </listitem>
</orderedlist>
</refsection>
@@ -1032,6 +1039,7 @@
<para>The native standardaddr of address_standardizer extension is at this time a bit richer than norm_addy since its designed to support international addresses (including country). standardaddr equivalent fields are:</para>
<para>house_num,predir, name, suftype, sufdir, unit, city, state, postcode</para>
+ <para>Enhanced: 2.4.0 norm_addy object includes additional fields zip4 and address_alphanumeric.</para>
<orderedlist>
<listitem>
<para><varname>address</varname> is an integer: The street number</para>
@@ -1063,6 +1071,12 @@
<listitem>
<para><varname>parsed</varname> boolean - denotes if addess was formed from normalize process. The normalize_address function sets this to true before returning the address.</para>
</listitem>
+ <listitem>
+ <para><varname>zip4</varname> last 4 digits of a 9 digit zip code. Availability: PostGIS 2.4.0.</para>
+ </listitem>
+ <listitem>
+ <para><varname>address_alphanumeric</varname> Full street number even if it has alpha characters like 17R. Parsing of this is better using <xref linkend="Pagc_Normalize_Address" /> function. Availability: PostGIS 2.4.0.</para>
+ </listitem>
</orderedlist>
</refsection>
Modified: trunk/extensions/postgis_tiger_geocoder/Makefile.in
===================================================================
--- trunk/extensions/postgis_tiger_geocoder/Makefile.in 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/extensions/postgis_tiger_geocoder/Makefile.in 2017-07-27 16:19:13 UTC (rev 15508)
@@ -93,6 +93,7 @@
# to create schema by setting schema to tiger_geocoder in control
#also remove tiger_data from extension if it is part of it
sql_bits/tiger_geocoder_minor.sql.in: ../../extras/tiger_geocoder/utility/set_search_path.sql \
+ sql_bits/upgrade_before.sql.in \
../../extras/tiger_geocoder/geocode_settings.sql \
../../extras/tiger_geocoder/tiger_loader_2016.sql \
../../extras/tiger_geocoder/utility/utmzone.sql \
Modified: trunk/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_create.sql.in
===================================================================
--- trunk/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_create.sql.in 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_create.sql.in 2017-07-27 16:19:13 UTC (rev 15508)
@@ -8,4 +8,6 @@
location VARCHAR,
stateAbbrev VARCHAR,
zip VARCHAR,
- parsed BOOLEAN);
+ parsed BOOLEAN,
+ zip4 varchar,
+ address_alphanumeric varchar);
Added: trunk/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_upgrade.sql.in
===================================================================
--- trunk/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_upgrade.sql.in (rev 0)
+++ trunk/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_upgrade.sql.in 2017-07-27 16:19:13 UTC (rev 15508)
@@ -0,0 +1,2 @@
+ALTER TYPE tiger.norm_addy ADD ATTRIBUTE zip4 varchar(4);
+ALTER TYPE tiger.norm_addy ADD ATTRIBUTE address_alphanumeric varchar;
\ No newline at end of file
Modified: trunk/extras/tiger_geocoder/create_geocode.sql
===================================================================
--- trunk/extras/tiger_geocoder/create_geocode.sql 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/extras/tiger_geocoder/create_geocode.sql 2017-07-27 16:19:13 UTC (rev 15508)
@@ -2,7 +2,7 @@
-- PostGIS - Spatial Types for PostgreSQL
-- http://postgis.net
--
--- Copyright (C) 2010, 2011 Regina Obe and Leo Hsu
+-- Copyright (C) 2010-2017 Regina Obe and Leo Hsu
-- Copyright (C) 2008 Stephen Frost (et al)
-- reintegrated back into PostGIS code base from Steven's git (http://www.snowman.net/git/tiger_geocoder/)
-- Copyright Refractions Research
@@ -23,7 +23,7 @@
-- Type used to pass around a normalized address between functions
-- This is s bit dangerous since it could potentially drop peoples tables
-- TODO: put in logic to check if any tables have norm_addy and don't drop if they do
-DROP TYPE IF EXISTS norm_addy CASCADE;
+DROP TYPE IF EXISTS norm_addy;
CREATE TYPE norm_addy AS (
address INTEGER,
preDirAbbrev VARCHAR,
@@ -34,7 +34,7 @@
location VARCHAR,
stateAbbrev VARCHAR,
zip VARCHAR,
- parsed BOOLEAN);
+ parsed BOOLEAN, zip4 varchar, address_alphanumeric varchar);
-- System/General helper functions
\i utility/utmzone.sql
Modified: trunk/extras/tiger_geocoder/normalize/normalize_address.sql
===================================================================
--- trunk/extras/tiger_geocoder/normalize/normalize_address.sql 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/extras/tiger_geocoder/normalize/normalize_address.sql 2017-07-27 16:19:13 UTC (rev 15508)
@@ -96,6 +96,9 @@
-- Assume that the address begins with a digit, and extract it from
-- the input string.
addressString := substring(rawInput from E'^([0-9].*?)[ ,/.]');
+
+ -- try to pull full street number including non-digits like 1R
+ result.address_alphanumeric := substring(rawInput from E'^([0-9a-zA-Z].*?)[ ,/.]');
IF debug_flag THEN
raise notice '% addressString: %', clock_timestamp(), addressString;
@@ -111,7 +114,12 @@
zipString := COALESCE(substring(rawInput from ws || '([0-9]{5})-[0-9]{0,4}$'),
substring(rawInput from ws || '([0-9]{2,5})$'),
substring(rawInput from ws || '([0-9]{6,14})$'));
+
+ result.zip4 := COALESCE(substring(rawInput from ws || '[0-9]{5}-([0-9]{0,4})$'),substring(rawInput from ws || '[0-9]{5}([0-9]{0,4})$'));
+ IF debug_flag THEN
+ raise notice '% zip4: %', clock_timestamp(), result.zip4;
+ END IF;
-- Check if all we got was a zipcode, of either form
IF zipString IS NULL THEN
zipString := substring(rawInput from '^([0-9]{5})$');
Modified: trunk/extras/tiger_geocoder/normalize/pprint_addy.sql
===================================================================
--- trunk/extras/tiger_geocoder/normalize/pprint_addy.sql 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/extras/tiger_geocoder/normalize/pprint_addy.sql 2017-07-27 16:19:13 UTC (rev 15508)
@@ -20,7 +20,7 @@
RETURN NULL;
END IF;
- result := cull_null(input.address::text)
+ result := COALESCE(input.address_alphanumeric, cull_null(input.address::text))
|| COALESCE(' ' || input.preDirAbbrev, '')
|| CASE WHEN is_pretype(input.streetTypeAbbrev) THEN ' ' || input.streetTypeAbbrev ELSE '' END
|| COALESCE(' ' || input.streetName, '')
@@ -35,7 +35,7 @@
|| cull_null(input.location)
|| CASE WHEN input.location IS NOT NULL THEN ', ' ELSE '' END
|| COALESCE(input.stateAbbrev || ' ' , '')
- || cull_null(input.zip);
+ || cull_null(input.zip) || COALESCE('-' || input.zip4, '');
RETURN trim(result);
Modified: trunk/extras/tiger_geocoder/pagc_normalize/pagc_normalize_address.sql
===================================================================
--- trunk/extras/tiger_geocoder/pagc_normalize/pagc_normalize_address.sql 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/extras/tiger_geocoder/pagc_normalize/pagc_normalize_address.sql 2017-07-27 16:19:13 UTC (rev 15508)
@@ -23,15 +23,17 @@
result.location := var_parse_rec.city;
result.stateAbbrev := trim(var_parse_rec.state);
result.zip := var_parse_rec.zip;
+ result.zip4 := NULLIF(var_parse_rec.zipplus,'');
var_rec := standardize_address('pagc_lex'
, 'pagc_gaz'
, 'pagc_rules'
, COALESCE(var_parse_rec.address1,''),
- COALESCE(', ' || var_parse_rec.city,'') || COALESCE(', ' || var_parse_rec.state, '') || COALESCE(' ' || var_parse_rec.zip,'') ) ;
+ COALESCE(var_parse_rec.city,'') || COALESCE(', ' || var_parse_rec.state, '') || COALESCE(' ' || var_parse_rec.zip,'') ) ;
-- For address number only put numbers and stop if reach a non-number e.g. 123-456 will return 123
result.address := to_number(substring(var_rec.house_num, '[0-9]+'), '99999999');
+ result.address_alphanumeric := var_rec.house_num;
--get rid of extraneous spaces before we return
result.zip := COALESCE(var_rec.postcode,result.zip);
result.streetName := trim(var_rec.name);
@@ -41,7 +43,7 @@
result.streettypeAbbrev := trim(COALESCE(var_rec.suftype, var_rec.pretype));
result.preDirAbbrev := trim(var_rec.predir);
result.postDirAbbrev := trim(var_rec.sufdir);
- result.internal := trim(var_rec.unit);
+ result.internal := trim(regexp_replace(replace(var_rec.unit, '#',''), '([0-9]+)\s+([A-Za-z]){0,1}', E'\\1\\2'));
result.parsed := TRUE;
RETURN result;
END
Modified: trunk/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql
===================================================================
--- trunk/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql 2017-07-27 16:19:13 UTC (rev 15508)
@@ -1089,7 +1089,7 @@
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (144, 2, '93RD', '93RD', 15, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (145, 1, '9MI', 'NINE MILE', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (146, 1, 'A', 'A', 18, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (147, 2, 'A', 'A', 7, false);
+--INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (147, 2, 'A', 'A', 7, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (148, 1, 'A F B', 'AIR FORCE BASE', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (149, 2, 'A F B', 'AIR FORCE BASE', 24, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (150, 1, 'A F S', 'AIR FORCE BASE', 1, false);
@@ -1151,20 +1151,21 @@
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (215, 1, 'ANNEX', 'ANNEX', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (217, 1, 'ANNX', 'ANNEX', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (219, 1, 'ANX', 'ANNEX', 1, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (221, 1, 'AP', 'APARTMENT', 16, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (222, 1, 'APART', 'APARTMENT', 16, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (223, 1, 'APARTEMENT', 'APARTEMENT', 16, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (224, 1, 'APARTMENT', 'APARTMENT', 16, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (225, 1, 'APARTMENTS', 'APARTMENTS', 24, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (221, 1, 'AP', 'APT', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (222, 1, 'APART', 'APT', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (223, 1, 'APARTEMENT', 'APT', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2938, 1, 'APARTMENT', 'APARTMENT', 2, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (224, 1, 'APARTMENT', 'APT', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (225, 1, 'APARTMENTS', 'APS', 24, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (226, 1, 'APARTADO', 'BOX', 14, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (227, 1, 'APO', 'APO', 14, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (228, 1, 'APP', 'APARTEMENT', 16, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (229, 1, 'APPART', 'APARTEMENT', 16, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (230, 1, 'APPT', 'APARTEMENT', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (228, 1, 'APP', 'APT', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (229, 1, 'APPART', 'APT', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (230, 1, 'APPT', 'APT', 16, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (231, 1, 'APRK', 'AIRPORT', 24, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (232, 1, 'APS', 'APARTMENTS', 24, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (233, 1, 'APT', 'APARTMENT', 16, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (234, 1, 'APT NO', 'APARTMENT', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (232, 1, 'APS', 'APS', 24, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (233, 1, 'APT', 'APT', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (234, 1, 'APT NO', 'APT', 16, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (235, 1, 'APTMT', 'APARTMENT', 16, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (236, 1, 'APTS', 'APARTMENTS', 24, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (237, 1, 'AR', 'ARRIERE', 17, false);
@@ -1810,14 +1811,14 @@
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1026, 1, 'FIVE MILE', 'FIVE MILE', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1027, 1, 'FIVE POINTS', 'FIVE POINTS', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1028, 1, 'FIVE TOWN', 'FIVE TOWN', 1, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1029, 1, 'FL', 'FLOOR', 17, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1029, 1, 'FL', 'FL', 17, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1030, 1, 'FLAT', 'FLAT', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1031, 1, 'FLD', 'FIELD', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1032, 1, 'FLDS', 'FIELDS', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1033, 1, 'FLLS', 'FALLS', 1, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1034, 1, 'FLOOR', 'FLOOR', 17, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1035, 2, 'FLOOR', 'FLOOR', 1, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1036, 1, 'FLR', 'FLOOR', 17, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1034, 1, 'FLOOR', 'FL', 17, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1035, 2, 'FLOOR', 'FL', 1, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1036, 1, 'FLR', 'FL', 17, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1037, 1, 'FLS', 'FALLS', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1038, 1, 'FLT', 'FLAT', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1039, 1, 'FLTS', 'FLATS', 1, false);
@@ -2495,7 +2496,7 @@
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1813, 1, 'OUTSIDE', 'OUTSIDE OF', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1817, 1, 'P BOX', 'POST OFFICE BOX', 14, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1818, 1, 'P BX', 'POST OFFICE BOX', 14, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1819, 1, 'P H', 'PENTHOUSE', 17, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1819, 1, 'P H', 'PH', 17, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1820, 1, 'P O', 'POST OFFICE BOX', 14, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1821, 1, 'P O B', 'POST OFFICE BOX', 14, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1695, 1, 'NE', 'NE', 22, false);
@@ -2542,8 +2543,9 @@
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1866, 1, 'PAVL', 'PAVILLION', 24, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1867, 2, 'PAVL', 'PAVILLION', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1869, 1, 'PDA', 'PARADERO', 1, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1870, 1, 'PENTHOUSE', 'PENTHOUSE', 17, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1871, 1, 'PH', 'PENTHOUSE', 17, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2939, 1, 'PENTHOUSE', 'PENTHOUSE', 2, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1870, 1, 'PENTHOUSE', 'PH', 17, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1871, 1, 'PH', 'PH', 17, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1873, 1, 'PIECE', 'PIECE', 16, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1874, 2, 'PIECE', 'PIECE', 17, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (1875, 1, 'PIER', 'PIER', 16, false);
@@ -2968,7 +2970,7 @@
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2358, 1, 'SRA', 'RURAL ROUTE', 8, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2360, 1, 'SRV RTE', 'SERVICE ROUTE', 2, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2364, 1, 'SS', 'SUBURBAN SERVICE', 8, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2366, 2, 'ST', 'SAINT', 7, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2366, 2, 'SAINT', 'SAINT', 7, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2371, 1, 'ST R', 'STAR ROUTE', 6, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2372, 2, 'ST R', 'STAR ROUTE', 8, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2376, 2, 'ST ROUTE', 'STAR ROUTE', 8, false);
@@ -3012,7 +3014,8 @@
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2451, 1, 'STS', 'STREETS', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2452, 1, 'STUDIO', 'STUDIO', 16, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2453, 2, 'STUDIO', 'STUDIO', 1, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2454, 1, 'SU', 'SUITE', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2454, 1, 'SU', 'STE', 16, false);
+
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2455, 1, 'SUBD', 'SUBDIVISION', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2456, 2, 'SUBD', 'SUBDIVISION', 2, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2457, 1, 'SUBDIV', 'SUBDIVISION', 1, false);
@@ -3056,11 +3059,11 @@
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2467, 1, 'SUD OUEST', 'SUD OUEST', 22, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2468, 1, 'SUDEST', 'SUD EST', 22, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2469, 1, 'SUDOUEST', 'SUD OUEST', 22, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2470, 1, 'SUIT', 'SUITE', 16, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2471, 2, 'SUIT', 'SUITE', 1, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2472, 1, 'SUITE', 'SUITE', 16, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2473, 1, 'SUITES', 'SUITES', 16, false);
-INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2474, 2, 'SUITES', 'SUITES', 24, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2470, 1, 'SUIT', 'STE', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2471, 2, 'SUIT', 'STE', 1, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2472, 1, 'SUITE', 'STE', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2473, 1, 'SUITES', 'STE', 16, false);
+INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2474, 2, 'SUITES', 'STE', 24, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2475, 1, 'SUMMIT', 'SUMMIT', 1, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2477, 1, 'SV RTE', 'SERVICE ROUTE', 2, false);
INSERT INTO pagc_lex (id, seq, word, stdword, token, is_custom) VALUES (2481, 1, 'SWP', 'SWAMP', 1, false);
Modified: trunk/extras/tiger_geocoder/regress/normalize_address_regress
===================================================================
--- trunk/extras/tiger_geocoder/regress/normalize_address_regress 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/extras/tiger_geocoder/regress/normalize_address_regress 2017-07-27 16:19:13 UTC (rev 15508)
@@ -1,45 +1,45 @@
-#887|2450|N|COLORADO|St|||PHILADELPHIA|PA|19132|t
-#1051a|212||3rd|Ave|N|Suite 560|Minneapolis|MN|55401|t
-#1051b|3937||43RD|Ave|S||MINNEAPOLIS|MN|55406|t
-#1051c|212|N|3rd|Ave|||Minneapolis|MN|55401|t
-#1051d|212||3rd|Ave|N||Minneapolis|MN|55401|t
-529||Main|St|||Boston|MA|02129|t
-529||Main|St|||Boston|MA|02129|t
-529||Main|St|||Boston|MA||t
-529||Main|St||Apt 201|Boston|MA|02129|t
-529||Main|St||Apt 201|Boston|MA|02129|t
-529||Main|St||Apt 201|Boston|MA||t
-#1108a|529||Main|St||Suite 201|Boston|MA|02129|t
-#1073a|212||3rd|Ave|N||MINNEAPOLIS|MN|553404|t
-#1073b|212||3rd|Ave|N||MINNEAPOLIS|MN|55401|t
-#1073c|529||Main|St|||Boston|MA|021|t
-#1086a|949|N|3rd|St|||New Hyde Park|NY|11040|t
-#1086b|949|N|3rd|St|||New Hyde Park|NY|11040|t
-#1076a|16725||24|Co Rd|||Plymouth|MN|55447|t
-#1076b|16725||24|Co Rd|||Plymouth|MN|55447|t
-#1076c|13800||9|Co Hwy|||Andover|MN|55304|t
-#1076d|13800||9||||Andover|MN|55304|t
-#1076e|14||Forest|Rd|||Acton|MA||t
-#1076f|1940||C|Co Rd|W||Roseville|MN|55113|t
-#1076g|3900||6|Rte|||Eastham|MA|02642|t
-#1076h|4533||PARK|Ave|S||MINNEAPOLIS|MN|55407|t
-#1076i|4533||33|Co Rd|||MINNEAPOLIS|MN|55407|t
-#1109a|4373||LAKE|Dr|||ROBBINSDALE|MN|55422|t
-#1109b|4373||LAKE|Dr|||ROBBINSDALE|MN|55422|t
-#1074a|3420||RHODE ISLAND|Ave|S||ST. LOUIS PARK|MN|55426|t
-#1074b|26||Court|St|||Boston|MA|02109|t
-#1112a|8401|W|35W|Svc Dr|NE||Blaine|MN|55449|t
-#1112b|8401||35W||||Blaine|MN|55449|t
-#1112c|8401||35W||W||Blaine|MN|55449|t
-#1112d|8401|W|35W||||Blaine|MN|55449|t
-#1112e|8401|W|35W||||Blaine|MN|55449|t
+#887|2450|N|COLORADO|St|||PHILADELPHIA|PA|19132|t||
+#1051a|212||3rd|Ave|N|Suite 560|Minneapolis|MN|55401|t||
+#1051b|3937||43RD|Ave|S||MINNEAPOLIS|MN|55406|t||
+#1051c|212|N|3rd|Ave|||Minneapolis|MN|55401|t||
+#1051d|212||3rd|Ave|N||Minneapolis|MN|55401|t||
+529||Main|St|||Boston|MA|02129|t||
+529||Main|St|||Boston|MA|02129|t||
+529||Main|St|||Boston|MA||t||
+529||Main|St||Apt 201|Boston|MA|02129|t||
+529||Main|St||Apt 201|Boston|MA|02129|t||
+529||Main|St||Apt 201|Boston|MA||t||
+#1108a|529||Main|St||Suite 201|Boston|MA|02129|t||
+#1073a|212||3rd|Ave|N||MINNEAPOLIS|MN|553404|t||
+#1073b|212||3rd|Ave|N||MINNEAPOLIS|MN|55401|t||
+#1073c|529||Main|St|||Boston|MA|021|t||
+#1086a|949|N|3rd|St|||New Hyde Park|NY|11040|t||
+#1086b|949|N|3rd|St|||New Hyde Park|NY|11040|t||
+#1076a|16725||24|Co Rd|||Plymouth|MN|55447|t||
+#1076b|16725||24|Co Rd|||Plymouth|MN|55447|t||
+#1076c|13800||9|Co Hwy|||Andover|MN|55304|t||
+#1076d|13800||9||||Andover|MN|55304|t||
+#1076e|14||Forest|Rd|||Acton|MA||t||
+#1076f|1940||C|Co Rd|W||Roseville|MN|55113|t||
+#1076g|3900||6|Rte|||Eastham|MA|02642|t||
+#1076h|4533||PARK|Ave|S||MINNEAPOLIS|MN|55407|t||
+#1076i|4533||33|Co Rd|||MINNEAPOLIS|MN|55407|t||
+#1109a|4373||LAKE|Dr|||ROBBINSDALE|MN|55422|t||
+#1109b|4373||LAKE|Dr|||ROBBINSDALE|MN|55422|t||
+#1074a|3420||RHODE ISLAND|Ave|S||ST. LOUIS PARK|MN|55426|t||
+#1074b|26||Court|St|||Boston|MA|02109|t||
+#1112a|8401|W|35W|Svc Dr|NE||Blaine|MN|55449|t||
+#1112b|8401||35W||||Blaine|MN|55449|t||
+#1112c|8401||35W||W||Blaine|MN|55449|t||
+#1112d|8401|W|35W||||Blaine|MN|55449|t||
+#1112e|8401|W|35W||||Blaine|MN|55449|t||
#1125a|19596 Co Rd 480, COLCORD, OK 74338
-#1125b|4345 353 Rte, SALAMANCA, NY 14779|4345||353|Rte|||SALAMANCA|NY|14779|t
-#1125c|19799 State Rte O, COSBY, MO 64436|19799||O|State Rte|||COSBY|MO|64436|t
-#1125d|Interstate 90, Boston, MA|||Interstate 90||||Boston|MA||t
-#1125e|I-90,Boston, MA|||I-90,Boston|||||MA||t
-#1125f|I 90, Boston, MA|||I 90||||Boston|MA||t
-#1310a|1110 W CAPITOL Ave, WEST SACRAMENTO, CA|1110|W|CAPITOL|Ave|||WEST SACRAMENTO|CA||t
-#1614a|8435 Co Rd 20 SE, ROCHESTER, MN 55904|8435||20|Co Rd|SE||ROCHESTER|MN|55904|t
-#1614b|3208 US Hwy 52, Rochester, MN 55901|3208||52|US Hwy|||Rochester|MN|55901|t
-#1108a|529 Main St, Suite 201, Boston, MA 02129|529||Main|St||Suite 201|Boston|MA|02129|t
+#1125b|4345 353 Rte, SALAMANCA, NY 14779|4345||353|Rte|||SALAMANCA|NY|14779|t||
+#1125c|19799 State Rte O, COSBY, MO 64436|19799||O|State Rte|||COSBY|MO|64436|t||
+#1125d|Interstate 90, Boston, MA|||Interstate 90||||Boston|MA||t||
+#1125e|I-90,Boston, MA|||I-90,Boston|||||MA||t||
+#1125f|I 90, Boston, MA|||I 90||||Boston|MA||t||
+#1310a|1110 W CAPITOL Ave, WEST SACRAMENTO, CA|1110|W|CAPITOL|Ave|||WEST SACRAMENTO|CA||t||
+#1614a|8435 Co Rd 20 SE, ROCHESTER, MN 55904|8435||20|Co Rd|SE||ROCHESTER|MN|55904|t||
+#1614b|3208 US Hwy 52, Rochester, MN 55901|3208||52|US Hwy|||Rochester|MN|55901|t||
+#1108a|529 Main St, Suite 201, Boston, MA 02129|529||Main|St||Suite 201|Boston|MA|02129|t||
Modified: trunk/extras/tiger_geocoder/regress/pagc_normalize_address_regress
===================================================================
--- trunk/extras/tiger_geocoder/regress/pagc_normalize_address_regress 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/extras/tiger_geocoder/regress/pagc_normalize_address_regress 2017-07-27 16:19:13 UTC (rev 15508)
@@ -1,53 +1,53 @@
true
-#887|2450|N|COLORADO|ST|||PHILADELPHIA|PA|19132|t
-#1051a|212||3RD|AVE|N|SUITE 560|MINNEAPOLIS|MN|55401|t
-#1051b|3937||43RD|AVE|S||MINNEAPOLIS|MN|55406|t
-#1051c|212|N|3RD|AVE|||MINNEAPOLIS|MN|55401|t
-#1051d|212||3RD|AVE|||MINNEAPOLIS|MN|N 55401|t
-529||MAIN|ST|||BOSTON|MA|02129|t
-529||MAIN|ST|||BOSTON|MA|02129|t
-529||MAIN|ST|||BOSTON|MA||t
-529||MAIN|ST||APARTMENT 201|BOSTON|MA|02129|t
-529||MAIN|ST||APARTMENT 201|BOSTON|MA|02129|t
-529||MAIN|ST||APARTMENT 201|BOSTON|MA||t
-#1108a|529||MAIN|ST||SUITE 201|BOSTON|MA|02129|t
-#1073a|212||3RD|AVE|N||MINNEAPOLIS|MN|55340|t
-#1073b|212||3RD|AVE|N||MINNEAPOLIS|MN|55401|t
-#1073c|529||MAIN|ST||# BOSTON|MASSACHUSETTS||021|t
-#1086a|949|N|3RD|ST|||NEW HYDE PARK|NY|11040|t
-#1086b|949|N|3RD|ST|||NEW HYDE PARK|NY|11040|t
-#1076a|16725||24|CO RD|||PLYMOUTH|MN|55447|t
-#1076b|16725||24|CO RD|||PLYMOUTH|MN|55447|t
-#1076c|13800||9|CO HWY|||ANDOVER|MN|55304|t
-#1076d|13800||9||||ANDOVER|MN|55304|t
-#1076e|14||FOREST|RD|||ACTON|MA||t
-#1076f|1940||C|CO RD|W||ROSEVILLE|MN|55113|t
-#1076g|3900||6|RTE|||EASTHAM|MA|02642|t
-#1076h|4533||PARK|AVE|S||MINNEAPOLIS|MN|55407|t
-#1076i|4533||33|CO RD|||MINNEAPOLIS|MN|55407|t
-#1109a|4373||LAKE|DR|||ROBBINSDALE|MN|55422|t
-#1109b|4373||LAKE|DR|||ROBBINSDALE|MN|55422|t
-#1074a|3420||RHODE ISLAND|AVE|S||SAINT LOUIS PARK|MN|55426|t
-#1074b|26||COURT|ST|||BOSTON|MA|02109|t
-#1112a|8401|W|35 W|SVC DR|NE||BLAINE|MN|55449|t
-#1112b|8401||35 W||||BLAINE|MN|55449|t
-#1112c|8401||35 W||W||BLAINE|MN|55449|t
-#1112d|8401|W|35||W||BLAINE|MN|55449|t
-#1112e|8401|W|35||W||BLAINE|MN|55449|t
+#887|2450|N|COLORADO|ST|||PHILADELPHIA|PA|19132|t||2450
+#1051a|212||3RD|AVE|N|STE 560|MINNEAPOLIS|MN|55401|t||212
+#1051b|3937||43RD|AVE|S||MINNEAPOLIS|MN|55406|t||3937
+#1051c|212|N|3RD|AVE|||MINNEAPOLIS|MN|55401|t||212
+#1051d|212||3RD|AVE|||MINNEAPOLIS|MN|N 55401|t||212
+529||MAIN|ST|||BOSTON|MA|02129|t||529
+529||MAIN|ST|||BOSTON|MA|02129|t||529
+529||MAIN|ST|||BOSTON|MA||t||529
+529||MAIN|ST||APT 201|BOSTON|MA|02129|t||529
+529||MAIN|ST||APT 201|BOSTON|MA|02129|t||529
+529||MAIN|ST||APT 201|BOSTON|MA||t||529
+#1108a|529||MAIN|ST||STE 201|BOSTON|MA|02129|t||529
+#1073a|212||3RD|AVE|N||MINNEAPOLIS|MN|55340|t|4|212
+#1073b|212||3RD|AVE|N||MINNEAPOLIS|MN|55401|t||212
+#1073c|529||MAIN|ST||BOSTON|MASSACHUSETTS||021|t||529
+#1086a|949|N|3RD|ST|||NEW HYDE PARK|NY|11040|t||949
+#1086b|949|N|3RD|ST|||NEW HYDE PARK|NY|11040|t||949
+#1076a|16725||24|CO RD|||PLYMOUTH|MN|55447|t||16725
+#1076b|16725||24|CO RD|||PLYMOUTH|MN|55447|t||16725
+#1076c|13800||9|CO HWY|||ANDOVER|MN|55304|t||13800
+#1076d|13800||9||||ANDOVER|MN|55304|t||13800
+#1076e|14||FOREST|RD|||ACTON|MA||t||14
+#1076f|1940||C|CO RD|W||ROSEVILLE|MN|55113|t||1940
+#1076g|3900||6|RTE|||EASTHAM|MA|02642|t||3900
+#1076h|4533||PARK|AVE|S||MINNEAPOLIS|MN|55407|t||4533
+#1076i|4533||33|CO RD|||MINNEAPOLIS|MN|55407|t||4533
+#1109a|4373||LAKE|DR|||ROBBINSDALE|MN|55422|t||4373
+#1109b|4373||LAKE|DR|||ROBBINSDALE|MN|55422|t||4373
+#1074a|3420||RHODE ISLAND|AVE|S||SAINT LOUIS PARK|MN|55426|t||3420
+#1074b|26||COURT|ST|||BOSTON|MA|02109|t||26
+#1112a|8401|W|35 W|SVC DR|NE||BLAINE|MN|55449|t||8401
+#1112b|8401||35 W||||BLAINE|MN|55449|t||8401
+#1112c|8401||35 W||W||BLAINE|MN|55449|t||8401
+#1112d|8401|W|35||W||BLAINE|MN|55449|t||8401
+#1112e|8401|W|35||W||BLAINE|MN|55449|t||8401
#1125a|19596 CO RD 480, COLCORD, OK 74338
-#1125b|4345 353 RTE, SALAMANCA, NY 14779|4345||353|RTE|||SALAMANCA|NY|14779|t
-#1125c|19799 STATE RTE 0, COSBY, MO 64436|19799||0|STATE RTE|||COSBY|MO|64436|t
-#1125d|||||||||||t
-#1125e|||||||||||t
-#1125f|||||||||||t
-#1310a|1110 W CAPITOL AVE, WEST SACRAMENTO, CA|1110|W|CAPITOL|AVE|||WEST SACRAMENTO|CA||t
-#1614a|8435 CO RD 20 SE, ROCHESTER, MN 55904|8435||20|CO RD|SE||ROCHESTER|MN|55904|t
-#1614b|3208 US RTE 52, ROCHESTER, MN 55901|3208||52|US RTE|||ROCHESTER|MN|55901|t
-#1108a|529 MAIN ST, SUITE 201, BOSTON, MA 02129|529||MAIN|ST||SUITE 201|BOSTON|MA|02129|t
-#3259a|212||3RD|AVE|N|SUITE 560|MINNEAPOLIS|MN|55401|t
-#3259b|3937||43RD|AVE|S||MINNEAPOLIS|MN|55406|t
-#2981|1566||NEW STATE HWY||||RAYNHAM|MA||t
-#2978a|10||DORRANCE|ST|||PROVIDENCE|RI||t
-#2978b|10||DORRANCE|ST|||PROVIDENCE|RI||t
-#2978c|10||DORRANCE|ST|||PROVIDENCE|RI||t
+#1125b|4345 353 RTE, SALAMANCA, NY 14779|4345||353|RTE|||SALAMANCA|NY|14779|t||4345
+#1125c|19799 STATE RTE 0, COSBY, MO 64436|19799||0|STATE RTE|||COSBY|MO|64436|t||19799
+#1125d|||||||||||t||
+#1125e|||||||||||t||
+#1125f|||||||||||t||
+#1310a|1110 W CAPITOL AVE, WEST SACRAMENTO, CA|1110|W|CAPITOL|AVE|||WEST SACRAMENTO|CA||t||1110
+#1614a|8435 CO RD 20 SE, ROCHESTER, MN 55904|8435||20|CO RD|SE||ROCHESTER|MN|55904|t||8435
+#1614b|3208 US RTE 52, ROCHESTER, MN 55901|3208||52|US RTE|||ROCHESTER|MN|55901|t||3208
+#1108a|529 MAIN ST, STE 201, BOSTON, MA 02129|529||MAIN|ST||STE 201|BOSTON|MA|02129|t||529
+#3259a|212||3RD|AVE|N|STE 560|MINNEAPOLIS|MN|55401|t||212
+#3259b|3937||43RD|AVE|S||MINNEAPOLIS|MN|55406|t||3937
+#2981|1566||NEW STATE HWY||||RAYNHAM|MA||t||1566
+#2978a|10||DORRANCE|ST|||PROVIDENCE|RI||t||10 20
+#2978b|10||DORRANCE|ST|||PROVIDENCE|RI||t||10 20
+#2978c|10||DORRANCE|ST|||PROVIDENCE|RI||t||10 20
false
Modified: trunk/extras/tiger_geocoder/regress/regress.sql
===================================================================
--- trunk/extras/tiger_geocoder/regress/regress.sql 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/extras/tiger_geocoder/regress/regress.sql 2017-07-27 16:19:13 UTC (rev 15508)
@@ -1,4 +1,4 @@
-\a
+\a \pset null ''
--SET seq_page_cost='1000';
SELECT set_geocode_setting('debug_reverse_geocode', 'false');
SELECT set_geocode_setting('debug_geocode_address', 'false');
Modified: trunk/extras/tiger_geocoder/upgrade_geocode.sql
===================================================================
--- trunk/extras/tiger_geocoder/upgrade_geocode.sql 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/extras/tiger_geocoder/upgrade_geocode.sql 2017-07-27 16:19:13 UTC (rev 15508)
@@ -24,6 +24,18 @@
ALTER TABLE state_lookup ADD COLUMN statefp char(2);
UPDATE state_lookup SET statefp = lpad(st_code::text,2,'0') WHERE statefp IS NULL;
ALTER TABLE state_lookup ADD CONSTRAINT state_lookup_statefp_key UNIQUE(statefp);
+
+-- these introduced in PostGIS 2.4
+DO language plpgsql
+$$
+ BEGIN
+ ALTER TYPE tiger.norm_addy ADD ATTRIBUTE zip4 varchar;
+ ALTER TYPE tiger.norm_addy ADD ATTRIBUTE address_alphanumeric varchar;
+ EXCEPTION
+ WHEN others THEN -- ignore the error probably cause it already exists
+ END;
+$$;
+
CREATE INDEX idx_tiger_edges_countyfp ON edges USING btree(countyfp);
CREATE INDEX idx_tiger_faces_countyfp ON faces USING btree(countyfp);
CREATE INDEX tiger_place_the_geom_gist ON place USING gist(the_geom);
Modified: trunk/extras/tiger_geocoder/upgrade_geocoder.bat
===================================================================
--- trunk/extras/tiger_geocoder/upgrade_geocoder.bat 2017-07-27 08:05:13 UTC (rev 15507)
+++ trunk/extras/tiger_geocoder/upgrade_geocoder.bat 2017-07-27 16:19:13 UTC (rev 15508)
@@ -1,19 +1,19 @@
-set PGPORT=5432
-set PGHOST=localhost
-set PGUSER=postgres
-set PGPASSWORD=yourpasswordhere
-set THEDB=geocoder
-set PGBIN=C:\Program Files\PostgreSQL\9.4\bin
-set PGCONTRIB=C:\Program Files\PostgreSQL\9.4\share\contrib
-
-"%PGBIN%\psql" -d "%THEDB%" -f "upgrade_geocode.sql"
-
-REM "%PGBIN%\psql" -d "%THEDB%" -c "ALTER EXTENSION postgis_tiger_geocoder UPDATE;"
-
-REM unremark the loader line to update your loader scripts
-REM note this wipes out your custom settings in loader_* tables
-REM "%PGBIN%\psql" -d "%THEDB%" -f "tiger_loader_2016.sql"
-cd regress
-REM "%PGBIN%\psql" -d "%THEDB%" -t -f regress.sql
-pause
-
+set PGPORT=5432
+set PGHOST=localhost
+set PGUSER=postgres
+set PGPASSWORD=yourpasswordhere
+set THEDB=geocoder
+set PGBIN=C:\Program Files\PostgreSQL\9.6\bin
+set PGCONTRIB=C:\Program Files\PostgreSQL\9.6\share\contrib
+
+"%PGBIN%\psql" -d "%THEDB%" -f "upgrade_geocode.sql"
+
+REM "%PGBIN%\psql" -d "%THEDB%" -c "ALTER EXTENSION postgis_tiger_geocoder UPDATE;"
+
+REM unremark the loader line to update your loader scripts
+REM note this wipes out your custom settings in loader_* tables
+REM "%PGBIN%\psql" -d "%THEDB%" -f "tiger_loader_2016.sql"
+cd regress
+REM "%PGBIN%\psql" -d "%THEDB%" -t -f regress.sql
+pause
+
More information about the postgis-tickets
mailing list