[postgis-tickets] r15553 - Reverse geocoder missing street suffix direction.
Regina Obe
lr at pcorp.us
Thu Aug 17 20:53:05 PDT 2017
Author: robe
Date: 2017-08-17 20:53:05 -0700 (Thu, 17 Aug 2017)
New Revision: 15553
Modified:
trunk/extras/tiger_geocoder/geocode/reverse_geocode.sql
trunk/extras/tiger_geocoder/regress/reverse_geocode_regress
trunk/extras/tiger_geocoder/regress/reverse_geocode_regress.sql
Log:
Reverse geocoder missing street suffix direction.
References #3806 for PostGIS 2.4.0
Modified: trunk/extras/tiger_geocoder/geocode/reverse_geocode.sql
===================================================================
--- trunk/extras/tiger_geocoder/geocode/reverse_geocode.sql 2017-08-11 16:38:20 UTC (rev 15552)
+++ trunk/extras/tiger_geocoder/geocode/reverse_geocode.sql 2017-08-18 03:53:05 UTC (rev 15553)
@@ -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: trunk/extras/tiger_geocoder/regress/reverse_geocode_regress
===================================================================
--- trunk/extras/tiger_geocoder/regress/reverse_geocode_regress 2017-08-11 16:38:20 UTC (rev 15552)
+++ trunk/extras/tiger_geocoder/regress/reverse_geocode_regress 2017-08-18 03:53:05 UTC (rev 15553)
@@ -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
Modified: trunk/extras/tiger_geocoder/regress/reverse_geocode_regress.sql
===================================================================
--- trunk/extras/tiger_geocoder/regress/reverse_geocode_regress.sql 2017-08-11 16:38:20 UTC (rev 15552)
+++ trunk/extras/tiger_geocoder/regress/reverse_geocode_regress.sql 2017-08-18 03:53:05 UTC (rev 15553)
@@ -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
-\timing
\ No newline at end of file
+SELECT '#3806', pprint_addy(addy[1]) FROM reverse_geocode(ST_Point(-93.271872, 44.98510)); -- 212 3rd Ave N, Minneapolis, MN 55401
+\timing
More information about the postgis-tickets
mailing list