Merge pull request #550 from lonvia/decrease-line-length

[nominatim.git] / lib / Geocode.php

diff --git a/lib/Geocode.php b/lib/Geocode.php
index 7fa349cacefdfddf5d1fb2fed9932e317a1b9327..d6ff6aadfbcd9ea7bcdbe25a6a6c9f8056e82c85 100644 (file)
--- a/lib/Geocode.php
+++ b/lib/Geocode.php
@@ -399,7 +399,10 @@ class Geocode
         if ($this->bIncludeNameDetails) $sSQL .= "hstore_to_json(name)::text as names,";
         $sSQL .= "avg(ST_X(centroid)) as lon,avg(ST_Y(centroid)) as lat, ";
         $sSQL .= $sImportanceSQL."coalesce(importance,0.75-(rank_search::float/40)) as importance, ";
         if ($this->bIncludeNameDetails) $sSQL .= "hstore_to_json(name)::text as names,";
         $sSQL .= "avg(ST_X(centroid)) as lon,avg(ST_Y(centroid)) as lat, ";
         $sSQL .= $sImportanceSQL."coalesce(importance,0.75-(rank_search::float/40)) as importance, ";

-        $sSQL .= "(select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(CASE WHEN placex.rank_search < 28 THEN placex.place_id ELSE placex.parent_place_id END) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance, ";
+        $sSQL .= "(select max(p.importance*(p.rank_address+2))";
+        $sSQL .= "   from place_addressline s, placex p";
+        $sSQL .= "   where s.place_id = min(CASE WHEN placex.rank_search < 28 THEN placex.place_id ELSE placex.parent_place_id END)";
+        $sSQL .= "   and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance, ";

         $sSQL .= "(extratags->'place') as extra_place ";
         $sSQL .= "from placex where place_id in ($sPlaceIDs) ";
         $sSQL .= "and (placex.rank_address between $this->iMinAddressRank and $this->iMaxAddressRank ";
         $sSQL .= "(extratags->'place') as extra_place ";
         $sSQL .= "from placex where place_id in ($sPlaceIDs) ";
         $sSQL .= "and (placex.rank_address between $this->iMinAddressRank and $this->iMaxAddressRank ";


@@ -431,15 +434,21 @@ class Geocode
             if (CONST_Use_US_Tiger_Data) {
                 // Tiger search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join)
                 $sSQL .= " union";
             if (CONST_Use_US_Tiger_Data) {
                 // Tiger search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join)
                 $sSQL .= " union";

-                $sSQL .= " select 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 30 as rank_search, 30 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code";
-                $sSQL .= ", get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) as langaddress ";
-                $sSQL .= ", null as placename";
-                $sSQL .= ", null as ref";
+                $sSQL .= " select 'T' as osm_type, place_id as osm_id, 'place' as class,";
+                $sSQL .= " 'house' as type, null as admin_level, 30 as rank_search,";
+                $sSQL .= " 30 as rank_address, min(place_id) as place_id,";
+                $sSQL .= " min(parent_place_id) as parent_place_id, 'us' as country_code,";
+                $sSQL .= " get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) as langaddress,";
+                $sSQL .= " null as placename, null as ref";

                 if ($this->bIncludeExtraTags) $sSQL .= ", null as extra";
                 if ($this->bIncludeNameDetails) $sSQL .= ", null as names";
                 $sSQL .= ", avg(st_x(centroid)) as lon, avg(st_y(centroid)) as lat,";
                 $sSQL .= $sImportanceSQL."-1.15 as importance ";
                 if ($this->bIncludeExtraTags) $sSQL .= ", null as extra";
                 if ($this->bIncludeNameDetails) $sSQL .= ", null as names";
                 $sSQL .= ", avg(st_x(centroid)) as lon, avg(st_y(centroid)) as lat,";
                 $sSQL .= $sImportanceSQL."-1.15 as importance ";

-                $sSQL .= ", (select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(blub.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance ";
+                $sSQL .= ", (select max(p.importance*(p.rank_address+2))";
+                $sSQL .= "   from place_addressline s, placex p";
+                $sSQL .= "   where s.place_id = min(blub.parent_place_id)";
+                $sSQL .= "   and p.place_id = s.address_place_id and s.isaddress";
+                $sSQL .= "   and p.importance is not null) as addressimportance ";

                 $sSQL .= ", null as extra_place ";
                 $sSQL .= " from (select place_id";
                 // interpolate the Tiger housenumbers here
                 $sSQL .= ", null as extra_place ";
                 $sSQL .= " from (select place_id";
                 // interpolate the Tiger housenumbers here


@@ -453,7 +462,10 @@ class Geocode
             // osmline
             // interpolation line search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join)
             $sSQL .= " union ";
             // osmline
             // interpolation line search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join)
             $sSQL .= " union ";

-            $sSQL .= "select 'W' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 30 as rank_search, 30 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, calculated_country_code as country_code, ";
+            $sSQL .= "select 'W' as osm_type, place_id as osm_id, 'place' as class,";
+            $sSQL .= " 'house' as type, null as admin_level, 30 as rank_search,";
+            $sSQL .= " 30 as rank_address, min(place_id) as place_id,";
+            $sSQL .= " min(parent_place_id) as parent_place_id, calculated_country_code as country_code, ";

             $sSQL .= "get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) as langaddress, ";
             $sSQL .= "null as placename, ";
             $sSQL .= "null as ref, ";
             $sSQL .= "get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) as langaddress, ";
             $sSQL .= "null as placename, ";
             $sSQL .= "null as ref, ";


@@ -477,7 +489,10 @@ class Geocode
 
             if (CONST_Use_Aux_Location_data) {
                 $sSQL .= " union ";
 
             if (CONST_Use_Aux_Location_data) {
                 $sSQL .= " union ";

-                $sSQL .= "select 'L' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 0 as rank_search, 0 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code, ";
+                $sSQL .= "select 'L' as osm_type, place_id as osm_id, 'place' as class,";
+                $sSQL .= " 'house' as type, null as admin_level, 0 as rank_search,";
+                $sSQL .= " 0 as rank_address, min(place_id) as place_id,";
+                $sSQL .= " min(parent_place_id) as parent_place_id, 'us' as country_code, ";

                 $sSQL .= "get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) as langaddress, ";
                 $sSQL .= "null as placename, ";
                 $sSQL .= "null as ref, ";
                 $sSQL .= "get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) as langaddress, ";
                 $sSQL .= "null as placename, ";
                 $sSQL .= "null as ref, ";


@@ -485,7 +500,11 @@ class Geocode
                 if ($this->bIncludeNameDetails) $sSQL .= "null as names, ";
                 $sSQL .= "avg(ST_X(centroid)) as lon, avg(ST_Y(centroid)) as lat, ";
                 $sSQL .= $sImportanceSQL."-1.10 as importance, ";
                 if ($this->bIncludeNameDetails) $sSQL .= "null as names, ";
                 $sSQL .= "avg(ST_X(centroid)) as lon, avg(ST_Y(centroid)) as lat, ";
                 $sSQL .= $sImportanceSQL."-1.10 as importance, ";

-                $sSQL .= "(select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(location_property_aux.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance, ";
+                $sSQL .= "(select max(p.importance*(p.rank_address+2))";
+                $sSQL .= " from place_addressline s, placex p";
+                $sSQL .= " where s.place_id = min(location_property_aux.parent_place_id)";
+                $sSQL .= " and p.place_id = s.address_place_id and s.isaddress";
+                $sSQL .= " and p.importance is not null) as addressimportance, ";

                 $sSQL .= "null as extra_place ";
                 $sSQL .= "from location_property_aux where place_id in ($sPlaceIDs) ";
                 $sSQL .= "and 30 between $this->iMinAddressRank and $this->iMaxAddressRank ";
                 $sSQL .= "null as extra_place ";
                 $sSQL .= "from location_property_aux where place_id in ($sPlaceIDs) ";
                 $sSQL .= "and 30 between $this->iMinAddressRank and $this->iMaxAddressRank ";


@@ -1275,18 +1294,23 @@ class Geocode
                             $sSQL .= " limit $this->iLimit";
                             if (CONST_Debug) var_dump($sSQL);
                             $aPlaceIDs = chksql($this->oDB->getCol($sSQL));
                             $sSQL .= " limit $this->iLimit";
                             if (CONST_Debug) var_dump($sSQL);
                             $aPlaceIDs = chksql($this->oDB->getCol($sSQL));

-                            
+

                             // if nothing found, search in the interpolation line table
                             if (!sizeof($aPlaceIDs)) {
                                 // do we need to use transliteration and the regex for housenumbers???
                                 //new query for lines, not housenumbers anymore
                             // if nothing found, search in the interpolation line table
                             if (!sizeof($aPlaceIDs)) {
                                 // do we need to use transliteration and the regex for housenumbers???
                                 //new query for lines, not housenumbers anymore

+                                $sSQL = "select distinct place_id from location_property_osmline";
+                                $sSQL .= " where parent_place_id in (".$sPlaceIDs.") and (";

                                 if ($searchedHousenumber%2 == 0) {
                                     //if housenumber is even, look for housenumber in streets with interpolationtype even or all
                                 if ($searchedHousenumber%2 == 0) {
                                     //if housenumber is even, look for housenumber in streets with interpolationtype even or all

-                                    $sSQL = "select distinct place_id from location_property_osmline where parent_place_id in (".$sPlaceIDs.") and (interpolationtype='even' or interpolationtype='all') and ".$searchedHousenumber.">=startnumber and ".$searchedHousenumber."<=endnumber";
+                                    $sSQL .= "interpolationtype='even'";

                                 } else {
                                     //look for housenumber in streets with interpolationtype odd or all
                                 } else {
                                     //look for housenumber in streets with interpolationtype odd or all

-                                    $sSQL = "select distinct place_id from location_property_osmline where parent_place_id in (".$sPlaceIDs.") and (interpolationtype='odd' or interpolationtype='all') and ".$searchedHousenumber.">=startnumber and ".$searchedHousenumber."<=endnumber";
+                                    $sSQL .= "interpolationtype='odd'";

                                 }
                                 }

+                                $sSQL .= " or interpolationtype='all') and ";
+                                $sSQL .= $searchedHousenumber.">=startnumber and ";
+                                $sSQL .= $searchedHousenumber."<=endnumber";

 
                                 if (sizeof($this->aExcludePlaceIDs)) {
                                     $sSQL .= " and place_id not in (".join(',', $this->aExcludePlaceIDs).")";
 
                                 if (sizeof($this->aExcludePlaceIDs)) {
                                     $sSQL .= " and place_id not in (".join(',', $this->aExcludePlaceIDs).")";


@@ -1296,7 +1320,7 @@ class Geocode
                                 //get place IDs
                                 $aPlaceIDs = chksql($this->oDB->getCol($sSQL, 0));
                             }
                                 //get place IDs
                                 $aPlaceIDs = chksql($this->oDB->getCol($sSQL, 0));
                             }

-                                
+

                             // If nothing found try the aux fallback table
                             if (CONST_Use_Aux_Location_data && !sizeof($aPlaceIDs)) {
                                 $sSQL = "select place_id from location_property_aux where parent_place_id in (".$sPlaceIDs.") and housenumber = '".pg_escape_string($aSearch['sHouseNumber'])."'";
                             // If nothing found try the aux fallback table
                             if (CONST_Use_Aux_Location_data && !sizeof($aPlaceIDs)) {
                                 $sSQL = "select place_id from location_property_aux where parent_place_id in (".$sPlaceIDs.") and housenumber = '".pg_escape_string($aSearch['sHouseNumber'])."'";


@@ -1310,14 +1334,16 @@ class Geocode
 
                             //if nothing was found in placex or location_property_aux, then search in Tiger data for this housenumber(location_property_tiger)
                             if (CONST_Use_US_Tiger_Data && !sizeof($aPlaceIDs)) {
 
                             //if nothing was found in placex or location_property_aux, then search in Tiger data for this housenumber(location_property_tiger)
                             if (CONST_Use_US_Tiger_Data && !sizeof($aPlaceIDs)) {

-                                //new query for lines, not housenumbers anymore
+                                $sSQL = "select distinct place_id from location_property_tiger";
+                                $sSQL .= " where parent_place_id in (".$sPlaceIDs.") and (";

                                 if ($searchedHousenumber%2 == 0) {
                                 if ($searchedHousenumber%2 == 0) {

-                                    //if housenumber is even, look for housenumber in streets with interpolationtype even or all
-                                    $sSQL = "select distinct place_id from location_property_tiger where parent_place_id in (".$sPlaceIDs.") and (interpolationtype='even' or interpolationtype='all') and ".$searchedHousenumber.">=startnumber and ".$searchedHousenumber."<=endnumber";
+                                    $sSQL .= "interpolationtype='even'";

                                 } else {
                                 } else {

-                                    //look for housenumber in streets with interpolationtype odd or all
-                                    $sSQL = "select distinct place_id from location_property_tiger where parent_place_id in (".$sPlaceIDs.") and (interpolationtype='odd' or interpolationtype='all') and ".$searchedHousenumber.">=startnumber and ".$searchedHousenumber."<=endnumber";
+                                    $sSQL .= "interpolationtype='odd'";

                                 }
                                 }

+                                $sSQL .= " or interpolationtype='all') and ";
+                                $sSQL .= $searchedHousenumber.">=startnumber and ";
+                                $sSQL .= $searchedHousenumber."<=endnumber";

 
                                 if (sizeof($this->aExcludePlaceIDs)) {
                                     $sSQL .= " and place_id not in (".join(',', $this->aExcludePlaceIDs).")";
 
                                 if (sizeof($this->aExcludePlaceIDs)) {
                                     $sSQL .= " and place_id not in (".join(',', $this->aExcludePlaceIDs).")";


@@ -1365,7 +1391,11 @@ class Geocode
                                 $sPlaceGeom = false;
                                 if ($this->iMaxRank < 9 && $bCacheTable) {
                                     // Try and get a polygon to search in instead
                                 $sPlaceGeom = false;
                                 if ($this->iMaxRank < 9 && $bCacheTable) {
                                     // Try and get a polygon to search in instead

-                                    $sSQL = "select geometry from placex where place_id in ($sPlaceIDs) and rank_search < $this->iMaxRank + 5 and st_geometrytype(geometry) in ('ST_Polygon','ST_MultiPolygon') order by rank_search asc limit 1";
+                                    $sSQL = "select geometry from placex";
+                                    $sSQL .= " where place_id in ($sPlaceIDs)";
+                                    $sSQL .= " and rank_search < $this->iMaxRank + 5";
+                                    $sSQL .= " and st_geometrytype(geometry) in ('ST_Polygon','ST_MultiPolygon')";
+                                    $sSQL .= " order by rank_search asc limit 1";

                                     if (CONST_Debug) var_dump($sSQL);
                                     $sPlaceGeom = chksql($this->oDB->getOne($sSQL));
                                 }
                                     if (CONST_Debug) var_dump($sSQL);
                                     $sPlaceGeom = chksql($this->oDB->getOne($sSQL));
                                 }