-- 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);
- 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[],
isguess boolean
);
- drop type nearfeaturecentr cascade;
+ drop type if exists nearfeaturecentr cascade;
create type nearfeaturecentr as (
place_id BIGINT,
keywords int[],
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);
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
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,
useragent text,
language text,
query text,
+ searchterm text,
endtime timestamp,
results integer,
format text,
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,
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;
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,
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 (
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 (
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);
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;
);
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;
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;
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))."]";
{
$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);
{
// 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);
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();
projects / nominatim.git / commitdiff