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;
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,
--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;
--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 = 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);
+ 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;
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