Merge remote-tracking branch 'upstream/master'

[nominatim.git] / sql / functions / utils.sql

diff --git a/sql/functions/utils.sql b/sql/functions/utils.sql
index ccb6a2033ca057ec521150711c16637c780ba99d..6697ff971953a32972521d825a79016e25681f45 100644 (file)
--- a/sql/functions/utils.sql
+++ b/sql/functions/utils.sql
@@ -95,90 +95,6 @@ $$
 LANGUAGE plpgsql IMMUTABLE;
 
 
 LANGUAGE plpgsql IMMUTABLE;
 
 

-
-CREATE OR REPLACE FUNCTION reverse_place_diameter(rank_search SMALLINT)
-  RETURNS FLOAT
-  AS $$
-BEGIN
-  IF rank_search <= 4 THEN
-    RETURN 5.0;
-  ELSIF rank_search <= 8 THEN
-    RETURN 1.8;
-  ELSIF rank_search <= 12 THEN
-    RETURN 0.6;
-  ELSIF rank_search <= 17 THEN
-    RETURN 0.16;
-  ELSIF rank_search <= 18 THEN
-    RETURN 0.08;
-  ELSIF rank_search <= 19 THEN
-    RETURN 0.04;
-  END IF;
-
-  RETURN 0.02;
-END;
-$$
-LANGUAGE plpgsql IMMUTABLE;
-
-
-CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT,
-                                             OUT rank_search SMALLINT,
-                                             OUT rank_address SMALLINT)
-AS $$
-DECLARE
-  part TEXT;
-BEGIN
-    rank_search := 30;
-    rank_address := 30;
-    postcode := upper(postcode);
-
-    IF country_code = 'gb' THEN
-        IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
-            rank_search := 25;
-            rank_address := 5;
-        ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
-            rank_search := 23;
-            rank_address := 5;
-        ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
-            rank_search := 21;
-            rank_address := 5;
-        END IF;
-
-    ELSEIF country_code = 'sg' THEN
-        IF postcode ~ '^([0-9]{6})$' THEN
-            rank_search := 25;
-            rank_address := 11;
-        END IF;
-
-    ELSEIF country_code = 'de' THEN
-        IF postcode ~ '^([0-9]{5})$' THEN
-            rank_search := 21;
-            rank_address := 11;
-        END IF;
-
-    ELSE
-        -- Guess at the postcode format and coverage (!)
-        IF postcode ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
-            rank_search := 21;
-            rank_address := 11;
-        ELSE
-            -- Does it look splitable into and area and local code?
-            part := substring(postcode from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
-
-            IF part IS NOT NULL THEN
-                rank_search := 25;
-                rank_address := 11;
-            ELSEIF postcode ~ '^[- :A-Z0-9]{6,}$' THEN
-                rank_search := 21;
-                rank_address := 11;
-            END IF;
-        END IF;
-    END IF;
-
-END;
-$$
-LANGUAGE plpgsql IMMUTABLE;
-
-

 -- Find the nearest artificial postcode for the given geometry.
 -- TODO For areas there should not be more than two inside the geometry.
 CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY)
 -- Find the nearest artificial postcode for the given geometry.
 -- TODO For areas there should not be more than two inside the geometry.
 CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY)


@@ -321,7 +237,7 @@ BEGIN
     IF word_ids is not null THEN
       parent_place_id := getNearestNamedRoadPlaceId(partition, centroid, word_ids);
       IF parent_place_id is not null THEN
     IF word_ids is not null THEN
       parent_place_id := getNearestNamedRoadPlaceId(partition, centroid, word_ids);
       IF parent_place_id is not null THEN

-        --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent.place_id;
+        --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent_place_id;

         RETURN parent_place_id;
       END IF;
     END IF;
         RETURN parent_place_id;
       END IF;
     END IF;


@@ -333,7 +249,7 @@ BEGIN
     IF word_ids is not null THEN
       parent_place_id := getNearestNamedPlacePlaceId(partition, centroid, word_ids);
       IF parent_place_id is not null THEN
     IF word_ids is not null THEN
       parent_place_id := getNearestNamedPlacePlaceId(partition, centroid, word_ids);
       IF parent_place_id is not null THEN

-        --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent.place_id;
+        --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent_place_id;

         RETURN parent_place_id;
       END IF;
     END IF;
         RETURN parent_place_id;
       END IF;
     END IF;


@@ -356,6 +272,25 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

+CREATE OR REPLACE FUNCTION near_feature_rank_distance(rank_search INTEGER)
+  RETURNS FLOAT
+  AS $$
+BEGIN
+  IF rank_search <= 16 THEN -- city
+    RETURN 7500;
+  ELSIF rank_search <= 18 THEN -- town
+    RETURN 4000;
+  ELSIF rank_search <= 19 THEN -- village
+    RETURN 2000;
+  ELSIF rank_search  <= 20 THEN -- hamlet
+    RETURN 1000;
+  END IF;
+
+  RETURN 500;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+

 
 CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2),
                                         partition INTEGER, keywords INTEGER[],
 
 CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2),
                                         partition INTEGER, keywords INTEGER[],


@@ -366,18 +301,11 @@ CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2)
 DECLARE
   locationid INTEGER;
   centroid GEOMETRY;
 DECLARE
   locationid INTEGER;
   centroid GEOMETRY;

-  diameter FLOAT;
-  x BOOLEAN;
-  splitGeom RECORD;
+  radius FLOAT;

   secgeo GEOMETRY;
   postcode TEXT;
 BEGIN
   secgeo GEOMETRY;
   postcode TEXT;
 BEGIN

-
-  IF rank_search > 25 THEN
-    RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
-  END IF;
-
-  x := deleteLocationArea(partition, place_id, rank_search);
+  PERFORM deleteLocationArea(partition, place_id, rank_search);

 
   -- add postcode only if it contains a single entry, i.e. ignore postcode lists
   postcode := NULL;
 
   -- add postcode only if it contains a single entry, i.e. ignore postcode lists
   postcode := NULL;


@@ -389,32 +317,18 @@ BEGIN
     centroid := ST_Centroid(geometry);
 
     FOR secgeo IN select split_geometry(geometry) AS geom LOOP
     centroid := ST_Centroid(geometry);
 
     FOR secgeo IN select split_geometry(geometry) AS geom LOOP

-      x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
+      PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);

     END LOOP;
 
     END LOOP;
 

-  ELSE
-
-    diameter := 0.02;
-    IF rank_address = 0 THEN
-      diameter := 0.02;
-    ELSEIF rank_search <= 14 THEN
-      diameter := 1.2;
-    ELSEIF rank_search <= 15 THEN
-      diameter := 1;
-    ELSEIF rank_search <= 16 THEN
-      diameter := 0.5;
-    ELSEIF rank_search <= 17 THEN
-      diameter := 0.2;
-    ELSEIF rank_search <= 21 THEN
-      diameter := 0.05;
-    ELSEIF rank_search = 25 THEN
-      diameter := 0.005;
-    END IF;
+  ELSEIF ST_GeometryType(geometry) = 'ST_Point' THEN
+    radius := near_feature_rank_distance(rank_search);
+    --DEBUG: RAISE WARNING 'adding % radius %', place_id, radius;

 
 

---    RAISE WARNING 'adding % diameter %', place_id, diameter;
-
-    secgeo := ST_Buffer(geometry, diameter);
-    x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, ST_Centroid(geometry), secgeo);
+    -- Create a bounding box with an extent computed from the radius (in meters).
+    secgeo := ST_Envelope(ST_Collect(
+                            ST_Project(geometry, radius, 0.785398)::geometry,
+                            ST_Project(geometry, radius, 3.9269908)::geometry));
+    PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, geometry, secgeo);

 
   END IF;
 
 
   END IF;
 


@@ -515,7 +429,7 @@ DECLARE
   geo RECORD;
 BEGIN
   -- 10000000000 is ~~ 1x1 degree
   geo RECORD;
 BEGIN
   -- 10000000000 is ~~ 1x1 degree

-  FOR geo IN select quad_split_geometry(geometry, 0.25, 20) as geom LOOP
+  FOR geo IN select quad_split_geometry(geometry, 0.01, 20) as geom LOOP

     RETURN NEXT geo.geom;
   END LOOP;
   RETURN;
     RETURN NEXT geo.geom;
   END LOOP;
   RETURN;


@@ -553,7 +467,7 @@ DECLARE
   placegeom GEOMETRY;
   geom GEOMETRY;
   diameter FLOAT;
   placegeom GEOMETRY;
   geom GEOMETRY;
   diameter FLOAT;

-  rank INTEGER;
+  rank SMALLINT;

 BEGIN
   UPDATE placex SET indexed_status = 2 WHERE place_id = placeid;
   SELECT geometry, rank_search FROM placex WHERE place_id = placeid INTO placegeom, rank;
 BEGIN
   UPDATE placex SET indexed_status = 2 WHERE place_id = placeid;
   SELECT geometry, rank_search FROM placex WHERE place_id = placeid INTO placegeom, rank;


@@ -566,22 +480,7 @@ BEGIN
         AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place'));
       END LOOP;
     ELSE
         AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place'));
       END LOOP;
     ELSE

-        diameter := 0;
-        IF rank = 11 THEN
-          diameter := 0.05;
-        ELSEIF rank < 18 THEN
-          diameter := 0.1;
-        ELSEIF rank < 20 THEN
-          diameter := 0.05;
-        ELSEIF rank = 21 THEN
-          diameter := 0.001;
-        ELSEIF rank < 24 THEN
-          diameter := 0.02;
-        ELSEIF rank < 26 THEN
-          diameter := 0.002; -- 100 to 200 meters
-        ELSEIF rank < 28 THEN
-          diameter := 0.001; -- 50 to 100 meters
-        END IF;
+        diameter := update_place_diameter(rank);

         IF diameter > 0 THEN
           IF rank >= 26 THEN
             -- roads may cause reparenting for >27 rank places
         IF diameter > 0 THEN
           IF rank >= 26 THEN
             -- roads may cause reparenting for >27 rank places