[postgis-tickets] r14849 - Schema qualify calls and prevent error when tiger street number is not a number (just return first number part)
Regina Obe
lr at pcorp.us
Sun Apr 17 14:45:33 PDT 2016
Author: robe
Date: 2016-04-17 14:45:32 -0700 (Sun, 17 Apr 2016)
New Revision: 14849
Modified:
branches/2.2/extras/tiger_geocoder/geocode/geocode.sql
branches/2.2/extras/tiger_geocoder/geocode/geocode_address.sql
branches/2.2/extras/tiger_geocoder/geocode/geocode_intersection.sql
Log:
Schema qualify calls and prevent error when tiger street number is not a number (just return first number part)
References #3531 #3451 for PostGIS 2.2.3
Modified: branches/2.2/extras/tiger_geocoder/geocode/geocode.sql
===================================================================
--- branches/2.2/extras/tiger_geocoder/geocode/geocode.sql 2016-04-17 20:38:14 UTC (rev 14848)
+++ branches/2.2/extras/tiger_geocoder/geocode/geocode.sql 2016-04-17 21:45:32 UTC (rev 14849)
@@ -74,7 +74,7 @@
)
*
FROM
- geocode_address(IN_ADDY, max_results, restrict_geom) a
+ tiger.geocode_address(IN_ADDY, max_results, restrict_geom) a
ORDER BY
(a.addy).address,
(a.addy).predirabbrev,
@@ -109,7 +109,7 @@
-- No zip code, try state/location, need both or we'll get too much stuffs.
IF IN_ADDY.zip IS NOT NULL OR (IN_ADDY.stateAbbrev IS NOT NULL AND IN_ADDY.location IS NOT NULL) THEN
- FOR rec in SELECT * FROM geocode_location(IN_ADDY, restrict_geom) As b ORDER BY b.rating LIMIT max_results
+ FOR rec in SELECT * FROM tiger.geocode_location(IN_ADDY, restrict_geom) As b ORDER BY b.rating LIMIT max_results
LOOP
ADDY := rec.addy;
GEOMOUT := rec.geomout;
Modified: branches/2.2/extras/tiger_geocoder/geocode/geocode_address.sql
===================================================================
--- branches/2.2/extras/tiger_geocoder/geocode/geocode_address.sql 2016-04-17 20:38:14 UTC (rev 14848)
+++ branches/2.2/extras/tiger_geocoder/geocode/geocode_address.sql 2016-04-17 21:45:32 UTC (rev 14849)
@@ -40,7 +40,7 @@
var_restrict_geom = ST_SnapToGrid(ST_Transform(restrict_geom, 4269), 0.000001);
END IF;
END IF;
- var_bfilter := ' SELECT zcta5ce FROM zcta5 AS zc
+ var_bfilter := ' SELECT zcta5ce FROM tiger.zcta5 AS zc
WHERE zc.statefp = ' || quote_nullable(in_statefp) || '
AND ST_Intersects(zc.the_geom, ' || quote_literal(var_restrict_geom::text) || '::geometry) ' ;
@@ -57,7 +57,7 @@
--This signals bad zip input, only use the range if it falls in the place zip range
IF length(parsed.zip) != 5 AND parsed.location IS NOT NULL THEN
stmt := 'SELECT ARRAY(SELECT DISTINCT zip
- FROM zip_lookup_base AS z
+ FROM tiger.zip_lookup_base AS z
WHERE z.statefp = $1
AND z.zip = ANY($3) AND lower(z.city) LIKE lower($2) || ''%''::text ' || COALESCE(' AND z.zip IN(' || var_bfilter || ')', '') || ')::varchar[] AS zip ORDER BY zip' ;
EXECUTE stmt INTO zip_info USING in_statefp, parsed.location, zip_info.zip;
@@ -77,7 +77,7 @@
-- If no good zips just include all for the location
-- We do a like instead of absolute check since tiger sometimes tacks things like Town at end of places
stmt := 'SELECT ARRAY(SELECT DISTINCT zip
- FROM zip_lookup_base AS z
+ FROM tiger.zip_lookup_base AS z
WHERE z.statefp = $1
AND lower(z.city) LIKE lower($2) || ''%''::text ' || COALESCE(' AND z.zip IN(' || var_bfilter || ')', '') || ')::varchar[] AS zip ORDER BY zip' ;
EXECUTE stmt INTO zip_info USING in_statefp, parsed.location;
@@ -103,7 +103,7 @@
|| ' sufdirabrv, prequalabr)
)
As rank
- FROM featnames As f INNER JOIN addr As ad ON (f.tlid = ad.tlid)
+ FROM tiger.featnames As f INNER JOIN tiger.addr As ad ON (f.tlid = ad.tlid)
WHERE $10 = f.statefp AND $10 = ad.statefp
'
|| CASE WHEN length(parsed.streetName) > 5 THEN ' AND (lower(f.fullname) LIKE (COALESCE($5 || '' '','''') || lower($2) || ''%'')::text OR lower(f.name) = lower($2) OR soundex(f.name) = soundex($2) ) ' ELSE ' AND lower(f.name) = lower($2) ' END
@@ -160,10 +160,10 @@
a.zip,
p.name as place
- FROM a INNER JOIN edges As b ON (a.statefp = b.statefp AND a.tlid = b.tlid '
+ FROM a INNER JOIN tiger.edges As b ON (a.statefp = b.statefp AND a.tlid = b.tlid '
|| ')
- INNER JOIN faces AS f ON ($10 = f.statefp AND ( (b.tfidl = f.tfid AND a.side = ''L'') OR (b.tfidr = f.tfid AND a.side = ''R'' ) ))
- INNER JOIN place p ON ($10 = p.statefp AND f.placefp = p.placefp '
+ INNER JOIN tiger.faces AS f ON ($10 = f.statefp AND ( (b.tfidl = f.tfid AND a.side = ''L'') OR (b.tfidr = f.tfid AND a.side = ''R'' ) ))
+ INNER JOIN tiger.place p ON ($10 = p.statefp AND f.placefp = p.placefp '
|| CASE WHEN parsed.location > '' AND zip_info.zip IS NULL THEN ' AND ( lower(p.name) LIKE (lower($3::text) || ''%'') ) ' ELSE '' END
|| ')
WHERE a.statefp = $10 AND b.statefp = $10 '
@@ -174,7 +174,7 @@
ORDER BY 10 , 11 DESC
LIMIT 20
) AS sub
- JOIN state s ON ($10 = s.statefp)
+ JOIN tiger.state s ON ($10 = s.statefp)
ORDER BY 1,2,3,4,5,6,7,9
LIMIT 20) As foo ORDER BY sub_rating, exact_address DESC LIMIT ' || max_results*10 ;
@@ -347,7 +347,7 @@
|| ' AND $1::integer <= greatest_hn(b.fromhn,b.tohn) '
|| ' AND ($1 % 2)::numeric::integer = (to_number(b.fromhn,''99999999'') % 2)'
|| ' as exact_address, a.name, a.prequalabr, a.pretypabrv '
- || ' FROM featnames a join addr b ON (a.tlid = b.tlid AND a.statefp = b.statefp )'
+ || ' FROM tiger.featnames a join tiger.addr b ON (a.tlid = b.tlid AND a.statefp = b.statefp )'
|| ' WHERE'
|| ' a.statefp = ' || quote_literal(zip_info.statefp) || ' AND a.mtfcc LIKE ''S%'' '
|| coalesce(' AND b.zip IN (''' || array_to_string(zip_info.zip,''',''') || ''') ','')
@@ -358,14 +358,14 @@
|| ' ORDER BY 11'
|| ' LIMIT 20'
|| ' ) AS sub'
- || ' JOIN edges e ON (' || quote_literal(zip_info.statefp) || ' = e.statefp AND sub.tlid = e.tlid AND e.mtfcc LIKE ''S%'' '
+ || ' JOIN tiger.edges e ON (' || quote_literal(zip_info.statefp) || ' = e.statefp AND sub.tlid = e.tlid AND e.mtfcc LIKE ''S%'' '
|| CASE WHEN var_restrict_geom IS NOT NULL THEN ' AND ST_Intersects(e.the_geom, $8) ' ELSE '' END || ') '
- || ' JOIN state s ON (' || quote_literal(zip_info.statefp) || ' = s.statefp)'
- || ' JOIN faces f ON (' || quote_literal(zip_info.statefp) || ' = f.statefp AND (e.tfidl = f.tfid OR e.tfidr = f.tfid))'
- || ' LEFT JOIN zip_lookup_base zip ON (sub.zip = zip.zip AND zip.statefp=' || quote_literal(zip_info.statefp) || ')'
- || ' LEFT JOIN place p ON (' || quote_literal(zip_info.statefp) || ' = p.statefp AND f.placefp = p.placefp)'
- || ' LEFT JOIN county co ON (' || quote_literal(zip_info.statefp) || ' = co.statefp AND f.countyfp = co.countyfp)'
- || ' LEFT JOIN cousub cs ON (' || quote_literal(zip_info.statefp) || ' = cs.statefp AND cs.cosbidfp = sub.statefp || co.countyfp || f.cousubfp)'
+ || ' JOIN tiger.state s ON (' || quote_literal(zip_info.statefp) || ' = s.statefp)'
+ || ' JOIN tiger.faces f ON (' || quote_literal(zip_info.statefp) || ' = f.statefp AND (e.tfidl = f.tfid OR e.tfidr = f.tfid))'
+ || ' LEFT JOIN tiger.zip_lookup_base zip ON (sub.zip = zip.zip AND zip.statefp=' || quote_literal(zip_info.statefp) || ')'
+ || ' LEFT JOIN tiger.place p ON (' || quote_literal(zip_info.statefp) || ' = p.statefp AND f.placefp = p.placefp)'
+ || ' LEFT JOIN tiger.county co ON (' || quote_literal(zip_info.statefp) || ' = co.statefp AND f.countyfp = co.countyfp)'
+ || ' LEFT JOIN tiger.cousub cs ON (' || quote_literal(zip_info.statefp) || ' = cs.statefp AND cs.cosbidfp = sub.statefp || co.countyfp || f.cousubfp)'
|| ' WHERE'
|| ' ( (sub.side = ''L'' and e.tfidl = f.tfid) OR (sub.side = ''R'' and e.tfidr = f.tfid) ) '
|| ' ORDER BY 1,2,3,4,5,6,7,9'
@@ -392,7 +392,7 @@
END IF;
IF results.exact_address THEN
- ADDY.address := parsed.address;
+ ADDY.address := substring(parsed.address FROM '[0-9]+')::integer;
ELSE
ADDY.address := NULL;
END IF;
Modified: branches/2.2/extras/tiger_geocoder/geocode/geocode_intersection.sql
===================================================================
--- branches/2.2/extras/tiger_geocoder/geocode/geocode_intersection.sql 2016-04-17 20:38:14 UTC (rev 14848)
+++ branches/2.2/extras/tiger_geocoder/geocode/geocode_intersection.sql 2016-04-17 21:45:32 UTC (rev 14849)
@@ -140,7 +140,7 @@
ADDY.stateAbbrev := in_state;
ADDY.zip := results.zip;
ADDY.parsed := TRUE;
- ADDY.address := results.address;
+ ADDY.address := substring(results.address FROM '[0-9]+')::integer;
GEOMOUT := results.geom;
RATING := results.a_rating;
More information about the postgis-tickets
mailing list