Merge remote-tracking branch 'upstream/master'

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index f4be01e2d03bd9543f4bf6b2990657d5ed0f2d96..d55b0d652f5ad24faf42446b1ec13340016821b7 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -101,7 +101,7 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 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
   RETURNS INTEGER
   AS $$
 DECLARE


@@ -109,17 +109,17 @@ DECLARE
   return_word_id INTEGER;
 BEGIN
   lookup_token := ' '||trim(lookup_word);
   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');
   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;
 
   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
   RETURNS INTEGER
   AS $$
 DECLARE


@@ -127,10 +127,10 @@ DECLARE
   return_word_id INTEGER;
 BEGIN
   lookup_token := ' '||trim(lookup_word);
   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');
   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;
   END IF;
   RETURN return_word_id;
 END;


@@ -1140,6 +1140,8 @@ DECLARE
   nameaddress_vector INTEGER[];
 
   linked_node_id BIGINT;
   nameaddress_vector INTEGER[];
 
   linked_node_id BIGINT;

+  linked_importance FLOAT;
+  linked_wikipedia TEXT;

 
   result BOOLEAN;
 BEGIN
 
   result BOOLEAN;
 BEGIN


@@ -1246,7 +1248,8 @@ BEGIN
   END IF;
 
   -- Adding ourselves to the list simplifies address calculations later
   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;
 
   -- What level are we searching from
   search_maxrank := NEW.rank_search;


@@ -1489,7 +1492,8 @@ BEGIN
       FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
 
         FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) 
       FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
 
         FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) 

-          and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP
+          and osm_id = substring(relMember.member,2,10000)::bigint
+          and class = 'place' order by rank_search desc limit 1 LOOP

 
           -- If we don't already have one use this as the centre point of the geometry
           IF NEW.centroid IS NULL THEN
 
           -- If we don't already have one use this as the centre point of the geometry
           IF NEW.centroid IS NULL THEN


@@ -1510,6 +1514,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;
 
           -- 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;
         END LOOP;
 
       END LOOP;


@@ -1519,7 +1524,8 @@ BEGIN
         FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
 
           FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) 
         FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
 
           FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) 

-            and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP
+            and osm_id = substring(relMember.member,2,10000)::bigint
+            and class = 'place' order by rank_search desc limit 1 LOOP

 
             -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
             -- But that can be fixed by explicitly setting the label in the data
 
             -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
             -- But that can be fixed by explicitly setting the label in the data


@@ -1545,6 +1551,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;
 
               -- 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;
             END IF;
 
           END LOOP;


@@ -1587,6 +1594,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;
 
         -- 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;
 
       END LOOP;
     END IF;
 


@@ -1607,10 +1615,12 @@ BEGIN
       END IF;
     END IF;
 
       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;
     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;
     -- 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 +1744,8 @@ BEGIN
       IF location.rank_search > 4 THEN
           nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
       END IF;
       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
 
 
       IF location_isaddress THEN
 


@@ -1768,7 +1779,8 @@ BEGIN
 
           IF location.rank_search > 4 THEN
               nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
 
           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
               address_havelevel[location.rank_address] := true;
 
               IF location.rank_address > parent_place_id_rank THEN


@@ -1800,7 +1812,8 @@ BEGIN
 
         -- Add it to the list of search terms
         nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
 
         -- 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;
 
 
       END IF;
 


@@ -1912,7 +1925,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
   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;
       RETURN NULL;
     END IF;
   END IF;


@@ -1948,9 +1961,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  
   --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;
 --    RAISE WARNING 'Invalid Geometry: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
     RETURN null;
   END IF;


@@ -2039,7 +2051,8 @@ BEGIN
       AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
       AND st_area(NEW.geometry) < st_area(existing.geometry)*0.5
       THEN
       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;
         'Area reduced from '||st_area(existing.geometry)||' to '||st_area(NEW.geometry), existing.geometry, NEW.geometry);
       RETURN null;
     END IF;


@@ -2133,6 +2146,12 @@ BEGIN
           NEW.name := hstore('ref', NEW.address->'postcode');
       END IF;
 
           NEW.name := hstore('ref', NEW.address->'postcode');
       END IF;
 

+      IF NEW.class in ('boundary')
+         AND ST_GeometryType(NEW.geometry) not in ('ST_Polygon','ST_MultiPolygon') THEN
+          DELETE FROM placex where place_id = existingplacex.place_id;
+          RETURN NULL;
+      END IF;
+

       update placex set 
         name = NEW.name,
         address = NEW.address,
       update placex set 
         name = NEW.name,
         address = NEW.address,


@@ -2150,6 +2169,20 @@ BEGIN
           update location_property_osmline p set indexed_status = 2 from planet_osm_ways w where p.linegeo && NEW.geometry and p.osm_id = w.id and NEW.osm_id = any(w.nodes);
       END IF;
 
           update location_property_osmline p set indexed_status = 2 from planet_osm_ways w where p.linegeo && NEW.geometry and p.osm_id = w.id and NEW.osm_id = any(w.nodes);
       END IF;
 

+      -- linked places should get potential new naming and addresses
+      IF existingplacex.linked_place_id is not NULL THEN
+        update placex x set
+          name = p.name,
+          extratags = p.extratags,
+          indexed_status = 2
+        from place p
+        where x.place_id = existingplacex.linked_place_id
+              and x.indexed_status = 0
+              and x.osm_type = p.osm_type
+              and x.osm_id = p.osm_id
+              and x.class = p.class;
+      END IF;
+

     END IF;
 
     -- Abort the add (we modified the existing place instead)
     END IF;
 
     -- Abort the add (we modified the existing place instead)


@@ -2370,7 +2403,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,
       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) 
       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) 


@@ -2387,7 +2420,7 @@ BEGIN
     IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN
       location.isaddress := FALSE;
     END IF;
     IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN
       location.isaddress := FALSE;
     END IF;

-    IF searchpostcode IS NULL and location.isaddress and location.type != 'postcode' and location.postcode IS NOT NULL THEN
+    IF searchpostcode IS NULL and location.isaddress and location.type != 'postcode' and location.postcode IS NOT NULL and location.postcode not similar to '%(,|;)%' THEN

       searchpostcode := location.postcode;
     END IF;
     IF location.rank_address = 4 AND location.isaddress THEN
       searchpostcode := location.postcode;
     END IF;
     IF location.rank_address = 4 AND location.isaddress THEN