$$
LANGUAGE 'plpgsql' IMMUTABLE;
+-- returns NULL if the word is too common
CREATE OR REPLACE FUNCTION getorcreate_word_id(lookup_word TEXT)
RETURNS INTEGER
AS $$
DECLARE
lookup_token TEXT;
return_word_id INTEGER;
+ count INTEGER;
BEGIN
lookup_token := trim(lookup_word);
- SELECT min(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id;
+ SELECT min(word_id), max(search_name_count) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id, count;
IF return_word_id IS NULL THEN
return_word_id := nextval('seq_word');
INSERT INTO word VALUES (return_word_id, lookup_token, regexp_replace(lookup_token,E'([^0-9])\\1+',E'\\1','g'), null, null, null, null, 0, null);
+ ELSE
+ IF count > get_maxwordfreq() THEN
+ return_word_id := NULL;
+ END IF;
END IF;
RETURN return_word_id;
END;
FOR j IN 1..array_upper(words, 1) LOOP
IF (words[j] != '') THEN
w = getorcreate_word_id(words[j]);
- IF NOT (ARRAY[w] <@ result) THEN
+ IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
result := result || w;
END IF;
END IF;
s := make_standard_name(words[j]);
IF s != '' THEN
w := getorcreate_word_id(s);
- IF NOT (ARRAY[w] <@ result) THEN
+ IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
result := result || w;
END IF;
END IF;
FOR j IN 1..array_upper(words, 1) LOOP
IF (words[j] != '') THEN
w = getorcreate_word_id(words[j]);
- IF NOT (ARRAY[w] <@ result) THEN
+ IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
result := result || w;
END IF;
END IF;
s := make_standard_name(words[j]);
IF s != '' THEN
w := getorcreate_word_id(s);
- IF NOT (ARRAY[w] <@ result) THEN
+ IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN
result := result || w;
END IF;
END IF;
isArea := true;
centroid := ST_Centroid(geometry);
- FOR geometry IN select split_geometry(geometry) as geometry LOOP
- x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, centroid, geometry);
+ FOR secgeo IN select split_geometry(geometry) AS geom LOOP
+ x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, centroid, secgeo);
END LOOP;
ELSEIF rank_search < 26 THEN
-- null record of right type
select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::INTEGER and type = 'house' limit 1 INTO nextnode;
select ST_SetSRID(ST_Point(lon::float/10000000,lat::float/10000000),4326) from planet_osm_nodes where id = waynodes[nodeidpos] INTO nextnode.geometry;
+ IF nextnode.geometry IS NULL THEN
+ -- we don't have any information about this point, most likely
+ -- because an excerpt was updated and the node never imported
+ -- because the interpolation is outside the region of the excerpt.
+ -- Give up.
+ RETURN newpoints;
+ END IF;
ELSE
select * from placex where place_id = search_place_id INTO nextnode;
END IF;
location_rank_search INTEGER;
location_distance FLOAT;
+ location_parent GEOMETRY;
+ location_isaddress BOOLEAN;
tagpairid INTEGER;
result BOOLEAN;
BEGIN
+ -- deferred delete
+ IF OLD.indexed_status = 100 THEN
+ --DEBUG: RAISE WARNING 'placex_update_delete % %',NEW.osm_type,NEW.osm_id;
+ delete from placex where place_id = OLD.place_id;
+ RETURN NULL;
+ END IF;
+
IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 OR NEW.linked_place_id is not null THEN
RETURN NEW;
END IF;
RETURN NEW;
END IF;
- -- deferred delete
- IF OLD.indexed_status = 100 THEN
- --DEBUG: RAISE WARNING 'placex_update_delete % %',NEW.osm_type,NEW.osm_id;
- delete from placex where place_id = OLD.place_id;
- RETURN NULL;
- END IF;
-
IF OLD.indexed_status != 0 THEN
--DEBUG: RAISE WARNING 'placex_update_0 % %',NEW.osm_type,NEW.osm_id;
-- RAISE WARNING 'get_osm_rel_members, label';
FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
- select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
- and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 into linkedPlacex;
+ FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
+ and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 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));
- END IF;
+ -- 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;
+
+ -- merge in the label name, re-init word vector
+ IF NOT linkedPlacex.name IS NULL THEN
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := make_keywords(NEW.name);
+ END IF;
- -- merge in the label name, re-init word vector
- NEW.name := linkedPlacex.name || NEW.name;
- name_vector := make_keywords(NEW.name);
+ -- merge in extra tags
+ IF NOT linkedPlacex.extratags IS NULL THEN
+ NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+ END IF;
- -- 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;
- -- mark the linked place (excludes from search results)
- UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+ END LOOP;
END LOOP;
FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
- select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
- and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 into linkedPlacex;
+ FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1)
+ and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP
- -- 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
+ -- 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_address = linkedPlacex.rank_address 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;
+ -- 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;
- -- merge in the name, re-init word vector
- NEW.name := linkedPlacex.name || NEW.name;
- name_vector := make_keywords(NEW.name);
+ -- merge in the name, re-init word vector
+ IF NOT linkedPlacex.name IS NULL THEN
+ NEW.name := linkedPlacex.name || NEW.name;
+ name_vector := make_keywords(NEW.name);
+ END IF;
- -- merge in extra tags
- NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+ -- merge in extra tags
+ IF NOT linkedPlacex.extratags IS NULL THEN
+ NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+ 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;
+ -- 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;
- END IF;
+ -- keep a note of the node id in case we need it for wikipedia in a bit
+ linked_node_id := linkedPlacex.osm_id;
+ END IF;
+
+ END LOOP;
END LOOP;
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.rank_address = NEW.rank_address
AND placex.place_id != NEW.place_id
AND placex.osm_type = 'N'::char(1) AND placex.rank_search < 26
AND st_covers(NEW.geometry, placex.geometry)
-- RAISE WARNING 'ISIN: %', isin_tokens;
-- Process area matches
- location_rank_search := 100;
+ location_rank_search := 0;
location_distance := 0;
--- RAISE WARNING ' getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens;
- FOR location IN SELECT distinct * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
+ location_parent := NULL;
+ -- added ourself as address already
+ address_havelevel[NEW.rank_address] := true;
+ -- RAISE WARNING ' getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens;
+ FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
--RAISE WARNING ' AREA: %',location;
- IF location.rank_search < location_rank_search THEN
- location_rank_search := location.rank_search;
+ IF location.rank_address != location_rank_search THEN
+ location_rank_search := location.rank_address;
location_distance := location.distance * 1.5;
END IF;
IF location.distance < location_distance OR NOT location.isguess THEN
+ location_isaddress := NOT address_havelevel[location.rank_address];
+ IF location_isaddress AND location.isguess AND location_parent IS NOT NULL THEN
+ location_isaddress := ST_Contains(location_parent,location.centroid);
+ END IF;
+
+ -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress;
-- Add it to the list of search terms
nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
- INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
- address_havelevel[location.rank_address] := true;
+ INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address);
+
+ IF location_isaddress THEN
+ address_havelevel[location.rank_address] := true;
+ IF NOT location.isguess THEN
+ SELECT geometry FROM placex WHERE place_id = location.place_id INTO location_parent;
+ END IF;
+ END IF;
--RAISE WARNING ' Terms: (%) %',location, nameaddress_vector;
FOR i IN 1..array_upper(isin_tokens, 1) LOOP
--RAISE WARNING ' getNearestNamedFeature: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
- FOR location IN SELECT distinct * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
+ FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
--RAISE WARNING ' ISIN: %',location;
-- for long ways we should add search terms for the entire length
IF st_length(NEW.geometry) > 0.05 THEN
- location_rank_search := 100;
+ 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_search < location_rank_search THEN
- location_rank_search := location.rank_search;
+ IF location.rank_address != location_rank_search THEN
+ location_rank_search := location.rank_address;
location_distance := location.distance * 1.5;
END IF;
b BOOLEAN;
classtable TEXT;
BEGIN
- RAISE WARNING 'placex_delete % %',OLD.osm_type,OLD.osm_id;
+ -- RAISE WARNING 'placex_delete % %',OLD.osm_type,OLD.osm_id;
- update placex set linked_place_id = null where linked_place_id = OLD.place_id;
+ update placex set linked_place_id = null, indexed_status = 2 where linked_place_id = OLD.place_id and indexed_status = 0;
--DEBUG: RAISE WARNING 'placex_delete:01 % %',OLD.osm_type,OLD.osm_id;
- update placex set indexed_status = 2 where linked_place_id = OLD.place_id and indexed_status = 0;
+ update placex set linked_place_id = null where linked_place_id = OLD.place_id;
--DEBUG: RAISE WARNING 'placex_delete:02 % %',OLD.osm_type,OLD.osm_id;
IF OLD.rank_address < 30 THEN
--DEBUG: RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;
- -- deleting large polygons can have a massive effect ont he system - require manual intervention to let them through
- IF st_area(OLD.geometry) > 2 THEN
+ -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through
+ IF st_area(OLD.geometry) > 2 and st_isvalid(OLD.geometry) THEN
insert into import_polygon_delete values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type);
RETURN NULL;
END IF;
FOR location IN select * from get_addressdata(for_place_id) where isaddress order by rank_address desc LOOP
currresult := trim(get_name_by_language(location.name, languagepref));
- IF currresult != prevresult AND currresult IS NOT NULL THEN
+ IF currresult != prevresult AND currresult IS NOT NULL AND result[(100 - location.rank_address)] IS NULL THEN
result[(100 - location.rank_address)] := trim(get_name_by_language(location.name, languagepref));
prevresult := currresult;
END IF;
END IF;
IF for_place_id IS NULL THEN
- select parent_place_id, calculated_country_code, housenumber, rank_address, postcode, name, class, type from placex
+ select parent_place_id, calculated_country_code, housenumber, rank_search, postcode, name, class, type from placex
WHERE place_id = in_place_id and rank_address = 30
INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
END IF;
IF for_place_id IS NULL THEN
for_place_id := in_place_id;
- select calculated_country_code, housenumber, rank_address, postcode, null from placex where place_id = for_place_id
+ select calculated_country_code, housenumber, rank_search, postcode, null from placex where place_id = for_place_id
INTO searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename;
END IF;
$$
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION tigger_create_interpolation(linegeo GEOMETRY, in_startnumber INTEGER,
- in_endnumber INTEGER, interpolationtype TEXT,
- in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER
- AS $$
-DECLARE
-
- startnumber INTEGER;
- endnumber INTEGER;
- stepsize INTEGER;
- housenum INTEGER;
- newpoints INTEGER;
- numberrange INTEGER;
- rangestartnumber INTEGER;
- place_centroid GEOMETRY;
- out_partition INTEGER;
- out_parent_place_id BIGINT;
- location RECORD;
- address_street_word_id INTEGER;
-
-BEGIN
-
- IF in_endnumber > in_startnumber THEN
- startnumber = in_startnumber;
- endnumber = in_endnumber;
- ELSE
- startnumber = in_endnumber;
- endnumber = in_startnumber;
- END IF;
-
- numberrange := endnumber - startnumber;
- rangestartnumber := startnumber;
-
- IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN
- startnumber := startnumber + 1;
- stepsize := 2;
- ELSE
- IF (interpolationtype = 'odd' OR interpolationtype = 'even') THEN
- stepsize := 2;
- ELSE -- everything else assumed to be 'all'
- stepsize := 1;
- END IF;
- END IF;
-
- -- Filter out really broken tiger data
- IF numberrange > 0 AND (numberrange::float/stepsize::float > 500)
- AND ST_length(linegeo)/(numberrange::float/stepsize::float) < 0.000001 THEN
- RAISE WARNING 'Road too short for number range % to % on %, % (%)',startnumber,endnumber,in_street,in_isin,
- ST_length(linegeo)/(numberrange::float/stepsize::float);
- RETURN 0;
- END IF;
-
- place_centroid := ST_Centroid(linegeo);
- 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(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 getNearestParellelRoadFeature(out_partition, linegeo) 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(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'), 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;
-
- RETURN newpoints;
-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 $$
IF st_intersects(geometry, secbox) THEN
secgeo := st_intersection(geometry, secbox);
IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN
- FOR geo IN select quad_split_geometry(secgeo, maxarea, remainingdepth) as geometry LOOP
- IF NOT ST_IsEmpty(geo.geometry) AND ST_GeometryType(geo.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
+ FOR geo IN select quad_split_geometry(secgeo, maxarea, remainingdepth) as geom LOOP
+ IF NOT ST_IsEmpty(geo.geom) AND ST_GeometryType(geo.geom) in ('ST_Polygon','ST_MultiPolygon') THEN
added := added + 1;
- RETURN NEXT geo.geometry;
+ RETURN NEXT geo.geom;
END IF;
END LOOP;
END IF;
geo RECORD;
BEGIN
-- 10000000000 is ~~ 1x1 degree
- FOR geo IN select quad_split_geometry(geometry, 0.25, 20) as geometry LOOP
- RETURN NEXT geo.geometry;
+ FOR geo IN select quad_split_geometry(geometry, 0.25, 20) as geom LOOP
+ RETURN NEXT geo.geom;
END LOOP;
RETURN;
END;
projects / nominatim.git / blobdiff