From d7fe328962bfd2e50e15f5c08b0f08c4cdb3bcc3 Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Fri, 5 Apr 2013 14:37:43 +0000 Subject: [PATCH] change to use some elements from parse_address git-svn-id: http://svn.osgeo.org/postgis/trunk@11255 b70326c6-7e19-0410-871a-916f4a2858ee --- .../pagc_normalize/pagc_normalize_address.sql | 32 +++++++++++-------- 1 file changed, 18 insertions(+), 14 deletions(-) diff --git a/extras/tiger_geocoder/tiger_2011/pagc_normalize/pagc_normalize_address.sql b/extras/tiger_geocoder/tiger_2011/pagc_normalize/pagc_normalize_address.sql index 541187b3f..b2ac33e41 100644 --- a/extras/tiger_geocoder/tiger_2011/pagc_normalize/pagc_normalize_address.sql +++ b/extras/tiger_geocoder/tiger_2011/pagc_normalize/pagc_normalize_address.sql @@ -12,7 +12,8 @@ CREATE OR REPLACE FUNCTION pagc_normalize_address(in_rawinput character varying) $$ DECLARE result norm_addy; - rec RECORD; + var_rec RECORD; + var_parse_rec RECORD; rawInput VARCHAR; BEGIN @@ -20,25 +21,28 @@ BEGIN result.parsed := FALSE; rawInput := trim(in_rawinput); + var_parse_rec := parse_address(rawInput); + result.location := var_parse_rec.city; + result.stateAbbrev := trim(var_parse_rec.state); + result.zip := var_parse_rec.zip; - rec := (SELECT standardize_address( 'select seq, word::text, stdword::text, token from tiger.pagc_gaz union all select seq, word::text, stdword::text, token from tiger.pagc_lex ' + var_rec := (SELECT standardize_address( 'select seq, word::text, stdword::text, token from tiger.pagc_gaz union all select seq, word::text, stdword::text, token from tiger.pagc_lex ' , 'select seq, word::text, stdword::text, token from tiger.pagc_gaz order by id' , 'select * from tiger.pagc_rules order by id' -, 'select 0::int4 as id, ' || quote_literal(COALESCE(address1,'')) || '::text As micro, - ' || quote_literal(COALESCE(city || ', ','') || COALESCE(state || ' ', '') || COALESCE(zip,'')) || '::text As macro') As pagc_addr - FROM (SELECT * FROM parse_address(rawInput) ) As a ) ; +, 'select 0::int4 as id, ' || quote_literal(COALESCE(var_parse_rec.address1,'')) || '::text As micro, + ' || quote_literal(COALESCE(var_parse_rec.city || ', ','') || COALESCE(var_parse_rec.state || ' ', '') || COALESCE(var_parse_rec.zip,'')) || '::text As macro') As pagc_addr ) ; -- 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(rec.house_num, '[0-9]+'), '99999999999'); + result.address := to_number(substring(var_rec.house_num, '[0-9]+'), '99999999999'); --get rid of extraneous spaces before we return - result.zip := rec.postcode; - result.streetName := trim(rec.name); - result.location := trim(rec.city); - result.stateAbbrev := trim(rec.state); + --result.zip := var_rec.postcode; + result.streetName := trim(var_rec.name); + --result.location := trim(var_rec.city); + --result.stateAbbrev := trim(rec.state); --this should be broken out separately like pagc, but normalizer doesn't have a slot for it - result.streettypeAbbrev := trim(COALESCE(rec.suftype, rec.pretype)); - result.preDirAbbrev := trim(rec.predir); - result.postDirAbbrev := trim(rec.sufdir); - result.internal := trim(rec.unit); + 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.parsed := TRUE; RETURN result; END -- 2.50.1