restrict number of results for reverse queries

When given a coordinate off the coast of a large town, the entire
town may end up in the potential results during the reverse query.
Postgres then needs to sort tens of thousands of results before it
can determine the clostest one. Given that the results at such a
large search radius are bound to be imprecise anyway, restrict
the number of results postgres should consider to 1000.

diff --git a/lib/ReverseGeocode.php b/lib/ReverseGeocode.php
index 31ebe7174fa66e0ab76730c7d81fd2bb779c48bd..ce2ee5e5db1a3d5bfba35b894349d0c9a07ab3f6 100644 (file)
--- a/lib/ReverseGeocode.php
+++ b/lib/ReverseGeocode.php
@@ -121,9 +121,17 @@ class ReverseGeocode
 
             $sSQL = 'select place_id,parent_place_id,rank_search,country_code,';
             $sSQL .= '  ST_distance('.$sPointSQL.', geometry) as distance';
 
             $sSQL = 'select place_id,parent_place_id,rank_search,country_code,';
             $sSQL .= '  ST_distance('.$sPointSQL.', geometry) as distance';

-            $sSQL .= ' FROM placex';
-            $sSQL .= ' WHERE ST_DWithin('.$sPointSQL.', geometry, '.$fSearchDiam.')';
-            $sSQL .= ' and rank_search != 28 and rank_search >= '.$iMaxRank;
+            $sSQL .= ' FROM ';
+            if ($fSearchDiam < 0.01) {
+                $sSQL .= ' placex';
+                $sSQL .= '   WHERE ST_DWithin('.$sPointSQL.', geometry, '.$fSearchDiam.')';
+                $sSQL .= '   AND';
+            } else {
+                $sSQL .= ' (SELECT * FROM placex ';
+                $sSQL .= '   WHERE ST_DWithin('.$sPointSQL.', geometry, '.$fSearchDiam.')';
+                $sSQL .= '   LIMIT 1000) as p WHERE';
+            }
+            $sSQL .= ' rank_search != 28 and rank_search >= '.$iMaxRank;

             $sSQL .= ' and (name is not null or housenumber is not null)';
             $sSQL .= ' and class not in (\'waterway\',\'railway\',\'tunnel\',\'bridge\',\'man_made\')';
             $sSQL .= ' and indexed_status = 0 ';
             $sSQL .= ' and (name is not null or housenumber is not null)';
             $sSQL .= ' and class not in (\'waterway\',\'railway\',\'tunnel\',\'bridge\',\'man_made\')';
             $sSQL .= ' and indexed_status = 0 ';

diff --git a/test/bdd/api/reverse/queries.feature b/test/bdd/api/reverse/queries.feature
index bc88db063b18740bd212e7f12b5e0ef0e7d28caa..b3ad20b201e91f019ecd8a0f1343d3c364a262b6 100644 (file)
--- a/test/bdd/api/reverse/queries.feature
+++ b/test/bdd/api/reverse/queries.feature
@@ -45,3 +45,9 @@ Feature: Reverse geocoding
         Then result addresses contain
           | house_number | road |
           | 5            | Clasingstraße |
         Then result addresses contain
           | house_number | road |
           | 5            | Clasingstraße |

+
+    Scenario: Location off the coast
+        When sending jsonv2 reverse coordinates 54.046489113,8.5546870529
+        Then results contain
+         | display_name |
+         | Freie und Hansestadt Hamburg, Deutschland |