]> git.openstreetmap.org Git - nominatim.git/blob - sql/tables.sql
deduplicate search term graph slightly earlier
[nominatim.git] / sql / tables.sql
1 drop table import_status;
2 CREATE TABLE import_status (
3   lastimportdate timestamp NOT NULL
4   );
5 GRANT SELECT ON import_status TO "www-data" ;
6
7 drop table import_osmosis_log;
8 CREATE TABLE import_osmosis_log (
9   batchend timestamp,
10   batchsize integer,
11   starttime timestamp,
12   endtime timestamp,
13   event text
14   );
15
16 --drop table IF EXISTS query_log;
17 CREATE TABLE query_log (
18   starttime timestamp,
19   query text,
20   ipaddress text,
21   endtime timestamp,
22   results integer
23   );
24 CREATE INDEX idx_query_log ON query_log USING BTREE (starttime);
25 GRANT INSERT ON query_log TO "www-data" ;
26
27 CREATE TABLE new_query_log (
28   type text,
29   starttime timestamp,
30   ipaddress text,
31   useragent text,
32   language text,
33   query text,
34   endtime timestamp,
35   results integer,
36   format text,
37   secret text
38   );
39 CREATE INDEX idx_new_query_log_starttime ON new_query_log USING BTREE (starttime);
40 GRANT INSERT ON new_query_log TO "www-data" ;
41 GRANT UPDATE ON new_query_log TO "www-data" ;
42 GRANT SELECT ON new_query_log TO "www-data" ;
43
44 create view vw_search_query_log as SELECT substr(query, 1, 50) AS query, starttime, endtime - starttime AS duration, substr(useragent, 1, 20) as 
45 useragent, language, results, ipaddress FROM new_query_log WHERE type = 'search' ORDER BY starttime DESC;
46
47 --drop table IF EXISTS report_log;
48 CREATE TABLE report_log (
49   starttime timestamp,
50   ipaddress text,
51   query text,
52   description text,
53   email text
54   );
55 GRANT INSERT ON report_log TO "www-data" ;
56
57 drop table IF EXISTS word;
58 CREATE TABLE word (
59   word_id INTEGER,
60   word_token text,
61   word_trigram text,
62   word text,
63   class text,
64   type text,
65   country_code varchar(2),
66   search_name_count INTEGER,
67   operator TEXT
68   );
69 SELECT AddGeometryColumn('word', 'location', 4326, 'GEOMETRY', 2);
70 CREATE INDEX idx_word_word_id on word USING BTREE (word_id);
71 CREATE INDEX idx_word_word_token on word USING BTREE (word_token);
72 --CREATE INDEX idx_word_trigram ON word USING gin(word_trigram gin_trgm_ops) WITH (fastupdate = off);
73 GRANT SELECT ON word TO "www-data" ;
74 DROP SEQUENCE seq_word;
75 CREATE SEQUENCE seq_word start 1;
76
77 drop table IF EXISTS location_area CASCADE;
78 CREATE TABLE location_area (
79   partition integer,
80   place_id INTEGER,
81   country_code VARCHAR(2), 
82   keywords INTEGER[],
83   rank_search INTEGER NOT NULL,
84   rank_address INTEGER NOT NULL,
85   isguess BOOL
86   );
87 SELECT AddGeometryColumn('location_area', 'centroid', 4326, 'POINT', 2);
88 SELECT AddGeometryColumn('location_area', 'geometry', 4326, 'GEOMETRY', 2);
89
90 CREATE TABLE location_area_large () INHERITS (location_area);
91 CREATE TABLE location_area_roadnear () INHERITS (location_area);
92 CREATE TABLE location_area_roadfar () INHERITS (location_area);
93
94 drop table IF EXISTS location_property CASCADE;
95 CREATE TABLE location_property (
96   place_id INTEGER,
97   partition integer,
98   parent_place_id INTEGER,
99   housenumber TEXT,
100   postcode TEXT
101   );
102 SELECT AddGeometryColumn('location_property', 'centroid', 4326, 'POINT', 2);
103
104 CREATE TABLE location_property_aux () INHERITS (location_property);
105 CREATE INDEX idx_location_property_aux_place_id ON location_property_aux USING BTREE (place_id);
106 CREATE INDEX idx_location_property_aux_parent_place_id ON location_property_aux USING BTREE (parent_place_id);
107 CREATE INDEX idx_location_property_aux_housenumber_parent_place_id ON location_property_aux USING BTREE (parent_place_id, housenumber);
108
109 CREATE TABLE location_property_tiger () INHERITS (location_property);
110 CREATE INDEX idx_location_property_tiger_place_id ON location_property_tiger USING BTREE (place_id);
111 CREATE INDEX idx_location_property_tiger_parent_place_id ON location_property_tiger USING BTREE (parent_place_id);
112 CREATE INDEX idx_location_property_tiger_housenumber_parent_place_id ON location_property_tiger USING BTREE (parent_place_id, housenumber);
113
114 drop table IF EXISTS search_name_blank CASCADE;
115 CREATE TABLE search_name_blank (
116   place_id INTEGER,
117   search_rank integer,
118   address_rank integer,
119   importance FLOAT,
120   country_code varchar(2),
121   name_vector integer[],
122   nameaddress_vector integer[]
123   );
124 SELECT AddGeometryColumn('search_name_blank', 'centroid', 4326, 'GEOMETRY', 2);
125
126 drop table IF EXISTS search_name;
127 CREATE TABLE search_name () INHERITS (search_name_blank);
128 CREATE INDEX search_name_name_vector_idx ON search_name USING GIN (name_vector gin__int_ops) WITH (fastupdate = off);
129 CREATE INDEX searchnameplacesearch_search_nameaddress_vector_idx ON search_name USING GIN (nameaddress_vector gin__int_ops) WITH (fastupdate = off);
130 CREATE INDEX idx_search_name_centroid ON search_name USING GIST (centroid);
131 CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id);
132
133 drop table IF EXISTS place_addressline;
134 CREATE TABLE place_addressline (
135   place_id INTEGER,
136   address_place_id INTEGER,
137   fromarea boolean,
138   isaddress boolean,
139   distance float,
140   cached_rank_address integer
141   );
142 CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id);
143 CREATE INDEX idx_place_addressline_address_place_id on place_addressline USING BTREE (address_place_id);
144
145 drop table IF EXISTS place_boundingbox CASCADE;
146 CREATE TABLE place_boundingbox (
147   place_id INTEGER,
148   minlat float,
149   maxlat float,
150   minlon float,
151   maxlon float,
152   numfeatures integer,
153   area float
154   );
155 CREATE INDEX idx_place_boundingbox_place_id on place_boundingbox USING BTREE (place_id);
156 SELECT AddGeometryColumn('place_boundingbox', 'outline', 4326, 'GEOMETRY', 2);
157 CREATE INDEX idx_place_boundingbox_outline ON place_boundingbox USING GIST (outline);
158 GRANT SELECT on place_boundingbox to "www-data" ;
159 GRANT INSERT on place_boundingbox to "www-data" ;
160
161 drop table IF EXISTS reverse_cache;
162 CREATE TABLE reverse_cache (
163   latlonzoomid integer,
164   country_code varchar(2),
165   place_id INTEGER
166   );
167 GRANT SELECT on reverse_cache to "www-data" ;
168 GRANT INSERT on reverse_cache to "www-data" ;
169 CREATE INDEX idx_reverse_cache_latlonzoomid ON reverse_cache USING BTREE (latlonzoomid);
170
171 drop table country;
172 CREATE TABLE country (
173   country_code varchar(2),
174   country_name hstore,
175   country_default_language_code varchar(2)
176   );
177 SELECT AddGeometryColumn('country', 'geometry', 4326, 'POLYGON', 2);
178 insert into country select iso3166::varchar(2), 'name:en'->cntry_name, null, 
179   ST_Transform(geometryn(the_geom, generate_series(1, numgeometries(the_geom))), 4326) from worldboundaries;
180 CREATE INDEX idx_country_country_code ON country USING BTREE (country_code);
181 CREATE INDEX idx_country_geometry ON country USING GIST (geometry);
182
183 drop table placex;
184 CREATE TABLE placex (
185   place_id INTEGER NOT NULL,
186   partition integer,
187   osm_type char(1),
188   osm_id INTEGER,
189   class TEXT NOT NULL,
190   type TEXT NOT NULL,
191   name HSTORE,
192   admin_level INTEGER,
193   housenumber TEXT,
194   street TEXT,
195   isin TEXT,
196   postcode TEXT,
197   country_code varchar(2),
198   extratags HSTORE,
199   parent_place_id INTEGER,
200   linked_place_id INTEGER,
201   rank_address INTEGER,
202   rank_search INTEGER,
203   importance FLOAT,
204   indexed_status INTEGER,
205   indexed_date TIMESTAMP,
206   geometry_sector INTEGER
207   );
208 SELECT AddGeometryColumn('placex', 'geometry', 4326, 'GEOMETRY', 2);
209 CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id);
210 CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id);
211 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search);
212 CREATE INDEX idx_placex_rank_address ON placex USING BTREE (rank_address);
213 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry);
214
215 --CREATE INDEX idx_placex_indexed ON placex USING BTREE (indexed);
216
217 CREATE INDEX idx_placex_pending ON placex USING BTREE (rank_search) where indexed_status > 0;
218 CREATE INDEX idx_placex_pendingsector ON placex USING BTREE (rank_search,geometry_sector) where indexed_status > 0;
219
220 --CREATE INDEX idx_placex_pendingbylatlon ON placex USING BTREE (geometry_index(geometry_sector,indexed,name),rank_search)  where geometry_index(geometry_sector,indexed,name) IS NOT NULL;
221
222 CREATE INDEX idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) where parent_place_id IS NOT NULL;
223 CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) where indexed_status > 0 and class='place' and type='houses';
224
225 CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id);
226 CLUSTER placex USING idx_placex_sector;
227
228 DROP SEQUENCE seq_place;
229 CREATE SEQUENCE seq_place start 1;
230 GRANT SELECT on placex to "www-data" ;
231 GRANT UPDATE ON placex to "www-data" ;
232 GRANT SELECT ON search_name to "www-data" ;
233 GRANT DELETE on search_name to "www-data" ;
234 GRANT INSERT on search_name to "www-data" ;
235 GRANT SELECT on place_addressline to "www-data" ;
236 GRANT INSERT ON place_addressline to "www-data" ;
237 GRANT DELETE on place_addressline to "www-data" ;
238 GRANT SELECT ON seq_word to "www-data" ;
239 GRANT UPDATE ON seq_word to "www-data" ;
240 GRANT INSERT ON word to "www-data" ;
241 GRANT SELECT ON planet_osm_ways to "www-data" ;
242 GRANT SELECT ON planet_osm_rels to "www-data" ;
243 GRANT SELECT on location_area to "www-data" ;
244 GRANT SELECT on country to "www-data" ;
245
246 -- insert creates the location tagbles, creates location indexes if indexed == true
247 CREATE TRIGGER placex_before_insert BEFORE INSERT ON placex
248     FOR EACH ROW EXECUTE PROCEDURE placex_insert();
249
250 -- update insert creates the location tables
251 CREATE TRIGGER placex_before_update BEFORE UPDATE ON placex
252     FOR EACH ROW EXECUTE PROCEDURE placex_update();
253
254 -- diff update triggers
255 CREATE TRIGGER placex_before_delete AFTER DELETE ON placex
256     FOR EACH ROW EXECUTE PROCEDURE placex_delete();
257 CREATE TRIGGER place_before_delete BEFORE DELETE ON place
258     FOR EACH ROW EXECUTE PROCEDURE place_delete();
259 CREATE TRIGGER place_before_insert BEFORE INSERT ON place
260     FOR EACH ROW EXECUTE PROCEDURE place_insert();
261
262 alter table placex add column geometry_sector INTEGER;
263 alter table placex add column indexed_status INTEGER;
264 alter table placex add column indexed_date TIMESTAMP;
265
266 update placex set geometry_sector = geometry_sector(geometry);
267 drop index idx_placex_pendingbylatlon;
268 drop index idx_placex_interpolation;
269 drop index idx_placex_sector;
270 CREATE INDEX idx_placex_pendingbylatlon ON placex USING BTREE (geometry_index(geometry_sector,indexed,name),rank_search) 
271   where geometry_index(geometry_sector,indexed,name) IS NOT NULL;
272 CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) where indexed = false and class='place' and type='houses';
273 CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id);
274
275 DROP SEQUENCE seq_postcodes;
276 CREATE SEQUENCE seq_postcodes start 1;
277
278 drop table import_polygon_error;
279 CREATE TABLE import_polygon_error (
280   osm_type char(1),
281   osm_id INTEGER,
282   class TEXT NOT NULL,
283   type TEXT NOT NULL,
284   name HSTORE,
285   country_code varchar(2),
286   updated timestamp,
287   errormessage text
288   );
289 SELECT AddGeometryColumn('import_polygon_error', 'prevgeometry', 4326, 'GEOMETRY', 2);
290 SELECT AddGeometryColumn('import_polygon_error', 'newgeometry', 4326, 'GEOMETRY', 2);
291 CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id);