From e1c23bbf5c338ae2efdc6b693747b965a13fcee3 Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Mon, 4 Apr 2011 16:25:48 +0000 Subject: [PATCH] Fix typo in tiger manual and fix for #887 -- it was really the issue of using , instead of space to separate state and zip that was the main culprit git-svn-id: http://svn.osgeo.org/postgis/trunk@7005 b70326c6-7e19-0410-871a-916f4a2858ee --- doc/extras_tigergeocoder.xml | 2 +- .../tiger_2010/normalize/location_extract.sql | 3 ++- .../tiger_2010/normalize/state_extract.sql | 9 ++++++++- 3 files changed, 11 insertions(+), 3 deletions(-) diff --git a/doc/extras_tigergeocoder.xml b/doc/extras_tigergeocoder.xml index 11f00bc98..5a4ff2ca2 100644 --- a/doc/extras_tigergeocoder.xml +++ b/doc/extras_tigergeocoder.xml @@ -366,7 +366,7 @@ wget http://www2.census.gov/geo/pvs/tiger2010st/44_Rhode_Island/ --no-parent --r Pretty print address a table of addresses - SELECT address As orig, pprint_addy(normalize_address(address)) As pretty_address; + SELECT address As orig, pprint_addy(normalize_address(address)) As pretty_address FROM addresses_to_geocode; orig | pretty_address diff --git a/extras/tiger_geocoder/tiger_2010/normalize/location_extract.sql b/extras/tiger_geocoder/tiger_2010/normalize/location_extract.sql index 1e1e3f8b4..b42028eee 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/location_extract.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/location_extract.sql @@ -33,6 +33,7 @@ BEGIN IF stateAbbrev IS NOT NULL THEN lstate := statefp FROM state WHERE state.stusps = stateAbbrev; END IF; + lstate := COALESCE(lstate,''); street_array := regexp_split_to_array(fullStreet,ws); word_count := array_upper(street_array,1); @@ -41,7 +42,7 @@ BEGIN FOR i IN 1..word_count LOOP CONTINUE WHEN street_array[word_count-i+1] IS NULL OR street_array[word_count-i+1] = ''; - tempString := street_array[word_count-i+1] || tempString; + tempString := COALESCE(street_array[word_count-i+1],'') || tempString; stmt := ' SELECT' || ' 1,' diff --git a/extras/tiger_geocoder/tiger_2010/normalize/state_extract.sql b/extras/tiger_geocoder/tiger_2010/normalize/state_extract.sql index fb1da58db..0a481a35d 100644 --- a/extras/tiger_geocoder/tiger_2010/normalize/state_extract.sql +++ b/extras/tiger_geocoder/tiger_2010/normalize/state_extract.sql @@ -18,14 +18,21 @@ DECLARE rec RECORD; test BOOLEAN; ws VARCHAR; + var_verbose boolean := false; BEGIN ws := E'[ ,.\t\n\f\r]'; + -- If there is a trailing space or , get rid of it + -- this is to handle case where people use , instead of space to separate state and zip + -- such as '2450 N COLORADO ST, PHILADELPHIA, PA, 19132' instead of '2450 N COLORADO ST, PHILADELPHIA, PA 19132' + + tempString := regexp_replace(rawInput, E'(.*)' || ws || '+', E'\\1'); -- Separate out the last word of the state, and use it to compare to -- the state lookup table to determine the entire name, as well as the -- abbreviation associated with it. The zip code may or may not have -- been found. - tempString := substring(rawInput from ws || E'+([^ ,.\t\n\f\r0-9]*?)$'); + tempString := substring(tempString from ws || E'+([^ ,.\t\n\f\r0-9]*?)$'); + IF var_verbose THEN RAISE NOTICE 'state_extract rawInput: % tempString: %', rawInput, tempString; END IF; SELECT INTO tempInt count(*) FROM (select distinct abbrev from state_lookup WHERE upper(abbrev) = upper(tempString)) as blah; IF tempInt = 1 THEN -- 2.50.1