$$
LANGUAGE plpgsql IMMUTABLE;
-CREATE OR REPLACE FUNCTION delete_location(OLD_place_id INTEGER) RETURNS BOOLEAN
+CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT) RETURNS BOOLEAN
AS $$
DECLARE
BEGIN
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION add_location(
- place_id INTEGER,
+ place_id BIGINT,
country_code varchar(2),
partition INTEGER,
keywords INTEGER[],
CREATE OR REPLACE FUNCTION update_location(
partition INTEGER,
- place_id INTEGER,
+ place_id BIGINT,
place_country_code varchar(2),
name hstore,
rank_search INTEGER,
$$
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION search_name_add_words(parent_place_id INTEGER, to_add INTEGER[])
+CREATE OR REPLACE FUNCTION search_name_add_words(parent_place_id BIGINT, to_add INTEGER[])
RETURNS BOOLEAN
AS $$
DECLARE
$$
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION update_location_nameonly(partition INTEGER, OLD_place_id INTEGER, name hstore) RETURNS BOOLEAN
+CREATE OR REPLACE FUNCTION update_location_nameonly(partition INTEGER, OLD_place_id BIGINT, name hstore) RETURNS BOOLEAN
AS $$
DECLARE
newkeywords INTEGER[];
originalnumberrange INTEGER;
housenum INTEGER;
linegeo GEOMETRY;
- search_place_id INTEGER;
+ search_place_id BIGINT;
defpostalcode TEXT;
havefirstpoint BOOLEAN;
search_maxrank INTEGER;
address_maxrank INTEGER;
address_street_word_id INTEGER;
- parent_place_id_rank INTEGER;
+ parent_place_id_rank BIGINT;
isin TEXT[];
isin_tokens INT[];
DELETE FROM place_boundingbox where place_id = NEW.place_id;
result := deleteRoad(NEW.partition, NEW.place_id);
result := deleteLocationArea(NEW.partition, NEW.place_id);
-
+
+ -- reclaculate country and partition (should probably have a country_code and calculated_country_code as seperate fields)
+ SELECT country_code from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO NEW.country_code;
NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
NEW.partition := get_partition(NEW.geometry, NEW.country_code);
NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
IF NEW.parent_place_id IS NOT NULL THEN
-- Add the street to the address as zero distance to force to front of list
- INSERT INTO place_addressline VALUES (NEW.place_id, NEW.parent_place_id, true, true, 0, 26);
+-- INSERT INTO place_addressline VALUES (NEW.place_id, NEW.parent_place_id, true, true, 0, 26);
address_havelevel[26] := true;
-- Import address details from parent, reclculating distance in process
- INSERT INTO place_addressline select NEW.place_id, x.address_place_id, x.fromarea, x.isaddress, ST_distance(NEW.geometry, placex.geometry), placex.rank_address
- from place_addressline as x join placex on (address_place_id = placex.place_id)
- where x.place_id = NEW.parent_place_id and x.address_place_id != NEW.parent_place_id;
+-- INSERT INTO place_addressline select NEW.place_id, x.address_place_id, x.fromarea, x.isaddress, ST_distance(NEW.geometry, placex.geometry), placex.rank_address
+-- from place_addressline as x join placex on (address_place_id = placex.place_id)
+-- where x.place_id = NEW.parent_place_id and x.address_place_id != NEW.parent_place_id;
-- Get the details of the parent road
select * from search_name where place_id = NEW.parent_place_id INTO location;
result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
END IF;
- result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, place_centroid);
+ result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid);
return NEW;
END IF;
-- Process area matches
location_rank_search := 100;
location_distance := 0;
---RAISE WARNING ' getNearFeatures(%,%,%,%)',NEW.partition, place_centroid, search_maxrank, isin_tokens;
+--RAISE WARNING ' getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens;
FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
--RAISE WARNING ' AREA: %',location;
location_distance := location.distance * 1.5;
END IF;
- IF location.distance < location_distance THEN
+ IF location.distance < location_distance OR NOT location.isguess THEN
-- Add it to the list of search terms
nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
address_havelevel[location.rank_address] := true;
+--RAISE WARNING ' Terms: (%) %',location, nameaddress_vector;
+
IF location.rank_address > parent_place_id_rank THEN
NEW.parent_place_id = location.place_id;
parent_place_id_rank = location.rank_address;
-- 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 ' ISIN: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
+--RAISE WARNING ' getNearestNamedFeature: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
+
+--RAISE WARNING ' ISIN: %',location;
+
nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
address_havelevel[location.rank_address] := true;
result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.country_code, NEW.geometry);
END IF;
- result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, place_centroid);
+ result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid);
-- INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, 0, NEW.country_code, name_vector, nameaddress_vector, place_centroid);
END IF;
CREATE OR REPLACE FUNCTION place_delete() RETURNS TRIGGER
AS $$
DECLARE
- placeid INTEGER;
+ placeid BIGINT;
BEGIN
-- RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;
+ -- deleting large polygons can have a massive effect ont he system - require manual intervention to let them through
+ IF st_area(OLD.geometry) > 2 THEN
+ insert into import_polygon_delete values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type);
+ RETURN NULL;
+ END IF;
+
-- mark for delete
UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
existing RECORD;
existingplacex RECORD;
existinggeometry GEOMETRY;
- existingplace_id INTEGER;
+ existingplace_id BIGINT;
result BOOLEAN;
partition INTEGER;
BEGIN
select * from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existingplacex;
-- Handle a place changing type by removing the old data
- -- My generated 'place' types are causing havok because they overlap with real tags
+ -- My generated 'place' types are causing havok because they overlap with real keys
-- TODO: move them to their own special purpose key/class to avoid collisions
--- IF existing.osm_type IS NULL AND (NEW.type not in ('postcode','house','houses')) THEN
--- DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type not in ('postcode','house','houses');
--- END IF;
+ IF existing.osm_type IS NULL AND (NEW.type not in ('postcode','house','houses')) THEN
+ DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type not in ('postcode','house','houses');
+ END IF;
-- RAISE WARNING 'Existing: %',existing.place_id;
END IF;
DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
+ DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
-- To paraphrase, if there isn't an existing item, OR if the admin level has changed, OR if it is a major change in geometry
IF existing.osm_type IS NULL
$$
LANGUAGE plpgsql IMMUTABLE;
-CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id INTEGER) RETURNS TEXT
+CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id BIGINT) RETURNS TEXT
AS $$
DECLARE
result TEXT[];
$$
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id INTEGER, languagepref TEXT[]) RETURNS TEXT
+CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT, languagepref TEXT[]) RETURNS TEXT
AS $$
DECLARE
result TEXT[];
DROP TYPE addressline CASCADE;
create type addressline as (
- place_id INTEGER,
+ place_id BIGINT,
osm_type CHAR(1),
osm_id INTEGER,
name HSTORE,
distance FLOAT
);
-CREATE OR REPLACE FUNCTION get_addressdata(in_place_id INTEGER) RETURNS setof addressline
+CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT) RETURNS setof addressline
AS $$
DECLARE
- for_place_id INTEGER;
+ for_place_id BIGINT;
result TEXT[];
search TEXT[];
found INTEGER;
found := 1000;
hadcountry := false;
+ FOR location IN
+ select placex.place_id, osm_type, osm_id,
+ CASE WHEN class = 'place' and type = 'postcode' THEN 'name' => postcode ELSE name END as name,
+ class, type, admin_level, true as fromarea, 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
+ 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 searchpostcode IS NOT NULL and location.type = 'postcode' 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;
+ 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.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 'name' => postcode ELSE name END as name,
distance,country_code
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) OR address_place_id = for_place_id)
+ and (cached_rank_address > 0 AND cached_rank_address < searchrankaddress)
+ and address_place_id != for_place_id
and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode OR rank_address < 4)
order by rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc
LOOP
$$
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION get_place_boundingbox(search_place_id INTEGER) RETURNS place_boundingbox
+CREATE OR REPLACE FUNCTION get_place_boundingbox(search_place_id BIGINT) RETURNS place_boundingbox
AS $$
DECLARE
result place_boundingbox;
LANGUAGE plpgsql;
-- don't do the operation if it would be slow
-CREATE OR REPLACE FUNCTION get_place_boundingbox_quick(search_place_id INTEGER) RETURNS place_boundingbox
+CREATE OR REPLACE FUNCTION get_place_boundingbox_quick(search_place_id BIGINT) RETURNS place_boundingbox
AS $$
DECLARE
result place_boundingbox;
$$
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION update_place(search_place_id INTEGER) RETURNS BOOLEAN
+CREATE OR REPLACE FUNCTION update_place(search_place_id BIGINT) RETURNS BOOLEAN
AS $$
DECLARE
result place_boundingbox;
rangestartnumber INTEGER;
place_centroid GEOMETRY;
partition INTEGER;
- parent_place_id INTEGER;
+ parent_place_id BIGINT;
location RECORD;
address_street_word_id INTEGER;
newpoints INTEGER;
place_centroid GEOMETRY;
partition INTEGER;
- parent_place_id INTEGER;
+ out_parent_place_id BIGINT;
location RECORD;
address_street_word_id INTEGER;
+ out_postcode TEXT;
BEGIN
place_centroid := ST_Centroid(pointgeo);
partition := get_partition(place_centroid, in_countrycode);
- parent_place_id := null;
+ out_parent_place_id := null;
address_street_word_id := get_name_id(make_standard_name(in_street));
IF address_street_word_id IS NOT NULL THEN
FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
- parent_place_id := location.place_id;
+ out_parent_place_id := location.place_id;
END LOOP;
END IF;
- IF parent_place_id IS NULL THEN
+ IF out_parent_place_id IS NULL THEN
FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
- parent_place_id := location.place_id;
+ out_parent_place_id := location.place_id;
END LOOP;
END IF;
+ out_postcode := in_postcode;
+ IF out_postcode IS NULL THEN
+ SELECT postcode from placex where place_id = out_parent_place_id INTO out_postcode;
+ END IF;
+ IF out_postcode IS NULL THEN
+ out_postcode := getNearestPostcode(partition, place_centroid);
+ END IF;
+
newpoints := 0;
insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)
- values (nextval('seq_place'), partition, parent_place_id, in_housenumber, in_postcode, place_centroid);
+ values (nextval('seq_place'), partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
newpoints := newpoints + 1;
RETURN newpoints;
projects / nominatim.git / blobdiff