From 3dba772852378683e864feab37f5a74b803e6030 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Tue, 22 Jan 2013 08:01:30 +0100 Subject: [PATCH] delete outdated entries from location_area_country --- sql/functions.sql | 8 ++++---- sql/partition-functions.src.sql | 7 ++++++- 2 files changed, 10 insertions(+), 5 deletions(-) diff --git a/sql/functions.sql b/sql/functions.sql index 0d691411..faa759cc 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -637,7 +637,7 @@ BEGIN -- RAISE WARNING 'Adding location with rank > 25 (% rank %)', place_id, rank_search; - x := deleteLocationArea(partition, place_id); + x := deleteLocationArea(partition, place_id, rank_search); isarea := false; IF (ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(geometry)) THEN @@ -704,7 +704,7 @@ CREATE OR REPLACE FUNCTION update_location( DECLARE b BOOLEAN; BEGIN - b := deleteLocationArea(partition, place_id); + b := deleteLocationArea(partition, place_id, rank_search); -- result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry); RETURN add_location(place_id, place_country_code, name, rank_search, rank_address, geometry); END; @@ -1323,7 +1323,7 @@ BEGIN DELETE FROM place_addressline WHERE place_id = NEW.place_id; DELETE FROM place_boundingbox where place_id = NEW.place_id; result := deleteRoad(NEW.partition, NEW.place_id); - result := deleteLocationArea(NEW.partition, NEW.place_id); + result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search); UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id; END IF; @@ -1884,7 +1884,7 @@ BEGIN --DEBUG: RAISE WARNING 'placex_delete:08 % %',OLD.osm_type,OLD.osm_id; IF OLD.rank_address < 26 THEN - b := deleteLocationArea(OLD.partition, OLD.place_id); + b := deleteLocationArea(OLD.partition, OLD.place_id, OLD.rank_search); END IF; --DEBUG: RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id; diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index 9ea70409..6afbbdda 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -31,10 +31,15 @@ END $$ LANGUAGE plpgsql; -create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$ +create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT, in_rank_search INTEGER) RETURNS BOOLEAN AS $$ DECLARE BEGIN + IF in_rank_search <= 4 THEN + DELETE from location_area_country WHERE place_id = in_place_id; + RETURN TRUE; + END IF; + -- start IF in_partition = -partition- THEN DELETE from location_area_large_-partition- WHERE place_id = in_place_id; -- 2.39.5