X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/1e30e578e0b8e51d64a9409086cb8b1a1d43643b..8498593a634e3d32b3b9417907c31bc95b6122f4:/sql/functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index f4be01e2..2606cdca 100644
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -101,7 +101,7 @@ END;
 $$
 LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION getorcreate_amenity(lookup_word TEXT, lookup_class text, lookup_type text)
+CREATE OR REPLACE FUNCTION getorcreate_amenity(lookup_word TEXT, normalized_word TEXT, lookup_class text, lookup_type text)
   RETURNS INTEGER
   AS $$
 DECLARE
@@ -109,17 +109,17 @@ DECLARE
   return_word_id INTEGER;
 BEGIN
   lookup_token := ' '||trim(lookup_word);
-  SELECT min(word_id) FROM word WHERE word_token = lookup_token and class=lookup_class and type = lookup_type into return_word_id;
+  SELECT min(word_id) FROM word WHERE word_token = lookup_token and word=normalized_word and class=lookup_class and type = lookup_type into return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');
-    INSERT INTO word VALUES (return_word_id, lookup_token, null, lookup_class, lookup_type, null, 0);
+    INSERT INTO word VALUES (return_word_id, lookup_token, normalized_word, lookup_class, lookup_type, null, 0);
   END IF;
   RETURN return_word_id;
 END;
 $$
 LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION getorcreate_amenityoperator(lookup_word TEXT, lookup_class text, lookup_type text, op text)
+CREATE OR REPLACE FUNCTION getorcreate_amenityoperator(lookup_word TEXT, normalized_word TEXT, lookup_class text, lookup_type text, op text)
   RETURNS INTEGER
   AS $$
 DECLARE
@@ -127,10 +127,10 @@ DECLARE
   return_word_id INTEGER;
 BEGIN
   lookup_token := ' '||trim(lookup_word);
-  SELECT min(word_id) FROM word WHERE word_token = lookup_token and class=lookup_class and type = lookup_type and operator = op into return_word_id;
+  SELECT min(word_id) FROM word WHERE word_token = lookup_token and word=normalized_word and class=lookup_class and type = lookup_type and operator = op into return_word_id;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');
-    INSERT INTO word VALUES (return_word_id, lookup_token, null, lookup_class, lookup_type, null, 0, op);
+    INSERT INTO word VALUES (return_word_id, lookup_token, normalized_word, lookup_class, lookup_type, null, 0, op);
   END IF;
   RETURN return_word_id;
 END;
@@ -1140,6 +1140,8 @@ DECLARE
   nameaddress_vector INTEGER[];
 
   linked_node_id BIGINT;
+  linked_importance FLOAT;
+  linked_wikipedia TEXT;
 
   result BOOLEAN;
 BEGIN
@@ -1246,7 +1248,8 @@ BEGIN
   END IF;
 
   -- Adding ourselves to the list simplifies address calculations later
-  INSERT INTO place_addressline VALUES (NEW.place_id, NEW.place_id, true, true, 0, NEW.rank_address); 
+  INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address)
+    VALUES (NEW.place_id, NEW.place_id, true, true, 0, NEW.rank_address); 
 
   -- What level are we searching from
   search_maxrank := NEW.rank_search;
@@ -1510,6 +1513,7 @@ BEGIN
 
           -- keep a note of the node id in case we need it for wikipedia in a bit
           linked_node_id := linkedPlacex.osm_id;
+          select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance;
         END LOOP;
 
       END LOOP;
@@ -1545,6 +1549,7 @@ BEGIN
 
               -- keep a note of the node id in case we need it for wikipedia in a bit
               linked_node_id := linkedPlacex.osm_id;
+              select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance;
             END IF;
 
           END LOOP;
@@ -1587,6 +1592,7 @@ BEGIN
 
         -- keep a note of the node id in case we need it for wikipedia in a bit
         linked_node_id := linkedPlacex.osm_id;
+        select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance;
       END LOOP;
     END IF;
 
@@ -1607,10 +1613,12 @@ BEGIN
       END IF;
     END IF;
 
-    -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance
-    IF NEW.importance is null THEN
-      select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.country_code) INTO NEW.wikipedia,NEW.importance;
+    -- Use the maximum importance if a one could be computed from the linked object.
+    IF linked_importance is not null AND
+        (NEW.importance is null or NEW.importance < linked_importance) THEN
+        NEW.importance = linked_importance;
     END IF;
+
     -- Still null? how about looking it up by the node id
     IF NEW.importance IS NULL THEN
       select language||':'||title,importance from wikipedia_article where osm_type = 'N'::char(1) and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
@@ -1734,7 +1742,8 @@ BEGIN
       IF location.rank_search > 4 THEN
           nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
       END IF;
-      INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address);
+      INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address)
+        VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address);
 
       IF location_isaddress THEN
 
@@ -1768,7 +1777,8 @@ BEGIN
 
           IF location.rank_search > 4 THEN
               nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
-              INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
+              INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address)
+                VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
               address_havelevel[location.rank_address] := true;
 
               IF location.rank_address > parent_place_id_rank THEN
@@ -1800,7 +1810,8 @@ BEGIN
 
         -- Add it to the list of search terms
         nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
-        INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, false, location.distance, location.rank_address); 
+        INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address)
+          VALUES (NEW.place_id, location.place_id, true, false, location.distance, location.rank_address); 
 
       END IF;
 
@@ -1912,7 +1923,7 @@ BEGIN
   IF st_area(OLD.geometry) > 2 and st_isvalid(OLD.geometry) THEN
     SELECT bool_or(not (rank_address = 0 or rank_address > 26)) as ranked FROM placex WHERE osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type INTO has_rank;
     IF has_rank THEN
-      insert into import_polygon_delete values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type);
+      insert into import_polygon_delete (osm_type, osm_id, class, type) values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type);
       RETURN NULL;
     END IF;
   END IF;
@@ -1948,9 +1959,8 @@ BEGIN
   --DEBUG: RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry);
   -- filter wrong tupels
   IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN  
-    INSERT INTO import_polygon_error values (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type,
-                                             NEW.name, NEW.address->'country',
-      now(), ST_IsValidReason(NEW.geometry), null, NEW.geometry);
+    INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name, country_code, updated, errormessage, prevgeometry, newgeometry)
+      VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.address->'country', now(), ST_IsValidReason(NEW.geometry), null, NEW.geometry);
 --    RAISE WARNING 'Invalid Geometry: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
     RETURN null;
   END IF;
@@ -2039,7 +2049,8 @@ BEGIN
       AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
       AND st_area(NEW.geometry) < st_area(existing.geometry)*0.5
       THEN
-      INSERT INTO import_polygon_error values (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.country_code, now(), 
+      INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name, country_code, updated, errormessage, prevgeometry, newgeometry)
+        VALUES (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.address->'country', now(), 
         'Area reduced from '||st_area(existing.geometry)||' to '||st_area(NEW.geometry), existing.geometry, NEW.geometry);
       RETURN null;
     END IF;
@@ -2370,7 +2381,7 @@ BEGIN
       CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
       CASE WHEN extratags ? 'place' THEN 'place' ELSE class END as class,
       CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as type,
-      admin_level, fromarea, isaddress,
+      admin_level, fromarea, isaddress and linked_place_id is NULL as isaddress,
       CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
       distance,country_code,postcode
       from place_addressline join placex on (address_place_id = placex.place_id) 
