RETURN nearcountry.country_code;
END LOOP;
--- RAISE WARNING 'natural earth: %', ST_AsText(place_centre);
-
- -- Natural earth data
- FOR nearcountry IN select country_code from country_naturalearthdata where st_covers(geometry, place_centre) limit 1
- LOOP
- RETURN nearcountry.country_code;
- END LOOP;
-
-- RAISE WARNING 'near osm fallback: %', ST_AsText(place_centre);
--
RETURN nearcountry.country_code;
END LOOP;
--- RAISE WARNING 'near natural earth: %', ST_AsText(place_centre);
-
- -- Natural earth data
- FOR nearcountry IN select country_code from country_naturalearthdata where st_dwithin(geometry, place_centre, 0.5) limit 1
- LOOP
- RETURN nearcountry.country_code;
- END LOOP;
-
RETURN NULL;
END;
$$
END IF;
ELSEIF NEW.class = 'boundary' AND NOT is_area THEN
return NULL;
+ ELSEIF NEW.class = 'boundary' AND NEW.type = 'administrative'
+ AND NEW.admin_level <= 4 AND NEW.osm_type = 'W' THEN
+ return NULL;
ELSEIF NEW.class = 'railway' AND NEW.type in ('rail') THEN
return NULL;
ELSEIF NEW.osm_type = 'N' AND NEW.class = 'highway' THEN
END IF;
-- some postcorrections
- IF NEW.class = 'place' THEN
- IF NEW.type in ('continent', 'sea', 'country', 'state') AND NEW.osm_type = 'N' THEN
- NEW.rank_address := 0;
- END IF;
- ELSEIF NEW.class = 'waterway' AND NEW.osm_type = 'R' THEN
+ IF NEW.class = 'waterway' AND NEW.osm_type = 'R' THEN
-- Slightly promote waterway relations so that they are processed
-- before their members.
NEW.rank_search := NEW.rank_search - 1;
NEW.country_code := NULL;
END IF;
--- Block import below rank 22
--- IF NEW.rank_search > 22 THEN
--- RETURN NULL;
--- END IF;
-
--DEBUG: RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
RETURN NEW; -- %DIFFUPDATES% The following is not needed until doing diff updates, and slows the main index process down
DECLARE
place_centroid GEOMETRY;
+ near_centroid GEOMETRY;
search_maxdistance FLOAT[];
search_mindistance FLOAT[];
-- Speed up searches - just use the centroid of the feature
-- cheaper but less acurate
place_centroid := ST_PointOnSurface(NEW.geometry);
+ -- For searching near features rather use the centroid
+ near_centroid := ST_Centroid(NEW.geometry);
NEW.centroid := null;
NEW.postcode := null;
--DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(place_centroid);
--DEBUG: RAISE WARNING 'Waterway processed';
END IF;
- -- Adding ourselves to the list simplifies address calculations later
- INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address)
- VALUES (NEW.place_id, NEW.place_id, true, true, 0, NEW.rank_address);
-
-- What level are we searching from
search_maxrank := NEW.rank_search;
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
- SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id;
+ SELECT place_id from getNearestNamedRoadFeature(NEW.partition, near_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
- SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id;
+ SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, near_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;
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;
+ SELECT place_id from getNearestNamedRoadFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id;
EXIT WHEN NEW.parent_place_id is not NULL;
END IF;
END IF;
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;
+ SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id;
EXIT WHEN NEW.parent_place_id is not NULL;
END IF;
END IF;
-- Still nothing, just use the nearest road
IF NEW.parent_place_id IS NULL THEN
- SELECT place_id FROM getNearestRoadFeature(NEW.partition, place_centroid) INTO NEW.parent_place_id;
+ SELECT place_id FROM getNearestRoadFeature(NEW.partition, near_centroid) INTO NEW.parent_place_id;
END IF;
--DEBUG: RAISE WARNING 'Checked for nearest way (%)', NEW.parent_place_id;
NEW.postcode := location.postcode;
END IF;
IF NEW.postcode is null THEN
- NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid);
+ NEW.postcode := get_nearest_postcode(NEW.country_code, near_centroid);
END IF;
END IF;
CASE WHEN NEW.rank_search >= 26
AND NEW.rank_search < 30
THEN NEW.geometry
- ELSE place_centroid END,
+ ELSE near_centroid END,
search_maxrank, isin_tokens)
LOOP
IF location.rank_address != location_rank_search THEN
distance FLOAT
);
+-- Compute the list of address parts for the given place.
+--
+-- If in_housenumber is greator or equal 0, look for an interpolation.
CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber INTEGER) RETURNS setof addressline
AS $$
DECLARE
searchhousename HSTORE;
searchrankaddress INTEGER;
searchpostcode TEXT;
+ postcode_isaddress BOOL;
searchclass TEXT;
searchtype TEXT;
countryname HSTORE;
- hadcountry BOOLEAN;
BEGIN
+ -- The place ein question might not have a direct entry in place_addressline.
+ -- Look for the parent of such places then and save if in for_place_id.
+
+ postcode_isaddress := true;
+
-- first query osmline (interpolation lines)
- select parent_place_id, country_code, 30, postcode, null, 'place', 'house' from location_property_osmline
- WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber
- INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
- IF for_place_id IS NOT NULL THEN
- searchhousenumber = in_housenumber::text;
+ IF in_housenumber >= 0 THEN
+ SELECT parent_place_id, country_code, in_housenumber::text, 30, postcode,
+ null, 'place', 'house'
+ FROM location_property_osmline
+ WHERE place_id = in_place_id AND in_housenumber>=startnumber
+ AND in_housenumber <= endnumber
+ INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress,
+ searchpostcode, searchhousename, searchclass, searchtype;
END IF;
--then query tiger data
-- %NOTIGERDATA% IF 0 THEN
- IF for_place_id IS NULL THEN
- select parent_place_id,'us', 30, postcode, null, 'place', 'house' from location_property_tiger
- WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber
- INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
- IF for_place_id IS NOT NULL THEN
- searchhousenumber = in_housenumber::text;
- END IF;
+ IF for_place_id IS NULL AND in_housenumber >= 0 THEN
+ SELECT parent_place_id, 'us', in_housenumber::text, 30, postcode, null,
+ 'place', 'house'
+ FROM location_property_tiger
+ WHERE place_id = in_place_id AND in_housenumber >= startnumber
+ AND in_housenumber <= endnumber
+ INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress,
+ searchpostcode, searchhousename, searchclass, searchtype;
END IF;
-- %NOTIGERDATA% END IF;
-- %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
+ SELECT parent_place_id, 'us', housenumber, 30, postcode, null, 'place', 'house'
+ FROM location_property_aux
WHERE place_id = in_place_id
- INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
+ 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'
+ 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;
+ INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode,
+ searchclass, searchtype;
END IF;
+ -- POI objects in the placex table
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
- INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
+ 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
+ INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress,
+ searchpostcode, searchhousename, searchclass, searchtype;
END IF;
+ -- If for_place_id is still NULL at this point then the object has its own
+ -- entry in place_address line. However, still check if there is not linked
+ -- place we should be using instead.
IF for_place_id IS NULL THEN
select coalesce(linked_place_id, place_id), country_code,
housenumber, rank_search, postcode, null
--RAISE WARNING '% % % %',searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode;
- found := 1000;
- hadcountry := false;
- FOR location IN
- 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
- where place_id = for_place_id
+ found := 1000; -- the lowest rank_address included
+
+ -- Return the record for the base entry.
+ FOR location IN
+ SELECT placex.place_id, osm_type, osm_id, name,
+ class, type, admin_level,
+ type not in ('postcode', 'postal_code') 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
+ WHERE place_id = for_place_id
LOOP
--RAISE WARNING '%',location;
IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN
searchcountrycode := location.country_code;
END IF;
- IF location.type in ('postcode', 'postal_code') THEN
- location.isaddress := FALSE;
- ELSEIF location.rank_address = 4 THEN
- hadcountry := true;
- END IF;
- IF location.rank_address < 4 AND NOT hadcountry THEN
- select name from country_name where country_code = searchcountrycode limit 1 INTO countryname;
- IF countryname IS NOT NULL THEN
- countrylocation := ROW(null, null, null, countryname, 'place', 'country', null, true, true, 4, 0)::addressline;
- RETURN NEXT countrylocation;
- END IF;
+ IF location.rank_address < 4 THEN
+ -- no country locations for ranks higher than country
+ searchcountrycode := NULL;
END IF;
- countrylocation := ROW(location.place_id, location.osm_type, location.osm_id, location.name, location.class,
- location.type, location.admin_level, true, location.isaddress, location.rank_address,
- location.distance)::addressline;
+ countrylocation := ROW(location.place_id, location.osm_type, location.osm_id,
+ location.name, location.class, 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, 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,
- 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 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
+ FOR location IN
+ 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 and linked_place_id is NULL as isaddress,
+ CASE 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 >= 4 AND cached_rank_address < searchrankaddress)
+ 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
--RAISE WARNING '%',location;
IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN
searchcountrycode := location.country_code;
END IF;
IF location.type in ('postcode', 'postal_code') THEN
- location.isaddress := FALSE;
- END IF;
- IF location.rank_address = 4 AND location.isaddress THEN
- hadcountry := true;
- END IF;
- IF location.rank_address < 4 AND NOT hadcountry THEN
- select name from country_name where country_code = searchcountrycode limit 1 INTO countryname;
- IF countryname IS NOT NULL THEN
- countrylocation := ROW(null, null, null, countryname, 'place', 'country', null, true, true, 4, 0)::addressline;
- RETURN NEXT countrylocation;
+ postcode_isaddress := false;
+ IF location.osm_type != 'R' THEN
+ location.isaddress := FALSE;
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,
+ 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.distance)::addressline;
RETURN NEXT countrylocation;
found := location.rank_address;
END LOOP;
+ -- If no country was included yet, add the name information from country_name.
IF found > 4 THEN
- select name from country_name where country_code = searchcountrycode limit 1 INTO countryname;
+ SELECT name FROM country_name
+ WHERE country_code = searchcountrycode LIMIT 1 INTO countryname;
--RAISE WARNING '% % %',found,searchcountrycode,countryname;
IF countryname IS NOT NULL THEN
- location := ROW(null, null, null, countryname, 'place', 'country', null, true, true, 4, 0)::addressline;
+ location := ROW(null, null, null, countryname, 'place', 'country',
+ null, true, true, 4, 0)::addressline;
RETURN NEXT location;
END IF;
END IF;
+ -- Finally add some artificial rows.
IF searchcountrycode IS NOT NULL THEN
- location := ROW(null, null, null, hstore('ref', searchcountrycode), 'place', 'country_code', null, true, false, 4, 0)::addressline;
+ location := ROW(null, null, null, hstore('ref', searchcountrycode),
+ 'place', 'country_code', null, true, false, 4, 0)::addressline;
RETURN NEXT location;
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, searchclass,
+ searchtype, null, true, true, 29, 0)::addressline;
RETURN NEXT location;
END IF;
IF searchhousenumber IS NOT NULL THEN
- location := ROW(in_place_id, null, null, hstore('ref', searchhousenumber), 'place', 'house_number', null, true, true, 28, 0)::addressline;
+ location := ROW(in_place_id, null, null, hstore('ref', searchhousenumber),
+ 'place', 'house_number', null, true, true, 28, 0)::addressline;
RETURN NEXT location;
END IF;
IF searchpostcode IS NOT NULL THEN
- location := ROW(null, null, null, hstore('ref', searchpostcode), 'place', 'postcode', null, true, true, 5, 0)::addressline;
+ location := ROW(null, null, null, hstore('ref', searchpostcode), 'place',
+ 'postcode', null, false, postcode_isaddress, 5, 0)::addressline;
RETURN NEXT location;
END IF;
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION get_searchrank_label(rank INTEGER) RETURNS TEXT
- AS $$
-DECLARE
-BEGIN
- IF rank < 2 THEN
- RETURN 'Continent';
- ELSEIF rank < 4 THEN
- RETURN 'Sea';
- ELSEIF rank < 8 THEN
- RETURN 'Country';
- ELSEIF rank < 12 THEN
- RETURN 'State';
- ELSEIF rank < 16 THEN
- RETURN 'County';
- ELSEIF rank = 16 THEN
- RETURN 'City';
- ELSEIF rank = 17 THEN
- RETURN 'Town / Island';
- ELSEIF rank = 18 THEN
- RETURN 'Village / Hamlet';
- ELSEIF rank = 20 THEN
- RETURN 'Suburb';
- ELSEIF rank = 21 THEN
- RETURN 'Postcode Area';
- ELSEIF rank = 22 THEN
- RETURN 'Croft / Farm / Locality / Islet';
- ELSEIF rank = 23 THEN
- RETURN 'Postcode Area';
- ELSEIF rank = 25 THEN
- RETURN 'Postcode Point';
- ELSEIF rank = 26 THEN
- RETURN 'Street / Major Landmark';
- ELSEIF rank = 27 THEN
- RETURN 'Minory Street / Path';
- ELSEIF rank = 28 THEN
- RETURN 'House / Building';
- ELSE
- RETURN 'Other: '||rank;
- END IF;
-
-END;
-$$
-LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION get_addressrank_label(rank INTEGER) RETURNS TEXT
- AS $$
-DECLARE
-BEGIN
- IF rank = 0 THEN
- RETURN 'None';
- ELSEIF rank < 2 THEN
- RETURN 'Continent';
- ELSEIF rank < 4 THEN
- RETURN 'Sea';
- ELSEIF rank = 5 THEN
- RETURN 'Postcode';
- ELSEIF rank < 8 THEN
- RETURN 'Country';
- ELSEIF rank < 12 THEN
- RETURN 'State';
- ELSEIF rank < 16 THEN
- RETURN 'County';
- ELSEIF rank = 16 THEN
- RETURN 'City';
- ELSEIF rank = 17 THEN
- RETURN 'Town / Village / Hamlet';
- ELSEIF rank = 20 THEN
- RETURN 'Suburb';
- ELSEIF rank = 21 THEN
- RETURN 'Postcode Area';
- ELSEIF rank = 22 THEN
- RETURN 'Croft / Farm / Locality / Islet';
- ELSEIF rank = 23 THEN
- RETURN 'Postcode Area';
- ELSEIF rank = 25 THEN
- RETURN 'Postcode Point';
- ELSEIF rank = 26 THEN
- RETURN 'Street / Major Landmark';
- ELSEIF rank = 27 THEN
- RETURN 'Minory Street / Path';
- ELSEIF rank = 28 THEN
- RETURN 'House / Building';
- ELSE
- RETURN 'Other: '||rank;
- END IF;
-
-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 $$
projects / nominatim.git / blobdiff