X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/2d6f00945ab433f51be5df0576625e29d5b6300c..394a00f52146a1385bf35c2d67e88bbba6fefcc1:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index dc692804..ab3571b0 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -474,31 +474,6 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; -CREATE OR REPLACE FUNCTION get_word_score(wordscores wordscore[], words text[]) RETURNS integer - AS $$ -DECLARE - idxword integer; - idxscores integer; - result integer; -BEGIN - IF (wordscores is null OR words is null) THEN - RETURN 0; - END IF; - - result := 0; - FOR idxword in 1 .. array_upper(words, 1) LOOP - FOR idxscores in 1 .. array_upper(wordscores, 1) LOOP - IF wordscores[idxscores].word = words[idxword] THEN - result := result + wordscores[idxscores].score; - END IF; - END LOOP; - END LOOP; - - RETURN result; -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - CREATE OR REPLACE FUNCTION get_country_code(place geometry) RETURNS TEXT AS $$ DECLARE @@ -548,21 +523,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; $$ @@ -787,8 +747,73 @@ END; $$ LANGUAGE plpgsql; +-- find the parant road of an interpolation +CREATE OR REPLACE FUNCTION get_interpolation_parent(wayid BIGINT, street TEXT, place TEXT, + partition INTEGER, centroid GEOMETRY, geom GEOMETRY) +RETURNS BIGINT AS $$ +DECLARE + addr_street TEXT; + addr_place TEXT; + parent_place_id BIGINT; + address_street_word_ids INTEGER[]; + + waynodes BIGINT[]; -CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT) RETURNS INTEGER + location RECORD; +BEGIN + addr_street = street; + addr_place = place; + + IF addr_street is null and addr_place is null THEN + select nodes from planet_osm_ways where id = wayid INTO waynodes; + FOR location IN SELECT street, addr_place from placex + where osm_type = 'N' and osm_id = ANY(nodes) + and (street is not null or addr_place is not null) + and indexed_status < 100 + limit 1 LOOP + addr_street = location.street; + addr_place = location.addr_place; + END LOOP; + END IF; + + IF addr_street IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(addr_street)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT place_id from getNearestNamedRoadFeature(partition, centroid, address_street_word_ids) LOOP + parent_place_id := location.place_id; + END LOOP; + END IF; + END IF; + + IF parent_place_id IS NULL AND addr_place IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(addr_place)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT place_id from getNearestNamedPlaceFeature(partition, centroid, address_street_word_ids) LOOP + parent_place_id := location.place_id; + END LOOP; + END IF; + END IF; + + IF parent_place_id is null THEN + FOR location IN SELECT place_id FROM placex + WHERE ST_DWithin(geom, placex.geometry, 0.001) and placex.rank_search = 26 + ORDER BY (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+ + ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+ + ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,1))) ASC limit 1 + LOOP + parent_place_id := location.place_id; + END LOOP; + END IF; + + RETURN parent_place_id; +END; +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT, + parent_place_id BIGINT, partition INTEGER, + country_code TEXT, geometry_sector INTEGER, + defpostalcode TEXT, geom GEOMETRY) RETURNS INTEGER AS $$ DECLARE @@ -806,8 +831,6 @@ DECLARE linegeo GEOMETRY; splitline GEOMETRY; sectiongeo GEOMETRY; - search_place_id BIGINT; - defpostalcode TEXT; BEGIN IF interpolationtype = 'odd' OR interpolationtype = 'even' THEN @@ -826,13 +849,7 @@ BEGIN RETURN 0; END IF; - select postcode, geometry from placex where osm_type = 'W' and osm_id = wayid - INTO defpostalcode, linegeo; - - IF ST_GeometryType(linegeo) != 'ST_LineString' THEN - RETURN 0; - END IF; - + linegeo := geom; startnumber := NULL; newpoints := 0; @@ -842,19 +859,18 @@ BEGIN -- it is guaranteed to be the original node. For place/house types use the -- one with the smallest id because the original node was created first. -- Ignore all nodes marked for deletion. (Might happen when the type changes.) - select place_id from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and indexed_status < 100 order by (type = 'house'),place_id limit 1 INTO search_place_id; - IF search_place_id IS NOT NULL THEN - select * from placex where place_id = search_place_id INTO nextnode; + select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT + and indexed_status < 100 + order by (type = 'house'),place_id limit 1 INTO nextnode; + IF nextnode.place_id IS NOT NULL THEN - 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 +878,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; @@ -884,19 +900,25 @@ BEGIN FOR housenum IN startnumber..endnumber BY stepsize LOOP -- this should really copy postcodes but it puts a huge burden on -- the system for no big benefit ideally postcodes should move up to the way - insert into placex (osm_type, osm_id, class, type, admin_level, + insert into placex (place_id, partition, osm_type, osm_id, class, type, admin_level, housenumber, street, addr_place, isin, postcode, country_code, parent_place_id, rank_address, rank_search, indexed_status, geometry) - values ('N', prevnode.osm_id, 'place', 'house', prevnode.admin_level, + values (nextval('seq_place'), partition, 'N', prevnode.osm_id, 'place', 'house', prevnode.admin_level, housenum, prevnode.street, prevnode.addr_place, prevnode.isin, coalesce(prevnode.postcode, defpostalcode), prevnode.country_code, prevnode.parent_place_id, prevnode.rank_address, prevnode.rank_search, - 1, ST_LineInterpolatePoint(sectiongeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float)); + 0, ST_LineInterpolatePoint(sectiongeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float)); newpoints := newpoints + 1; --RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum; 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; @@ -922,6 +944,10 @@ DECLARE BEGIN --DEBUG: RAISE WARNING '% %',NEW.osm_type,NEW.osm_id; + IF place_id is not null THEN + RETURN NEW; -- already indexed (happens for interpolated addresses + END IF; + -- just block these IF NEW.class in ('landuse','natural') and NEW.name is null THEN -- RAISE WARNING 'empty landuse %',NEW.osm_id; @@ -1088,8 +1114,6 @@ BEGIN NEW.rank_address := NEW.rank_search; ELSEIF NEW.type in ('houses') THEN -- can't guarantee all required nodes loaded yet due to caching in osm2pgsql - -- insert new point into place for each derived building - --i := create_interpolation(NEW.osm_id, NEW.housenumber); NEW.rank_search := 28; NEW.rank_address := 0; END IF; @@ -1107,7 +1131,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 +1147,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; @@ -1255,8 +1283,6 @@ DECLARE search_maxdistance FLOAT[]; search_mindistance FLOAT[]; address_havelevel BOOLEAN[]; --- search_scores wordscore[]; --- search_scores_pos INTEGER; i INTEGER; iMax FLOAT; @@ -1281,6 +1307,7 @@ DECLARE location_distance FLOAT; location_parent GEOMETRY; location_isaddress BOOLEAN; + location_keywords INTEGER[]; tagpairid INTEGER; @@ -1321,7 +1348,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; @@ -1330,17 +1356,12 @@ BEGIN RETURN NEW; END IF; - IF NEW.class = 'place' AND NEW.type = 'houses' THEN - i := create_interpolation(NEW.osm_id, NEW.housenumber); - RETURN NEW; - END IF; - -- Speed up searches - just use the centroid of the feature -- cheaper but less acurate 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 @@ -1363,19 +1384,31 @@ BEGIN END IF; NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid); + -- interpolations XXXXX + IF NEW.class = 'place' AND NEW.type = 'houses'THEN + IF osm_type = 'W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN + NEW.parent_place_id := get_interpolation_parent(NEW.osm_id, NEW.street, NEW.addr_place, + NEW.partition, place_centroid, NEW.geometry); + IF NEW.parent_place_id is not null THEN + i := create_interpolation(NEW.osm_id, NEW.housenumber); + END IF; + END IF; + RETURN NEW; + END IF; + -- waterway ways are linked when they are part of a relation and have the same class/type IF NEW.osm_type = 'R' and NEW.class = 'waterway' THEN - FOR relation IN select * from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[] + FOR relation_members IN select members from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[] LOOP - FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP - IF relation.members[i+1] in ('', 'main_stream', 'side_stream') AND substring(relation.members[i],1,1) = 'w' THEN - --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation.parts[i]; - FOR location IN SELECT * FROM placex - WHERE osm_type = 'W' and osm_id = substring(relation.members[i],2,200)::bigint + FOR i IN 1..array_upper(relation_members, 1) BY 2 LOOP + IF relation_members[i+1] in ('', 'main_stream', 'side_stream') AND substring(relation_members[i],1,1) = 'w' THEN + --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation.members[i]; + FOR linked_node_id IN SELECT place_id FROM placex + WHERE osm_type = 'W' and osm_id = substring(relation_members[i],2,200)::bigint and class = NEW.class and type = NEW.type - and ( relation.members[i+1] != 'side_stream' or NEW.name->'name' = name->'name') + and ( relation_members[i+1] != 'side_stream' or NEW.name->'name' = name->'name') LOOP - UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = location.place_id; + UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id; END LOOP; END IF; END LOOP; @@ -1828,10 +1861,26 @@ BEGIN IF location.rank_address != location_rank_search THEN location_rank_search := location.rank_address; - location_distance := location.distance * 1.5; + IF location.isguess THEN + location_distance := location.distance * 1.5; + ELSE + IF location.rank_address <= 12 THEN + -- for county and above, if we have an area consider that exact + -- (It would be nice to relax the constraint for places close to + -- the boundary but we'd need the exact geometry for that. Too + -- expensive.) + location_distance = 0; + ELSE + -- Below county level remain slightly fuzzy. + location_distance := location.distance * 0.5; + END IF; + END IF; + ELSE + CONTINUE WHEN location.keywords <@ location_keywords; END IF; IF location.distance < location_distance OR NOT location.isguess THEN + location_keywords := location.keywords; location_isaddress := NOT address_havelevel[location.rank_address]; IF location_isaddress AND location.isguess AND location_parent IS NOT NULL THEN @@ -2329,29 +2378,6 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; ---CREATE OR REPLACE FUNCTION get_connected_ways(way_ids INTEGER[]) RETURNS SETOF planet_osm_ways --- AS $$ ---DECLARE --- searchnodes INTEGER[]; --- location RECORD; --- j INTEGER; ---BEGIN --- --- searchnodes := '{}'; --- FOR j IN 1..array_upper(way_ids, 1) LOOP --- FOR location IN --- select nodes from planet_osm_ways where id = way_ids[j] LIMIT 1 --- LOOP --- IF not (ARRAY[location.nodes] <@ searchnodes) THEN --- searchnodes := searchnodes || location.nodes; --- END IF; --- END LOOP; --- END LOOP; --- --- RETURN QUERY select * from planet_osm_ways where nodes && searchnodes and NOT ARRAY[id] <@ way_ids; ---END; ---$$ ---LANGUAGE plpgsql IMMUTABLE; CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id BIGINT) RETURNS TEXT AS $$ @@ -2591,86 +2617,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