From be6683fb87dd253bfaa3807d25f25501eabb1662 Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Wed, 3 Apr 2013 12:54:46 +0000 Subject: [PATCH] #2261: fix creation of next next upgrade script, add null protection for pagc_normalize_address git-svn-id: http://svn.osgeo.org/postgis/trunk@11247 b70326c6-7e19-0410-871a-916f4a2858ee --- extensions/postgis_tiger_geocoder/Makefile.in | 2 +- .../tiger_2011/pagc_normalize/pagc_normalize_address.sql | 4 ++-- 2 files changed, 3 insertions(+), 3 deletions(-) diff --git a/extensions/postgis_tiger_geocoder/Makefile.in b/extensions/postgis_tiger_geocoder/Makefile.in index 1686278f9..dd5110a54 100644 --- a/extensions/postgis_tiger_geocoder/Makefile.in +++ b/extensions/postgis_tiger_geocoder/Makefile.in @@ -37,7 +37,7 @@ sql/$(EXTENSION).sql: sql_bits/tiger_geocoder.sql sql_bits/mark_editable_object #this is a cludge to allow upgrading from same SVN to same SVN sql/$(EXTENSION)--$(EXTVERSION)--$(EXTVERSION)next.sql: ../postgis_extension_helper.sql sql_bits/remove_from_extension.sql.in sql/tiger_geocoder_upgrade_minor.sql sql_bits/mark_editable_objects.sql.in sql_bits/tiger_geocoder_comments.sql ../postgis_extension_helper_uninstall.sql - cp $< $@ + cat $^ > $@ sql/$(EXTENSION)--$(EXTVERSION)next--$(EXTVERSION).sql: sql/$(EXTENSION)--$(EXTVERSION)--$(EXTVERSION)next.sql cp $< $@ 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 5044e685d..541187b3f 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 @@ -24,8 +24,8 @@ BEGIN 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(address1) || '::text As micro, - ' || quote_literal(city || ', ' || state || ' ' || zip) || '::text As macro') As pagc_addr +, '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 ) ; -- 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'); -- 2.50.1