remove street and addr_place columns in placex

The information is onyl needed for parenting,
so no need to store it permanently.

diff --git a/lib/PlaceLookup.php b/lib/PlaceLookup.php
index bd49543a5c3b96df6b164877c251cec719e3902a..40d003fa7c593fc81e3aee0c62c258f8613850c4 100644 (file)
--- a/lib/PlaceLookup.php
+++ b/lib/PlaceLookup.php
@@ -103,7 +103,7 @@ class PlaceLookup
         $bIsInterpolation = $sType == 'interpolation';
 
         if ($bIsTiger) {
         $bIsInterpolation = $sType == 'interpolation';
 
         if ($bIsTiger) {

-            $sSQL = "select place_id,partition, 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, housenumber, null as street, postcode,";
+            $sSQL = "select place_id,partition, 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, housenumber, postcode,";

             $sSQL .= " 'us' as country_code, parent_place_id, null as linked_place_id, 30 as rank_address, 30 as rank_search,";
             $sSQL .= " coalesce(null,0.75-(30::float/40)) as importance, null as indexed_status, null as indexed_date, null as wikipedia, 'us' as country_code, ";
             $sSQL .= " get_address_by_language(place_id, housenumber, $sLanguagePrefArraySQL) as langaddress,";
             $sSQL .= " 'us' as country_code, parent_place_id, null as linked_place_id, 30 as rank_address, 30 as rank_search,";
             $sSQL .= " coalesce(null,0.75-(30::float/40)) as importance, null as indexed_status, null as indexed_date, null as wikipedia, 'us' as country_code, ";
             $sSQL .= " get_address_by_language(place_id, housenumber, $sLanguagePrefArraySQL) as langaddress,";


@@ -119,7 +119,7 @@ class PlaceLookup
             $sSQL .= " END as housenumber";
             $sSQL .= " from location_property_tiger where place_id = ".$iPlaceID.") as blub1) as blub2";
         } elseif ($bIsInterpolation) {
             $sSQL .= " END as housenumber";
             $sSQL .= " from location_property_tiger where place_id = ".$iPlaceID.") as blub1) as blub2";
         } elseif ($bIsInterpolation) {

-            $sSQL = "select place_id, partition, 'W' as osm_type, osm_id, 'place' as class, 'house' as type, null admin_level, housenumber, null as street, postcode,";
+            $sSQL = "select place_id, partition, 'W' as osm_type, osm_id, 'place' as class, 'house' as type, null admin_level, housenumber, postcode,";

             $sSQL .= " country_code, parent_place_id, null as linked_place_id, 30 as rank_address, 30 as rank_search,";
             $sSQL .= " (0.75-(30::float/40)) as importance, null as indexed_status, null as indexed_date, null as wikipedia, country_code, ";
             $sSQL .= " get_address_by_language(place_id, housenumber, $sLanguagePrefArraySQL) as langaddress,";
             $sSQL .= " country_code, parent_place_id, null as linked_place_id, 30 as rank_address, 30 as rank_search,";
             $sSQL .= " (0.75-(30::float/40)) as importance, null as indexed_status, null as indexed_date, null as wikipedia, country_code, ";
             $sSQL .= " get_address_by_language(place_id, housenumber, $sLanguagePrefArraySQL) as langaddress,";


@@ -139,7 +139,7 @@ class PlaceLookup
             // and not interpolated
         } else {
             $sSQL = "select placex.place_id, partition, osm_type, osm_id, class,";
             // and not interpolated
         } else {
             $sSQL = "select placex.place_id, partition, osm_type, osm_id, class,";

-            $sSQL .= " type, admin_level, housenumber, street, postcode, country_code,";
+            $sSQL .= " type, admin_level, housenumber, postcode, country_code,";

             $sSQL .= " parent_place_id, linked_place_id, rank_address, rank_search, ";
             $sSQL .= " coalesce(importance,0.75-(rank_search::float/40)) as importance, indexed_status, indexed_date, wikipedia, country_code, ";
             $sSQL .= " get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) as langaddress,";
             $sSQL .= " parent_place_id, linked_place_id, rank_address, rank_search, ";
             $sSQL .= " coalesce(importance,0.75-(rank_search::float/40)) as importance, indexed_status, indexed_date, wikipedia, country_code, ";
             $sSQL .= " get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) as langaddress,";

diff --git a/sql/functions.sql b/sql/functions.sql
index a2b8d9c491c07180f11e91de26a8e9127eaff9e1..1c7603780c61c95723b7114914b978355c9d56eb 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -911,9 +911,9 @@ BEGIN
 
         -- work around bug in postgis, this may have been fixed in 2.0.0 (see http://trac.osgeo.org/postgis/ticket/547)
         update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 
 
         -- work around bug in postgis, this may have been fixed in 2.0.0 (see http://trac.osgeo.org/postgis/ticket/547)
         update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 

-         AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and addr_place is not null));
+         AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and address ? 'place'));

         update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 
         update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 

-         AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and addr_place is not null));
+         AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and address ? 'place'));

       END IF;
     ELSE
       -- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(
       END IF;
     ELSE
       -- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(


@@ -945,7 +945,7 @@ BEGIN
           update location_property_osmline set indexed_status = 2 where indexed_status = 0 and ST_DWithin(location_property_osmline.linegeo, NEW.geometry, diameter);
         ELSEIF NEW.rank_search >= 16 THEN
           -- up to rank 16, street-less addresses may need reparenting
           update location_property_osmline set indexed_status = 2 where indexed_status = 0 and ST_DWithin(location_property_osmline.linegeo, NEW.geometry, diameter);
         ELSEIF NEW.rank_search >= 16 THEN
           -- up to rank 16, street-less addresses may need reparenting

-          update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null or addr_place is not null);
+          update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null or address ? 'place');

         ELSE
           -- for all other places the search terms may change as well
           update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null);
         ELSE
           -- for all other places the search terms may change as well
           update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null);


@@ -1122,7 +1122,10 @@ DECLARE
   address_street_word_id INTEGER;
   address_street_word_ids INTEGER[];
   parent_place_id_rank BIGINT;
   address_street_word_id INTEGER;
   address_street_word_ids INTEGER[];
   parent_place_id_rank BIGINT;

-  
+
+  addr_street TEXT;
+  addr_place TEXT;
+

   isin TEXT[];
   isin_tokens INT[];
 
   isin TEXT[];
   isin_tokens INT[];
 


@@ -1192,8 +1195,9 @@ BEGIN
         i := getorcreate_housenumber_id(make_standard_name(NEW.housenumber));
       END IF;
 
         i := getorcreate_housenumber_id(make_standard_name(NEW.housenumber));
       END IF;
 

-      NEW.street = NEW.address->'street';
-      NEW.addr_place = NEW.address->'place';
+      addr_street = NEW.address->'street';
+      addr_place = NEW.address->'place';
+

       NEW.postcode = NEW.address->'postcode';
   END IF;
 
       NEW.postcode = NEW.address->'postcode';
   END IF;
 


@@ -1289,7 +1293,7 @@ BEGIN
 
     -- if we have a POI and there is no address information,
     -- see if we can get it from a surrounding building
 
     -- if we have a POI and there is no address information,
     -- see if we can get it from a surrounding building

-    IF NEW.osm_type = 'N' AND NEW.street IS NULL AND NEW.addr_place IS NULL
+    IF NEW.osm_type = 'N' AND addr_street IS NULL AND addr_place IS NULL

        AND NEW.housenumber IS NULL THEN
       FOR location IN select * from placex where ST_Covers(geometry, place_centroid)
             and address is not null
        AND NEW.housenumber IS NULL THEN
       FOR location IN select * from placex where ST_Covers(geometry, place_centroid)
             and address is not null


@@ -1298,8 +1302,8 @@ BEGIN
             limit 1
       LOOP
         NEW.housenumber := location.address->'housenumber';
             limit 1
       LOOP
         NEW.housenumber := location.address->'housenumber';

-        NEW.street := location.address->'street';
-        NEW.addr_place := location.address->'place';
+        addr_street := location.address->'street';
+        addr_place := location.address->'place';

       END LOOP;
     END IF;
 
       END LOOP;
     END IF;
 


@@ -1324,8 +1328,8 @@ BEGIN
 
 
     -- Note that addr:street links can only be indexed once the street itself is indexed
 
 
     -- Note that addr:street links can only be indexed once the street itself is indexed

-    IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN
-      address_street_word_ids := get_name_ids(make_standard_name(NEW.street));
+    IF NEW.parent_place_id IS NULL AND addr_street IS NOT NULL THEN
+      address_street_word_ids := get_name_ids(make_standard_name(addr_street));

       IF address_street_word_ids IS NOT NULL THEN
         FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
             NEW.parent_place_id := location.place_id;
       IF address_street_word_ids IS NOT NULL THEN
         FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
             NEW.parent_place_id := location.place_id;


@@ -1333,8 +1337,8 @@ BEGIN
       END IF;
     END IF;
 
       END IF;
     END IF;
 

-    IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN
-      address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place));
+    IF NEW.parent_place_id IS NULL AND addr_place IS NOT NULL THEN
+      address_street_word_ids := get_name_ids(make_standard_name(addr_place));

       IF address_street_word_ids IS NOT NULL THEN
         FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
           NEW.parent_place_id := location.place_id;
       IF address_street_word_ids IS NOT NULL THEN
         FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
           NEW.parent_place_id := location.place_id;


@@ -1356,7 +1360,7 @@ BEGIN
     -- Is this node part of a way?
     IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
 
     -- Is this node part of a way?
     IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
 

-      FOR location IN select p.place_id, p.osm_id, p.parent_place_id, p.rank_search, p.street, p.addr_place from placex p, planet_osm_ways w
+      FOR location IN select p.place_id, p.osm_id, p.parent_place_id, p.rank_search, p.address from placex p, planet_osm_ways w

          where p.osm_type = 'W' and p.rank_search >= 26 and p.geometry && NEW.geometry and w.id = p.osm_id and NEW.osm_id = any(w.nodes) 
       LOOP
 
          where p.osm_type = 'W' and p.rank_search >= 26 and p.geometry && NEW.geometry and w.id = p.osm_id and NEW.osm_id = any(w.nodes) 
       LOOP
 


@@ -1367,8 +1371,8 @@ BEGIN
         END IF;
 
         -- If the way mentions a street or place address, try that for parenting.
         END IF;
 
         -- If the way mentions a street or place address, try that for parenting.

-        IF NEW.parent_place_id IS NULL AND location.street IS NOT NULL THEN
-          address_street_word_ids := get_name_ids(make_standard_name(location.street));
+        IF NEW.parent_place_id IS NULL AND location.address ? 'street' THEN
+          address_street_word_ids := get_name_ids(make_standard_name(location.address->'street'));

           IF address_street_word_ids IS NOT NULL THEN
             FOR linkedplacex IN SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
                 NEW.parent_place_id := linkedplacex.place_id;
           IF address_street_word_ids IS NOT NULL THEN
             FOR linkedplacex IN SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
                 NEW.parent_place_id := linkedplacex.place_id;


@@ -1376,8 +1380,8 @@ BEGIN
           END IF;
         END IF;
 
           END IF;
         END IF;
 

-        IF NEW.parent_place_id IS NULL AND location.addr_place IS NOT NULL THEN
-          address_street_word_ids := get_name_ids(make_standard_name(location.addr_place));
+        IF NEW.parent_place_id IS NULL AND location.address ? 'place' THEN
+          address_street_word_ids := get_name_ids(make_standard_name(location.address->'place'));

           IF address_street_word_ids IS NOT NULL THEN
             FOR linkedplacex IN SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
               NEW.parent_place_id := linkedplacex.place_id;
           IF address_street_word_ids IS NOT NULL THEN
             FOR linkedplacex IN SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
               NEW.parent_place_id := linkedplacex.place_id;


@@ -2823,9 +2827,9 @@ BEGIN
     IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
       FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
         update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) 
     IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
       FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
         update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) 

-        AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and addr_place is not null));
+        AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address > 'place'));

         update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) 
         update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) 

-        AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and addr_place is not null));
+        AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place'));

       END LOOP;
     ELSE
         diameter := 0;
       END LOOP;
     ELSE
         diameter := 0;


@@ -2850,7 +2854,7 @@ BEGIN
             update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter);
           ELSEIF rank >= 16 THEN
             -- up to rank 16, street-less addresses may need reparenting
             update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter);
           ELSEIF rank >= 16 THEN
             -- up to rank 16, street-less addresses may need reparenting

-            update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null or addr_place is not null);
+            update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null or address ? 'place');

           ELSE
             -- for all other places the search terms may change as well
             update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null);
           ELSE
             -- for all other places the search terms may change as well
             update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null);

diff --git a/sql/tables.sql b/sql/tables.sql
index 7584cdc8ebd0bb94b42a1429e84e883d82d3d77c..8be7c39eb9198f7440dd65814deb621f05c390c3 100644 (file)
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -144,8 +144,6 @@ CREATE TABLE placex (
   wikipedia TEXT, -- calculated wikipedia article name (language:title)
   geometry_sector INTEGER,
   country_code varchar(2),
   wikipedia TEXT, -- calculated wikipedia article name (language:title)
   geometry_sector INTEGER,
   country_code varchar(2),

-  street TEXT,
-  addr_place TEXT,

   housenumber TEXT,
   postcode TEXT
   ) {ts:search-data};
   housenumber TEXT,
   postcode TEXT
   ) {ts:search-data};

diff --git a/test/bdd/db/import/parenting.feature b/test/bdd/db/import/parenting.feature
index 2650f6f06aa3313e57a57b025af8cec872a4324a..401a58f14676dd08425bb6395c8b4ae42b6fa582 100644 (file)
--- a/test/bdd/db/import/parenting.feature
+++ b/test/bdd/db/import/parenting.feature
@@ -325,11 +325,11 @@ Feature: Parenting of objects
          | W3  | highway  | residential | foo  | :w-NS |
         When importing
         Then placex contains
          | W3  | highway  | residential | foo  | :w-NS |
         When importing
         Then placex contains

-         | object | parent_place_id | street | addr_place | housenumber |
-         | W1     | W3              | foo    | nowhere    | 3 |
-         | N1     | W3              | foo    | nowhere    | 3 |
-         | N2     | W3              | foo    | nowhere    | 3 |
-         | N3     | W3              | foo    | nowhere    | 3 |
+         | object | parent_place_id | housenumber |
+         | W1     | W3              | 3 |
+         | N1     | W3              | 3 |
+         | N2     | W3              | 3 |
+         | N3     | W3              | 3 |

 
     Scenario: POIs don't inherit from streets
         Given the scene building-on-street-corner
 
     Scenario: POIs don't inherit from streets
         Given the scene building-on-street-corner


@@ -344,8 +344,8 @@ Feature: Parenting of objects
          | W3  | highway  | residential | foo  | :w-NS |
         When importing
         Then placex contains
          | W3  | highway  | residential | foo  | :w-NS |
         When importing
         Then placex contains

-         | object | parent_place_id | street | addr_place | housenumber |
-         | N1     | W3              | None   | None       | None |
+         | object | parent_place_id | housenumber |
+         | N1     | W3              | None |

 
     Scenario: POIs with own address do not inherit building address
         Given the scene building-on-street-corner
 
     Scenario: POIs with own address do not inherit building address
         Given the scene building-on-street-corner


@@ -370,11 +370,11 @@ Feature: Parenting of objects
          | W3  | highway  | residential | foo  | :w-NS |
         When importing
         Then placex contains
          | W3  | highway  | residential | foo  | :w-NS |
         When importing
         Then placex contains

-         | object | parent_place_id | street | addr_place | housenumber |
-         | W1     | N4              | None   | theplace   | 3 |
-         | N1     | W2              | bar    | None       | None |
-         | N2     | W3              | None   | None       | 4 |
-         | N3     | W2              | None   | nowhere    | None |
+         | object | parent_place_id | housenumber |
+         | W1     | N4              | 3 |
+         | N1     | W2              | None |
+         | N2     | W3              | 4 |
+         | N3     | W2              | None |

 
     Scenario: POIs parent a road if they are attached to it
         Given the scene points-on-roads
 
     Scenario: POIs parent a road if they are attached to it
         Given the scene points-on-roads

diff --git a/website/details.php b/website/details.php
index aa72f69f00c33ad6037a3efcbe774b988049728c..7f0a951faa0e30f080fbff00aadffaa5e1ade0a0 100755 (executable)
--- a/website/details.php
+++ b/website/details.php
@@ -83,7 +83,7 @@ $hLog = logStart($oDB, 'details', $_SERVER['QUERY_STRING'], $aLangPrefOrder);
 
 // Get the details for this point
 $sSQL = "SELECT place_id, osm_type, osm_id, class, type, name, admin_level,";
 
 // Get the details for this point
 $sSQL = "SELECT place_id, osm_type, osm_id, class, type, name, admin_level,";

-$sSQL .= "    housenumber, street, postcode, country_code,";
+$sSQL .= "    housenumber, postcode, country_code,";

 $sSQL .= "    importance, wikipedia,";
 $sSQL .= "    to_char(indexed_date, 'YYYY-MM-DD HH24:MI') AS indexed_date,";
 $sSQL .= "    parent_place_id, ";
 $sSQL .= "    importance, wikipedia,";
 $sSQL .= "    to_char(indexed_date, 'YYYY-MM-DD HH24:MI') AS indexed_date,";
 $sSQL .= "    parent_place_id, ";