-- Trigger functions for the placex table.
+-- Retrieve the data needed by the indexer for updating the place.
+--
+-- Return parameters:
+-- name list of names
+-- address list of address tags, either from the object or a surrounding
+-- building
+-- country_feature If the place is a country feature, this contains the
+-- country code, otherwise it is null.
+CREATE OR REPLACE FUNCTION placex_prepare_update(p placex,
+ OUT name HSTORE,
+ OUT address HSTORE,
+ OUT country_feature VARCHAR)
+ AS $$
+BEGIN
+ -- For POI nodes, check if the address should be derived from a surrounding
+ -- building.
+ IF p.rank_search < 30 OR p.osm_type != 'N' OR p.address is not null THEN
+ RAISE WARNING 'self address for % %', p.osm_type, p.osm_id;
+ address := p.address;
+ ELSE
+ -- The additional && condition works around the misguided query
+ -- planner of postgis 3.0.
+ SELECT placex.address || hstore('_inherited', '') INTO address
+ FROM placex
+ WHERE ST_Covers(geometry, p.centroid)
+ and geometry && p.centroid
+ and placex.address is not null
+ and (placex.address ? 'housenumber' or placex.address ? 'street' or placex.address ? 'place')
+ and rank_search = 30 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
+ LIMIT 1;
+ RAISE WARNING 'other address for % %: % (%)', p.osm_type, p.osm_id, address, p.centroid;
+ END IF;
+
+ address := address - '_unlisted_place'::TEXT;
+ name := p.name;
+
+ country_feature := CASE WHEN p.admin_level = 2
+ and p.class = 'boundary' and p.type = 'administrative'
+ and p.osm_type = 'R'
+ THEN p.country_code
+ ELSE null
+ END;
+END;
+$$
+LANGUAGE plpgsql STABLE;
+
+
-- Find the parent road of a POI.
--
-- \returns Place ID of parent object or NULL if none
END IF;
IF bnd.name ? 'name' THEN
- bnd_name := make_standard_name(bnd.name->'name');
+ bnd_name := lower(bnd.name->'name');
IF bnd_name = '' THEN
bnd_name := NULL;
END IF;
IF bnd.extratags ? 'place' and bnd_name is not null THEN
FOR linked_placex IN
SELECT * FROM placex
- WHERE make_standard_name(name->'name') = bnd_name
+ WHERE (position(lower(name->'name') in bnd_name) > 0
+ OR position(bnd_name in lower(name->'name')) > 0)
AND placex.class = 'place' AND placex.type = bnd.extratags->'place'
AND placex.osm_type = 'N'
AND placex.linked_place_id is null
AND placex.rank_search < 26 -- needed to select the right index
- AND _st_covers(bnd.geometry, placex.geometry)
+ AND placex.type != 'postcode'
+ AND ST_Covers(bnd.geometry, placex.geometry)
LOOP
{% if debug %}RAISE WARNING 'Found type-matching place node %', linked_placex.osm_id;{% endif %}
RETURN linked_placex;
AND placex.linked_place_id is null
AND placex.rank_search < 26
AND _st_covers(bnd.geometry, placex.geometry)
- ORDER BY make_standard_name(name->'name') = bnd_name desc
+ ORDER BY lower(name->'name') = bnd_name desc
LOOP
{% if debug %}RAISE WARNING 'Found wikidata-matching place node %', linked_placex.osm_id;{% endif %}
RETURN linked_placex;
{% if debug %}RAISE WARNING 'Looking for nodes with matching names';{% endif %}
FOR linked_placex IN
SELECT placex.* from placex
- WHERE make_standard_name(name->'name') = bnd_name
+ WHERE lower(name->'name') = bnd_name
AND ((bnd.rank_address > 0
and bnd.rank_address = (compute_place_rank(placex.country_code,
'N', placex.class,
false, placex.postcode)).address_rank)
OR (bnd.rank_address = 0 and placex.rank_search = bnd.rank_search))
AND placex.osm_type = 'N'
+ AND placex.class = 'place'
AND placex.linked_place_id is null
AND placex.rank_search < 26 -- needed to select the right index
- AND _st_covers(bnd.geometry, placex.geometry)
+ AND placex.type != 'postcode'
+ AND ST_Covers(bnd.geometry, placex.geometry)
LOOP
{% if debug %}RAISE WARNING 'Found matching place node %', linked_placex.osm_id;{% endif %}
RETURN linked_placex;
NEW.place_id := nextval('seq_place');
NEW.indexed_status := 1; --STATUS_NEW
- NEW.country_code := lower(get_country_code(NEW.geometry));
+ NEW.centroid := ST_PointOnSurface(NEW.geometry);
+ NEW.country_code := lower(get_country_code(NEW.centroid));
NEW.partition := get_partition(NEW.country_code);
- NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
+ NEW.geometry_sector := geometry_sector(NEW.partition, NEW.centroid);
IF NEW.osm_type = 'X' THEN
-- E'X'ternal records should already be in the right format so do nothing
nameaddress_vector INTEGER[];
addr_nameaddress_vector INTEGER[];
- inherited_address HSTORE;
-
linked_node_id BIGINT;
linked_importance FLOAT;
linked_wikipedia TEXT;
-- update not necessary for osmline, cause linked_place_id does not exist
NEW.extratags := NEW.extratags - 'linked_place'::TEXT;
- NEW.address := NEW.address - '_unlisted_place'::TEXT;
IF NEW.linked_place_id is not null THEN
{% if debug %}RAISE WARNING 'place already linked to %', NEW.linked_place_id;{% endif %}
{% if debug %}RAISE WARNING 'finding street for % %', NEW.osm_type, NEW.osm_id;{% endif %}
NEW.parent_place_id := null;
- -- if we have a POI and there is no address information,
- -- see if we can get it from a surrounding building
- inherited_address := ''::HSTORE;
- IF NEW.osm_type = 'N' AND addr_street IS NULL AND addr_place IS NULL
- AND NEW.housenumber IS NULL THEN
- FOR location IN
- -- The additional && condition works around the misguided query
- -- planner of postgis 3.0.
- SELECT address from placex where ST_Covers(geometry, NEW.centroid)
- and geometry && NEW.centroid
- and (address ? 'housenumber' or address ? 'street' or address ? 'place')
- and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
- limit 1
- LOOP
- NEW.housenumber := location.address->'housenumber';
- addr_street := location.address->'street';
- addr_place := location.address->'place';
- inherited_address := location.address;
- END LOOP;
- END IF;
-
-- We have to find our parent road.
NEW.parent_place_id := find_parent_for_poi(NEW.osm_type, NEW.osm_id,
NEW.partition,
{% if not db.reverse_only %}
IF array_length(name_vector, 1) is not NULL
- OR inherited_address is not NULL OR NEW.address is not NULL
+ OR NEW.address is not NULL
THEN
SELECT * INTO name_vector, nameaddress_vector
FROM create_poi_search_terms(NEW.place_id,
NEW.partition, NEW.parent_place_id,
- inherited_address || NEW.address,
+ NEW.address,
NEW.country_code, NEW.housenumber,
name_vector, NEW.centroid);
END IF;
{% endif %}
+ -- If the address was inherited from a surrounding building,
+ -- do not add it permanently to the table.
+ IF NEW.address ? '_inherited' THEN
+ IF NEW.address ? '_unlisted_place' THEN
+ NEW.address := hstore('_unlisted_place', NEW.address->'_unlisted_place');
+ ELSE
+ NEW.address := null;
+ END IF;
+ END IF;
+
RETURN NEW;
END IF;
projects / nominatim.git / blobdiff