From: Sarah Hoffmann Date: Tue, 22 Jan 2013 07:14:58 +0000 (+0100) Subject: Merge remote-tracking branch 'upstream/master' X-Git-Tag: deploy~647 X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/5140b12aa290e7de8fd0c53f4977e881e5fee0e1?hp=b57dc6addfe6d91ad78642d1f6881d206b7e425a Merge remote-tracking branch 'upstream/master' Conflicts: sql/partition-functions.src.sql sql/partition-tables.src.sql --- 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/partitions.src.sql b/sql/partition-functions.src.sql similarity index 77% rename from sql/partitions.src.sql rename to sql/partition-functions.src.sql index ab5b1163..6afbbdda 100644 --- a/sql/partitions.src.sql +++ b/sql/partition-functions.src.sql @@ -1,62 +1,3 @@ -drop type nearplace cascade; -create type nearplace as ( - place_id BIGINT -); - -drop type nearfeature cascade; -create type nearfeature as ( - place_id BIGINT, - keywords int[], - rank_address integer, - rank_search integer, - distance float, - isguess boolean -); - -drop type nearfeaturecentr cascade; -create type nearfeaturecentr as ( - place_id BIGINT, - keywords int[], - rank_address integer, - rank_search integer, - distance float, - isguess boolean, - centroid GEOMETRY -); - -CREATE TABLE location_area_country () INHERITS (location_area_large); -CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) TABLESPACE ssd; - -CREATE TABLE search_name_country () INHERITS (search_name_blank); -CREATE INDEX idx_search_name_country_place_id ON search_name_country USING BTREE (place_id) TABLESPACE ssd; -CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector) WITH (fastupdate = off) TABLESPACE ssd; - --- start -CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large); -CREATE INDEX idx_location_area_large_-partition-_place_id ON location_area_large_-partition- USING BTREE (place_id) TABLESPACE ssd; -CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large_-partition- USING GIST (geometry) TABLESPACE ssd; - -CREATE TABLE search_name_-partition- () INHERITS (search_name_blank); -CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id) TABLESPACE ssd; -CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid) TABLESPACE ssd; -CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector) WITH (fastupdate = off) TABLESPACE ssd; - -CREATE TABLE location_property_-partition- () INHERITS (location_property); -CREATE INDEX idx_location_property_-partition-_place_id ON location_property_-partition- USING BTREE (place_id); -CREATE INDEX idx_location_property_-partition-_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id); -CREATE INDEX idx_location_property_-partition-_housenumber_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id, housenumber); - -CREATE TABLE location_road_-partition- ( - partition integer, - place_id BIGINT, - country_code VARCHAR(2) - ); -SELECT AddGeometryColumn('location_road_-partition-', 'geometry', 4326, 'GEOMETRY', 2); -CREATE INDEX idx_location_road_-partition-_geometry ON location_road_-partition- USING GIST (geometry) TABLESPACE ssd; -CREATE INDEX idx_location_road_-partition-_place_id ON location_road_-partition- USING BTREE (place_id) TABLESPACE ssd; - --- end - create or replace function getNearFeatures(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeaturecentr AS $$ DECLARE r nearfeaturecentr%rowtype; @@ -90,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; diff --git a/sql/partition-tables.src.sql b/sql/partition-tables.src.sql new file mode 100644 index 00000000..13b23d9f --- /dev/null +++ b/sql/partition-tables.src.sql @@ -0,0 +1,58 @@ +drop type nearplace cascade; +create type nearplace as ( + place_id BIGINT +); + +drop type nearfeature cascade; +create type nearfeature as ( + place_id BIGINT, + keywords int[], + rank_address integer, + rank_search integer, + distance float, + isguess boolean +); + +drop type nearfeaturecentr cascade; +create type nearfeaturecentr as ( + place_id BIGINT, + keywords int[], + rank_address integer, + rank_search integer, + distance float, + isguess boolean, + centroid GEOMETRY +); + +CREATE TABLE location_area_country () INHERITS (location_area_large); +CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) TABLESPACE ssd; + +CREATE TABLE search_name_country () INHERITS (search_name_blank); +CREATE INDEX idx_search_name_country_place_id ON search_name_country USING BTREE (place_id) TABLESPACE ssd; +CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector) WITH (fastupdate = off) TABLESPACE ssd; + +-- start +CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large); +CREATE INDEX idx_location_area_large_-partition-_place_id ON location_area_large_-partition- USING BTREE (place_id) TABLESPACE ssd; +CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large_-partition- USING GIST (geometry) TABLESPACE ssd; + +CREATE TABLE search_name_-partition- () INHERITS (search_name_blank); +CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id) TABLESPACE ssd; +CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid) TABLESPACE ssd; +CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector) WITH (fastupdate = off) TABLESPACE ssd; + +CREATE TABLE location_property_-partition- () INHERITS (location_property); +CREATE INDEX idx_location_property_-partition-_place_id ON location_property_-partition- USING BTREE (place_id); +CREATE INDEX idx_location_property_-partition-_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id); +CREATE INDEX idx_location_property_-partition-_housenumber_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id, housenumber); + +CREATE TABLE location_road_-partition- ( + partition integer, + place_id BIGINT, + country_code VARCHAR(2) + ); +SELECT AddGeometryColumn('location_road_-partition-', 'geometry', 4326, 'GEOMETRY', 2); +CREATE INDEX idx_location_road_-partition-_geometry ON location_road_-partition- USING GIST (geometry) TABLESPACE ssd; +CREATE INDEX idx_location_road_-partition-_place_id ON location_road_-partition- USING BTREE (place_id) TABLESPACE ssd; + +-- end diff --git a/utils/setup.php b/utils/setup.php index 6bd21055..ba86e92c 100755 --- a/utils/setup.php +++ b/utils/setup.php @@ -24,13 +24,14 @@ array('enable-debug-statements', '', 0, 1, 0, 0, 'bool', 'Include debug warning statements in pgsql commands'), array('create-minimal-tables', '', 0, 1, 0, 0, 'bool', 'Create minimal main tables'), array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'), - array('create-partitions', '', 0, 1, 0, 0, 'bool', 'Create required partition tables and triggers'), + array('create-partition-tables', '', 0, 1, 0, 0, 'bool', 'Create required partition tables'), + array('create-partition-functions', '', 0, 1, 0, 0, 'bool', 'Create required partition triggers'), array('import-wikipedia-articles', '', 0, 1, 0, 0, 'bool', 'Import wikipedia article dump'), array('load-data', '', 0, 1, 0, 0, 'bool', 'Copy data to live tables from import table'), array('disable-token-precalc', '', 0, 1, 0, 0, 'bool', 'Disable name precalculation (EXPERT)'), array('import-tiger-data', '', 0, 1, 0, 0, 'bool', 'Import tiger data (not included in \'all\')'), array('calculate-postcodes', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'), - array('create-roads', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'), + array('create-roads', '', 0, 1, 0, 0, 'bool', ''), array('osmosis-init', '', 0, 1, 0, 0, 'bool', 'Generate default osmosis configuration'), array('index', '', 0, 1, 0, 0, 'bool', 'Index the data'), array('index-noanalyse', '', 0, 1, 0, 0, 'bool', 'Do not perform analyse operations during index (EXPERT)'), @@ -223,9 +224,9 @@ pgsqlRunScript($sTemplate); } - if ($aCMDResult['create-partitions'] || $aCMDResult['all']) + if ($aCMDResult['create-partition-tables'] || $aCMDResult['all']) { - echo "Partitions\n"; + echo "Partition Tables\n"; $bDidSomething = true; $oDB =& getDB(); $sSQL = 'select partition from country_name order by country_code'; @@ -236,7 +237,36 @@ } $aPartitions[] = 0; - $sTemplate = file_get_contents(CONST_BasePath.'/sql/partitions.src.sql'); + $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql'); + preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER); + foreach($aMatches as $aMatch) + { + $sResult = ''; + foreach($aPartitions as $sPartitionName) + { + $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]); + } + $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate); + } + + pgsqlRunScript($sTemplate); + } + + + if ($aCMDResult['create-partition-functions'] || $aCMDResult['all']) + { + echo "Partition Functions\n"; + $bDidSomething = true; + $oDB =& getDB(); + $sSQL = 'select partition from country_name order by country_code'; + $aPartitions = $oDB->getCol($sSQL); + if (PEAR::isError($aPartitions)) + { + fail($aPartitions->getMessage()); + } + $aPartitions[] = 0; + + $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql'); preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER); foreach($aMatches as $aMatch) {