DECLARE
o TEXT;
BEGIN
- o := gettokenstring(transliteration(name));
+ o := public.gettokenstring(public.transliteration(name));
RETURN trim(substr(o,1,length(o)));
END;
$$
$$
LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION getorcreate_postcode_id(postcode TEXT)
+ RETURNS INTEGER
+ AS $$
+DECLARE
+ lookup_token TEXT;
+ lookup_word TEXT;
+ return_word_id INTEGER;
+BEGIN
+ lookup_word := upper(trim(postcode));
+ lookup_token := ' ' || make_standard_name(lookup_word);
+ SELECT min(word_id) FROM word WHERE word_token = lookup_token and class='place' and type='postcode' into return_word_id;
+ IF return_word_id IS NULL THEN
+ return_word_id := nextval('seq_word');
+ INSERT INTO word VALUES (return_word_id, lookup_token, lookup_word, 'place', 'postcode', null, 0);
+ END IF;
+ RETURN return_word_id;
+END;
+$$
+LANGUAGE plpgsql;
+
CREATE OR REPLACE FUNCTION getorcreate_country(lookup_word TEXT, lookup_country_code varchar(2))
RETURNS INTEGER
AS $$
$$
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)
BEGIN
rank_search := 30;
rank_address := 30;
+ postcode := upper(postcode);
IF country_code = 'gb' THEN
IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY) RETURNS TEXT
AS $$
DECLARE
- item RECORD;
+ outcode TEXT;
+ cnt INTEGER;
BEGIN
- 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;
+ -- 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
+ SELECT min(postcode), count(*) FROM
+ (SELECT postcode FROM location_postcode
+ WHERE ST_Contains(geom, location_postcode.geometry) LIMIT 2) sub
+ INTO outcode, cnt;
+
+ IF cnt = 1 THEN
+ RETURN outcode;
+ ELSE
+ RETURN null;
+ END IF;
+ END IF;
- RETURN null;
+ 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 outcode;
END;
$$
LANGUAGE plpgsql;
x := deleteLocationArea(partition, place_id, rank_search);
-- add postcode only if it contains a single entry, i.e. ignore postcode lists
- IF in_postcode IS NULL OR in_postcode similar to '%(,|;)%' THEN
- postcode := NULL;
- ELSE
- postcode := in_postcode;
+ postcode := NULL;
+ IF in_postcode is not null AND in_postcode not similar to '%(,|;)%' THEN
+ postcode := upper(trim (in_postcode));
END IF;
IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
RETURN NULL;
END IF;
- NEW.postcode := NEW.address->'postcode';
- NEW.name := hstore('ref', NEW.postcode);
+ NEW.name := hstore('ref', NEW.address->'postcode');
- SELECT * FROM get_postcode_rank(NEW.country_code, NEW.postcode)
+ 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;
linegeo GEOMETRY;
splitline GEOMETRY;
sectiongeo GEOMETRY;
+ interpol_postcode TEXT;
postcode TEXT;
- seg_postcode TEXT;
BEGIN
-- deferred delete
IF OLD.indexed_status = 100 THEN
NEW.address->'place',
NEW.partition, place_centroid, NEW.linegeo);
-
- IF NEW.address is not NULL and NEW.address ? 'postcode' THEN
- NEW.postcode = NEW.address->'postcode';
+ IF NEW.address is not NULL AND NEW.address ? 'postcode' AND NEW.address->'postcode' not similar to '%(,|;)%' THEN
+ interpol_postcode := NEW.address->'postcode';
+ housenum := getorcreate_postcode_id(NEW.address->'postcode');
+ ELSE
+ interpol_postcode := NULL;
END IF;
-- if the line was newly inserted, split the line as necessary
linegeo := NEW.linegeo;
startnumber := NULL;
- postcode := NEW.postcode;
FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
sectiongeo := ST_Reverse(sectiongeo);
END IF;
- seg_postcode := coalesce(postcode,
- prevnode.address->'postcode',
- nextnode.address->'postcode');
+ -- determine postcode
+ postcode := coalesce(interpol_postcode,
+ prevnode.address->'postcode',
+ nextnode.address->'postcode',
+ postcode);
+
+ IF postcode is NULL THEN
+ SELECT placex.postcode FROM placex WHERE place_id = NEW.parent_place_id INTO postcode;
+ END IF;
+ IF postcode is NULL THEN
+ postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry);
+ END IF;
IF NEW.startnumber IS NULL THEN
NEW.startnumber := startnumber;
NEW.endnumber := endnumber;
NEW.linegeo := sectiongeo;
- NEW.postcode := seg_postcode;
+ NEW.postcode := upper(trim(postcode));
ELSE
insert into location_property_osmline
(linegeo, partition, osm_id, parent_place_id,
geometry_sector, indexed_status)
values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
startnumber, endnumber, NEW.interpolationtype,
- NEW.address, seg_postcode,
+ NEW.address, postcode,
NEW.country_code, NEW.geometry_sector, 0);
END IF;
END IF;
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');
+ END IF;
END IF;
-- Speed up searches - just use the centroid of the feature
-- cheaper but less acurate
place_centroid := ST_PointOnSurface(NEW.geometry);
NEW.centroid := null;
+ NEW.postcode := null;
--DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(place_centroid);
-- recalculate country and partition
--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.address is not null AND NEW.address ? 'postcode' THEN
- NEW.postcode = NEW.address->'postcode';
- ELSE
- SELECT postcode FROM placex WHERE place_id = parent_place_id INTO NEW.postcode;
- END IF;
- IF NEW.postcode is null THEN
- NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid);
- END IF;
-
- -- Merge the postcode into the parent's address if necessary
- IF NEW.postcode IS NOT NULL THEN
- --DEBUG: RAISE WARNING 'Merging postcode into parent';
- isin_tokens := '{}'::int[];
- address_street_word_id := getorcreate_word_id(make_standard_name(NEW.postcode));
- IF address_street_word_id is not null
- and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN
- isin_tokens := isin_tokens || address_street_word_id;
- END IF;
- address_street_word_id := getorcreate_name_id(make_standard_name(NEW.postcode));
- IF address_street_word_id is not null
- and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN
- isin_tokens := isin_tokens || address_street_word_id;
- END IF;
- IF isin_tokens != '{}'::int[] THEN
- UPDATE search_name
- SET nameaddress_vector = search_name.nameaddress_vector || isin_tokens
- WHERE place_id = NEW.parent_place_id;
- END IF;
+ IF NEW.rank_search > 4 THEN
+ IF NEW.address is not null AND NEW.address ? 'postcode' THEN
+ NEW.postcode = upper(trim(NEW.address->'postcode'));
+ ELSE
+ SELECT postcode FROM placex WHERE place_id = NEW.parent_place_id INTO NEW.postcode;
+ END IF;
+ IF NEW.postcode is null THEN
+ NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid);
+ END IF;
END IF;
-- If there is no name it isn't searchable, don't bother to create a search record
-- 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
$$
LANGUAGE plpgsql IMMUTABLE;
-
-CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id BIGINT) RETURNS TEXT
- AS $$
-DECLARE
- result TEXT[];
- search TEXT[];
- for_postcode TEXT;
- found INTEGER;
- location RECORD;
-BEGIN
-
- found := 1000;
- search := ARRAY['ref'];
- result := '{}';
-
- select postcode from placex where place_id = for_place_id limit 1 into for_postcode;
-
- FOR location IN
- select rank_address,name,distance,length(name::text) as namelength
- from place_addressline join placex on (address_place_id = placex.place_id)
- where place_addressline.place_id = for_place_id and rank_address in (5,11)
- order by rank_address desc,rank_search desc,fromarea desc,distance asc,namelength desc
- LOOP
- IF array_upper(search, 1) IS NOT NULL AND array_upper(location.name, 1) IS NOT NULL THEN
- FOR j IN 1..array_upper(search, 1) LOOP
- FOR k IN 1..array_upper(location.name, 1) LOOP
- IF (found > location.rank_address AND location.name[k].key = search[j] AND location.name[k].value != '') AND NOT result @> ARRAY[trim(location.name[k].value)] AND (for_postcode IS NULL OR location.name[k].value ilike for_postcode||'%') THEN
- result[(100 - location.rank_address)] := trim(location.name[k].value);
- found := location.rank_address;
- END IF;
- END LOOP;
- END LOOP;
- END IF;
- END LOOP;
-
- RETURN array_to_string(result,', ');
-END;
-$$
-LANGUAGE plpgsql;
-
--housenumber only needed for tiger data
CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT, housenumber INTEGER, languagepref TEXT[]) RETURNS TEXT
AS $$
-- %NOAUXDATA% IF 0 THEN
IF for_place_id IS NULL THEN
select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux
- WHERE place_id = in_place_id
+ WHERE place_id = in_place_id
INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
END IF;
-- %NOAUXDATA% END IF;
+ -- postcode table
+ IF for_place_id IS NULL THEN
+ select parent_place_id, country_code, rank_address, postcode, 'place', 'postcode'
+ FROM location_postcode
+ WHERE place_id = in_place_id
+ INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode, searchclass, searchtype;
+ END IF;
+
IF for_place_id IS NULL THEN
select parent_place_id, country_code, housenumber, rank_search, postcode, name, class, type from placex
WHERE place_id = in_place_id and rank_search > 27
found := 1000;
hadcountry := false;
FOR location IN
- select placex.place_id, osm_type, osm_id,
- CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
- class, type, admin_level, true as fromarea, true as isaddress,
+ select placex.place_id, osm_type, osm_id, name,
+ class, type, admin_level, true as isaddress,
CASE WHEN rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
0 as distance, country_code, postcode
from placex
IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN
searchcountrycode := location.country_code;
END IF;
- IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN
+ IF location.type in ('postcode', 'postal_code') THEN
location.isaddress := FALSE;
- END IF;
- IF searchpostcode IS NULL and location.postcode IS NOT NULL THEN
- searchpostcode := location.postcode;
- END IF;
- IF location.rank_address = 4 AND location.isaddress THEN
+ ELSEIF location.rank_address = 4 THEN
hadcountry := true;
END IF;
IF location.rank_address < 4 AND NOT hadcountry THEN
END IF;
END IF;
countrylocation := ROW(location.place_id, location.osm_type, location.osm_id, location.name, location.class,
- location.type, location.admin_level, location.fromarea, location.isaddress, location.rank_address,
+ location.type, location.admin_level, true, location.isaddress, location.rank_address,
location.distance)::addressline;
RETURN NEXT countrylocation;
found := location.rank_address;
END LOOP;
FOR location IN
- select placex.place_id, osm_type, osm_id,
- CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
+ 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
IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN
searchcountrycode := location.country_code;
END IF;
- IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN
+ IF location.type in ('postcode', 'postal_code') THEN
location.isaddress := FALSE;
END IF;
- IF searchpostcode IS NULL and location.isaddress and location.type != 'postcode' and location.postcode IS NOT NULL and location.postcode not similar to '%(,|;)%' THEN
- searchpostcode := location.postcode;
- END IF;
IF location.rank_address = 4 AND location.isaddress THEN
hadcountry := true;
END IF;
IF searchhousename IS NOT NULL THEN
location := ROW(in_place_id, null, null, searchhousename, searchclass, searchtype, null, true, true, 29, 0)::addressline;
--- location := ROW(in_place_id, null, null, searchhousename, 'place', 'house_name', null, true, true, 29, 0)::addressline;
RETURN NEXT location;
END IF;
projects / nominatim.git / blobdiff