DECLARE
o TEXT;
BEGIN
- o := gettokenstring(transliteration(name));
+ o := public.gettokenstring(public.transliteration(name));
RETURN trim(substr(o,1,length(o)));
END;
$$
$$
LANGUAGE plpgsql IMMUTABLE;
+CREATE OR REPLACE FUNCTION reverse_place_diameter(rank_search SMALLINT)
+ RETURNS FLOAT
+ AS $$
+BEGIN
+ IF rank_search <= 4 THEN
+ RETURN 5.0;
+ ELSIF rank_search <= 8 THEN
+ RETURN 1.8;
+ ELSIF rank_search <= 12 THEN
+ RETURN 0.6;
+ ELSIF rank_search <= 17 THEN
+ RETURN 0.16;
+ ELSIF rank_search <= 18 THEN
+ RETURN 0.08;
+ ELSIF rank_search <= 19 THEN
+ RETURN 0.04;
+ END IF;
+
+ RETURN 0.02;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT,
OUT rank_search SMALLINT, OUT rank_address SMALLINT)
CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY) RETURNS TEXT
AS $$
DECLARE
- item RECORD;
+ outcode TEXT;
+ cnt INTEGER;
BEGIN
-- If the geometry is an area then only one postcode must be within
-- that area, otherwise consider the area as not having a postcode.
IF ST_GeometryType(geom) in ('ST_Polygon','ST_MultiPolygon') THEN
- FOR item IN
- SELECT min(postcode) as postcode, count(*) as cnt FROM
+ SELECT min(postcode), count(*) FROM
(SELECT postcode FROM location_postcode
WHERE ST_Contains(geom, location_postcode.geometry) LIMIT 2) sub
- LOOP
- IF item.cnt > 1 THEN
- RETURN null;
- ELSEIF item.cnt = 1 THEN
- RETURN item.postcode;
- END IF;
- END LOOP;
+ INTO outcode, cnt;
+
+ IF cnt = 1 THEN
+ RETURN outcode;
+ ELSE
+ RETURN null;
+ END IF;
END IF;
- FOR item IN
- SELECT postcode FROM location_postcode
- WHERE ST_DWithin(geom, location_postcode.geometry, 0.05)
- AND location_postcode.country_code = country
- ORDER BY ST_Distance(geom, location_postcode.geometry)
- LIMIT 1
- LOOP
- RETURN item.postcode;
- END LOOP;
+ SELECT postcode FROM location_postcode
+ WHERE ST_DWithin(geom, location_postcode.geometry, 0.05)
+ AND location_postcode.country_code = country
+ ORDER BY ST_Distance(geom, location_postcode.geometry) LIMIT 1
+ INTO outcode;
- RETURN null;
+ RETURN outcode;
END;
$$
LANGUAGE plpgsql;
SELECT * FROM get_postcode_rank(NEW.country_code, NEW.address->'postcode')
INTO NEW.rank_search, NEW.rank_address;
+ IF NOT ST_GeometryType(NEW.geometry) IN ('ST_Polygon','ST_MultiPolygon') THEN
+ NEW.rank_address := 0;
+ END IF;
+
ELSEIF NEW.class = 'place' THEN
IF NEW.type in ('continent') THEN
NEW.rank_search := 2;
ELSE
NEW.rank_address := 0;
END IF;
+ ELSEIF NEW.class = 'leisure' and NEW.type in ('park') THEN
+ NEW.rank_search := 24;
+ NEW.rank_address := 0;
ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN
NEW.rank_search := 18;
NEW.rank_address := 0;
NEW.startnumber := startnumber;
NEW.endnumber := endnumber;
NEW.linegeo := sectiongeo;
- NEW.postcode := postcode;
+ NEW.postcode := upper(trim(postcode));
ELSE
insert into location_property_osmline
(linegeo, partition, osm_id, parent_place_id,
relation_members TEXT[];
relMember RECORD;
linkedplacex RECORD;
+ addr_item RECORD;
search_diameter FLOAT;
search_prevdiameter FLOAT;
search_maxrank INTEGER;
i := getorcreate_housenumber_id(make_standard_name(NEW.housenumber));
END IF;
- addr_street = NEW.address->'street';
- addr_place = NEW.address->'place';
+ addr_street := NEW.address->'street';
+ addr_place := NEW.address->'place';
IF NEW.address ? 'postcode' and NEW.address->'postcode' not similar to '%(,|;)%' THEN
i := getorcreate_postcode_id(NEW.address->'postcode');
--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 class = NEW.class and type in ('river', 'stream', 'canal', 'drain', 'ditch')
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 = linked_node_id;
-- see if we can get it from a surrounding building
IF NEW.osm_type = 'N' AND addr_street IS NULL AND addr_place IS NULL
AND NEW.housenumber IS NULL THEN
- FOR location IN select * from placex where ST_Covers(geometry, place_centroid)
+ FOR location IN select address from placex where ST_Covers(geometry, place_centroid)
and address is not null
and (address ? 'housenumber' or address ? 'street' or address ? 'place')
and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
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
- FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
- NEW.parent_place_id := location.place_id;
- END LOOP;
+ SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_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
- FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
- NEW.parent_place_id := location.place_id;
- END LOOP;
+ SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_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;
-- Is this node part of an interpolation?
IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
- FOR location IN
- SELECT q.parent_place_id FROM location_property_osmline q, planet_osm_ways x
- WHERE q.linegeo && NEW.geometry and x.id = q.osm_id and NEW.osm_id = any(x.nodes)
- LIMIT 1
- LOOP
- NEW.parent_place_id := location.parent_place_id;
- END LOOP;
+ SELECT q.parent_place_id FROM location_property_osmline q, planet_osm_ways x
+ WHERE q.linegeo && NEW.geometry and x.id = q.osm_id and NEW.osm_id = any(x.nodes)
+ LIMIT 1 INTO NEW.parent_place_id;
END IF;
--DEBUG: RAISE WARNING 'Checked for interpolation (%)', NEW.parent_place_id;
-- Is this node part of a way?
IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
- FOR location IN select p.place_id, p.osm_id, p.parent_place_id, p.rank_search, p.address from placex p, planet_osm_ways w
- where p.osm_type = 'W' and p.rank_search >= 26 and p.geometry && NEW.geometry and w.id = p.osm_id and NEW.osm_id = any(w.nodes)
+ FOR location IN
+ SELECT p.place_id, p.osm_id, p.rank_search, p.address from placex p, planet_osm_ways w
+ WHERE p.osm_type = 'W' and p.rank_search >= 26 and p.geometry && NEW.geometry and w.id = p.osm_id and NEW.osm_id = any(w.nodes)
LOOP
--DEBUG: RAISE WARNING 'Node is part of way % ', location.osm_id;
-- Way IS a road then we are on it - that must be our road
- IF location.rank_search < 28 AND NEW.parent_place_id IS NULL THEN
+ IF location.rank_search < 28 THEN
--RAISE WARNING 'node in way that is a street %',location;
NEW.parent_place_id := location.place_id;
+ EXIT;
END IF;
--DEBUG: RAISE WARNING 'Checked if way is street (%)', NEW.parent_place_id;
-- If the way mentions a street or place address, try that for parenting.
- IF NEW.parent_place_id IS NULL AND 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
- 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;
+ IF location.address is not null THEN
+ 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;
+ EXIT WHEN NEW.parent_place_id is not NULL;
+ END IF;
END IF;
- END IF;
- --DEBUG: RAISE WARNING 'Checked for addr:street in way (%)', NEW.parent_place_id;
+ --DEBUG: RAISE WARNING 'Checked for addr:street in way (%)', NEW.parent_place_id;
- IF NEW.parent_place_id IS NULL AND 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
- 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;
+ 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;
+ EXIT WHEN NEW.parent_place_id is not NULL;
+ END IF;
END IF;
- END IF;
--DEBUG: RAISE WARNING 'Checked for addr:place in way (%)', NEW.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]
- 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 'node in 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;
+ FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.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 'node in 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;
- END IF;
+ END IF;
+ END LOOP;
+ EXIT WHEN NEW.parent_place_id is not null;
--DEBUG: RAISE WARNING 'Checked for street relation in way (%)', NEW.parent_place_id;
END LOOP;
-
END IF;
-- Still nothing, just use the nearest road
IF NEW.parent_place_id IS NULL THEN
- FOR location IN SELECT place_id FROM getNearestRoadFeature(NEW.partition, place_centroid) LOOP
- NEW.parent_place_id := location.place_id;
- END LOOP;
+ SELECT place_id FROM getNearestRoadFeature(NEW.partition, place_centroid) INTO NEW.parent_place_id;
END IF;
--DEBUG: RAISE WARNING 'Checked for nearest way (%)', NEW.parent_place_id;
IF NEW.parent_place_id IS NOT NULL THEN
-- Get the details of the parent road
- select * from search_name where place_id = NEW.parent_place_id INTO location;
+ select s.country_code, s.name_vector, s.nameaddress_vector from search_name s
+ where s.place_id = NEW.parent_place_id INTO location;
NEW.country_code := location.country_code;
--DEBUG: RAISE WARNING 'Got parent details from search name';
-- determine postcode
IF NEW.rank_search > 4 THEN
IF NEW.address is not null AND NEW.address ? 'postcode' THEN
- NEW.postcode = NEW.address->'postcode';
+ NEW.postcode = upper(trim(NEW.address->'postcode'));
ELSE
SELECT postcode FROM placex WHERE place_id = NEW.parent_place_id INTO NEW.postcode;
END IF;
-- Just be happy with inheriting from parent road only
IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
- result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.address->'postcode', NEW.geometry);
+ result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry);
--DEBUG: RAISE WARNING 'Place added to location table';
END IF;
END IF;
-- make sure all names are in the word table
- IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN
+ IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R' THEN
perform create_country(NEW.name, lower(NEW.country_code));
--DEBUG: RAISE WARNING 'Country names updated';
END IF;
parent_place_id_rank = 0;
- -- convert isin to array of tokenids
+ -- convert address store to array of tokenids
--DEBUG: RAISE WARNING 'Starting address search';
isin_tokens := '{}'::int[];
IF NEW.address IS NOT NULL THEN
- isin := avals(NEW.address);
- IF array_upper(isin, 1) IS NOT NULL THEN
- FOR i IN 1..array_upper(isin, 1) LOOP
- -- TODO further split terms with comma and semicolon
- address_street_word_id := get_name_id(make_standard_name(isin[i]));
+ FOR addr_item IN SELECT * FROM each(NEW.address)
+ LOOP
+ IF addr_item.key IN ('city', 'tiger:county', 'state', 'suburb', 'province', 'district', 'region', 'county', 'municipality', 'hamlet', 'village', 'subdistrict', 'town', 'neighbourhood', 'quarter', 'parish') THEN
+ address_street_word_id := get_name_id(make_standard_name(addr_item.value));
IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
isin_tokens := isin_tokens || address_street_word_id;
END IF;
-
- -- merge word into address vector
- address_street_word_id := get_word_id(make_standard_name(isin[i]));
+ address_street_word_id := get_word_id(make_standard_name(addr_item.value));
IF address_street_word_id IS NOT NULL THEN
nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
END IF;
- END LOOP;
- END IF;
- END IF;
+ END IF;
+ IF addr_item.key = 'is_in' THEN
+ -- is_in items need splitting
+ isin := regexp_split_to_array(addr_item.value, E'[;,]');
+ IF array_upper(isin, 1) IS NOT NULL THEN
+ FOR i IN 1..array_upper(isin, 1) LOOP
+ address_street_word_id := get_name_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+ isin_tokens := isin_tokens || address_street_word_id;
+ END IF;
- -- %NOTIGERDATA% IF 0 THEN
- -- for the USA we have an additional address table. Merge in zip codes from there too
- IF NEW.rank_search = 26 AND NEW.country_code = 'us' THEN
- FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP
- address_street_word_id := get_name_id(make_standard_name(location.postcode));
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
- isin_tokens := isin_tokens || address_street_word_id;
-
- -- also merge in the single word version
- address_street_word_id := get_word_id(make_standard_name(location.postcode));
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ -- merge word into address vector
+ address_street_word_id := get_word_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ END IF;
+ END LOOP;
+ END IF;
+ END IF;
END LOOP;
END IF;
- --DEBUG: RAISE WARNING 'Tiger postcodes collected';
- -- %NOTIGERDATA% END IF;
+ nameaddress_vector := array_merge(nameaddress_vector, isin_tokens);
-- RAISE WARNING 'ISIN: %', isin_tokens;
END LOOP;
--DEBUG: RAISE WARNING 'address computed';
- -- try using the isin value to find parent places
- IF array_upper(isin_tokens, 1) IS NOT NULL THEN
- FOR i IN 1..array_upper(isin_tokens, 1) LOOP
---RAISE WARNING ' getNearestNamedFeature: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
- IF NOT ARRAY[isin_tokens[i]] <@ nameaddress_vector THEN
-
- FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
-
---RAISE WARNING ' ISIN: %',location;
-
- IF location.rank_search > 4 THEN
- nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
- INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address)
- VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
- IF NEW.postcode is null AND location.postcode is not null
- AND NOT address_havelevel[location.rank_address] THEN
- NEW.postcode := location.postcode;
- END IF;
-
- address_havelevel[location.rank_address] := true;
-
- IF location.rank_address > parent_place_id_rank THEN
- NEW.parent_place_id = location.place_id;
- parent_place_id_rank = location.rank_address;
- END IF;
- END IF;
- END LOOP;
-
- END IF;
-
- END LOOP;
- END IF;
- --DEBUG: RAISE WARNING 'isin tokens processed';
-
- -- for long ways we should add search terms for the entire length
- IF st_length(NEW.geometry) > 0.05 THEN
-
- location_rank_search := 0;
- location_distance := 0;
-
- FOR location IN SELECT * from getNearFeatures(NEW.partition, NEW.geometry, search_maxrank, isin_tokens) LOOP
-
- IF location.rank_address != location_rank_search THEN
- location_rank_search := location.rank_address;
- location_distance := location.distance * 1.5;
- END IF;
-
- IF location.rank_search > 4 AND location.distance < location_distance THEN
-
- -- Add it to the list of search terms
- nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
- INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address)
- VALUES (NEW.place_id, location.place_id, true, false, location.distance, location.rank_address);
-
- END IF;
-
- END LOOP;
-
- END IF;
- --DEBUG: RAISE WARNING 'search terms for long ways added';
-
IF NEW.address is not null AND NEW.address ? 'postcode'
AND NEW.address->'postcode' not similar to '%(,|;)%' THEN
- NEW.postcode := NEW.address->'postcode';
+ NEW.postcode := upper(trim(NEW.address->'postcode'));
END IF;
IF NEW.postcode is null AND NEW.rank_search > 8 THEN
IF NEW.name IS NOT NULL THEN
IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
- result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.address->'postcode', NEW.geometry);
+ result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry);
--DEBUG: RAISE WARNING 'added to location (full)';
END IF;
-- To paraphrase, if there isn't an existing item, OR if the admin level has changed
IF existingplacex.osm_type IS NULL OR
- (coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.class = 'boundary' AND existingplacex.type = 'administrative')
+ (existingplacex.class = 'boundary' AND
+ ((coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.type = 'administrative') OR
+ (existingplacex.type != NEW.type)))
THEN
IF existingplacex.osm_type IS NOT NULL THEN
select placex.place_id, osm_type, osm_id, name,
CASE WHEN extratags ? 'place' THEN 'place' ELSE class END as class,
CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as type,
- admin_level, fromarea, isaddress,
+ admin_level, fromarea, isaddress and linked_place_id is NULL as isaddress,
CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
distance,country_code,postcode
from place_addressline join placex on (address_place_id = placex.place_id)
where place_addressline.place_id = for_place_id
and (cached_rank_address > 0 AND cached_rank_address < searchrankaddress)
- and address_place_id != for_place_id
+ and address_place_id != for_place_id and linked_place_id is null
and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode)
order by rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc
LOOP
projects / nominatim.git / blobdiff