give www-data select rights on query_log

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 035eaf901f2478d20d0ef395a3b7b0bd2461b966..4d05d79b8111454d18ef7fb35df9f189df201d51 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -83,18 +83,24 @@ END;
 $$
 LANGUAGE 'plpgsql' IMMUTABLE;
 
+-- returns NULL if the word is too common
 CREATE OR REPLACE FUNCTION getorcreate_word_id(lookup_word TEXT) 
   RETURNS INTEGER
   AS $$
 DECLARE
   lookup_token TEXT;
   return_word_id INTEGER;
+  count INTEGER;
 BEGIN
   lookup_token := trim(lookup_word);
-  SELECT min(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id;
+  SELECT min(word_id), max(search_name_count) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id, count;
   IF return_word_id IS NULL THEN
     return_word_id := nextval('seq_word');
     INSERT INTO word VALUES (return_word_id, lookup_token, regexp_replace(lookup_token,E'([^0-9])\\1+',E'\\1','g'), null, null, null, null, 0, null);
+  ELSE
+    IF count > get_maxwordfreq() THEN
+      return_word_id := NULL;
+    END IF;
   END IF;
   RETURN return_word_id;
 END;
@@ -290,6 +296,34 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
+CREATE OR REPLACE FUNCTION create_country(src HSTORE, lookup_country_code varchar(2)) RETURNS VOID
+  AS $$
+DECLARE
+  s TEXT;
+  w INTEGER;
+  words TEXT[];
+  item RECORD;
+  j INTEGER;
+BEGIN
+  FOR item IN SELECT (each(src)).* LOOP
+
+    s := make_standard_name(item.value);
+    w := getorcreate_country(s, lookup_country_code);
+
+    words := regexp_split_to_array(item.value, E'[,;()]');
+    IF array_upper(words, 1) != 1 THEN
+      FOR j IN 1..array_upper(words, 1) LOOP
+        s := make_standard_name(words[j]);
+        IF s != '' THEN
+          w := getorcreate_country(s, lookup_country_code);
+        END IF;
+      END LOOP;
+    END IF;
+  END LOOP;
+END;
+$$
+LANGUAGE plpgsql;
+
 CREATE OR REPLACE FUNCTION make_keywords(src HSTORE) RETURNS INTEGER[]
   AS $$
 DECLARE
@@ -317,7 +351,7 @@ BEGIN
       FOR j IN 1..array_upper(words, 1) LOOP
         IF (words[j] != '') THEN
           w = getorcreate_word_id(words[j]);
-          IF NOT (ARRAY[w] <@ result) THEN
+          IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
             result := result || w;
           END IF;
         END IF;
@@ -330,7 +364,7 @@ BEGIN
         s := make_standard_name(words[j]);
         IF s != '' THEN
           w := getorcreate_word_id(s);
-          IF NOT (ARRAY[w] <@ result) THEN
+          IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
             result := result || w;
           END IF;
         END IF;
@@ -379,7 +413,7 @@ BEGIN
     FOR j IN 1..array_upper(words, 1) LOOP
       IF (words[j] != '') THEN
         w = getorcreate_word_id(words[j]);
-        IF NOT (ARRAY[w] <@ result) THEN
+        IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
           result := result || w;
         END IF;
       END IF;
@@ -392,7 +426,7 @@ BEGIN
       s := make_standard_name(words[j]);
       IF s != '' THEN
         w := getorcreate_word_id(s);
-        IF NOT (ARRAY[w] <@ result) THEN
+        IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
           result := result || w;
         END IF;
       END IF;
@@ -916,9 +950,9 @@ BEGIN
     default_language := get_country_language_code(NEW.calculated_country_code);
     IF default_language IS NOT NULL THEN
       IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
-        NEW.name := NEW.name || (('name:'||default_language) => (NEW.name -> 'name'));
+        NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
       ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
-        NEW.name := NEW.name || ('name' => (NEW.name -> ('name:'||default_language)));
+        NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
       END IF;
     END IF;
   END IF;
@@ -945,7 +979,7 @@ BEGIN
             RETURN NULL;
         END IF;
 
-        NEW.name := 'ref'=>NEW.postcode;
+        NEW.name := hstore('ref', NEW.postcode);
 
         IF NEW.calculated_country_code = 'gb' THEN
 
@@ -1301,9 +1335,9 @@ BEGIN
       default_language := get_country_language_code(NEW.calculated_country_code);
       IF default_language IS NOT NULL THEN
         IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
-          NEW.name := NEW.name || (('name:'||default_language) => (NEW.name -> 'name'));
+          NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
         ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
-          NEW.name := NEW.name || ('name' => (NEW.name -> ('name:'||default_language)));
+          NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
         END IF;
       END IF;
     END IF;
@@ -1620,6 +1654,11 @@ BEGIN
 
     END IF;
 
+    -- make sure all names are in the word table
+    IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN
+      perform create_country(NEW.name, lower(NEW.country_code));
+    END IF;
+
     NEW.parent_place_id = 0;
     parent_place_id_rank = 0;
 
@@ -1674,7 +1713,9 @@ BEGIN
 
         -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress;
         -- Add it to the list of search terms
-        nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
+        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);
 
         IF location_isaddress THEN
@@ -1705,15 +1746,16 @@ BEGIN
 
   --RAISE WARNING '  ISIN: %',location;
 
-            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);
-            address_havelevel[location.rank_address] := true;
+            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);
+                address_havelevel[location.rank_address] := true;
 
-            IF location.rank_address > parent_place_id_rank THEN
-              NEW.parent_place_id = location.place_id;
-              parent_place_id_rank = location.rank_address;
+                IF location.rank_address > parent_place_id_rank THEN
+                  NEW.parent_place_id = location.place_id;
+                  parent_place_id_rank = location.rank_address;
+                END IF;
             END IF;
-
           END LOOP;
 
         END IF;
@@ -1734,7 +1776,7 @@ BEGIN
           location_distance := location.distance * 1.5;
         END IF;
 
-        IF location.distance < location_distance THEN
+        IF location.rank_search > 4 AND location.distance < location_distance THEN
 
           -- Add it to the list of search terms
           nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
@@ -2271,7 +2313,7 @@ BEGIN
   hadcountry := false;
   FOR location IN 
     select placex.place_id, osm_type, osm_id,
-      CASE WHEN class = 'place' and type = 'postcode' THEN 'name' => postcode ELSE name END as name,
+      CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
       class, type, admin_level, true as fromarea, true as isaddress,
       CASE WHEN rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
       0 as distance, calculated_country_code
@@ -2304,7 +2346,7 @@ BEGIN
 
   FOR location IN 
     select placex.place_id, osm_type, osm_id,
-      CASE WHEN class = 'place' and type = 'postcode' THEN 'name' => postcode ELSE name END as name,
+      CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
       class, type, admin_level, fromarea, 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,calculated_country_code
@@ -2349,7 +2391,7 @@ BEGIN
   END IF;
 
   IF searchcountrycode IS NOT NULL THEN
-    location := ROW(null, null, null, 'ref'=>searchcountrycode, 'place', 'country_code', null, true, false, 4, 0)::addressline;
+    location := ROW(null, null, null, hstore('ref', searchcountrycode), 'place', 'country_code', null, true, false, 4, 0)::addressline;
     RETURN NEXT location;
   END IF;
 
@@ -2360,12 +2402,12 @@ BEGIN
   END IF;
 
   IF searchhousenumber IS NOT NULL THEN
-    location := ROW(in_place_id, null, null, 'ref'=>searchhousenumber, 'place', 'house_number', null, true, true, 28, 0)::addressline;
+    location := ROW(in_place_id, null, null, hstore('ref', searchhousenumber), 'place', 'house_number', null, true, true, 28, 0)::addressline;
     RETURN NEXT location;
   END IF;
 
   IF searchpostcode IS NOT NULL THEN
-    location := ROW(null, null, null, 'ref'=>searchpostcode, 'place', 'postcode', null, true, true, 5, 0)::addressline;
+    location := ROW(null, null, null, hstore('ref', searchpostcode), 'place', 'postcode', null, true, true, 5, 0)::addressline;
     RETURN NEXT location;
   END IF;