[postgis-tickets] [SCM] PostGIS branch stable-3.1 updated. 3.1.8-20-gc0eb7aea0

[git at osgeo.org]

This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".

The branch, stable-3.1 has been updated
       via  c0eb7aea076e169a554ef894037126e9b0d145fe (commit)
      from  e0d077c71a185b97804b72489c55435baa9bdf60 (commit)

Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.

- Log -----------------------------------------------------------------
commit c0eb7aea076e169a554ef894037126e9b0d145fe
Author: Regina Obe <lr at pcorp.us>
Date:   Fri Jan 27 19:35:11 2023 -0500

    Prefer street segments that have a street name
    and address range over those missing either piece,
    as long as they are within 70 meters of reference point
    Closes #5331 for PostGIS 3.1.9

diff --git a/NEWS b/NEWS
index cdeba9ca8..780a4175a 100644
--- a/NEWS
+++ b/NEWS
@@ -13,6 +13,8 @@ xxxx/xx/xx
   - #5344, Include topology id sequence state in dumps (Sandro Santilli)
   - #5288, ST_LineCrossingDirection multi-cross error (Paul Ramsey)
   - #5347, _ST_BestSRID crashes on ARM with infinite geometries (Regina Obe)
+  - #5331, [postgis_tiger_geocoder] reverse_geocode, prefer addressable ranges
+           (Regina Obe, Locance)
 
 
 PostGIS 3.1.8
diff --git a/extras/tiger_geocoder/geocode/reverse_geocode.sql b/extras/tiger_geocoder/geocode/reverse_geocode.sql
index 5a103de1a..eca1364b8 100644
--- a/extras/tiger_geocoder/geocode/reverse_geocode.sql
+++ b/extras/tiger_geocoder/geocode/reverse_geocode.sql
@@ -1,6 +1,6 @@
  /***
  *
- * Copyright (C) 2011-2017 Regina Obe and Leo Hsu (Paragon Corporation)
+ * Copyright (C) 2011-2023 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
@@ -136,7 +136,11 @@ BEGIN
 
 	    -- 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
+
+		-- penalize ranges with no street name or no address range if there are others with addresses within 70 meters
+		var_stmt := var_stmt || ',  foo.fullname ASC NULLS LAST, dist LIMIT 50) As f
+		ORDER BY CASE WHEN f.dist < 70 THEN 0 ELSE f.dist END, CASE WHEN fullname > '''' THEN 0 ELSE 1 END,
+			CASE WHEN f.fromhn IS NOT NULL THEN 0 ELSE 1 END, f.dist ';
 
 	IF var_debug = true THEN
 	    RAISE NOTICE 'Statement 1: %', replace(var_stmt, '$1', var_rating_highway::text);

-----------------------------------------------------------------------

Summary of changes:
 NEWS                                              | 2 ++
 extras/tiger_geocoder/geocode/reverse_geocode.sql | 8 ++++++--
 2 files changed, 8 insertions(+), 2 deletions(-)


hooks/post-receive
-- 
PostGIS