From: Sarah Hoffmann Date: Tue, 9 Apr 2013 21:25:08 +0000 (+0200) Subject: Merge remote-tracking branch 'upstream/master' X-Git-Tag: deploy~624 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/9f880c367c4a9fd8a2950011eeba22c77ab0c914?hp=-c Merge remote-tracking branch 'upstream/master' Conflicts: sql/indices.src.sql sql/tables.sql --- 9f880c367c4a9fd8a2950011eeba22c77ab0c914 diff --combined sql/indices.src.sql index 2e5dde71,ea57e74b..4dc23bd2 --- a/sql/indices.src.sql +++ b/sql/indices.src.sql @@@ -1,30 -1,29 +1,29 @@@ -- Indices used only during search and update. -- These indices are created only after the indexing process is done. -CREATE INDEX idx_word_word_id on word USING BTREE (word_id); +CREATE INDEX idx_word_word_id on word USING BTREE (word_id) TABLESPACE ssd; -CREATE INDEX idx_search_name_nameaddress_vector ON search_name USING GIN (nameaddress_vector) WITH (fastupdate = off); -CREATE INDEX idx_search_name_name_vector ON search_name USING GIN (name_vector) WITH (fastupdate = off); -CREATE INDEX idx_search_name_centroid ON search_name USING GIST (centroid); +CREATE INDEX idx_search_name_nameaddress_vector ON search_name USING GIN (nameaddress_vector) WITH (fastupdate = off) TABLESPACE ssd; +CREATE INDEX idx_search_name_name_vector ON search_name USING GIN (name_vector) WITH (fastupdate = off) TABLESPACE ssd; +CREATE INDEX idx_search_name_centroid ON search_name USING GIST (centroid) TABLESPACE ssd; -CREATE INDEX idx_place_addressline_address_place_id on place_addressline USING BTREE (address_place_id); +CREATE INDEX idx_place_addressline_address_place_id on place_addressline USING BTREE (address_place_id) TABLESPACE ssd; -CREATE INDEX idx_place_boundingbox_place_id on place_boundingbox USING BTREE (place_id); -CREATE INDEX idx_place_boundingbox_outline ON place_boundingbox USING GIST (outline); +CREATE INDEX idx_place_boundingbox_place_id on place_boundingbox USING BTREE (place_id) TABLESPACE ssd; +CREATE INDEX idx_place_boundingbox_outline ON place_boundingbox USING GIST (outline) TABLESPACE ssd; DROP INDEX IF EXISTS idx_placex_rank_search; -CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search); -CREATE INDEX idx_placex_rank_address ON placex USING BTREE (rank_address); -CREATE INDEX idx_placex_pendingsector ON placex USING BTREE (rank_search,geometry_sector) where indexed_status > 0; -CREATE INDEX idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) where parent_place_id IS NOT NULL; -CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) where indexed_status > 0 and class='place' and type='houses'; -CREATE INDEX idx_location_area_country_place_id ON location_area_country USING BTREE (place_id); +CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search) TABLESPACE ssd; +CREATE INDEX idx_placex_rank_address ON placex USING BTREE (rank_address) TABLESPACE ssd; +CREATE INDEX idx_placex_pendingsector ON placex USING BTREE (rank_search,geometry_sector) TABLESPACE ssd where indexed_status > 0; +CREATE INDEX idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) TABLESPACE ssd where parent_place_id IS NOT NULL; +CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) TABLESPACE ssd where indexed_status > 0 and class='place' and type='houses'; +CREATE INDEX idx_location_area_country_place_id ON location_area_country USING BTREE (place_id) TABLESPACE ssd; -CREATE INDEX idx_search_name_country_centroid ON search_name_country USING GIST (centroid); +CREATE INDEX idx_search_name_country_centroid ON search_name_country USING GIST (centroid) TABLESPACE ssd; - CREATE INDEX idx_search_name_country_nameaddress_vector ON search_name_country USING GIN (nameaddress_vector) WITH (fastupdate = off) TABLESPACE ssd; -- start -CREATE INDEX idx_location_property_-partition-_centroid ON location_property_-partition- USING GIST (centroid); +CREATE INDEX idx_location_property_-partition-_centroid ON location_property_-partition- USING GIST (centroid) TABLESPACE ssd; -- end CREATE UNIQUE INDEX idx_place_osm_unique on place using btree(osm_id,osm_type,class,type); diff --combined sql/partition-tables.src.sql index 13b23d9f,29e3d282..e0b1fae0 --- a/sql/partition-tables.src.sql +++ b/sql/partition-tables.src.sql @@@ -1,9 -1,9 +1,9 @@@ - drop type nearplace cascade; + drop type if exists nearplace cascade; create type nearplace as ( place_id BIGINT ); - drop type nearfeature cascade; + drop type if exists nearfeature cascade; create type nearfeature as ( place_id BIGINT, keywords int[], @@@ -13,7 -13,7 +13,7 @@@ isguess boolean ); - drop type nearfeaturecentr cascade; + drop type if exists nearfeaturecentr cascade; create type nearfeaturecentr as ( place_id BIGINT, keywords int[], @@@ -24,22 -24,32 +24,32 @@@ centroid GEOMETRY ); + drop table IF EXISTS search_name_blank CASCADE; + CREATE TABLE search_name_blank ( + place_id BIGINT, + search_rank integer, + address_rank integer, + name_vector integer[] + ); + SELECT AddGeometryColumn('search_name_blank', 'centroid', 4326, 'GEOMETRY', 2); + + 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); @@@ -52,7 -62,7 +62,7 @@@ CREATE TABLE location_road_-partition- 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 sql/tables.sql index b5b59c16,3bb41e28..244f2036 --- a/sql/tables.sql +++ b/sql/tables.sql @@@ -23,6 -23,19 +23,6 @@@ CREATE TABLE import_npi_log event text ); ---drop table IF EXISTS query_log; -CREATE TABLE query_log ( - starttime timestamp, - query text, - ipaddress text, - endtime timestamp, - results integer - ); -CREATE INDEX idx_query_log ON query_log USING BTREE (starttime); -GRANT SELECT ON query_log TO "www-data" ; -GRANT INSERT ON query_log TO "www-data" ; -GRANT UPDATE ON query_log TO "www-data" ; - CREATE TABLE new_query_log ( type text, starttime timestamp, @@@ -30,7 -43,6 +30,7 @@@ useragent text, language text, query text, + searchterm text, endtime timestamp, results integer, format text, @@@ -41,6 -53,9 +41,6 @@@ GRANT INSERT ON new_query_log TO "www-d GRANT UPDATE ON new_query_log TO "www-data" ; GRANT SELECT ON new_query_log TO "www-data" ; -create view vw_search_query_log as SELECT substr(query, 1, 50) AS query, starttime, endtime - starttime AS duration, substr(useragent, 1, 20) as -useragent, language, results, ipaddress FROM new_query_log WHERE type = 'search' ORDER BY starttime DESC; - --drop table IF EXISTS report_log; CREATE TABLE report_log ( starttime timestamp, @@@ -55,17 -70,14 +55,14 @@@ drop table IF EXISTS word CREATE TABLE word ( word_id INTEGER, word_token text, - word_trigram text, word text, class text, type text, country_code varchar(2), search_name_count INTEGER, operator TEXT - ); -CREATE INDEX idx_word_word_token on word USING BTREE (word_token); + ) TABLESPACE ssd; - SELECT AddGeometryColumn('word', 'location', 4326, 'GEOMETRY', 2); +CREATE INDEX idx_word_word_token on word USING BTREE (word_token) TABLESPACE ssd; - --CREATE INDEX idx_word_trigram ON word USING gin(word_trigram gin_trgm_ops) WITH (fastupdate = off); GRANT SELECT ON word TO "www-data" ; DROP SEQUENCE seq_word; CREATE SEQUENCE seq_word start 1; @@@ -109,8 -121,8 +106,8 @@@ CREATE INDEX idx_location_property_tige CREATE INDEX idx_location_property_tiger_housenumber_parent_place_id ON location_property_tiger USING BTREE (parent_place_id, housenumber); GRANT SELECT ON location_property_tiger TO "www-data"; - drop table IF EXISTS search_name_blank CASCADE; - CREATE TABLE search_name_blank ( + drop table IF EXISTS search_name; + CREATE TABLE search_name ( place_id BIGINT, search_rank integer, address_rank integer, @@@ -119,11 -131,8 +116,8 @@@ name_vector integer[], nameaddress_vector integer[] ); - SELECT AddGeometryColumn('search_name_blank', 'centroid', 4326, 'GEOMETRY', 2); - - drop table IF EXISTS search_name; - CREATE TABLE search_name () INHERITS (search_name_blank) TABLESPACE ssd; + SELECT AddGeometryColumn('search_name', 'centroid', 4326, 'GEOMETRY', 2); -CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id); +CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id) TABLESPACE ssd; drop table IF EXISTS place_addressline; CREATE TABLE place_addressline ( @@@ -133,8 -142,8 +127,8 @@@ isaddress boolean, distance float, cached_rank_address integer - ); -CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id); + ) TABLESPACE data; +CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id) TABLESPACE ssd; drop table IF EXISTS place_boundingbox CASCADE; CREATE TABLE place_boundingbox ( @@@ -187,14 -196,14 +181,14 @@@ CREATE TABLE placex wikipedia TEXT, -- calculated wikipedia article name (language:title) geometry_sector INTEGER, calculated_country_code varchar(2) - ); + ) TABLESPACE ssd; SELECT AddGeometryColumn('placex', 'centroid', 4326, 'GEOMETRY', 2); -CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id); -CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id); -CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id); -CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector); -CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry); -CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) WHERE osm_type='N' and rank_search < 26; +CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) TABLESPACE ssd; +CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) TABLESPACE ssd; +CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) TABLESPACE ssd; +CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) TABLESPACE ssd; +CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) TABLESPACE ssd; +CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) TABLESPACE ssd WHERE osm_type='N' and rank_search < 26; --CREATE INDEX idx_placex_indexed ON placex USING BTREE (indexed); @@@ -235,7 -244,7 +229,7 @@@ CREATE TRIGGER place_before_insert BEFO FOR EACH ROW EXECUTE PROCEDURE place_insert(); drop index idx_placex_sector; -CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id); +CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id) TABLESPACE ssd; DROP SEQUENCE seq_postcodes; CREATE SEQUENCE seq_postcodes start 1; @@@ -253,7 -262,7 +247,7 @@@ CREATE TABLE import_polygon_error ); SELECT AddGeometryColumn('import_polygon_error', 'prevgeometry', 4326, 'GEOMETRY', 2); SELECT AddGeometryColumn('import_polygon_error', 'newgeometry', 4326, 'GEOMETRY', 2); -CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id); +CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id) TABLESPACE ssd; GRANT SELECT ON import_polygon_error TO "www-data"; drop table import_polygon_delete; @@@ -263,7 -272,7 +257,7 @@@ CREATE TABLE import_polygon_delete class TEXT NOT NULL, type TEXT NOT NULL ); -CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id); +CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id) TABLESPACE ssd; GRANT SELECT ON import_polygon_delete TO "www-data"; drop sequence file; diff --combined website/search.php index 5733dde4,139ed0f6..9940d815 --- a/website/search.php +++ b/website/search.php @@@ -71,7 -71,6 +71,7 @@@ if (isset($aLangPrefOrder['name:de'])) $bReverseInPlan = true; if (isset($aLangPrefOrder['name:ru'])) $bReverseInPlan = true; if (isset($aLangPrefOrder['name:ja'])) $bReverseInPlan = true; + if (isset($aLangPrefOrder['name:pl'])) $bReverseInPlan = true; $sLanguagePrefArraySQL = "ARRAY[".join(',',array_map("getDBQuoted",$aLangPrefOrder))."]"; @@@ -320,7 -319,7 +320,7 @@@ { $sQuery = str_replace($aSpecialTerm[0], ' ', $sQuery); $sToken = $oDB->getOne("select make_standard_name('".$aSpecialTerm[1]."') as string"); - $sSQL = 'select * from (select word_id,word_token, word, class, type, location, country_code, operator'; + $sSQL = 'select * from (select word_id,word_token, word, class, type, country_code, operator'; $sSQL .= ' from word where word_token in (\' '.$sToken.'\')) as x where (class is not null and class not in (\'place\')) or country_code is not null'; if (CONST_Debug) var_Dump($sSQL); $aSearchWords = $oDB->getAll($sSQL); @@@ -397,10 -396,10 +397,10 @@@ { // Check which tokens we have, get the ID numbers - $sSQL = 'select word_id,word_token, word, class, type, location, country_code, operator, search_name_count'; + $sSQL = 'select word_id,word_token, word, class, type, country_code, operator, search_name_count'; $sSQL .= ' from word where word_token in ('.join(',',array_map("getDBQuoted",$aTokens)).')'; // $sSQL .= ' and search_name_count < '.CONST_Max_Word_Frequency; - // $sSQL .= ' group by word_token, word, class, type, location, country_code'; + // $sSQL .= ' group by word_token, word, class, type, country_code'; if (CONST_Debug) var_Dump($sSQL); @@@ -978,17 -977,12 +978,17 @@@ else $sSQL .= " limit ".$iLimit; - if (CONST_Debug) { var_dump($sSQL); } + if (CONST_Debug) var_dump($sSQL); + $iStartTime = time(); $aViewBoxPlaceIDs = $oDB->getAll($sSQL); if (PEAR::IsError($aViewBoxPlaceIDs)) { failInternalError("Could not get places for search terms.", $sSQL, $aViewBoxPlaceIDs); } + if (time() - $iStartTime > 60) { + file_put_contents(CONST_BasePath.'/log/long_queries.log', date('Y-m-d H:i:s', $iStartTime).' '.$sSQL."\n", FILE_APPEND); + } + //var_dump($aViewBoxPlaceIDs); // Did we have an viewbox matches? $aPlaceIDs = array();