add switch for osm2pgsql cache size

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 1baecfc0096f6bea7d1ab52417e7fc7939039040..b61f52b27640ac571edaf846b2de1ff180a48187 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -914,7 +914,7 @@ BEGIN
   NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
 
   -- copy 'name' to or from the default language (if there is a default language)
   NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
 
   -- copy 'name' to or from the default language (if there is a default language)

-  IF NEW.name is not null AND array_upper(%#NEW.name,1) > 1 THEN
+  IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN

     default_language := get_country_language_code(NEW.country_code);
     IF default_language IS NOT NULL THEN
       IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
     default_language := get_country_language_code(NEW.country_code);
     IF default_language IS NOT NULL THEN
       IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN


@@ -1029,7 +1029,7 @@ BEGIN
       ELSEIF NEW.type in ('suburb','croft','subdivision') THEN
         NEW.rank_search := 20;
         NEW.rank_address := NEW.rank_search;
       ELSEIF NEW.type in ('suburb','croft','subdivision') THEN
         NEW.rank_search := 20;
         NEW.rank_address := NEW.rank_search;

-      ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling') THEN
+      ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling','mountain_pass') THEN

         NEW.rank_search := 20;
         NEW.rank_address := 0;
       ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN
         NEW.rank_search := 20;
         NEW.rank_address := 0;
       ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN


@@ -1265,7 +1265,7 @@ BEGIN
 
     -- Thought this wasn't needed but when we add new languages to the country_name table
     -- we need to update the existing names
 
     -- Thought this wasn't needed but when we add new languages to the country_name table
     -- we need to update the existing names

-    IF NEW.name is not null AND array_upper(%#NEW.name,1) > 1 THEN
+    IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN

       default_language := get_country_language_code(NEW.country_code);
       IF default_language IS NOT NULL THEN
         IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
       default_language := get_country_language_code(NEW.country_code);
       IF default_language IS NOT NULL THEN
         IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN


@@ -1481,34 +1481,36 @@ BEGIN
 
         -- mark the linked place (excludes from search results)
         UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 
         -- mark the linked place (excludes from search results)
         UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;

-        DELETE from search_name where place_id = linkedPlacex.place_id;

 
       END LOOP;
 
 
       END LOOP;
 

-      FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
+      IF NEW.centroid IS NULL THEN

 
 

-        select * from placex where osm_type = upper(substring(relMember.member,1,1)) 
-          and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
+        FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP

 
 

-        IF NEW.name->'name' = linkedPlacex.name->'name' THEN
-          -- If we don't already have one use this as the centre point of the geometry
-          IF NEW.centroid IS NULL THEN
-            NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
-          END IF;
+          select * from placex where osm_type = upper(substring(relMember.member,1,1)) 
+            and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;

 
 

-          -- merge in the name, re-init word vector
-          NEW.name := linkedPlacex.name || NEW.name;
-          name_vector := make_keywords(NEW.name);
+          IF NEW.name->'name' = linkedPlacex.name->'name' AND NEW.rank_search = linkedPlacex.rank_search THEN
+            -- If we don't already have one use this as the centre point of the geometry
+            IF NEW.centroid IS NULL THEN
+              NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
+            END IF;

 
 

-          -- merge in extra tags
-          NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+            -- merge in the name, re-init word vector
+            NEW.name := linkedPlacex.name || NEW.name;
+            name_vector := make_keywords(NEW.name);

 
 

-          -- mark the linked place (excludes from search results)
-          UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
-          DELETE from search_name where place_id = linkedPlacex.place_id;
-        END IF;
+            -- merge in extra tags
+            NEW.extratags := linkedPlacex.extratags || NEW.extratags;

 
 

-      END LOOP;
+            -- mark the linked place (excludes from search results)
+            UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+          END IF;
+
+        END LOOP;
+
+      END IF;

 
       -- not found one yet? how about doing a name search
       IF NEW.centroid IS NULL THEN
 
       -- not found one yet? how about doing a name search
       IF NEW.centroid IS NULL THEN


@@ -1534,12 +1536,12 @@ BEGIN
 
           -- mark the linked place (excludes from search results)
           UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 
           -- mark the linked place (excludes from search results)
           UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;

-          DELETE from search_name where place_id = linkedPlacex.place_id;
+

         END LOOP;
       END IF;
 
       IF NEW.centroid IS NOT NULL THEN
         END LOOP;
       END IF;
 
       IF NEW.centroid IS NOT NULL THEN

-        place_centroid := NEW.centroid
+        place_centroid := NEW.centroid;

       END IF;
 
     END IF;
       END IF;
 
     END IF;


@@ -2523,8 +2525,8 @@ DECLARE
   numberrange INTEGER;
   rangestartnumber INTEGER;
   place_centroid GEOMETRY;
   numberrange INTEGER;
   rangestartnumber INTEGER;
   place_centroid GEOMETRY;

-  partition INTEGER;
-  parent_place_id BIGINT;
+  out_partition INTEGER;
+  out_parent_place_id BIGINT;

   location RECORD;
   address_street_word_id INTEGER;  
 
   location RECORD;
   address_street_word_id INTEGER;  
 


@@ -2561,32 +2563,32 @@ BEGIN
   END IF;
 
   place_centroid := ST_Centroid(linegeo);
   END IF;
 
   place_centroid := ST_Centroid(linegeo);

-  partition := get_partition(place_centroid, 'us');
-  parent_place_id := null;
+  out_partition := get_partition(place_centroid, 'us');
+  out_parent_place_id := null;

 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   IF address_street_word_id IS NOT NULL THEN
 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   IF address_street_word_id IS NOT NULL THEN

-    FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
-      parent_place_id := location.place_id;
+    FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
+      out_parent_place_id := location.place_id;

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

-  IF parent_place_id IS NULL THEN
-    FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(partition, linegeo) LOOP
-      parent_place_id := location.place_id;
+  IF out_parent_place_id IS NULL THEN
+    FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(out_partition, linegeo) LOOP
+      out_parent_place_id := location.place_id;

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

-  IF parent_place_id IS NULL THEN
-    FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
-      parent_place_id := location.place_id;
+  IF out_parent_place_id IS NULL THEN
+    FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
+      out_parent_place_id := location.place_id;

     END LOOP;    
   END IF;
 
   newpoints := 0;
   FOR housenum IN startnumber..endnumber BY stepsize LOOP
     insert into location_property_tiger (place_id, partition, parent_place_id, housenumber, postcode, centroid)
     END LOOP;    
   END IF;
 
   newpoints := 0;
   FOR housenum IN startnumber..endnumber BY stepsize LOOP
     insert into location_property_tiger (place_id, partition, parent_place_id, housenumber, postcode, centroid)

-    values (nextval('seq_place'), partition, parent_place_id, housenum, in_postcode,
+    values (nextval('seq_place'), out_partition, out_parent_place_id, housenum, in_postcode,

       ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
     newpoints := newpoints + 1;
   END LOOP;
       ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
     newpoints := newpoints + 1;
   END LOOP;


@@ -2603,7 +2605,7 @@ DECLARE
 
   newpoints INTEGER;
   place_centroid GEOMETRY;
 
   newpoints INTEGER;
   place_centroid GEOMETRY;

-  partition INTEGER;
+  out_partition INTEGER;

   out_parent_place_id BIGINT;
   location RECORD;
   address_street_word_id INTEGER;  
   out_parent_place_id BIGINT;
   location RECORD;
   address_street_word_id INTEGER;  


@@ -2612,18 +2614,18 @@ DECLARE
 BEGIN
 
   place_centroid := ST_Centroid(pointgeo);
 BEGIN
 
   place_centroid := ST_Centroid(pointgeo);

-  partition := get_partition(place_centroid, in_countrycode);
+  out_partition := get_partition(place_centroid, in_countrycode);

   out_parent_place_id := null;
 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   IF address_street_word_id IS NOT NULL THEN
   out_parent_place_id := null;
 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   IF address_street_word_id IS NOT NULL THEN

-    FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
+    FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP

       out_parent_place_id := location.place_id;
     END LOOP;
   END IF;
 
   IF out_parent_place_id IS NULL THEN
       out_parent_place_id := location.place_id;
     END LOOP;
   END IF;
 
   IF out_parent_place_id IS NULL THEN

-    FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
+    FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP

       out_parent_place_id := location.place_id;
     END LOOP;    
   END IF;
       out_parent_place_id := location.place_id;
     END LOOP;    
   END IF;


@@ -2633,12 +2635,12 @@ BEGIN
     SELECT postcode from placex where place_id = out_parent_place_id INTO out_postcode;
   END IF;
   IF out_postcode IS NULL THEN
     SELECT postcode from placex where place_id = out_parent_place_id INTO out_postcode;
   END IF;
   IF out_postcode IS NULL THEN

-    out_postcode := getNearestPostcode(partition, place_centroid);
+    out_postcode := getNearestPostcode(out_partition, place_centroid);

   END IF;
 
   newpoints := 0;
   insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)
   END IF;
 
   newpoints := 0;
   insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)

-    values (nextval('seq_place'), partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
+    values (nextval('seq_place'), out_partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);

   newpoints := newpoints + 1;
 
   RETURN newpoints;
   newpoints := newpoints + 1;
 
   RETURN newpoints;