X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/756f328ac9710fc3d3bb09bb09e6c4da27c3daaf..a6453a808db7dccd687a3fceeedff88b0fd2e06c:/sql/functions.sql?ds=sidebyside diff --git a/sql/functions.sql b/sql/functions.sql index 5b2f716a..b71ca380 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -46,7 +46,7 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; -CREATE OR REPLACE FUNCTION geometry_sector(place geometry) RETURNS INTEGER +CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place geometry) RETURNS INTEGER AS $$ DECLARE NEWgeometry geometry; @@ -59,7 +59,7 @@ BEGIN RETURN 0; END IF; END IF; - RETURN (500-ST_X(ST_Centroid(NEWgeometry))::integer)*1000 + (500-ST_Y(ST_Centroid(NEWgeometry))::integer); + RETURN (partition*1000000) + (500-ST_X(ST_Centroid(NEWgeometry))::integer)*1000 + (500-ST_Y(ST_Centroid(NEWgeometry))::integer); END; $$ LANGUAGE plpgsql IMMUTABLE; @@ -85,26 +85,6 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; -CREATE OR REPLACE FUNCTION geometry_index(place geometry, indexed BOOLEAN, name HSTORE) RETURNS INTEGER - AS $$ -BEGIN -IF indexed THEN RETURN NULL; END IF; -IF name is null THEN RETURN NULL; END IF; -RETURN geometry_sector(place); -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - -CREATE OR REPLACE FUNCTION geometry_index(sector integer, indexed BOOLEAN, name HSTORE) RETURNS INTEGER - AS $$ -BEGIN -IF indexed THEN RETURN NULL; END IF; -IF name is null THEN RETURN NULL; END IF; -RETURN sector; -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - CREATE OR REPLACE FUNCTION transliteration(text) RETURNS text AS '{modulepath}/nominatim.so', 'transliteration' LANGUAGE c IMMUTABLE STRICT; @@ -465,7 +445,7 @@ BEGIN --RAISE WARNING 'start: %', ST_AsText(place_centre); -- Try for a OSM polygon first - FOR nearcountry IN select country_code from location_area_country where country_code is not null and st_contains(geometry, place_centre) limit 1 + FOR nearcountry IN select country_code from location_area_country where country_code is not null and not isguess and st_contains(geometry, place_centre) limit 1 LOOP RETURN nearcountry.country_code; END LOOP; @@ -481,10 +461,16 @@ BEGIN --RAISE WARNING 'natural earth: %', ST_AsText(place_centre); -- Natural earth data (first fallback) --- FOR nearcountry IN select country_code from country_naturalearthdata where st_contains(geometry, place_centre) limit 1 --- LOOP --- RETURN nearcountry.country_code; --- END LOOP; + FOR nearcountry IN select country_code from country_naturalearthdata where st_contains(geometry, place_centre) limit 1 + LOOP + RETURN nearcountry.country_code; + END LOOP; + + -- Natural earth data (first fallback) + FOR nearcountry IN select country_code from country_naturalearthdata where st_distance(geometry, place_centre) < 0.5 limit 1 + LOOP + RETURN nearcountry.country_code; + END LOOP; --RAISE WARNING 'in country: %', ST_AsText(place_centre); @@ -527,7 +513,7 @@ CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR DECLARE nearcountry RECORD; BEGIN - FOR nearcountry IN select distinct country_default_language_code from country where country_code = search_country_code limit 1 + FOR nearcountry IN select distinct country_default_language_code from country_name where country_code = search_country_code limit 1 LOOP RETURN lower(nearcountry.country_default_language_code); END LOOP; @@ -536,17 +522,17 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; -CREATE OR REPLACE FUNCTION get_partition(place geometry, in_country_code VARCHAR(10)) RETURNS TEXT +CREATE OR REPLACE FUNCTION get_partition(place geometry, in_country_code VARCHAR(10)) RETURNS INTEGER AS $$ DECLARE place_centre GEOMETRY; nearcountry RECORD; BEGIN - FOR nearcountry IN select country_code from country_name where country_code = in_country_code + FOR nearcountry IN select partition from country_name where country_code = in_country_code LOOP - RETURN nearcountry.country_code; + RETURN nearcountry.partition; END LOOP; - RETURN 'none'; + RETURN 0; END; $$ LANGUAGE plpgsql IMMUTABLE; @@ -565,7 +551,7 @@ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION add_location( place_id INTEGER, country_code varchar(2), - partition varchar(10), + partition INTEGER, keywords INTEGER[], rank_search INTEGER, rank_address INTEGER, @@ -584,12 +570,13 @@ DECLARE lat INTEGER; centroid GEOMETRY; secgeo GEOMETRY; + secbox GEOMETRY; diameter FLOAT; x BOOLEAN; BEGIN - IF rank_search > 26 THEN - RAISE EXCEPTION 'Adding location with rank > 26 (% rank %)', place_id, rank_search; + IF rank_search > 25 THEN + RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search; END IF; x := deleteLocationArea(partition, place_id); @@ -610,9 +597,12 @@ BEGIN ELSE FOR lon IN xmin..(xmax-1) LOOP FOR lat IN ymin..(ymax-1) LOOP - secgeo := st_intersection(geometry, ST_SetSRID(ST_MakeBox2D(ST_Point(lon,lat),ST_Point(lon+1,lat+1)),4326)); - IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN - x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, centroid, secgeo); + secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(lon,lat),ST_Point(lon+1,lat+1)),4326); + IF st_intersects(geometry, secbox) THEN + secgeo := st_intersection(geometry, secbox); + IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN + x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, centroid, secgeo); + END IF; END IF; END LOOP; END LOOP; @@ -629,6 +619,8 @@ BEGIN diameter := 0.15; ELSEIF rank_search = 17 THEN diameter := 0.05; + ELSEIF rank_search = 21 THEN + diameter := 0.01; ELSEIF rank_search = 25 THEN diameter := 0.005; END IF; @@ -654,6 +646,7 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION update_location( + partition INTEGER, place_id INTEGER, place_country_code varchar(2), name hstore, @@ -666,7 +659,7 @@ CREATE OR REPLACE FUNCTION update_location( DECLARE b BOOLEAN; BEGIN - b := delete_location(place_id); + b := deleteLocationArea(partition, place_id); RETURN add_location(place_id, place_country_code, name, rank_search, rank_address, geometry); END; $$ @@ -865,6 +858,7 @@ DECLARE postcode TEXT; result BOOLEAN; country_code VARCHAR(2); + default_language VARCHAR(10); diameter FLOAT; BEGIN -- RAISE WARNING '%',NEW.osm_id; @@ -897,8 +891,20 @@ BEGIN NEW.indexed_status := 1; --STATUS_NEW NEW.country_code := get_country_code(NEW.geometry, NEW.country_code); - NEW.geometry_sector := geometry_sector(NEW.geometry); NEW.partition := get_partition(NEW.geometry, NEW.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) + IF NEW.name is not null THEN + default_language := get_country_language_code(NEW.country_code); + IF default_language IS NOT NULL THEN + IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN + NEW.name := NEW.name || (('name:'||default_language) => (NEW.name -> 'name')); + ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN + NEW.name := NEW.name || ('name' => (NEW.name -> 'name:'||default_language)); + END IF; + END IF; + END IF; IF NEW.admin_level > 15 THEN NEW.admin_level := 15; @@ -1180,7 +1186,7 @@ BEGIN RETURN NEW; END IF; - DELETE FROM search_name 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; @@ -1204,6 +1210,10 @@ BEGIN name_vector := name_vector + tagpairid; END IF; + FOR i IN 1..28 LOOP + address_havelevel[i] := false; + END LOOP; + --RAISE WARNING '% %', NEW.place_id, NEW.rank_search; -- For low level elements we inherit from our parent road @@ -1308,44 +1318,32 @@ BEGIN IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN address_street_word_id := get_name_id(make_standard_name(NEW.street)); ---RAISE WARNING 'street: % %', NEW.street, address_street_word_id; IF address_street_word_id IS NOT NULL THEN - FOR location IN SELECT place_id,ST_distance(NEW.geometry, search_name.centroid) as distance - FROM search_name WHERE search_name.name_vector @> ARRAY[address_street_word_id] - AND ST_DWithin(NEW.geometry, search_name.centroid, 0.01) and search_rank between 22 and 27 - ORDER BY ST_distance(NEW.geometry, search_name.centroid) ASC limit 1 - LOOP ---RAISE WARNING 'streetname found nearby %',location; + FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_id) LOOP NEW.parent_place_id := location.place_id; END LOOP; END IF; - -- Failed, fall back to nearest - don't just stop - IF NEW.parent_place_id IS NULL THEN ---RAISE WARNING 'unable to find streetname nearby % %',NEW.street,address_street_word_id; --- RETURN null; - END IF; END IF; --RAISE WARNING 'x4'; - IF NEW.parent_place_id IS NULL THEN - FOR location IN SELECT place_id FROM getNearRoads(NEW.partition, place_centroid) LOOP + -- Still nothing, just use the nearest road + search_diameter := 0.00005; + WHILE NEW.parent_place_id IS NULL AND search_diameter < 0.1 LOOP + FOR location IN SELECT place_id FROM placex + WHERE ST_DWithin(place_centroid, placex.geometry, search_diameter) and rank_search between 22 and 27 + ORDER BY ST_distance(NEW.geometry, placex.geometry) ASC limit 1 + LOOP NEW.parent_place_id := location.place_id; END LOOP; - END IF; + search_diameter := search_diameter * 2; + END LOOP; --RAISE WARNING 'x6 %',NEW.parent_place_id; -- If we didn't find any road fallback to standard method IF NEW.parent_place_id IS NOT NULL THEN - -- Some unnamed roads won't have been indexed, index now if needed --- ALL are now indexed! --- select count(*) from place_addressline where place_id = NEW.parent_place_id INTO parent_place_id_count; --- IF parent_place_id_count = 0 THEN --- UPDATE placex set indexed = true where indexed = false and place_id = NEW.parent_place_id; --- END IF; - -- Add the street to the address as zero distance to force to front of list INSERT INTO place_addressline VALUES (NEW.place_id, NEW.parent_place_id, true, true, 0, 26); address_havelevel[26] := true; @@ -1370,8 +1368,10 @@ BEGIN -- Performance, it would be more acurate to do all the rest of the import process but it takes too long -- Just be happy with inheriting from parent road only - INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_address, 0, NEW.country_code, - name_vector, nameaddress_vector, place_centroid); + result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, place_centroid); + +-- INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_address, 0, NEW.country_code, +-- name_vector, nameaddress_vector, place_centroid); return NEW; END IF; @@ -1423,16 +1423,10 @@ BEGIN IF array_upper(isin_tokens, 1) IS NOT NULL THEN FOR i IN 1..array_upper(isin_tokens, 1) LOOP - FOR location IN SELECT place_id,search_name.name_vector,address_rank, - ST_Distance(place_centroid, search_name.centroid) as distance - FROM search_name - WHERE search_name.name_vector @> ARRAY[isin_tokens[i]] - AND search_rank < NEW.rank_search - AND (country_code = NEW.country_code OR address_rank < 4) - ORDER BY ST_distance(NEW.geometry, centroid) ASC limit 1 - LOOP - nameaddress_vector := array_merge(nameaddress_vector, location.name_vector); - INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.address_rank], location.distance, location.address_rank); + FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP + nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); + INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); + address_havelevel[location.rank_address] := true; END LOOP; END LOOP; @@ -1445,8 +1439,9 @@ BEGIN result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry); END IF; - INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, 0, NEW.country_code, - name_vector, nameaddress_vector, place_centroid); + result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, place_centroid); + +-- INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, 0, NEW.country_code, name_vector, nameaddress_vector, place_centroid); END IF; END IF; @@ -1459,21 +1454,28 @@ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION placex_delete() RETURNS TRIGGER AS $$ DECLARE + b BOOLEAN; BEGIN ---IF OLD.rank_search < 26 THEN ---RAISE WARNING 'delete % % % % %',OLD.place_id,OLD.osm_type,OLD.osm_id,OLD.class,OLD.type; ---END IF; + IF OLD.rank_address < 30 THEN + + -- mark everything linked to this place for re-indexing + UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = OLD.place_id + and placex.place_id = place_addressline.place_id and indexed_status = 0; - -- mark everything linked to this place for re-indexing - UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = OLD.place_id - and placex.place_id = place_addressline.place_id and indexed_status = 0; + DELETE FROM place_addressline where address_place_id = OLD.place_id; + + END IF; + + IF OLD.rank_address < 26 THEN + b := deleteLocationArea(OLD.partition, OLD.place_id); + END IF; + + IF OLD.name is not null THEN + b := deleteSearchName(OLD.partition, OLD.place_id); + END IF; - -- do the actual delete - DELETE FROM location_area where place_id = OLD.place_id; - DELETE FROM search_name where place_id = OLD.place_id; DELETE FROM place_addressline where place_id = OLD.place_id; - DELETE FROM place_addressline where address_place_id = OLD.place_id; RETURN OLD; @@ -2205,4 +2207,60 @@ CREATE AGGREGATE array_agg(INT[]) initcond = '{}' ); +CREATE OR REPLACE FUNCTION tigger_create_interpolation(linegeo GEOMETRY, in_startnumber INTEGER, + in_endnumber INTEGER, interpolationtype TEXT, + in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER + AS $$ +DECLARE + + startnumber INTEGER; + endnumber INTEGER; + stepsize INTEGER; + housenum INTEGER; + newpoints INTEGER; + numberrange INTEGER; + rangestartnumber INTEGER; + +BEGIN + + IF in_endnumber > in_startnumber THEN + startnumber = in_startnumber; + endnumber = in_endnumber; + ELSE + startnumber = in_endnumber; + endnumber = in_startnumber; + END IF; + + numberrange := endnumber - startnumber; + rangestartnumber := startnumber; + + IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN + startnumber := startnumber + 1; + stepsize := 2; + ELSE + IF (interpolationtype = 'odd' OR interpolationtype = 'even') THEN + stepsize := 2; + ELSE -- everything else assumed to be 'all' + stepsize := 1; + END IF; + END IF; + + -- Filter out really broken tiger data + IF numberrange > 0 AND (numberrange::float/stepsize::float > 500) AND ST_length(linegeo)/(numberrange::float/stepsize::float) < 0.000001 THEN + RAISE WARNING 'Road too short for number range % to % on %, % (%)',startnumber,endnumber,in_street,in_isin,ST_length(linegeo)/(numberrange::float/stepsize::float); + RETURN 0; + END IF; + + newpoints := 0; + FOR housenum IN startnumber..endnumber BY stepsize LOOP + insert into placex (osm_type, osm_id, class, type, admin_level, housenumber, street, isin, postcode, + country_code, parent_place_id, rank_address, rank_search, indexed_status, geometry) + values ('T', nextval('seq_tigger_house'), 'place', 'house', null, housenum, in_street, in_isin, in_postcode, + 'us', null, 30, 30, 1, ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float)); + newpoints := newpoints + 1; + END LOOP; + RETURN newpoints; +END; +$$ +LANGUAGE plpgsql;