From f3b7d0b004d5b613215a040839a88533bed16270 Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Mon, 9 Jan 2012 14:56:09 +0000 Subject: [PATCH] #1392: Can't geocode intersecting highways or areas where no zip available git-svn-id: http://svn.osgeo.org/postgis/trunk@8713 b70326c6-7e19-0410-871a-916f4a2858ee --- .../geocode/geocode_intersection.sql | 47 ++++++++++++------- .../tiger_2010/regress/geocode_regress | 4 ++ .../tiger_2010/regress/geocode_regress.sql | 5 ++ 3 files changed, 38 insertions(+), 18 deletions(-) diff --git a/extras/tiger_geocoder/tiger_2010/geocode/geocode_intersection.sql b/extras/tiger_geocoder/tiger_2010/geocode/geocode_intersection.sql index 9357d068b..b740f9610 100644 --- a/extras/tiger_geocoder/tiger_2010/geocode/geocode_intersection.sql +++ b/extras/tiger_geocoder/tiger_2010/geocode/geocode_intersection.sql @@ -39,7 +39,8 @@ BEGIN var_zip := array_agg(zip) FROM zip_lookup_base WHERE statefp = in_statefp AND lower(city) = lower(in_city); END IF; - IF var_zip IS NULL THEN + -- if we don't have a city or zip, don't bother doing the zip check, just keep as null + IF var_zip IS NULL AND in_city > '' THEN var_zip := array_agg(zip) FROM zip_lookup_base WHERE statefp = in_statefp AND lower(city) LIKE lower(in_city) || '%' ; END IF; IF var_debug THEN @@ -48,30 +49,39 @@ BEGIN var_sql := ' WITH a1 AS (SELECT f.*, addr.fromhn, addr.tohn, addr.side , addr.zip - FROM addr INNER JOIN (SELECT * FROM featnames + FROM (SELECT * FROM featnames WHERE statefp = $1 AND ( lower(name) = $2 ' || - CASE WHEN length(var_na_road.streetName) > 5 THEN ' or lower(fullname) LIKE $6 || ''%'' ' ELSE '' END || ')' - || ') AS f ON (addr.tlid = f.tlid AND addr.statefp = f.statefp) - WHERE addr.statefp = $1 AND addr.zip = ANY($5::text[]) + CASE WHEN length(var_na_road.streetName) > 5 THEN ' or lower(fullname) LIKE $6 || ''%'' ' ELSE '' END || ')' + || ') AS f LEFT JOIN (SELECT * FROM addr WHERE addr.statefp = $1) As addr ON (addr.tlid = f.tlid AND addr.statefp = f.statefp) + WHERE $5::text[] IS NULL OR addr.zip = ANY($5::text[]) OR addr.zip IS NULL + ORDER BY CASE WHEN lower(f.fullname) = $6 THEN 0 ELSE 1 END + LIMIT 5000 ), a2 AS (SELECT f.*, addr.fromhn, addr.tohn, addr.side , addr.zip - FROM addr INNER JOIN (SELECT * FROM featnames + FROM (SELECT * FROM featnames WHERE statefp = $1 AND ( lower(name) = $4 ' || CASE WHEN length(var_na_inter1.streetName) > 5 THEN ' or lower(fullname) LIKE $7 || ''%'' ' ELSE '' END || ')' - || ' ) AS f ON (addr.tlid = f.tlid AND addr.statefp = f.statefp) - WHERE addr.statefp = $1 AND addr.zip = ANY($5::text[]) + || ' ) AS f LEFT JOIN (SELECT * FROM addr WHERE addr.statefp = $1) AS addr ON (addr.tlid = f.tlid AND addr.statefp = f.statefp) + WHERE $5::text[] IS NULL OR addr.zip = ANY($5::text[]) or addr.zip IS NULL + ORDER BY CASE WHEN lower(f.fullname) = $7 THEN 0 ELSE 1 END + LIMIT 5000 ), e1 AS (SELECT e.the_geom, e.tnidf, e.tnidt, a.*, CASE WHEN a.side = ''L'' THEN e.tfidl ELSE e.tfidr END AS tfid FROM a1 As a - INNER JOIN edges AS e ON a.tlid = e.tlid - WHERE e.statefp = $1 ) , + INNER JOIN edges AS e ON (e.statefp = a.statefp AND a.tlid = e.tlid) + WHERE e.statefp = $1 + ORDER BY CASE WHEN lower(a.name) = $4 THEN 0 ELSE 1 END + CASE WHEN lower(e.fullname) = $7 THEN 0 ELSE 1 END + LIMIT 1000) , e2 AS (SELECT e.the_geom, e.tnidf, e.tnidt, a.*, CASE WHEN a.side = ''L'' THEN e.tfidl ELSE e.tfidr END AS tfid - FROM e1 INNER JOIN edges AS e ON (e.statefp = $1 AND ST_Intersects(e.the_geom, e1.the_geom) + FROM (SELECT * FROM edges WHERE statefp = $1) AS e INNER JOIN a2 AS a ON (e.statefp = a.statefp AND a.tlid = e.tlid) + INNER JOIN e1 ON (e.statefp = e1.statefp AND ST_Intersects(e.the_geom, e1.the_geom) AND ARRAY[e.tnidf, e.tnidt] && ARRAY[e1.tnidf, e1.tnidt] ) - INNER JOIN a2 AS a ON a.tlid = e.tlid - WHERE e.statefp = $1 AND (lower(e.fullname) = $7 or lower(a.name) LIKE $4 || ''%'') + + WHERE (lower(e.fullname) = $7 or lower(a.name) LIKE $4 || ''%'') + ORDER BY CASE WHEN lower(a.name) = $4 THEN 0 ELSE 1 END + CASE WHEN lower(e.fullname) = $7 THEN 0 ELSE 1 END + LIMIT 100 ), segs AS (SELECT DISTINCT ON(e1.tlid, e1.side) CASE WHEN e1.tnidf = e2.tnidf OR e1.tnidf = e2.tnidt THEN @@ -83,7 +93,8 @@ BEGIN CASE WHEN e1.tnidf = e2.tnidf OR e1.tnidf = e2.tnidt THEN ST_StartPoint(ST_GeometryN(ST_Multi(e1.the_geom),1)) ELSE ST_EndPoint(ST_GeometryN(ST_Multi(e1.the_geom),1)) END AS geom , - CASE WHEN lower(p.name) = $3 THEN 0 ELSE 1 END + CASE WHEN lower(p.name) = $3 THEN 0 ELSE 1 END + + levenshtein_ignore_case(p.name, $3) + levenshtein_ignore_case(e1.name || COALESCE('' '' || e1.sufqualabr, ''''),$2) + CASE WHEN e1.fullname = $6 THEN 0 ELSE levenshtein_ignore_case(e1.fullname, $6) END + + levenshtein_ignore_case(e2.name || COALESCE('' '' || e2.sufqualabr, ''''),$4) @@ -108,13 +119,13 @@ BEGIN '$4', quote_nullable(lower(var_na_inter1.streetName) ) ), '$5', quote_nullable(var_zip) ), '$6', quote_nullable(lower(var_na_road.streetName || ' ' || COALESCE(var_na_road.streetTypeAbbrev,'') )) ) , - '$7', quote_nullable(lower(var_na_inter1.streetName || ' ' || COALESCE(var_na_inter1.streetTypeAbbrev,'') ) ) ) , + '$7', quote_nullable(trim(lower(var_na_inter1.streetName || ' ' || COALESCE(var_na_inter1.streetTypeAbbrev,'') )) ) ) , '$8', quote_nullable(in_state ) ), '$9', num_results::text ); END IF; - FOR results IN EXECUTE var_sql USING in_statefp, lower(var_na_road.streetName), lower(in_city), lower(var_na_inter1.streetName), var_zip, - lower(var_na_road.streetName || ' ' || COALESCE(var_na_road.streetTypeAbbrev,'')), - lower(var_na_inter1.streetName || ' ' || COALESCE(var_na_inter1.streetTypeAbbrev,'')), in_state, num_results LOOP + FOR results IN EXECUTE var_sql USING in_statefp, trim(lower(var_na_road.streetName)), lower(in_city), lower(var_na_inter1.streetName), var_zip, + trim(lower(var_na_road.streetName || ' ' || COALESCE(var_na_road.streetTypeAbbrev,''))), + trim(lower(var_na_inter1.streetName || ' ' || COALESCE(var_na_inter1.streetTypeAbbrev,''))), in_state, num_results LOOP ADDY.preDirAbbrev := results.fedirp; ADDY.streetName := results.fename; ADDY.streetTypeAbbrev := results.fetype; diff --git a/extras/tiger_geocoder/tiger_2010/regress/geocode_regress b/extras/tiger_geocoder/tiger_2010/regress/geocode_regress index e42b930e4..2f8f6d386 100644 --- a/extras/tiger_geocoder/tiger_2010/regress/geocode_regress +++ b/extras/tiger_geocoder/tiger_2010/regress/geocode_regress @@ -76,3 +76,7 @@ T18b|26 Court St, Boston, MA 02108|POINT(-71.05885 42.35911)|1 #1333a|98 Weld Ave, Boston, MA 02119|POINT(-71.099 42.314234)|3 #1333a|99 Weld Ave, Boston, MA 02119|POINT(-71.099 42.314234)|3 #1333b|98 Haverford St, Boston, MA 02130|POINT(-71.101375 42.31376)|0 +#1392a|State Hwy 121, Coppell, TX|POINT(-96.993416 32.987025)|8 +#1392a|State Hwy 121, Coppell, TX|POINT(-96.993397 32.985954)|8 +#1392b|State Hwy 121, Coppell, TX|POINT(-96.993416 32.987025)|18 +#1392b|State Hwy 121, Coppell, TX|POINT(-96.993397 32.985954)|18 diff --git a/extras/tiger_geocoder/tiger_2010/regress/geocode_regress.sql b/extras/tiger_geocoder/tiger_2010/regress/geocode_regress.sql index 249a2b56e..c7c7556c3 100644 --- a/extras/tiger_geocoder/tiger_2010/regress/geocode_regress.sql +++ b/extras/tiger_geocoder/tiger_2010/regress/geocode_regress.sql @@ -95,7 +95,12 @@ SELECT '#1145c' As ticket, pprint_addy((g).addy) As address, target, ST_AsText(S SELECT '#1145d' As ticket, pprint_addy((g).addy) As address, target, ST_AsText(ST_SnapToGrid((g).geomout, 0.00001)) As pt, (g).rating FROM (SELECT geocode(target,2) As g, target FROM (SELECT '8512 141 St Ct Apple Valley MN 55124'::text As target) As f) As foo; SELECT '#1145e' As ticket, pprint_addy((g).addy) As address, target, ST_AsText(ST_SnapToGrid((g).geomout, 0.00001)) As pt, (g).rating FROM (SELECT geocode(target) As g, target FROM (SELECT '103 36th St W Minneapolis MN 55409'::text As target) As f) As foo; +-- cross street intersection SELECT '#1333a' AS ticket, pprint_addy(addy), st_astext(geomout),rating FROM geocode_intersection('Weld', 'School', 'MA', 'Boston'); SELECT '#1333b' AS ticket, pprint_addy(addy), st_astext(geomout),rating FROM geocode_intersection('Haverford St','Germania St', 'MA', 'Boston', '02130',1); + +-- crossing highways fails -- zip check +SELECT '#1392a' AS ticket, pprint_addy(addy), st_astext(geomout),rating FROM geocode_intersection('State Hwy 121', 'N Denton Tap Rd', 'TX', 'Coppell', '', 2); +SELECT '#1392b' AS ticket, pprint_addy(addy), st_astext(geomout),rating FROM geocode_intersection('State Hwy 121', 'N Denton Tap Rd', 'TX','', '', 2); -- \timing -- 2.40.0