-- RAISE WARNING 'get_country_code, start: %', ST_AsText(place_centre);
-- Try for a OSM polygon
- FOR nearcountry IN select country_code from location_area_country where country_code is not null and not isguess and st_covers(geometry, place_centre) limit 1
+ FOR nearcountry IN select country_code from location_area_country where country_code is not null and st_covers(geometry, place_centre) limit 1
LOOP
RETURN nearcountry.country_code;
END LOOP;
$$
LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION osmline_reinsert(node_id BIGINT, geom GEOMETRY)
+ RETURNS BOOLEAN
+ AS $$
+DECLARE
+ existingline RECORD;
+BEGIN
+ SELECT w.id FROM planet_osm_ways w, location_property_osmline p
+ WHERE p.linegeo && geom and p.osm_id = w.id and p.indexed_status = 0
+ and node_id = any(w.nodes) INTO existingline;
+
+ IF existingline.id is not NULL THEN
+ DELETE FROM location_property_osmline WHERE osm_id = existingline.id;
+ INSERT INTO location_property_osmline (osm_id, address, linegeo)
+ SELECT osm_id, address, geometry FROM place
+ WHERE osm_type = 'W' and osm_id = existingline.id;
+ END IF;
+
+ RETURN true;
+END;
+$$
+LANGUAGE plpgsql;
+
CREATE OR REPLACE FUNCTION osmline_insert() RETURNS TRIGGER
AS $$
END IF;
-- some postcorrections
- IF NEW.class = 'place' THEN
- IF NEW.type in ('continent', 'sea', 'country', 'state') AND NEW.osm_type = 'N' THEN
- NEW.rank_address := 0;
- END IF;
- ELSEIF NEW.class = 'waterway' AND NEW.osm_type = 'R' THEN
+ IF NEW.class = 'waterway' AND NEW.osm_type = 'R' THEN
-- Slightly promote waterway relations so that they are processed
-- before their members.
NEW.rank_search := NEW.rank_search - 1;
NEW.country_code := NULL;
END IF;
--- Block import below rank 22
--- IF NEW.rank_search > 22 THEN
--- RETURN NULL;
--- END IF;
-
--DEBUG: RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
RETURN NEW; -- %DIFFUPDATES% The following is not needed until doing diff updates, and slows the main index process down
- IF NEW.rank_address > 0 THEN
+ IF NEW.osm_type = 'N' and NEW.rank_search > 28 THEN
+ -- might be part of an interpolation
+ result := osmline_reinsert(NEW.osm_id, NEW.geometry);
+ ELSEIF NEW.rank_address > 0 THEN
IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN
-- Performance: We just can't handle re-indexing for country level changes
IF st_area(NEW.geometry) < 1 THEN
DECLARE
place_centroid GEOMETRY;
+ near_centroid GEOMETRY;
search_maxdistance FLOAT[];
search_mindistance FLOAT[];
END IF;
--DEBUG: RAISE WARNING 'Copy over address tags';
+ -- housenumber is a computed field, so start with an empty value
+ NEW.housenumber := NULL;
IF NEW.address is not NULL THEN
IF NEW.address ? 'conscriptionnumber' THEN
i := getorcreate_housenumber_id(make_standard_name(NEW.address->'conscriptionnumber'));
-- Speed up searches - just use the centroid of the feature
-- cheaper but less acurate
place_centroid := ST_PointOnSurface(NEW.geometry);
+ -- For searching near features rather use the centroid
+ near_centroid := ST_Envelope(NEW.geometry);
NEW.centroid := null;
NEW.postcode := null;
--DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(place_centroid);
END LOOP;
NEW.importance := null;
- select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.country_code) INTO NEW.wikipedia,NEW.importance;
- IF NEW.importance IS NULL THEN
- select language||':'||title,importance from wikipedia_article where osm_type = NEW.osm_type and osm_id = NEW.osm_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
- END IF;
+ SELECT importance, wikipedia
+ FROM compute_importance(NEW.extratags, NEW.country_code, NEW.osm_type, NEW.osm_id)
+ INTO NEW.wikipedia,NEW.importance;
--DEBUG: RAISE WARNING 'Importance computed from wikipedia: %', NEW.importance;
IF NEW.parent_place_id IS NULL AND 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
- SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id;
+ SELECT place_id from getNearestNamedRoadFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id;
END IF;
END IF;
--DEBUG: RAISE WARNING 'Checked for addr:street (%)', NEW.parent_place_id;
IF NEW.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
- SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id;
+ SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id;
END IF;
END IF;
--DEBUG: RAISE WARNING 'Checked for addr:place (%)', NEW.parent_place_id;
IF location.address ? 'street' THEN
address_street_word_ids := get_name_ids(make_standard_name(location.address->'street'));
IF address_street_word_ids IS NOT NULL THEN
- SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id;
+ SELECT place_id from getNearestNamedRoadFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id;
EXIT WHEN NEW.parent_place_id is not NULL;
END IF;
END IF;
IF location.address ? 'place' THEN
address_street_word_ids := get_name_ids(make_standard_name(location.address->'place'));
IF address_street_word_ids IS NOT NULL THEN
- SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id;
+ SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id;
EXIT WHEN NEW.parent_place_id is not NULL;
END IF;
END IF;
-- Still nothing, just use the nearest road
IF NEW.parent_place_id IS NULL THEN
- SELECT place_id FROM getNearestRoadFeature(NEW.partition, place_centroid) INTO NEW.parent_place_id;
+ SELECT place_id FROM getNearestRoadFeature(NEW.partition, near_centroid) INTO NEW.parent_place_id;
END IF;
--DEBUG: RAISE WARNING 'Checked for nearest way (%)', NEW.parent_place_id;
NEW.postcode := location.postcode;
END IF;
IF NEW.postcode is null THEN
- NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid);
+ NEW.postcode := get_nearest_postcode(NEW.country_code, NEW.geometry);
END IF;
END IF;
-- mark the linked place (excludes from search results)
UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
- -- keep a note of the node id in case we need it for wikipedia in a bit
- linked_node_id := linkedPlacex.osm_id;
- select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance;
+ select wikipedia, importance
+ FROM compute_importance(linkedPlacex.extratags, NEW.country_code,
+ 'N', linkedPlacex.osm_id)
+ INTO linked_wikipedia,linked_importance;
--DEBUG: RAISE WARNING 'Linked label member';
END LOOP;
-- mark the linked place (excludes from search results)
UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
- -- keep a note of the node id in case we need it for wikipedia in a bit
- linked_node_id := linkedPlacex.osm_id;
- select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance;
+ select wikipedia, importance
+ FROM compute_importance(linkedPlacex.extratags, NEW.country_code,
+ 'N', linkedPlacex.osm_id)
+ INTO linked_wikipedia,linked_importance;
--DEBUG: RAISE WARNING 'Linked admin_center';
END IF;
-- mark the linked place (excludes from search results)
UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
- -- keep a note of the node id in case we need it for wikipedia in a bit
- linked_node_id := linkedPlacex.osm_id;
- select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance;
+ select wikipedia, importance
+ FROM compute_importance(linkedPlacex.extratags, NEW.country_code,
+ 'N', linkedPlacex.osm_id)
+ INTO linked_wikipedia,linked_importance;
--DEBUG: RAISE WARNING 'Linked named place';
END LOOP;
END IF;
(NEW.importance is null or NEW.importance < linked_importance) THEN
NEW.importance = linked_importance;
END IF;
-
- -- Still null? how about looking it up by the node id
- IF NEW.importance IS NULL THEN
- --DEBUG: RAISE WARNING 'Looking up importance by linked node id';
- select language||':'||title,importance from wikipedia_article where osm_type = 'N'::char(1) and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
- END IF;
-
END IF;
-- make sure all names are in the word table
-- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress;
-- Add it to the list of search terms
- IF NOT %REVERSE-ONLY% AND location.rank_search > 4 THEN
+ IF NOT %REVERSE-ONLY% THEN
nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
END IF;
INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address)
indexed_status = 2,
geometry = NEW.geometry
where place_id = existingplacex.place_id;
-
-- if a node(=>house), which is part of a interpolation line, changes (e.g. the street attribute) => mark this line for reparenting
-- (already here, because interpolation lines are reindexed before nodes, so in the second call it would be too late)
- IF NEW.osm_type='N' and NEW.class='place' and NEW.type='house' THEN
- -- Is this node part of an interpolation line? search for it in location_property_osmline and mark the interpolation line for reparenting
- update location_property_osmline p set indexed_status = 2 from planet_osm_ways w where p.linegeo && NEW.geometry and p.osm_id = w.id and NEW.osm_id = any(w.nodes);
+ IF NEW.osm_type='N'
+ and (coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
+ or existing.geometry::text != NEW.geometry::text)
+ THEN
+ result:= osmline_reinsert(NEW.osm_id, NEW.geometry);
END IF;
-- linked places should get potential new naming and addresses
searchhousename HSTORE;
searchrankaddress INTEGER;
searchpostcode TEXT;
+ postcode_isaddress BOOL;
searchclass TEXT;
searchtype TEXT;
countryname HSTORE;
-- The place ein question might not have a direct entry in place_addressline.
-- Look for the parent of such places then and save if in for_place_id.
+ postcode_isaddress := true;
+
-- first query osmline (interpolation lines)
IF in_housenumber >= 0 THEN
SELECT parent_place_id, country_code, in_housenumber::text, 30, postcode,
-- postcode table
IF for_place_id IS NULL THEN
- SELECT parent_place_id, country_code, rank_address, postcode, 'place', 'postcode'
+ SELECT parent_place_id, country_code, rank_search, postcode, 'place', 'postcode'
FROM location_postcode
WHERE place_id = in_place_id
INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode,
searchcountrycode := location.country_code;
END IF;
IF location.type in ('postcode', 'postal_code') THEN
- location.isaddress := FALSE;
+ postcode_isaddress := false;
+ IF location.osm_type != 'R' THEN
+ location.isaddress := FALSE;
+ END IF;
END IF;
countrylocation := ROW(location.place_id, location.osm_type, location.osm_id,
location.name, location.class, location.type,
IF searchpostcode IS NOT NULL THEN
location := ROW(null, null, null, hstore('ref', searchpostcode), 'place',
- 'postcode', null, true, true, 5, 0)::addressline;
+ 'postcode', null, false, postcode_isaddress, 5, 0)::addressline;
RETURN NEXT location;
END IF;
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION get_searchrank_label(rank INTEGER) RETURNS TEXT
- AS $$
-DECLARE
-BEGIN
- IF rank < 2 THEN
- RETURN 'Continent';
- ELSEIF rank < 4 THEN
- RETURN 'Sea';
- ELSEIF rank < 8 THEN
- RETURN 'Country';
- ELSEIF rank < 12 THEN
- RETURN 'State';
- ELSEIF rank < 16 THEN
- RETURN 'County';
- ELSEIF rank = 16 THEN
- RETURN 'City';
- ELSEIF rank = 17 THEN
- RETURN 'Town / Island';
- ELSEIF rank = 18 THEN
- RETURN 'Village / Hamlet';
- ELSEIF rank = 20 THEN
- RETURN 'Suburb';
- ELSEIF rank = 21 THEN
- RETURN 'Postcode Area';
- ELSEIF rank = 22 THEN
- RETURN 'Croft / Farm / Locality / Islet';
- ELSEIF rank = 23 THEN
- RETURN 'Postcode Area';
- ELSEIF rank = 25 THEN
- RETURN 'Postcode Point';
- ELSEIF rank = 26 THEN
- RETURN 'Street / Major Landmark';
- ELSEIF rank = 27 THEN
- RETURN 'Minory Street / Path';
- ELSEIF rank = 28 THEN
- RETURN 'House / Building';
- ELSE
- RETURN 'Other: '||rank;
- END IF;
-
-END;
-$$
-LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION get_addressrank_label(rank INTEGER) RETURNS TEXT
- AS $$
-DECLARE
-BEGIN
- IF rank = 0 THEN
- RETURN 'None';
- ELSEIF rank < 2 THEN
- RETURN 'Continent';
- ELSEIF rank < 4 THEN
- RETURN 'Sea';
- ELSEIF rank = 5 THEN
- RETURN 'Postcode';
- ELSEIF rank < 8 THEN
- RETURN 'Country';
- ELSEIF rank < 12 THEN
- RETURN 'State';
- ELSEIF rank < 16 THEN
- RETURN 'County';
- ELSEIF rank = 16 THEN
- RETURN 'City';
- ELSEIF rank = 17 THEN
- RETURN 'Town / Village / Hamlet';
- ELSEIF rank = 20 THEN
- RETURN 'Suburb';
- ELSEIF rank = 21 THEN
- RETURN 'Postcode Area';
- ELSEIF rank = 22 THEN
- RETURN 'Croft / Farm / Locality / Islet';
- ELSEIF rank = 23 THEN
- RETURN 'Postcode Area';
- ELSEIF rank = 25 THEN
- RETURN 'Postcode Point';
- ELSEIF rank = 26 THEN
- RETURN 'Street / Major Landmark';
- ELSEIF rank = 27 THEN
- RETURN 'Minory Street / Path';
- ELSEIF rank = 28 THEN
- RETURN 'House / Building';
- ELSE
- RETURN 'Other: '||rank;
- END IF;
-
-END;
-$$
-LANGUAGE plpgsql;
-
CREATE OR REPLACE FUNCTION aux_create_property(pointgeo GEOMETRY, in_housenumber TEXT,
in_street TEXT, in_isin TEXT, in_postcode TEXT, in_countrycode char(2)) RETURNS INTEGER
AS $$
$$
LANGUAGE plpgsql;
+DROP TYPE IF EXISTS place_importance CASCADE;
+create type place_importance as (
+ importance FLOAT,
+ wikipedia TEXT
+);
+
+CREATE OR REPLACE FUNCTION compute_importance(extratags HSTORE, country_code varchar(2), osm_type varchar(1), osm_id BIGINT)
+ RETURNS place_importance
+ AS $$
+DECLARE
+ match RECORD;
+ result place_importance;
+BEGIN
+ FOR match IN SELECT * FROM get_wikipedia_match(extratags, country_code) LOOP
+ result.importance := match.importance;
+ result.wikipedia := match.language || ':' || match.title;
+ RETURN result;
+ END LOOP;
+
+ IF extratags ? 'wikidata' THEN
+ FOR match IN SELECT * FROM wikipedia_article
+ WHERE wd_page_title = extratags->'wikidata'
+ ORDER BY importance DESC limit 1 LOOP
+ result.importance := match.importance;
+ result.wikipedia := match.language || ':' || match.title;
+ RETURN result;
+ END LOOP;
+ END IF;
+
+ RETURN null;
+END;
+$$
+LANGUAGE plpgsql;
+
CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT, maxdepth INTEGER)
RETURNS SETOF GEOMETRY
AS $$
projects / nominatim.git / blobdiff