LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION create_interpolation(wayid INTEGER, interpolationtype TEXT) RETURNS INTEGER
+CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT) RETURNS INTEGER
AS $$
DECLARE
NEW.place_id := nextval('seq_place');
NEW.indexed_status := 1; --STATUS_NEW
- NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+ IF NEW.rank_search >= 4 THEN
+ NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+ ELSE
+ NEW.country_code := NULL;
+ END IF;
+
NEW.partition := get_partition(NEW.geometry, NEW.country_code);
NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
-- copy 'name' to or from the default language (if there is a default language)
- IF NEW.name is not null AND array_upper(%#NEW.name,1) > 1 THEN
+ IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
default_language := get_country_language_code(NEW.country_code);
IF default_language IS NOT NULL THEN
IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
NEW.name := NEW.name || (('name:'||default_language) => (NEW.name -> 'name'));
ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
- NEW.name := NEW.name || ('name' => (NEW.name -> 'name:'||default_language));
+ NEW.name := NEW.name || ('name' => (NEW.name -> ('name:'||default_language)));
END IF;
END IF;
END IF;
-- By doing in postgres we have the country available to us - currently only used for postcode
IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
+ IF NEW.postcode IS NULL THEN
+ -- most likely just a part of a multipolygon postcode boundary, throw it away
+ RETURN NULL;
+ END IF;
+
NEW.name := 'ref'=>NEW.postcode;
IF NEW.country_code = 'gb' THEN
NEW.rank_search := 8;
NEW.rank_address := NEW.rank_search;
ELSEIF NEW.type in ('region') THEN
- NEW.rank_search := 10;
- NEW.rank_address := NEW.rank_search;
+ NEW.rank_search := 18; -- dropped from previous value of 10
+ NEW.rank_address := 0; -- So badly miss-used that better to just drop it!
ELSEIF NEW.type in ('county') THEN
NEW.rank_search := 12;
NEW.rank_address := NEW.rank_search;
ELSEIF NEW.type in ('suburb','croft','subdivision') THEN
NEW.rank_search := 20;
NEW.rank_address := NEW.rank_search;
- ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling') THEN
+ ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling','mountain_pass') THEN
NEW.rank_search := 20;
NEW.rank_address := 0;
+ -- Irish townlands, tagged as place=locality and locality=townland
+ IF (NEW.extratags -> 'locality') = 'townland' THEN
+ NEW.rank_address := 20;
+ END IF;
ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN
NEW.rank_search := 22;
NEW.rank_address := 22;
name_vector INTEGER[];
nameaddress_vector INTEGER[];
+ wiki_article TEXT;
+ wiki_article_title TEXT;
+ wiki_article_language TEXT;
+
result BOOLEAN;
BEGIN
END IF;
-- reclaculate country and partition (should probably have a country_code and calculated_country_code as seperate fields)
- SELECT country_code from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO NEW.country_code;
- NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+ IF NEW.rank_search >= 4 THEN
+ SELECT country_code from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO NEW.country_code;
+ NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+ ELSE
+ NEW.country_code := NULL;
+ END IF;
NEW.partition := get_partition(NEW.geometry, NEW.country_code);
NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
-- Thought this wasn't needed but when we add new languages to the country_name table
-- we need to update the existing names
- IF NEW.name is not null AND array_upper(%#NEW.name,1) > 1 THEN
+ IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
default_language := get_country_language_code(NEW.country_code);
IF default_language IS NOT NULL THEN
IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
NEW.name := NEW.name || (('name:'||default_language) => (NEW.name -> 'name'));
ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
- NEW.name := NEW.name || ('name' => (NEW.name -> 'name:'||default_language));
+ NEW.name := NEW.name || ('name' => (NEW.name -> ('name:'||default_language)));
END IF;
END IF;
END IF;
address_havelevel[i] := false;
END LOOP;
+ NEW.importance := null;
+ -- WARNING: see duplicate of code below (yuk!)
+ IF NEW.extratags?'wikipedia' THEN
+ wiki_article := replace(regexp_replace(NEW.extratags->'wikipedia',E'(.*?)([a-z]+).wikipedia.org/wiki/',E'\\2:'),' ','_');
+ wiki_article_title := split_part(wiki_article, ':', 2);
+ IF wiki_article_title IS NULL OR wiki_article_title = '' THEN
+ wiki_article_title := wiki_article;
+ wiki_article_language := 'en';
+ ELSE
+ wiki_article_language := lower(split_part(wiki_article, ':', 1));
+ END IF;
+--RAISE WARNING '% %', wiki_article_language, wiki_article_title;
+
+ select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title
+ from wikipedia_article
+ where language = wiki_article_language and
+ (title = wiki_article_title OR title = catch_decode_url_part(wiki_article_title) OR title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
+ UNION ALL
+ select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title
+ from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title)
+ where wikipedia_redirect.language = wiki_article_language and
+ (from_title = wiki_article_title OR from_title = catch_decode_url_part(wiki_article_title) OR from_title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
+ order by importance asc limit 1 INTO NEW.importance,NEW.wikipedia;
+
+ ELSE
+ select importance,language||':'||title from wikipedia_article where osm_type = NEW.osm_type and osm_id = NEW.osm_id order by importance asc limit 1 INTO NEW.importance,NEW.wikipedia;
+ END IF;
+
--RAISE WARNING '% %', NEW.place_id, NEW.rank_search;
-- For low level elements we inherit from our parent road
IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
-- Is this node part of a relation?
- FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['n'||NEW.osm_id]
+ FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['n'||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
--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::integer] LOOP
+ 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
LOOP
-- 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::integer] and members @> ARRAY['w'||location.osm_id]
+ 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
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::integer] and members @> ARRAY['w'||NEW.osm_id]
+ 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
-- mark the linked place (excludes from search results)
UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
- DELETE from search_name where place_id = linkedPlacex.place_id;
END LOOP;
- FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
+ IF NEW.centroid IS NULL THEN
- select * from placex where osm_type = upper(substring(relMember.member,1,1))
- and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
+ FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
- IF NEW.name->'name' = linkedPlacex.name->'name' THEN
- -- If we don't already have one use this as the centre point of the geometry
- IF NEW.centroid IS NULL THEN
- NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
- END IF;
+ select * from placex where osm_type = upper(substring(relMember.member,1,1))
+ and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
- -- merge in the name, re-init word vector
- NEW.name := linkedPlacex.name || NEW.name;
- name_vector := make_keywords(NEW.name);
+ -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
+ -- But that can be fixed by explicitly setting the label in the data
+ IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name')
+ AND NEW.rank_search = linkedPlacex.rank_search THEN
- -- merge in extra tags
- NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+ -- If we don't already have one use this as the centre point of the geometry
+ IF NEW.centroid IS NULL THEN
+ NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
+ 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;
- DELETE from search_name where place_id = linkedPlacex.place_id;
- END IF;
+ -- merge in the name, re-init word vector
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := make_keywords(NEW.name);
- END LOOP;
+ -- merge in extra tags
+ NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+
+ -- mark the linked place (excludes from search results)
+ UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+ END IF;
+
+ END LOOP;
+
+ END IF;
-- not found one yet? how about doing a name search
- IF NEW.centroid IS NULL THEN
- FOR linkedPlacex IN select placex.* from search_name join placex using (place_id) WHERE
- search_name.name_vector @> ARRAY[getorcreate_name_id(make_standard_name(NEW.name->'name'))]
- AND search_name.search_rank = NEW.rank_search
- AND search_name.place_id != NEW.place_id
- AND osm_type = 'N'
- AND NEW.name->'name' = placex.name->'name'
+ IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' THEN
+
+ FOR linkedPlacex IN select placex.* from placex WHERE
+ make_standard_name(name->'name') = make_standard_name(NEW.name->'name')
+ AND placex.rank_search = NEW.rank_search
+ AND placex.place_id != NEW.place_id
+ AND placex.osm_type = 'N' AND placex.rank_search < 26
AND st_contains(NEW.geometry, placex.geometry)
LOOP
+
-- If we don't already have one use this as the centre point of the geometry
IF NEW.centroid IS NULL THEN
NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
-- mark the linked place (excludes from search results)
UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
- DELETE from search_name where place_id = linkedPlacex.place_id;
+
END LOOP;
END IF;
IF NEW.centroid IS NOT NULL THEN
- place_centroid := NEW.centroid
+ place_centroid := NEW.centroid;
+ END IF;
+
+ -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance
+ -- WARNING: duplicate of code above (yuk!)
+ IF NEW.importance is null AND NEW.extratags?'wikipedia' THEN
+ wiki_article := replace(regexp_replace(NEW.extratags->'wikipedia',E'(.*?)([a-z]+).wikipedia.org/wiki/',E'\\2:'),' ','_');
+ wiki_article_title := split_part(wiki_article, ':', 2);
+ IF wiki_article_title IS NULL OR wiki_article_title = '' THEN
+ wiki_article_title := wiki_article;
+ wiki_article_language := 'en';
+ ELSE
+ wiki_article_language := lower(split_part(wiki_article, ':', 1));
+ END IF;
+
+ select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title
+ from wikipedia_article
+ where language = wiki_article_language and
+ (title = wiki_article_title OR title = catch_decode_url_part(wiki_article_title) OR title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
+ UNION ALL
+ select wikipedia_article.importance,wikipedia_article.language||':'||wikipedia_article.title
+ from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title)
+ where wikipedia_redirect.language = wiki_article_language and
+ (from_title = wiki_article_title OR from_title = catch_decode_url_part(wiki_article_title) OR from_title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
+ order by importance asc limit 1 INTO NEW.importance,NEW.wikipedia;
+
END IF;
END IF;
numberrange INTEGER;
rangestartnumber INTEGER;
place_centroid GEOMETRY;
- partition INTEGER;
- parent_place_id BIGINT;
+ out_partition INTEGER;
+ out_parent_place_id BIGINT;
location RECORD;
address_street_word_id INTEGER;
END IF;
place_centroid := ST_Centroid(linegeo);
- partition := get_partition(place_centroid, 'us');
- parent_place_id := null;
+ out_partition := get_partition(place_centroid, 'us');
+ out_parent_place_id := null;
address_street_word_id := get_name_id(make_standard_name(in_street));
IF address_street_word_id IS NOT NULL THEN
- FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
- parent_place_id := location.place_id;
+ FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
+ out_parent_place_id := location.place_id;
END LOOP;
END IF;
- IF parent_place_id IS NULL THEN
- FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(partition, linegeo) LOOP
- parent_place_id := location.place_id;
+ IF out_parent_place_id IS NULL THEN
+ FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(out_partition, linegeo) LOOP
+ out_parent_place_id := location.place_id;
END LOOP;
END IF;
- IF parent_place_id IS NULL THEN
- FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
- parent_place_id := location.place_id;
+ IF out_parent_place_id IS NULL THEN
+ FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
+ out_parent_place_id := location.place_id;
END LOOP;
END IF;
newpoints := 0;
FOR housenum IN startnumber..endnumber BY stepsize LOOP
insert into location_property_tiger (place_id, partition, parent_place_id, housenumber, postcode, centroid)
- values (nextval('seq_place'), partition, parent_place_id, housenum, in_postcode,
+ values (nextval('seq_place'), out_partition, out_parent_place_id, housenum, in_postcode,
ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
newpoints := newpoints + 1;
END LOOP;
newpoints INTEGER;
place_centroid GEOMETRY;
- partition INTEGER;
+ out_partition INTEGER;
out_parent_place_id BIGINT;
location RECORD;
address_street_word_id INTEGER;
BEGIN
place_centroid := ST_Centroid(pointgeo);
- partition := get_partition(place_centroid, in_countrycode);
+ out_partition := get_partition(place_centroid, in_countrycode);
out_parent_place_id := null;
address_street_word_id := get_name_id(make_standard_name(in_street));
IF address_street_word_id IS NOT NULL THEN
- FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
+ FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
out_parent_place_id := location.place_id;
END LOOP;
END IF;
IF out_parent_place_id IS NULL THEN
- FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
+ FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
out_parent_place_id := location.place_id;
END LOOP;
END IF;
SELECT postcode from placex where place_id = out_parent_place_id INTO out_postcode;
END IF;
IF out_postcode IS NULL THEN
- out_postcode := getNearestPostcode(partition, place_centroid);
+ out_postcode := getNearestPostcode(out_partition, place_centroid);
END IF;
newpoints := 0;
insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)
- values (nextval('seq_place'), partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
+ values (nextval('seq_place'), out_partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
newpoints := newpoints + 1;
RETURN newpoints;
$$
LANGUAGE plpgsql;
-
+-- See: http://stackoverflow.com/questions/6410088/how-can-i-mimic-the-php-urldecode-function-in-postgresql
+CREATE OR REPLACE FUNCTION decode_url_part(p varchar) RETURNS varchar
+ AS $$
+SELECT convert_from(CAST(E'\\x' || array_to_string(ARRAY(
+ SELECT CASE WHEN length(r.m[1]) = 1 THEN encode(convert_to(r.m[1], 'SQL_ASCII'), 'hex') ELSE substring(r.m[1] from 2 for 2) END
+ FROM regexp_matches($1, '%[0-9a-f][0-9a-f]|.', 'gi') AS r(m)
+), '') AS bytea), 'UTF8');
+$$
+LANGUAGE SQL IMMUTABLE STRICT;
+
+CREATE OR REPLACE FUNCTION catch_decode_url_part(p varchar) RETURNS varchar
+ AS $$
+DECLARE
+BEGIN
+ RETURN decode_url_part(p);
+EXCEPTION
+ WHEN others THEN return null;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
projects / nominatim.git / blobdiff