From 71c9adfdba238bb55a43fb69da0290c5b0e6e159 Mon Sep 17 00:00:00 2001 From: gemo1011 Date: Thu, 3 May 2018 16:57:01 +0200 Subject: [PATCH] performence update through subquerry --- lib/ReverseGeocode.php | 17 +++++++++++------ 1 file changed, 11 insertions(+), 6 deletions(-) diff --git a/lib/ReverseGeocode.php b/lib/ReverseGeocode.php index b6369643..f720eea3 100644 --- a/lib/ReverseGeocode.php +++ b/lib/ReverseGeocode.php @@ -92,19 +92,24 @@ class ReverseGeocode $iRankAddress = $aPoly['rank_address']; $iPlaceID = $aPoly['place_id']; - $sSQL = 'select place_id,parent_place_id,rank_address,country_code, linked_place_id,'; - $sSQL .=' ST_distance('.$sPointSQL.', geometry) as distance'; + $sSQL = 'SELECT *'; + $sSQL .= ' FROM ('; + $sSQL .= ' SELECT place_id, rank_address,country_code, linked_place_id, geometry,'; + $sSQL .= ' ST_distance('.$sPointSQL.', geometry) as distance'; $sSQL .= ' FROM placex'; $sSQL .= ' WHERE osm_type = \'N\''; $sSQL .= ' AND rank_address >= '.$iRankAddress; $sSQL .= ' AND rank_address <= LEAST(25, '.$iMaxRank.')'; - $sSQL .= ' AND ST_CONTAINS((SELECT geometry FROM placex WHERE place_id = '.$iPlaceID.'), geometry )'; $sSQL .= ' AND type != \'postcode\''; $sSQL .= ' AND name IS NOT NULL '; $sSQL .= ' and class not in (\'waterway\',\'railway\',\'tunnel\',\'bridge\',\'man_made\')'; - $sSQL .= ' ORDER BY rank_address DESC,'; - $sSQL .= ' distance ASC'; - $sSQL .= ' limit 1'; + $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 .= ' LIMIT 1'; + if (CONST_Debug) var_dump($sSQL); $aPlacNode = chksql( $this->oDB->getRow($sSQL), -- 2.39.5