also add explicit cast for varchar

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

diff --git a/sql/functions/utils.sql b/sql/functions/utils.sql
index 61033fb4d3a5db50118f66fa93a6a7378c4f5660..0a49eef52fc16ee1068642e73ac337880e2d1ed1 100644 (file)
--- a/sql/functions/utils.sql
+++ b/sql/functions/utils.sql
@@ -237,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;


@@ -249,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;


@@ -272,28 +272,45 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

+-- Create a bounding box with an extent computed from the radius (in meters)
+-- which in turn is derived from the given search rank.
+CREATE OR REPLACE FUNCTION place_node_fuzzy_area(geom GEOMETRY, rank_search INTEGER)
+  RETURNS GEOMETRY
+  AS $$
+DECLARE
+  radius FLOAT := 500;
+BEGIN
+  IF rank_search <= 16 THEN -- city
+    radius := 15000;
+  ELSIF rank_search <= 18 THEN -- town
+    radius := 4000;
+  ELSIF rank_search <= 19 THEN -- village
+    radius := 2000;
+  ELSIF rank_search  <= 20 THEN -- hamlet
+    radius := 1000;
+  END IF;
+
+  RETURN ST_Envelope(ST_Collect(
+                     ST_Project(geom, radius, 0.785398)::geometry,
+                     ST_Project(geom, radius, 3.9269908)::geometry));
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+

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

-                                        in_postcode TEXT, geometry GEOMETRY)
+                                        in_postcode TEXT, geometry GEOMETRY,
+                                        centroid GEOMETRY)

   RETURNS BOOLEAN
   AS $$
 DECLARE
   locationid INTEGER;
   RETURNS BOOLEAN
   AS $$
 DECLARE
   locationid INTEGER;

-  centroid GEOMETRY;
-  diameter FLOAT;
-  x BOOLEAN;
-  splitGeom RECORD;

   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;


@@ -302,35 +319,13 @@ BEGIN
   END IF;
 
   IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
   END IF;
 
   IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN

-    centroid := ST_Centroid(geometry);
-

     FOR secgeo IN select split_geometry(geometry) AS geom LOOP
     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;
-
---    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);
+  ELSEIF ST_GeometryType(geometry) = 'ST_Point' THEN
+    secgeo := place_node_fuzzy_area(geometry, rank_search);
+    PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, centroid, secgeo);

 
   END IF;
 
 
   END IF;
 


@@ -431,7 +426,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;


@@ -469,17 +464,23 @@ 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;
 BEGIN
   UPDATE placex SET indexed_status = 2 WHERE place_id = placeid;

-  SELECT geometry, rank_search FROM placex WHERE place_id = placeid INTO placegeom, rank;
+
+  SELECT geometry, rank_address INTO placegeom, rank
+    FROM placex WHERE place_id = placeid;
+

   IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN
   IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN

-    IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
-      FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
-        update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) 
-        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'));
-        update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry)) 
-        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'));
+    IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon')
+       AND rank > 0
+    THEN
+      FOR geom IN SELECT split_geometry(placegeom) LOOP
+        UPDATE placex SET indexed_status = 2
+         WHERE ST_Intersects(geom, placex.geometry)
+               and indexed_status = 0
+               and ((rank_address = 0 and rank_search > rank) or rank_address > rank)
+               and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place'));

       END LOOP;
     ELSE
         diameter := update_place_diameter(rank);
       END LOOP;
     ELSE
         diameter := update_place_diameter(rank);