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=-c Merge remote-tracking branch 'upstream/master' Conflicts: sql/partition-functions.src.sql sql/partition-tables.src.sql --- 5140b12aa290e7de8fd0c53f4977e881e5fee0e1 diff --combined sql/partition-tables.src.sql index 00000000,554c3b04..13b23d9f mode 000000,100644..100644 --- a/sql/partition-tables.src.sql +++ b/sql/partition-tables.src.sql @@@ -1,0 -1,58 +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); ++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); -CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector) WITH (fastupdate = off); ++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); -CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large_-partition- USING GIST (geometry); ++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); -CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid); -CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector) WITH (fastupdate = off); ++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); -CREATE INDEX idx_location_road_-partition-_place_id ON location_road_-partition- USING BTREE (place_id); ++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 --combined utils/setup.php index 6bd21055,1ebee32f..ba86e92c --- a/utils/setup.php +++ b/utils/setup.php @@@ -24,13 -24,14 +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)'), @@@ -154,9 -155,8 +155,9 @@@ echo "Please download and build osm2pgsql.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n"; fail("osm2pgsql not found in '$osm2pgsql'"); } + $osm2pgsql .= ' --tablespace-slim-index ssd --tablespace-main-index ssd --tablespace-main-data ssd --tablespace-slim-data data'; $osm2pgsql .= ' -lsc -O gazetteer --hstore'; - $osm2pgsql .= ' -C '.$iCacheMemory; + $osm2pgsql .= ' -C 16000'; $osm2pgsql .= ' -P '.$aDSNInfo['port']; $osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file']; passthruCheckReturn($osm2pgsql); @@@ -223,9 -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 -236,36 +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) { @@@ -452,7 -481,7 +482,7 @@@ $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,"; $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,"; $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y "; - $sSQL .= "from placex where postcode is not null group by calculated_country_code,postcode) as x"; + $sSQL .= "from placex where postcode is not null and calculated_country_code not in ('ie') group by calculated_country_code,postcode) as x"; if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection)); $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";