X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/a25810d31760550def09eed720a42bbd17c116b3..3ce0917874b55d8013d8bb37113e42dfb0c8b452:/sql/functions.sql?ds=sidebyside diff --git a/sql/functions.sql b/sql/functions.sql index fdaaad4a..afa687be 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -766,9 +766,9 @@ BEGIN 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) + FOR location IN SELECT placex.street, placex.addr_place from placex + where osm_type = 'N' and osm_id = ANY(waynodes) + and (placex.street is not null or placex.addr_place is not null) and indexed_status < 100 limit 1 LOOP addr_street = location.street; @@ -805,13 +805,17 @@ BEGIN END LOOP; END IF; + IF parent_place_id is null THEN + RETURN 0; + 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, + parent_id BIGINT, partition INTEGER, country_code TEXT, geometry_sector INTEGER, defpostalcode TEXT, geom GEOMETRY) RETURNS INTEGER AS $$ @@ -831,8 +835,12 @@ DECLARE linegeo GEOMETRY; splitline GEOMETRY; sectiongeo GEOMETRY; + pointgeo GEOMETRY; BEGIN + delete from placex where osm_type = 'W' and osm_id = wayid + and class = 'place' and type = 'address'; + IF interpolationtype = 'odd' OR interpolationtype = 'even' THEN stepsize := 2; ELSEIF interpolationtype = 'all' THEN @@ -861,7 +869,7 @@ BEGIN -- Ignore all nodes marked for deletion. (Might happen when the type changes.) 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; + order by (type = 'address'),place_id limit 1 INTO nextnode; IF nextnode.place_id IS NOT NULL THEN IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN @@ -897,21 +905,24 @@ BEGIN END IF; endnumber := endnumber - 1; + -- keep for compatibility with previous versions delete from placex where osm_type = 'N' and osm_id = prevnode.osm_id - and place_id != prevnode.place_id; + and place_id != prevnode.place_id and class = 'place' + and type = 'house'; FOR housenum IN startnumber..endnumber BY stepsize LOOP + pointgeo := ST_LineInterpolatePoint(sectiongeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float); insert into placex (place_id, partition, osm_type, osm_id, class, type, admin_level, housenumber, postcode, country_code, parent_place_id, rank_address, rank_search, indexed_status, indexed_date, geometry_sector, - geometry) - values (nextval('seq_place'), partition, 'N', prevnode.osm_id, + calculated_country_code, centroid, geometry) + values (nextval('seq_place'), partition, 'W', wayid, 'place', 'address', prevnode.admin_level, housenum, coalesce(prevnode.postcode, defpostalcode), - prevnode.country_code, parent_place_id, 30, 30, - 0, now(), geometry_sector, calculated_country_code, - ST_LineInterpolatePoint(sectiongeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float)); + prevnode.country_code, parent_id, 30, 30, + 0, now(), geometry_sector, country_code, + pointgeo, pointgeo); newpoints := newpoints + 1; --RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum; END LOOP; @@ -1257,6 +1268,7 @@ BEGIN END IF; END IF; + -- add to tables for special search -- Note: won't work on initial import because the classtype tables -- do not yet exist. It won't hurt either. @@ -1394,14 +1406,14 @@ BEGIN END IF; NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid); - -- interpolations XXXXX + -- interpolations IF NEW.class = 'place' AND NEW.type = 'houses'THEN - IF osm_type = 'W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN + IF NEW.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; + i := create_interpolation(NEW.osm_id, NEW.housenumber, NEW.parent_place_id, + NEW.partition, NEW.calculated_country_code, + NEW.geometry_sector, NEW.postcode, NEW.geometry); END IF; RETURN NEW; END IF; @@ -1476,41 +1488,67 @@ BEGIN NEW.parent_place_id := null; - -- to do that we have to find our parent road - -- Copy data from linked items (points on ways, addr:street links, relations) - -- Note that addr:street links can only be indexed once the street itself is indexed - IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN + -- if we have a POI and there is no address information, + -- see if we can get it from a surrounding building + IF NEW.osm_type = 'N' AND NEW.street IS NULL AND NEW.addr_place IS NULL + AND NEW.housenumber IS NULL THEN + FOR location IN select * from placex where ST_Covers(geometry, place_centroid) + and (housenumber is not null or street is not null or addr_place is not null) + and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') + limit 1 + LOOP + NEW.housenumber := location.housenumber; + NEW.street := location.street; + NEW.addr_place := location.addr_place; + END LOOP; + END IF; - -- if there is no address information, see if we can get it from a surrounding building - IF NEW.street IS NULL AND NEW.addr_place IS NULL AND NEW.housenumber IS NULL THEN - FOR location IN select * from placex where ST_Covers(geometry, place_centroid) and rank_search > 28 and (housenumber is not null or street is not null or addr_place is not null) AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') - LOOP - NEW.housenumber := location.housenumber; - NEW.street := location.street; - NEW.addr_place := location.addr_place; - END LOOP; - END IF; + -- We have to find our parent road. + -- Copy data from linked items (points on ways, addr:street links, relations) - -- Is this node part of a relation? - FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['n'||NEW.osm_id] + -- Is this object part of a relation? + FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY[lower(NEW.osm_type)||NEW.osm_id] LOOP -- At the moment we only process one type of relation - associatedStreet - IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN + IF relation.tags @> ARRAY['associatedStreet'] THEN FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN --RAISE WARNING 'node in relation %',relation; - SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint + SELECT place_id from placex where osm_type = 'W' + and osm_id = substring(relation.members[i],2,200)::bigint and rank_search = 26 and name is not null INTO NEW.parent_place_id; END IF; END LOOP; END IF; - END LOOP; + END LOOP; + + + -- Note that addr:street links can only be indexed once the street itself is indexed + IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(NEW.street)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := location.place_id; + END LOOP; + END IF; + END IF; + + IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := location.place_id; + END LOOP; + END IF; + END IF; + + IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN --RAISE WARNING 'x1'; -- Is this node part of a way? - FOR way IN select id from planet_osm_ways where nodes @> ARRAY[NEW.osm_id] LOOP ---RAISE WARNING '%', way; - FOR location IN select * from placex where osm_type = 'W' and osm_id = way.id + FOR location IN select p.* from placex p, planet_osm_ways w + where p.osm_type = 'W' and p.rank_search >= 26 + and p.geometry && NEW.geometry and p.osm_id = w.id and NEW.osm_id = any(w.nodes) LOOP --RAISE WARNING '%', location; -- Way IS a road then we are on it - that must be our road @@ -1519,6 +1557,11 @@ BEGIN NEW.parent_place_id := location.place_id; END IF; + -- If this way is a street interpolation line then it is probably as good as we are going to get + IF NEW.parent_place_id IS NULL AND location.class = 'place' and location.type='houses' THEN + NEW.parent_place_id := location.parent_place_id; + END IF; + -- Is the WAY part of a relation IF NEW.parent_place_id IS NULL THEN FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id] @@ -1534,73 +1577,29 @@ BEGIN END LOOP; END IF; END LOOP; - END IF; - - -- If the way contains an explicit name of a street copy it - -- Slightly less strict then above because data is copied from any object. - IF NEW.street IS NULL AND NEW.addr_place IS NULL THEN ---RAISE WARNING 'node in way that has a streetname %',location; - NEW.street := location.street; - NEW.addr_place := location.addr_place; END IF; - -- If this way is a street interpolation line then it is probably as good as we are going to get - IF NEW.parent_place_id IS NULL AND NEW.street IS NULL AND NEW.addr_place IS NULL AND location.class = 'place' and location.type='houses' THEN - -- Try and find a way that is close roughly parellel to this line - FOR relation IN SELECT place_id FROM placex - WHERE ST_DWithin(location.geometry, placex.geometry, 0.001) and placex.rank_search = 26 - and st_geometrytype(location.geometry) in ('ST_LineString') - ORDER BY (ST_distance(placex.geometry, ST_LineInterpolatePoint(location.geometry,0))+ - ST_distance(placex.geometry, ST_LineInterpolatePoint(location.geometry,0.5))+ - ST_distance(placex.geometry, ST_LineInterpolatePoint(location.geometry,1))) ASC limit 1 - LOOP ---RAISE WARNING 'using nearest street to address interpolation line,0.001 %',relation; - NEW.parent_place_id := relation.place_id; - END LOOP; + -- If the way mentions a street or place address, try that for parenting. + IF NEW.parent_place_id IS NULL AND location.street IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(location.street)); + IF address_street_word_ids IS NOT NULL THEN + FOR linkedplacex IN SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := linkedplacex.place_id; + END LOOP; + END IF; END IF; - END LOOP; - END LOOP; - - END IF; - ---RAISE WARNING 'x2'; - - IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'W' THEN - -- Is this way part of a relation? - FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['w'||NEW.osm_id] - LOOP - -- At the moment we only process one type of relation - associatedStreet - IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN - FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP - IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN ---RAISE WARNING 'way that is in a relation %',relation; - SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint - and rank_search = 26 and name is not null INTO NEW.parent_place_id; - END IF; - END LOOP; + IF NEW.parent_place_id IS NULL AND location.addr_place IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(location.addr_place)); + IF address_street_word_ids IS NOT NULL THEN + FOR linkedplacex IN SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := linkedplacex.place_id; + END LOOP; + END IF; END IF; - END LOOP; - END IF; - ---RAISE WARNING 'x3 %',NEW.parent_place_id; - IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(NEW.street)); - IF address_street_word_ids IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP - NEW.parent_place_id := location.place_id; - END LOOP; - END IF; - END IF; + END LOOP; - IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place)); - IF address_street_word_ids IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP - NEW.parent_place_id := location.place_id; - END LOOP; - END IF; END IF; --RAISE WARNING 'x4 %',NEW.parent_place_id; @@ -1643,7 +1642,7 @@ 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 - IF NEW.rank_search <= 25 THEN + IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry); END IF; @@ -1735,7 +1734,7 @@ BEGIN END IF; -- Name searches can be done for ways as well as relations - IF NEW.osm_type in ('W','R') AND NEW.rank_search < 26 THEN + IF NEW.osm_type in ('W','R') AND NEW.rank_search < 26 AND NEW.rank_address > 0 THEN -- not found one yet? how about doing a name search IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' THEN @@ -1979,7 +1978,7 @@ BEGIN -- if we have a name add this to the name search table IF NEW.name IS NOT NULL THEN - IF NEW.rank_search <= 25 THEN + IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry); END IF; @@ -2088,6 +2087,11 @@ BEGIN -- mark for delete UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type; + -- interpolations are special + IF OLD.class = 'place' and OLD.type = 'houses' THEN + UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = 'place' and type = 'address'; + END IF; + RETURN OLD; END; @@ -2114,6 +2118,11 @@ BEGIN --DEBUG: RAISE WARNING '%', existingplacex; END IF; + -- remove operator tag for most places, messes too much with search_name indexes + IF NEW.class not in ('amenity', 'shop') THEN + NEW.name := delete(NEW.name, 'operator'); + END IF; + -- Just block these - lots and pointless IF NEW.class in ('landuse','natural') and NEW.name is null THEN -- if the name tag was removed, older versions might still be lurking in the place table @@ -2304,6 +2313,12 @@ BEGIN END IF; + -- refuse to update multiplpoygons with too many objects, too much of a performance hit + IF ST_NumGeometries(NEW.geometry) > 2000 THEN + RAISE WARNING 'Dropping update of % % because of geometry complexity.', NEW.osm_type, NEW.osm_id; + RETURN NULL; + END IF; + IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '') OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::text, '') OR coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '') @@ -2356,6 +2371,11 @@ BEGIN END IF; + -- for interpolations invalidate all nodes on the line + IF NEW.class = 'place' and NEW.type = 'houses' and NEW.osm_type = 'W' THEN + update placex p set indexed_status = 2 from planet_osm_ways w where w.id = NEW.osm_id and p.osm_type = 'N' and p.osm_id = any(w.nodes); + END IF; + -- Abort the add (we modified the existing place instead) RETURN NULL;