fix case sensitivity of cyrillic ghe

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index dc69280446a333d4352175049c53c31d69c1f130..9186c6a31e4b0b9d7bbdcbf811c475ff12555898 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -548,21 +548,6 @@ BEGIN
     RETURN nearcountry.country_code;
   END LOOP;
 
     RETURN nearcountry.country_code;
   END LOOP;
 

-  -- WorldBoundaries data (second fallback - think there might be something broken in this data)
---  FOR nearcountry IN select country_code from country where st_covers(geometry, place_centre) limit 1
---  LOOP
---    RETURN nearcountry.country_code;
---  END LOOP;
-
---RAISE WARNING 'near country: %', ST_AsText(place_centre);
-
-  -- Still not in a country - try nearest within ~12 miles of a country
---  FOR nearcountry IN select country_code from country where st_distance(geometry, place_centre) < 0.5 
---    order by st_distance(geometry, place) limit 1
---  LOOP
---    RETURN nearcountry.country_code;
---  END LOOP;
-

   RETURN NULL;
 END;
 $$
   RETURN NULL;
 END;
 $$


@@ -846,15 +831,13 @@ BEGIN
     IF search_place_id IS NOT NULL THEN
       select * from placex where place_id = search_place_id INTO nextnode;
 
     IF search_place_id IS NOT NULL THEN
       select * from placex where place_id = search_place_id INTO nextnode;
 

-        IF nodeidpos < array_upper(waynodes, 1) THEN
+        IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN

           -- Make sure that the point is actually on the line. That might
           -- be a bit paranoid but ensures that the algorithm still works
           -- should osm2pgsql attempt to repair geometries.
           splitline := split_line_on_node(linegeo, nextnode.geometry);
           sectiongeo := ST_GeometryN(splitline, 1);
           -- Make sure that the point is actually on the line. That might
           -- be a bit paranoid but ensures that the algorithm still works
           -- should osm2pgsql attempt to repair geometries.
           splitline := split_line_on_node(linegeo, nextnode.geometry);
           sectiongeo := ST_GeometryN(splitline, 1);

-          IF ST_GeometryType(ST_GeometryN(splitline, 2)) = 'ST_LineString' THEN
-            linegeo := ST_GeometryN(splitline, 2);
-          END IF;
+          linegeo := ST_GeometryN(splitline, 2);

         ELSE
           sectiongeo = linegeo;
         END IF;
         ELSE
           sectiongeo = linegeo;
         END IF;


@@ -862,7 +845,7 @@ BEGIN
 
         IF startnumber IS NOT NULL AND endnumber IS NOT NULL
            AND @(startnumber - endnumber) < 1000 AND startnumber != endnumber
 
         IF startnumber IS NOT NULL AND endnumber IS NOT NULL
            AND @(startnumber - endnumber) < 1000 AND startnumber != endnumber

-           AND ST_GeometryType(linegeo) = 'ST_LineString' THEN
+           AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN

 
           IF (startnumber > endnumber) THEN
             housenum := endnumber;
 
           IF (startnumber > endnumber) THEN
             housenum := endnumber;


@@ -897,6 +880,12 @@ BEGIN
           END LOOP;
         END IF;
 
           END LOOP;
         END IF;
 

+        -- early break if we are out of line string,
+        -- might happen when a line string loops back on itself
+        IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
+            RETURN newpoints;
+        END IF;
+

         startnumber := substring(nextnode.housenumber,'[0-9]+')::integer;
         prevnode := nextnode;
     END IF;
         startnumber := substring(nextnode.housenumber,'[0-9]+')::integer;
         prevnode := nextnode;
     END IF;


@@ -1107,7 +1096,11 @@ BEGIN
       END IF;
     ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
       NEW.rank_search := 22;
       END IF;
     ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
       NEW.rank_search := 22;

-      NEW.rank_address := NEW.rank_search;
+      IF NEW.type in ('residential', 'farm', 'farmyard', 'industrial', 'commercial', 'allotments', 'retail') THEN
+        NEW.rank_address := NEW.rank_search;
+      ELSE
+        NEW.rank_address := 0;
+      END IF;

     ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN
       NEW.rank_search := 18;
       NEW.rank_address := 0;
     ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN
       NEW.rank_search := 18;
       NEW.rank_address := 0;


@@ -1119,7 +1112,7 @@ BEGIN
     -- any feature more than 5 square miles is probably worth indexing
     ELSEIF ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_Area(NEW.geometry) > 0.1 THEN
       NEW.rank_search := 22;
     -- any feature more than 5 square miles is probably worth indexing
     ELSEIF ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_Area(NEW.geometry) > 0.1 THEN
       NEW.rank_search := 22;

-      NEW.rank_address := NEW.rank_search;
+      NEW.rank_address := 0;

     ELSEIF NEW.class = 'highway' AND NEW.name is NULL AND 
            NEW.type in ('service','cycleway','path','footway','steps','bridleway','track','byway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN
 --      RAISE WARNING 'unnamed minor feature %',NEW.osm_id;
     ELSEIF NEW.class = 'highway' AND NEW.name is NULL AND 
            NEW.type in ('service','cycleway','path','footway','steps','bridleway','track','byway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN
 --      RAISE WARNING 'unnamed minor feature %',NEW.osm_id;


@@ -1321,7 +1314,6 @@ BEGIN
 
     result := deleteSearchName(NEW.partition, NEW.place_id);
     DELETE FROM place_addressline WHERE place_id = NEW.place_id;
 
     result := deleteSearchName(NEW.partition, NEW.place_id);
     DELETE FROM place_addressline WHERE place_id = NEW.place_id;

-    DELETE FROM place_boundingbox where place_id = NEW.place_id;

     result := deleteRoad(NEW.partition, NEW.place_id);
     result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
     UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
     result := deleteRoad(NEW.partition, NEW.place_id);
     result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
     UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;


@@ -1340,7 +1332,7 @@ BEGIN
     place_centroid := ST_PointOnSurface(NEW.geometry);
     NEW.centroid := null;
 
     place_centroid := ST_PointOnSurface(NEW.geometry);
     NEW.centroid := null;
 

-    -- reclaculate country and partition
+    -- recalculate country and partition

     IF NEW.rank_search = 4 THEN
       -- for countries, believe the mapped country code,
       -- so that we remain in the right partition if the boundaries
     IF NEW.rank_search = 4 THEN
       -- for countries, believe the mapped country code,
       -- so that we remain in the right partition if the boundaries


@@ -2591,86 +2583,10 @@ END;
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

-CREATE OR REPLACE FUNCTION get_place_boundingbox(search_place_id BIGINT) RETURNS place_boundingbox
-  AS $$
-DECLARE
-  result place_boundingbox;
-  numfeatures integer;
-BEGIN
-  select * from place_boundingbox into result where place_id = search_place_id;
-  IF result.place_id IS NULL THEN
--- remove  isaddress = true because if there is a matching polygon it always wins
-    select count(*) from place_addressline where address_place_id = search_place_id into numfeatures;
-    insert into place_boundingbox select place_id,
-             ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),4)),ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),2)),
-             ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),1)),ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),3)),
-             numfeatures, ST_Area(geometry),
-             geometry as area from location_area where place_id = search_place_id;
-    select * from place_boundingbox into result where place_id = search_place_id;
-  END IF;
-  IF result.place_id IS NULL THEN
--- TODO 0.0001
-    insert into place_boundingbox select address_place_id,
-             min(ST_Y(ST_Centroid(geometry))) as minlon,max(ST_Y(ST_Centroid(geometry))) as maxlon,
-             min(ST_X(ST_Centroid(geometry))) as minlat,max(ST_X(ST_Centroid(geometry))) as maxlat,
-             count(*), ST_Area(ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001)) as area,
-             ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001) as boundary 
-             from (select * from place_addressline where address_place_id = search_place_id order by cached_rank_address limit 4000) as place_addressline join placex using (place_id) 
-             where address_place_id = search_place_id
---               and (isaddress = true OR place_id = search_place_id)
-               and (st_length(geometry) < 0.01 or place_id = search_place_id)
-             group by address_place_id limit 1;
-    select * from place_boundingbox into result where place_id = search_place_id;
-  END IF;
-  return result;
-END;
-$$
-LANGUAGE plpgsql;
-
--- don't do the operation if it would be slow
-CREATE OR REPLACE FUNCTION get_place_boundingbox_quick(search_place_id BIGINT) RETURNS place_boundingbox
-  AS $$
-DECLARE
-  result place_boundingbox;
-  numfeatures integer;
-  rank integer;
-BEGIN
-  select * from place_boundingbox into result where place_id = search_place_id;
-  IF result IS NULL AND rank > 14 THEN
-    select count(*) from place_addressline where address_place_id = search_place_id and isaddress = true into numfeatures;
-    insert into place_boundingbox select place_id,
-             ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),4)),ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),2)),
-             ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),1)),ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),3)),
-             numfeatures, ST_Area(geometry),
-             geometry as area from location_area where place_id = search_place_id;
-    select * from place_boundingbox into result where place_id = search_place_id;
-  END IF;
-  IF result IS NULL THEN
-    select rank_search from placex where place_id = search_place_id into rank;
-    IF rank > 20 THEN
--- TODO 0.0001
-      insert into place_boundingbox select address_place_id,
-             min(ST_Y(ST_Centroid(geometry))) as minlon,max(ST_Y(ST_Centroid(geometry))) as maxlon,
-             min(ST_X(ST_Centroid(geometry))) as minlat,max(ST_X(ST_Centroid(geometry))) as maxlat,
-             count(*), ST_Area(ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001)) as area,
-             ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001) as boundary 
-             from place_addressline join placex using (place_id) 
-             where address_place_id = search_place_id 
-               and (isaddress = true OR place_id = search_place_id)
-               and (st_length(geometry) < 0.01 or place_id = search_place_id)
-             group by address_place_id limit 1;
-      select * from place_boundingbox into result where place_id = search_place_id;
-    END IF;
-  END IF;
-  return result;
-END;
-$$
-LANGUAGE plpgsql;

 
 CREATE OR REPLACE FUNCTION update_place(search_place_id BIGINT) RETURNS BOOLEAN
   AS $$
 DECLARE
 
 CREATE OR REPLACE FUNCTION update_place(search_place_id BIGINT) RETURNS BOOLEAN
   AS $$
 DECLARE

-  result place_boundingbox;

   numfeatures integer;
 BEGIN
   update placex set 
   numfeatures integer;
 BEGIN
   update placex set