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=356724fb8b8d34ba92b5161dec91e7afb18ab99e Merge remote-tracking branch 'upstream/master' Conflicts: sql/indices.src.sql sql/tables.sql --- diff --git a/sql/functions.sql b/sql/functions.sql index 0cb6d32a..e0d0897f 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -95,7 +95,7 @@ BEGIN SELECT min(word_id), max(search_name_count) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id, count; IF return_word_id IS NULL THEN return_word_id := nextval('seq_word'); - INSERT INTO word VALUES (return_word_id, lookup_token, regexp_replace(lookup_token,E'([^0-9])\\1+',E'\\1','g'), null, null, null, null, 0, null); + INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, 0); ELSE IF count > get_maxwordfreq() THEN return_word_id := NULL; @@ -117,7 +117,7 @@ BEGIN SELECT min(word_id) FROM word WHERE word_token = lookup_token and class='place' and type='house' into return_word_id; IF return_word_id IS NULL THEN return_word_id := nextval('seq_word'); - INSERT INTO word VALUES (return_word_id, lookup_token, null, null, 'place', 'house', null, 0, null); + INSERT INTO word VALUES (return_word_id, lookup_token, null, 'place', 'house', null, 0); END IF; RETURN return_word_id; END; @@ -135,7 +135,7 @@ BEGIN SELECT min(word_id) FROM word WHERE word_token = lookup_token and country_code=lookup_country_code into return_word_id; IF return_word_id IS NULL THEN return_word_id := nextval('seq_word'); - INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, lookup_country_code, 0, null); + INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, lookup_country_code, 0); END IF; RETURN return_word_id; END; @@ -153,7 +153,7 @@ BEGIN SELECT min(word_id) FROM word WHERE word_token = lookup_token and class=lookup_class and type = lookup_type into return_word_id; IF return_word_id IS NULL THEN return_word_id := nextval('seq_word'); - INSERT INTO word VALUES (return_word_id, lookup_token, null, null, lookup_class, lookup_type, null, 0, null); + INSERT INTO word VALUES (return_word_id, lookup_token, null, lookup_class, lookup_type, null, 0); END IF; RETURN return_word_id; END; @@ -171,7 +171,7 @@ BEGIN SELECT min(word_id) FROM word WHERE word_token = lookup_token into return_word_id; IF return_word_id IS NULL THEN return_word_id := nextval('seq_word'); - INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, null, 0, null); + INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, 0); END IF; RETURN return_word_id; END; @@ -203,7 +203,7 @@ BEGIN SELECT min(word_id) FROM word WHERE word_token = lookup_token and class=lookup_class and type = lookup_type and operator = op into return_word_id; IF return_word_id IS NULL THEN return_word_id := nextval('seq_word'); - INSERT INTO word VALUES (return_word_id, lookup_token, null, null, lookup_class, lookup_type, null, 0, op, null); + INSERT INTO word VALUES (return_word_id, lookup_token, null, lookup_class, lookup_type, null, 0, op); END IF; RETURN return_word_id; END; @@ -222,7 +222,7 @@ BEGIN SELECT min(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id; IF return_word_id IS NULL THEN return_word_id := nextval('seq_word'); - INSERT INTO word VALUES (return_word_id, lookup_token, regexp_replace(lookup_token,E'([^0-9])\\1+',E'\\1','g'), src_word, null, null, null, 0, null); + INSERT INTO word VALUES (return_word_id, lookup_token, src_word, null, null, null, 0); -- nospace_lookup_token := replace(replace(lookup_token, '-',''), ' ',''); -- IF ' '||nospace_lookup_token != lookup_token THEN -- INSERT INTO word VALUES (return_word_id, '-'||nospace_lookup_token, null, src_word, null, null, null, 0, null); @@ -1560,7 +1560,7 @@ BEGIN result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry); END IF; - result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid); + result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry); return NEW; END IF; @@ -1891,9 +1891,8 @@ BEGIN result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.calculated_country_code, NEW.geometry); END IF; - result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid); + result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry); --- INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, 0, NEW.calculated_country_code, name_vector, nameaddress_vector, place_centroid); END IF; -- If we've not managed to pick up a better one - default centroid @@ -2344,7 +2343,7 @@ END; $$ LANGUAGE plpgsql; -DROP TYPE addressline CASCADE; +DROP TYPE IF EXISTS addressline CASCADE; create type addressline as ( place_id BIGINT, osm_type CHAR(1), diff --git a/sql/indices.src.sql b/sql/indices.src.sql index 2e5dde71..4dc23bd2 100644 --- a/sql/indices.src.sql +++ b/sql/indices.src.sql @@ -21,7 +21,6 @@ CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) TA 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) 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) TABLESPACE ssd; diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index 0e26849d..77f6caa8 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -166,7 +166,7 @@ create or replace function insertSearchName( in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_name_vector INTEGER[], in_nameaddress_vector INTEGER[], in_rank_search INTEGER, in_rank_address INTEGER, in_importance FLOAT, - in_centroid GEOMETRY) RETURNS BOOLEAN AS $$ + in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$ DECLARE BEGIN @@ -176,16 +176,16 @@ BEGIN IF in_rank_search <= 4 THEN DELETE FROM search_name_country WHERE place_id = in_place_id; - INSERT INTO search_name_country values (in_place_id, in_rank_search, in_rank_address, in_importance, in_country_code, - in_name_vector, in_nameaddress_vector, in_centroid); + INSERT INTO search_name_country values (in_place_id, in_rank_search, in_rank_address, + in_name_vector, in_geometry); RETURN TRUE; END IF; -- start IF in_partition = -partition- THEN DELETE FROM search_name_-partition- values WHERE place_id = in_place_id; - INSERT INTO search_name_-partition- values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code, - in_name_vector, in_nameaddress_vector, in_centroid); + INSERT INTO search_name_-partition- values (in_place_id, in_rank_search, in_rank_address, + in_name_vector, in_geometry); RETURN TRUE; END IF; -- end diff --git a/sql/partition-tables.src.sql b/sql/partition-tables.src.sql index 13b23d9f..e0b1fae0 100644 --- a/sql/partition-tables.src.sql +++ b/sql/partition-tables.src.sql @@ -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 @@ create type nearfeature as ( isguess boolean ); -drop type nearfeaturecentr cascade; +drop type if exists nearfeaturecentr cascade; create type nearfeaturecentr as ( place_id BIGINT, keywords int[], @@ -24,6 +24,16 @@ create type nearfeaturecentr as ( 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) TABLESPACE ssd; diff --git a/sql/tables.sql b/sql/tables.sql index b5b59c16..244f2036 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -55,7 +55,6 @@ drop table IF EXISTS word; CREATE TABLE word ( word_id INTEGER, word_token text, - word_trigram text, word text, class text, type text, @@ -63,9 +62,7 @@ CREATE TABLE word ( search_name_count INTEGER, operator TEXT ) 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 +106,8 @@ CREATE INDEX idx_location_property_tiger_parent_place_id ON location_property_ti 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,10 +116,7 @@ CREATE TABLE search_name_blank ( 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) TABLESPACE ssd; drop table IF EXISTS place_addressline; diff --git a/website/search.php b/website/search.php index 5733dde4..9940d815 100755 --- a/website/search.php +++ b/website/search.php @@ -320,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 +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);