-- score integer
--);
-
CREATE OR REPLACE FUNCTION getclasstypekey(c text, t text) RETURNS TEXT
AS $$
DECLARE
result := result || w;
END IF;
+ w := getorcreate_word_id(s);
+
+ IF NOT (ARRAY[w] <@ result) THEN
+ result := result || w;
+ END IF;
+
words := string_to_array(s, ' ');
IF array_upper(words, 1) IS NOT NULL THEN
FOR j IN 1..array_upper(words, 1) LOOP
result := result || w;
END IF;
+ w := getorcreate_word_id(s);
+
+ IF NOT (ARRAY[w] <@ result) THEN
+ result := result || w;
+ END IF;
+
words := string_to_array(s, ' ');
IF array_upper(words, 1) IS NOT NULL THEN
FOR j IN 1..array_upper(words, 1) LOOP
-- RAISE WARNING 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
- x := deleteLocationArea(partition, place_id);
+ x := deleteLocationArea(partition, place_id, rank_search);
isarea := false;
IF (ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(geometry)) THEN
DECLARE
b BOOLEAN;
BEGIN
- b := deleteLocationArea(partition, place_id);
+ b := deleteLocationArea(partition, place_id, rank_search);
-- result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
RETURN add_location(place_id, place_country_code, name, rank_search, rank_address, geometry);
END;
DECLARE
newpoints INTEGER;
- waynodes integer[];
- nodeid INTEGER;
+ waynodes BIGINT[];
+ nodeid BIGINT;
prevnode RECORD;
nextnode RECORD;
startnumber INTEGER;
FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
- select min(place_id) from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::INTEGER and type = 'house' INTO search_place_id;
+ select min(place_id) from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and type = 'house' INTO search_place_id;
IF search_place_id IS NULL 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 * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT 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
RETURN NULL;
END IF;
- IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 OR NEW.linked_place_id is not null THEN
+ IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
RETURN NEW;
END IF;
RETURN NEW;
END IF;
+ -- TODO: this test is now redundant?
IF OLD.indexed_status != 0 THEN
- --DEBUG: RAISE WARNING 'placex_update_0 % %',NEW.osm_type,NEW.osm_id;
NEW.indexed_date = now();
- IF NEW.class = 'place' AND NEW.type = 'houses' THEN
- i := create_interpolation(NEW.osm_id, NEW.housenumber);
+ result := deleteSearchName(NEW.partition, NEW.place_id);
+ DELETE FROM place_addressline WHERE place_id = NEW.place_id;
+ DELETE FROM place_boundingbox where place_id = NEW.place_id;
+ result := deleteRoad(NEW.partition, NEW.place_id);
+ result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
+ UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
+
+ IF NEW.linked_place_id is not null THEN
RETURN NEW;
END IF;
- IF OLD.indexed_status > 0 THEN
- result := deleteSearchName(NEW.partition, NEW.place_id);
- DELETE FROM place_addressline WHERE place_id = NEW.place_id;
- DELETE FROM place_boundingbox where place_id = NEW.place_id;
- result := deleteRoad(NEW.partition, NEW.place_id);
- result := deleteLocationArea(NEW.partition, NEW.place_id);
- UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
+ IF NEW.class = 'place' AND NEW.type = 'houses' THEN
+ i := create_interpolation(NEW.osm_id, NEW.housenumber);
+ RETURN NEW;
END IF;
-- Speed up searches - just use the centroid of the feature
--RAISE WARNING 'finding street for %', NEW;
+ -- We won't get a better centroid, besides these places are too small to care
+ NEW.centroid := place_centroid;
+
NEW.parent_place_id := null;
-- to do that we have to find our parent road
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
+ 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]));
+ 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;
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
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
isin_tokens := isin_tokens || address_street_word_id;
END IF;
+
+ -- merge 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;
+
+ -- for the USA we have an additional address table. Merge in zip codes from there too
+ IF NEW.rank_search = 26 AND NEW.calculated_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]);
+ END LOOP;
+ END IF;
+
-- RAISE WARNING 'ISIN: %', isin_tokens;
-- Process area matches
--DEBUG: RAISE WARNING 'placex_delete:08 % %',OLD.osm_type,OLD.osm_id;
IF OLD.rank_address < 26 THEN
- b := deleteLocationArea(OLD.partition, OLD.place_id);
+ b := deleteLocationArea(OLD.partition, OLD.place_id, OLD.rank_search);
END IF;
--DEBUG: RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id;
create type addressline as (
place_id BIGINT,
osm_type CHAR(1),
- osm_id INTEGER,
+ osm_id BIGINT,
name HSTORE,
class TEXT,
type TEXT,