force countries into partitions as determined by country code

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 62847e39a1b79dc1f8399e8ce8d97207ad7dba95..3c3a01ccb320a0513239b087a8d58ce7e4bebe9e 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -554,20 +554,6 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 LANGUAGE plpgsql IMMUTABLE;
 

-CREATE OR REPLACE FUNCTION get_country_code(place geometry, in_country_code VARCHAR(2)) RETURNS TEXT
-  AS $$
-DECLARE
-  nearcountry RECORD;
-BEGIN
-  FOR nearcountry IN select country_code from country_name where country_code = lower(in_country_code)
-  LOOP
-    RETURN nearcountry.country_code;
-  END LOOP;
-  RETURN get_country_code(place);
-END;
-$$
-LANGUAGE plpgsql IMMUTABLE;
-

 CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR(2)) RETURNS TEXT
   AS $$
 DECLARE
 CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR(2)) RETURNS TEXT
   AS $$
 DECLARE


@@ -596,10 +582,9 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 LANGUAGE plpgsql IMMUTABLE;
 

-CREATE OR REPLACE FUNCTION get_partition(place geometry, in_country_code VARCHAR(10)) RETURNS INTEGER
+CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10)) RETURNS INTEGER

   AS $$
 DECLARE
   AS $$
 DECLARE

-  place_centre GEOMETRY;

   nearcountry RECORD;
 BEGIN
   FOR nearcountry IN select partition from country_name where country_code = in_country_code
   nearcountry RECORD;
 BEGIN
   FOR nearcountry IN select partition from country_name where country_code = in_country_code


@@ -965,9 +950,9 @@ BEGIN
   NEW.place_id := nextval('seq_place');
   NEW.indexed_status := 1; --STATUS_NEW
 
   NEW.place_id := nextval('seq_place');
   NEW.indexed_status := 1; --STATUS_NEW
 

-  NEW.calculated_country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+  NEW.calculated_country_code := lower(get_country_code(NEW.geometry));

 
 

-  NEW.partition := get_partition(NEW.geometry, NEW.calculated_country_code);
+  NEW.partition := get_partition(NEW.calculated_country_code);

   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)


@@ -1078,18 +1063,6 @@ BEGIN
       ELSEIF NEW.type in ('village','hamlet','municipality','district','unincorporated_area','borough') THEN
         NEW.rank_search := 19;
         NEW.rank_address := 16;
       ELSEIF NEW.type in ('village','hamlet','municipality','district','unincorporated_area','borough') THEN
         NEW.rank_search := 19;
         NEW.rank_address := 16;

-      ELSEIF NEW.type in ('airport') AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
-        NEW.rank_search := 18;
-        NEW.rank_address := 17;
-      ELSEIF NEW.type in ('moor') AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
-        NEW.rank_search := 17;
-        NEW.rank_address := 18;
-      ELSEIF NEW.type in ('moor') THEN
-        NEW.rank_search := 17;
-        NEW.rank_address := 0;
-      ELSEIF NEW.type in ('national_park') THEN
-        NEW.rank_search := 18;
-        NEW.rank_address := 18;

       ELSEIF NEW.type in ('suburb','croft','subdivision','isolated_dwelling') THEN
         NEW.rank_search := 20;
         NEW.rank_address := NEW.rank_search;
       ELSEIF NEW.type in ('suburb','croft','subdivision','isolated_dwelling') THEN
         NEW.rank_search := 20;
         NEW.rank_address := NEW.rank_search;


@@ -1100,12 +1073,9 @@ BEGIN
         IF (NEW.extratags -> 'locality') = 'townland' THEN
           NEW.rank_address := 20;
         END IF;
         IF (NEW.extratags -> 'locality') = 'townland' THEN
           NEW.rank_address := 20;
         END IF;

-      ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN
+      ELSEIF NEW.type in ('neighbourhood') THEN

         NEW.rank_search := 22;
         NEW.rank_address := 22;
         NEW.rank_search := 22;
         NEW.rank_address := 22;

-      ELSEIF NEW.type in ('airport','street') THEN
-        NEW.rank_search := 26;
-        NEW.rank_address := NEW.rank_search;

       ELSEIF NEW.type in ('house','building') THEN
         NEW.rank_search := 30;
         NEW.rank_address := NEW.rank_search;
       ELSEIF NEW.type in ('house','building') THEN
         NEW.rank_search := 30;
         NEW.rank_address := NEW.rank_search;


@@ -1357,13 +1327,26 @@ BEGIN
     NEW.centroid := null;
 
     -- reclaculate country and partition
     NEW.centroid := null;
 
     -- reclaculate country and partition

-    IF NEW.rank_search >= 4 THEN
-      --NEW.calculated_country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
-      NEW.calculated_country_code := lower(get_country_code(place_centroid));
+    IF NEW.rank_search = 4 THEN
+      -- for countries, believe the mapped country code,
+      -- so that we remain in the right partition if the boundaries
+      -- suddenly expand.
+      NEW.partition := get_partition(lower(NEW.country_code));
+      IF NEW.partition = 0 THEN
+        NEW.calculated_country_code := lower(get_country_code(place_centroid));
+        NEW.partition := get_partition(NEW.calculated_country_code);
+      ELSE
+        NEW.calculated_country_code := lower(NEW.country_code);
+      END IF;

     ELSE
     ELSE

-      NEW.calculated_country_code := NULL;
+      IF NEW.rank_search > 4 THEN
+        --NEW.calculated_country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+        NEW.calculated_country_code := lower(get_country_code(place_centroid));
+      ELSE
+        NEW.calculated_country_code := NULL;
+      END IF;
+      NEW.partition := get_partition(NEW.calculated_country_code);

     END IF;
     END IF;

-    NEW.partition := get_partition(place_centroid, NEW.calculated_country_code);

     NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid);
 
     -- Adding ourselves to the list simplifies address calculations later
     NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid);
 
     -- Adding ourselves to the list simplifies address calculations later


@@ -2258,6 +2241,16 @@ BEGIN
       geometry = NEW.geometry
       where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
 
       geometry = NEW.geometry
       where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
 

+    IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
+        IF NEW.postcode IS NULL THEN
+            -- postcode was deleted, no longer retain in placex
+            DELETE FROM placex where place_id = existingplacex.place_id;
+            RETURN NULL;
+        END IF;
+
+        NEW.name := hstore('ref', NEW.postcode);
+    END IF;
+

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


@@ -2808,7 +2801,7 @@ DECLARE
 BEGIN
 
   place_centroid := ST_Centroid(pointgeo);
 BEGIN
 
   place_centroid := ST_Centroid(pointgeo);

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

   out_parent_place_id := null;
 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   out_parent_place_id := null;
 
   address_street_word_id := get_name_id(make_standard_name(in_street));