avoid 'SELECT *'

[nominatim.git] / lib / ReverseGeocode.php

diff --git a/lib/ReverseGeocode.php b/lib/ReverseGeocode.php
index 41da26676f771cbd1750b66a3a790400be469e0d..d06166a5e534f2ce4effe0ca4f7778f5326b5445 100644 (file)
--- a/lib/ReverseGeocode.php
+++ b/lib/ReverseGeocode.php
@@ -96,8 +96,8 @@ class ReverseGeocode
     {
         // searches for polygon in table country_osm_grid which contains the searchpoint
         // and searches for the nearest place node to the searchpoint in this polygon
     {
         // searches for polygon in table country_osm_grid which contains the searchpoint
         // and searches for the nearest place node to the searchpoint in this polygon

-        $sSQL = 'SELECT * FROM country_osm_grid';
-        $sSQL .= ' WHERE ST_CONTAINS (geometry, '.$sPointSQL.' )';
+        $sSQL = 'SELECT country_code FROM country_osm_grid';
+        $sSQL .= ' WHERE ST_CONTAINS (geometry, '.$sPointSQL.') limit 1';

         
         $aPoly = chksql(
             $this->oDB->getRow($sSQL),
         
         $aPoly = chksql(
             $this->oDB->getRow($sSQL),


@@ -106,15 +106,16 @@ class ReverseGeocode
         if ($aPoly) {
             $sCountryCode = $aPoly['country_code'];
             
         if ($aPoly) {
             $sCountryCode = $aPoly['country_code'];
             

-            $sSQL = 'SELECT *, ST_distance('.$sPointSQL.', geometry) as distance';
+            $sSQL = 'SELECT place_id, 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 .= ' FROM placex';
             $sSQL .= ' WHERE osm_type = \'N\'';
             $sSQL .= ' AND country_code = \''.$sCountryCode.'\'';
             $sSQL .= ' AND rank_address > 0';

-            $sSQL .= ' AND rank_address <= ' .$iMaxRank;
+            $sSQL .= ' AND rank_address <= ' .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 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 .= ' LIMIT 1';
             
             $sSQL .= ' ORDER BY distance ASC, rank_address DESC';
             $sSQL .= ' LIMIT 1';
             


@@ -139,8 +140,8 @@ class ReverseGeocode
         // no polygon search over country-level
         if ($iMaxRank < 4) $iMaxRank = 4;
         // search for polygon
         // no polygon search over country-level
         if ($iMaxRank < 4) $iMaxRank = 4;
         // search for polygon

-        $sSQL = 'SELECT * FROM';
-        $sSQL .= '(select place_id,parent_place_id,rank_address, rank_search, country_code, geometry';
+        $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 .= ' FROM placex';
         $sSQL .= ' WHERE ST_GeometryType(geometry) in (\'ST_Polygon\', \'ST_MultiPolygon\')';
         $sSQL .= ' AND rank_address Between 4 AND ' .$iMaxRank;


@@ -180,8 +181,8 @@ class ReverseGeocode
                 } elseif ($iMaxRank <= 25) {
                     $fSearchDiam = 0.1;
                 }
                 } elseif ($iMaxRank <= 25) {
                     $fSearchDiam = 0.1;
                 }

-                
-                $sSQL = 'SELECT *';
+
+                $sSQL = 'SELECT place_id';

                 $sSQL .= ' FROM (';
                 $sSQL .= ' SELECT place_id, rank_address,country_code, geometry,';
                 $sSQL .= ' ST_distance('.$sPointSQL.', geometry) as distance';
                 $sSQL .= ' FROM (';
                 $sSQL .= ' SELECT place_id, rank_address,country_code, geometry,';
                 $sSQL .= ' ST_distance('.$sPointSQL.', geometry) as distance';