$$
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 $$
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;
-- add postcode only if it contains a single entry, i.e. ignore postcode lists
postcode := NULL;
IF in_postcode is not null AND in_postcode not similar to '%(,|;)%' THEN
- postcode := upper(trim (both ' ' from in_postcode));
+ 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;
ELSEIF NEW.class = 'place' THEN
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;
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 '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;
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;
$$
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)
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