From 646fa53b44e1ed3624b3af532dfd9a773560eb1e Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Sat, 4 Aug 2018 18:44:17 +0200 Subject: [PATCH] improve place node search when no areas found Only look for place nodes in a certain radius according to the rank_search of the place node. --- lib/ReverseGeocode.php | 77 ++++++++++++++-------------- sql/functions.sql | 22 ++++++++ test/bdd/api/reverse/queries.feature | 12 +++-- test/bdd/api/search/params.feature | 2 +- test/bdd/steps/queries.py | 21 ++++++++ 5 files changed, 91 insertions(+), 43 deletions(-) diff --git a/lib/ReverseGeocode.php b/lib/ReverseGeocode.php index 8e5e8bd1..5648fedf 100644 --- a/lib/ReverseGeocode.php +++ b/lib/ReverseGeocode.php @@ -106,17 +106,21 @@ class ReverseGeocode if ($aPoly) { $sCountryCode = $aPoly['country_code']; - $sSQL = 'SELECT place_id, ST_distance('.$sPointSQL.', geometry) as distance'; + // look for place nodes with the given country code + $sSQL = 'SELECT place_id FROM'; + $sSQL .= ' (SELECT place_id, rank_search,'; + $sSQL .= ' ST_distance('.$sPointSQL.', geometry) as distance'; $sSQL .= ' FROM placex'; $sSQL .= ' WHERE osm_type = \'N\''; $sSQL .= ' AND country_code = \''.$sCountryCode.'\''; - $sSQL .= ' AND rank_address > 0'; - $sSQL .= ' AND rank_address <= ' .min(25, $iMaxRank); + $sSQL .= ' AND rank_search > 4'; + $sSQL .= ' AND rank_search <= ' .min(25, $iMaxRank); $sSQL .= ' AND type != \'postcode\''; $sSQL .= ' AND name IS NOT NULL '; $sSQL .= ' and indexed_status = 0 and linked_place_id is null'; - $sSQL .= ' AND ST_DWithin('.$sPointSQL.', geometry, 1.0)'; - $sSQL .= ' ORDER BY distance ASC, rank_address DESC'; + $sSQL .= ' AND ST_DWithin('.$sPointSQL.', geometry, 5.0)) p '; + $sSQL .= 'WHERE distance <= reverse_place_diameter(rank_search)'; + $sSQL .= ' ORDER BY rank_search DESC, distance ASC'; $sSQL .= ' LIMIT 1'; if (CONST_Debug) var_dump($sSQL); @@ -127,6 +131,23 @@ class ReverseGeocode if ($aPlacNode) { return $aPlacNode; } + + // still nothing, then return the country object + $sSQL = 'SELECT place_id, ST_distance('.$sPointSQL.', centroid) as distance'; + $sSQL .= ' FROM placex'; + $sSQL .= ' WHERE country_code = \''.$sCountryCode.'\''; + $sSQL .= ' AND rank_search = 4 AND rank_address = 4'; + $sSQL .= ' AND class in (\'boundary\', \'place\')'; + $sSQL .= ' ORDER BY distance ASC'; + + if (CONST_Debug) var_dump($sSQL); + $aPlacNode = chksql( + $this->oDB->getRow($sSQL), + 'Could not determine place node.' + ); + if ($aPlacNode) { + return $aPlacNode; + } } } @@ -138,13 +159,13 @@ class ReverseGeocode // polygon search begins at suburb-level if ($iMaxRank > 25) $iMaxRank = 25; // no polygon search over country-level - if ($iMaxRank < 4) $iMaxRank = 4; + if ($iMaxRank < 5) $iMaxRank = 5; // search for polygon $sSQL = 'SELECT place_id, parent_place_id, rank_address, rank_search FROM'; $sSQL .= '(select place_id, parent_place_id, rank_address, rank_search, country_code, geometry'; $sSQL .= ' FROM placex'; $sSQL .= ' WHERE ST_GeometryType(geometry) in (\'ST_Polygon\', \'ST_MultiPolygon\')'; - $sSQL .= ' AND rank_address Between 4 AND ' .$iMaxRank; + $sSQL .= ' AND rank_address Between 5 AND ' .$iMaxRank; $sSQL .= ' AND geometry && '.$sPointSQL; $sSQL .= ' AND type != \'postcode\' '; $sSQL .= ' AND name is not null'; @@ -165,49 +186,27 @@ class ReverseGeocode $iPlaceID = $aPoly['place_id']; if ($iRankAddress != $iMaxRank) { - //search diameter for the place node search - if ($iMaxRank <= 4) { - $fSearchDiam = 4; - } elseif ($iMaxRank <= 8) { - $fSearchDiam = 2; - } elseif ($iMaxRank <= 10) { - $fSearchDiam = 1; - } elseif ($iMaxRank <= 12) { - $fSearchDiam = 0.8; - } elseif ($iMaxRank <= 17) { - $fSearchDiam = 0.6; - } elseif ($iMaxRank <= 18) { - $fSearchDiam = 0.2; - } elseif ($iMaxRank <= 25) { - $fSearchDiam = 0.1; - } - - $sSQL = 'SELECT place_id'; - $sSQL .= ' FROM ('; - $sSQL .= ' SELECT place_id, rank_address,country_code, geometry,'; + $sSQL = 'SELECT place_id FROM '; + $sSQL .= '(SELECT place_id, rank_search, country_code, geometry,'; $sSQL .= ' ST_distance('.$sPointSQL.', geometry) as distance'; $sSQL .= ' FROM placex'; $sSQL .= ' WHERE osm_type = \'N\''; - if ($iRankAddress = 16) { - // using rank_search because of a better differentiation for place nodes at rank_address 16 - $sSQL .= ' AND rank_search > '.$iRankSearch; - $sSQL .= ' AND rank_search <= ' .$iMaxRank; - $sSQL .= ' AND class = \'place\''; - } else { - $sSQL .= ' AND rank_address > '.$iRankAddress; - $sSQL .= ' AND rank_address <= ' .$iMaxRank; - } - $sSQL .= ' AND ST_DWithin('.$sPointSQL.', geometry, '.$fSearchDiam.')'; + // using rank_search because of a better differentiation + // for place nodes at rank_address 16 + $sSQL .= ' AND rank_search > '.$iRankSearch; + $sSQL .= ' AND rank_search <= ' .$iMaxRank; + $sSQL .= ' AND class = \'place\''; $sSQL .= ' AND type != \'postcode\''; $sSQL .= ' AND name IS NOT NULL '; - $sSQL .= ' and indexed_status = 0 and linked_place_id is null'; + $sSQL .= ' AND indexed_status = 0 AND linked_place_id is null'; // preselection through bbox $sSQL .= ' AND (SELECT geometry FROM placex WHERE place_id = '.$iPlaceID.') && geometry'; $sSQL .= ' ORDER BY distance ASC,'; $sSQL .= ' rank_address DESC'; $sSQL .= ' limit 500) as a'; $sSQL .= ' WHERE ST_CONTAINS((SELECT geometry FROM placex WHERE place_id = '.$iPlaceID.'), geometry )'; - $sSQL .= ' ORDER BY distance ASC, rank_address DESC'; + $sSQL .= ' AND distance <= reverse_place_diameter(rank_search)'; + $sSQL .= ' ORDER BY distance ASC, rank_search DESC'; $sSQL .= ' LIMIT 1'; if (CONST_Debug) var_dump($sSQL); diff --git a/sql/functions.sql b/sql/functions.sql index ada9fb50..7c62bc93 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -256,6 +256,28 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; +CREATE OR REPLACE FUNCTION reverse_place_diameter(rank_search SMALLINT) + RETURNS FLOAT + AS $$ +BEGIN + IF rank_search <= 4 THEN + RETURN 5.0; + ELSIF rank_search <= 8 THEN + RETURN 1.8; + ELSIF rank_search <= 12 THEN + RETURN 0.6; + ELSIF rank_search <= 17 THEN + RETURN 0.16; + ELSIF rank_search <= 18 THEN + RETURN 0.08; + ELSIF rank_search <= 19 THEN + RETURN 0.04; + END IF; + + RETURN 0.02; +END; +$$ +LANGUAGE plpgsql IMMUTABLE; CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT, OUT rank_search SMALLINT, OUT rank_address SMALLINT) diff --git a/test/bdd/api/reverse/queries.feature b/test/bdd/api/reverse/queries.feature index a6c1dd34..e06b1775 100644 --- a/test/bdd/api/reverse/queries.feature +++ b/test/bdd/api/reverse/queries.feature @@ -48,8 +48,14 @@ Feature: Reverse geocoding Scenario: Location off the coast When sending jsonv2 reverse coordinates 54.046489113,8.5546870529 + Then results contain + | display_name | + | Freie und Hansestadt Hamburg, Deutschland | + + Scenario: When slightly outside town, the town is not shown + When sending jsonv2 reverse coordinates -32.122,-56.114 | zoom | - | 5 | + | 15 | Then results contain - | error | - | Unable to geocode | + | display_name | + | Tacuarembó, Uruguay | diff --git a/test/bdd/api/search/params.feature b/test/bdd/api/search/params.feature index fcd2b603..feacd5f9 100644 --- a/test/bdd/api/search/params.feature +++ b/test/bdd/api/search/params.feature @@ -111,7 +111,7 @@ Feature: Search queries When sending json search query "restaurant" | bounded | viewbox | | 1 | 9.93027,53.61634,10.10073,53.54500 | - Then result has bounding box in 53.54500,53.61634,9.93027,10.10073 + Then result has centroid in 53.54500,53.61634,9.93027,10.10073 Scenario: Prefer results within viewbox When sending json search query "25 de Mayo" with address diff --git a/test/bdd/steps/queries.py b/test/bdd/steps/queries.py index 62bc295e..fd13dd13 100644 --- a/test/bdd/steps/queries.py +++ b/test/bdd/steps/queries.py @@ -593,6 +593,27 @@ def step_impl(context, lid, coords): assert_greater_equal(bbox[2], coord[2]) assert_less_equal(bbox[3], coord[3]) +@then(u'result (?P\d+ )?has centroid in (?P[\d,.-]+)') +def step_impl(context, lid, coords): + if lid is None: + context.execute_steps("then at least 1 result is returned") + bboxes = zip(context.response.property_list('lat'), + context.response.property_list('lon')) + else: + context.execute_steps("then more than %sresults are returned" % lid) + res = context.response.result[int(lid)] + bboxes = [ (res['lat'], res['lon']) ] + + coord = [ float(x) for x in coords.split(',') ] + + for lat, lon in bboxes: + lat = float(lat) + lon = float(lon) + assert_greater_equal(lat, coord[0]) + assert_less_equal(lat, coord[1]) + assert_greater_equal(lon, coord[2]) + assert_less_equal(lon, coord[3]) + @then(u'there are(?P no)? duplicates') def check_for_duplicates(context, neg): context.execute_steps("then at least 1 result is returned") -- 2.43.2