X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/2d6f00945ab433f51be5df0576625e29d5b6300c..6704f7bbb08127cdf2cb7e1e1fc28605b2273bfa:/sql/functions.sql?ds=sidebyside diff --git a/sql/functions.sql b/sql/functions.sql index dc692804..9186c6a3 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -548,21 +548,6 @@ BEGIN 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; $$ @@ -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 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); - 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; @@ -862,7 +845,7 @@ BEGIN 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; @@ -897,6 +880,12 @@ BEGIN 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; @@ -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; - 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; @@ -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; - 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; @@ -1321,7 +1314,6 @@ BEGIN 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; @@ -1340,7 +1332,7 @@ BEGIN 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 @@ -2591,86 +2583,10 @@ END; $$ 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 - result place_boundingbox; numfeatures integer; BEGIN update placex set