[postgis-tickets] r15554 - Reverse geocoder missing street suffix direction.

Regina Obe lr at pcorp.us
Thu Aug 17 21:02:00 PDT 2017


Author: robe
Date: 2017-08-17 21:02:00 -0700 (Thu, 17 Aug 2017)
New Revision: 15554

Modified:
   branches/2.3/NEWS
   branches/2.3/extras/tiger_geocoder/geocode/reverse_geocode.sql
   branches/2.3/extras/tiger_geocoder/regress/reverse_geocode_regress
   branches/2.3/extras/tiger_geocoder/regress/reverse_geocode_regress.sql
Log:
Reverse geocoder missing street suffix direction.
Closes #3806 for PostGIS 2.3.4

Modified: branches/2.3/NEWS
===================================================================
--- branches/2.3/NEWS	2017-08-18 03:53:05 UTC (rev 15553)
+++ branches/2.3/NEWS	2017-08-18 04:02:00 UTC (rev 15554)
@@ -11,6 +11,7 @@
   - #3792, mark all 3D related including SFCGAL as parallel safe
   - #3800, shp2pgql-gui allow export of foreign tables, materialized views,
            and partitioned tables
+  - #3806, Reverse geocoder missing suffix direction when present
 
 
 PostGIS 2.3.3

Modified: branches/2.3/extras/tiger_geocoder/geocode/reverse_geocode.sql
===================================================================
--- branches/2.3/extras/tiger_geocoder/geocode/reverse_geocode.sql	2017-08-18 03:53:05 UTC (rev 15553)
+++ branches/2.3/extras/tiger_geocoder/geocode/reverse_geocode.sql	2017-08-18 04:02:00 UTC (rev 15554)
@@ -1,6 +1,6 @@
  /***
  *
- * Copyright (C) 2011-2014 Regina Obe and Leo Hsu (Paragon Corporation)
+ * Copyright (C) 2011-2017 Regina Obe and Leo Hsu (Paragon Corporation)
  **/
 -- This function given a point try to determine the approximate street address (norm_addy form)
 -- and array of cross streets, as well as interpolated points along the streets
@@ -68,7 +68,7 @@
 	IF var_zip > '' THEN
 	      var_addy.zip := var_zip ;
 	END IF;
-	
+
 	var_stmt := 'SELECT z.name  FROM place As z WHERE  z.statefp =  $1 AND ST_Intersects(the_geom, $2) LIMIT 1;';
 	EXECUTE var_stmt INTO var_place USING var_state, var_pt ;
 	IF var_place > '' THEN
@@ -91,7 +91,7 @@
 		-- We don't have any data for this county
 		RETURN;
 	END IF;
-	
+
 	var_addy.stateAbbrev = var_stusps;
 
 	-- Find the street edges that this point is closest to with tolerance of 0.005 but only consider the edge if the point is contained in the right or left face
@@ -121,7 +121,7 @@
 				FROM e LEFT JOIN addr As a ON (a.statefp = ' || quote_literal(var_state) || '  AND e.tlid = a.tlid and e.eside = a.side)
 				)
 		SELECT *
-		FROM (SELECT DISTINCT ON(tlid,side)  foo.fullname, foo.predirabrv, foo.streetname, foo.streettypeabbrev, foo.zip,  foo.center_pt,
+		FROM (SELECT DISTINCT ON(tlid,side)  foo.fullname, foo.predirabrv, foo.streetname, foo.sufdirabrv, foo.streettypeabbrev, foo.zip,  foo.center_pt,
 			  side, to_number(CASE WHEN trim(fromhn) ~ ''^[0-9]+$'' THEN fromhn ELSE NULL END,''99999999'')  As fromhn, to_number(CASE WHEN trim(tohn) ~ ''^[0-9]+$'' THEN tohn ELSE NULL END,''99999999'') As tohn,
 			  ST_GeometryN(ST_Multi(line),1) As line, dist
 		FROM
@@ -133,11 +133,11 @@
 			    WHERE featnames.statefp = ' || quote_literal(var_state) ||'   ) AS n ON (n.statefp =  e.statefp AND n.tlid = e.tlid)
 				ORDER BY dist LIMIT 50 ) As foo
 				ORDER BY foo.tlid, foo.side, ';
-				
+
 	    -- for numbered street/road use var_rating_highway to determine whether to prefer numbered or not (0 no pref, 1 prefer numbered, 2 prefer named)
 		var_stmt := var_stmt || ' CASE $1 WHEN 0 THEN 0  WHEN 1 THEN CASE WHEN foo.fullname ~ ''[0-9]+'' THEN 0 ELSE 1 END ELSE CASE WHEN foo.fullname > '''' AND NOT (foo.fullname ~ ''[0-9]+'') THEN 0 ELSE 1 END END ';
 		var_stmt := var_stmt || ',  foo.fullname ASC NULLS LAST, dist LIMIT 50) As f ORDER BY f.dist, CASE WHEN fullname > '''' THEN 0 ELSE 1 END '; --don't bother penalizing for distance if less than 20 meters
-				
+
 	IF var_debug = true THEN
 	    RAISE NOTICE 'Statement 1: %', replace(var_stmt, '$1', var_rating_highway::text);
 	END IF;
@@ -156,6 +156,7 @@
             var_addy.streetname = var_redge.streetname;
             var_addy.streettypeabbrev := var_redge.streettypeabbrev;
             var_addy.predirabbrev := var_redge.predirabrv;
+			var_addy.postDirAbbrev := var_redge.sufdirabrv;
         END IF;
 
         IF ST_Intersects(var_redge.line, var_primary_line) THEN
@@ -210,12 +211,13 @@
 					var_addy_alt.streetname := var_addy.streetname;
 					var_addy_alt.streettypeabbrev := var_addy.streettypeabbrev;
                     var_addy_alt.predirabbrev := var_addy.predirabbrev;
+					var_addy_alt.postDirAbbrev := var_addy.sufdirabrv;
 					addy[array_upper(addy,1) - 1 ] := var_addy_alt;
 					IF var_debug THEN
 						RAISE NOTICE 'Replaced with : %, %', var_addy_alt, clock_timestamp();
 					END IF;
 				END IF;
-				
+
 				IF var_debug THEN
 					RAISE NOTICE 'End Get matching edges loop: %', clock_timestamp();
 					RAISE NOTICE 'Final addresses: %, %', addy, clock_timestamp();

Modified: branches/2.3/extras/tiger_geocoder/regress/reverse_geocode_regress
===================================================================
--- branches/2.3/extras/tiger_geocoder/regress/reverse_geocode_regress	2017-08-18 03:53:05 UTC (rev 15553)
+++ branches/2.3/extras/tiger_geocoder/regress/reverse_geocode_regress	2017-08-18 04:02:00 UTC (rev 15554)
@@ -5,3 +5,4 @@
 58 Massachusetts Ave, Cambridge, MA 02139|{"(58,,Massachusetts,Ave,,,Cambridge,MA,02139,)","(7,,Wellesley,St,,,Cambridge,MA,02139,)","(7,,Massachusetts,Ave,,,Cambridge,MA,02139,)"}
 #1913|I- 95, Needham, MA 02494
 #2927|77 N Washington St, Boston, MA 02114
+#3806|212 3rd Ave N, Minneapolis, MN 55401
\ No newline at end of file

Modified: branches/2.3/extras/tiger_geocoder/regress/reverse_geocode_regress.sql
===================================================================
--- branches/2.3/extras/tiger_geocoder/regress/reverse_geocode_regress.sql	2017-08-18 03:53:05 UTC (rev 15553)
+++ branches/2.3/extras/tiger_geocoder/regress/reverse_geocode_regress.sql	2017-08-18 04:02:00 UTC (rev 15554)
@@ -6,4 +6,5 @@
 SELECT pprint_addy(addy[1]), addy FROM reverse_geocode(ST_Point(-71.09436,42.35981)); -- 58 Massachusetts Ave, Cambridge, MA 02139 (this gives me different answer but might be tiger change)
 SELECT '#1913' As ticket, pprint_addy(addy[1]) FROM reverse_geocode(ST_Point(-71.2248416, 42.30344833)); -- I- 95, Needham, MA 02494
 SELECT '#2927', pprint_addy(addy[1]) FROM reverse_geocode(ST_Point(-71.058246,42.36514)); -- 77 N Washington St, Boston, MA 02114
+SELECT '#3806', pprint_addy(addy[1]) FROM reverse_geocode(ST_Point(-93.271872, 44.98510)); -- 212 3rd Ave N, Minneapolis, MN 55401
 \timing
\ No newline at end of file



More information about the postgis-tickets mailing list