From 5f354e71510ea3e2fb9165c8583545c5ddeaa4b6 Mon Sep 17 00:00:00 2001
From: Regina Obe <lr@pcorp.us>
Date: Thu, 20 Sep 2012 11:54:44 +0000
Subject: [PATCH] remark out regress and also put in name in database #2009
 turn off loading of addrfeat #1614 add U.S. and COUNTY RD as street_type
 highways

git-svn-id: http://svn.osgeo.org/postgis/trunk@10309 b70326c6-7e19-0410-871a-916f4a2858ee
---
 .../tiger_2011/regress/normalize_address_regress   |  4 +++-
 .../regress/normalize_address_regress.sql          |  4 ++++
 .../tiger_2011/tables/lookup_tables_2011.sql       |  2 ++
 .../tiger_2011/tiger_loader_2011.sql               |  2 +-
 .../tiger_geocoder/tiger_2011/upgrade_geocode.sql  | 14 +++++++++++---
 .../tiger_geocoder/tiger_2011/upgrade_geocoder.bat |  2 +-
 6 files changed, 22 insertions(+), 6 deletions(-)

diff --git a/extras/tiger_geocoder/tiger_2011/regress/normalize_address_regress b/extras/tiger_geocoder/tiger_2011/regress/normalize_address_regress
index 4c8430db6..5d1791f2c 100644
--- a/extras/tiger_geocoder/tiger_2011/regress/normalize_address_regress
+++ b/extras/tiger_geocoder/tiger_2011/regress/normalize_address_regress
@@ -39,4 +39,6 @@
 #1125d|I- 90, Boston, MA|||90|I-|||Boston|MA||t
 #1125e|I-90, Boston, MA|||I-90||||Boston|MA||t
 #1125f|I- 90, Boston, MA|||90|I-|||Boston|MA||t
-#1310a|1110 W CAPITOL Ave, WEST SACRAMENTO, CA|1110|W|CAPITOL|Ave|||WEST SACRAMENTO|CA||t
\ No newline at end of file
+#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
\ No newline at end of file
diff --git a/extras/tiger_geocoder/tiger_2011/regress/normalize_address_regress.sql b/extras/tiger_geocoder/tiger_2011/regress/normalize_address_regress.sql
index e440f3540..a6710dad2 100644
--- a/extras/tiger_geocoder/tiger_2011/regress/normalize_address_regress.sql
+++ b/extras/tiger_geocoder/tiger_2011/regress/normalize_address_regress.sql
@@ -88,4 +88,8 @@ SELECT '#1125f' As ticket, pprint_addy(addy), addy.* FROM normalize_address('I 9
 
 -- location with prefixes getting caught in post prefix
 SELECT '#1310a' As ticket, pprint_addy(addy), addy.* FROM normalize_address('1110 W CAPITOL AVE, WEST SACRAMENTO, CA') As addy;
+
+-- #1614 County Rd
+SELECT '#1614a' As ticket, pprint_addy(addy), addy.* FROM normalize_address('8435 COUNTY RD 20 SE, ROCHESTER, MN 55904') As addy;
+SELECT '#1614b' As ticket, pprint_addy(addy), addy.* FROM normalize_address('3208 U.S. 52, Rochester, MN 55901') As addy;
 \timing
diff --git a/extras/tiger_geocoder/tiger_2011/tables/lookup_tables_2011.sql b/extras/tiger_geocoder/tiger_2011/tables/lookup_tables_2011.sql
index dafaf41cf..c14d60d56 100644
--- a/extras/tiger_geocoder/tiger_2011/tables/lookup_tables_2011.sql
+++ b/extras/tiger_geocoder/tiger_2011/tables/lookup_tables_2011.sql
@@ -717,6 +717,7 @@ SELECT name, abbrev, true
         ('COUNTY HIGHWAY', 'Co Hwy'),
         ('COUNTY HIGH WAY', 'Co Hwy'),
         ('COUNTY ROAD', 'Co Rd'),
+        ('COUNTY RD', 'Co Rd'),
         ('CO RD', 'Co Rd'),
         ('CORD', 'Co Rd'),
         ('CO RTE', 'Co Rte'),
@@ -772,6 +773,7 @@ SELECT name, abbrev, true
         ('US HWY', 'US Hwy'),
         ('US HIGHWAY', 'US Hwy'),
         ('US HIGH WAY', 'US Hwy'),
+        ('U.S.', 'US Hwy'),
         ('US RTE', 'US Rte'),
         ('US ROUTE', 'US Rte'),
         ('US RT', 'US Rte'),
diff --git a/extras/tiger_geocoder/tiger_2011/tiger_loader_2011.sql b/extras/tiger_geocoder/tiger_2011/tiger_loader_2011.sql
index 8d58fa530..3eb7539d7 100644
--- a/extras/tiger_geocoder/tiger_2011/tiger_loader_2011.sql
+++ b/extras/tiger_geocoder/tiger_2011/tiger_loader_2011.sql
@@ -234,7 +234,7 @@ VALUES(9, 'addr', 'addr', true, true, false,false, 'a',
 	${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"',  ARRAY['gid','statefp','fromarmid', 'toarmid']);
 	
 INSERT INTO loader_lookuptables(process_order, lookup_name, table_name, load, level_county, level_state, single_geom_mode, insert_mode, pre_load_process, post_load_process,columns_exclude )
-VALUES(9, 'addrfeat', 'addrfeat', true, true, false,true, 'a', 
+VALUES(9, 'addrfeat', 'addrfeat', false, true, false,true, 'a', 
 	'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${table_name} PRIMARY KEY (gid)) INHERITS(${table_name});ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ALTER COLUMN statefp SET DEFAULT ''${state_fips}'';" ',
 	'${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');"
 	${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"',  ARRAY['gid','statefp','fromarmid', 'toarmid']);
diff --git a/extras/tiger_geocoder/tiger_2011/upgrade_geocode.sql b/extras/tiger_geocoder/tiger_2011/upgrade_geocode.sql
index 0c2cda640..d1cc4fa07 100644
--- a/extras/tiger_geocoder/tiger_2011/upgrade_geocode.sql
+++ b/extras/tiger_geocoder/tiger_2011/upgrade_geocode.sql
@@ -131,8 +131,8 @@ CREATE TYPE norm_addy AS (
     parsed BOOLEAN); */
 -- prefix and suffix street names for numbered highways
 CREATE TEMPORARY TABLE temp_types AS
-SELECT name, abbrev
-    FROM  ( VALUES
+SELECT name, abbrev, true
+    FROM (VALUES
         ('CAM', 'Cam'),
         ('CAM.', 'Cam'),
         ('CAMINO', 'Cam'),
@@ -141,6 +141,7 @@ SELECT name, abbrev
         ('COUNTY HIGHWAY', 'Co Hwy'),
         ('COUNTY HIGH WAY', 'Co Hwy'),
         ('COUNTY ROAD', 'Co Rd'),
+        ('COUNTY RD', 'Co Rd'),
         ('CO RD', 'Co Rd'),
         ('CORD', 'Co Rd'),
         ('CO RTE', 'Co Rte'),
@@ -172,6 +173,11 @@ SELECT name, abbrev
         ('I', 'I-'),
         ('I-', 'I-'),
         ('INTERSTATE', 'I-'),
+        ('INTERSTATE ROUTE', 'I-'),
+        ('INTERSTATE RTE', 'I-'),
+        ('INTERSTATE RTE.', 'I-'),
+        ('INTERSTATE RT', 'I-'),
+        ('LOOP', 'Loop'),
         ('ROUTE', 'Rte'),
         ('RTE', 'Rte'),
         ('RT', 'Rte'),
@@ -191,6 +197,7 @@ SELECT name, abbrev
         ('US HWY', 'US Hwy'),
         ('US HIGHWAY', 'US Hwy'),
         ('US HIGH WAY', 'US Hwy'),
+        ('U.S.', 'US Hwy'),
         ('US RTE', 'US Rte'),
         ('US ROUTE', 'US Rte'),
         ('US RT', 'US Rte'),
@@ -199,7 +206,8 @@ SELECT name, abbrev
         ('USFS HIGH WAY', 'USFS Hwy'),
         ('USFS RD', 'USFS Rd'),
         ('USFS ROAD', 'USFS Rd')
-           ) t(name, abbrev);
+           ) t(name, abbrev)
+           WHERE t.name NOT IN(SELECT name FROM street_type_lookup);
            
 DELETE FROM street_type_lookup WHERE name IN(SELECT name FROM temp_types);         
 INSERT INTO street_type_lookup (name, abbrev, is_hw) 
diff --git a/extras/tiger_geocoder/tiger_2011/upgrade_geocoder.bat b/extras/tiger_geocoder/tiger_2011/upgrade_geocoder.bat
index 9cc978e84..8cd94c82f 100755
--- a/extras/tiger_geocoder/tiger_2011/upgrade_geocoder.bat
+++ b/extras/tiger_geocoder/tiger_2011/upgrade_geocoder.bat
@@ -9,6 +9,6 @@ set PGCONTRIB=C:\Program Files\PostgreSQL\8.4\share\contrib
 "%PGBIN%\psql"  -d "%THEDB%" -f "upgrade_geocode.sql"
 "%PGBIN%\psql"  -d "%THEDB%" -f "tiger_loader_2011.sql"
 cd regress
-"%PGBIN%\psql" -t -f regress.sql
+REM "%PGBIN%\psql"  -d "%THEDB%" -t -f regress.sql
 pause
 
-- 
2.40.0