1 drop table import_status;
2 CREATE TABLE import_status (
3 lastimportdate timestamp NOT NULL
5 GRANT SELECT ON import_status TO "www-data" ;
7 drop table import_osmosis_log;
8 CREATE TABLE import_osmosis_log (
16 --drop table IF EXISTS query_log;
17 CREATE TABLE query_log (
24 CREATE INDEX idx_query_log ON query_log USING BTREE (starttime);
25 GRANT INSERT ON query_log TO "www-data" ;
27 CREATE TABLE new_query_log (
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" ;
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;
47 --drop table IF EXISTS report_log;
48 CREATE TABLE report_log (
55 GRANT INSERT ON report_log TO "www-data" ;
57 drop table IF EXISTS word;
65 country_code varchar(2),
66 search_name_count INTEGER,
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);
73 GRANT SELECT ON word TO "www-data" ;
74 DROP SEQUENCE seq_word;
75 CREATE SEQUENCE seq_word start 1;
77 drop table IF EXISTS location_area CASCADE;
78 CREATE TABLE location_area (
79 partition varchar(10),
81 country_code VARCHAR(2),
83 rank_search INTEGER NOT NULL,
84 rank_address INTEGER NOT NULL,
87 SELECT AddGeometryColumn('location_area', 'centroid', 4326, 'POINT', 2);
88 SELECT AddGeometryColumn('location_area', 'geometry', 4326, 'GEOMETRY', 2);
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);
94 drop table IF EXISTS search_name;
95 CREATE TABLE search_name (
100 country_code varchar(2),
101 name_vector integer[],
102 nameaddress_vector integer[]
104 CREATE INDEX search_name_name_vector_idx ON search_name USING GIN (name_vector gin__int_ops);
105 CREATE INDEX searchnameplacesearch_search_nameaddress_vector_idx ON search_name USING GIN (nameaddress_vector gin__int_ops);
106 SELECT AddGeometryColumn('search_name', 'centroid', 4326, 'GEOMETRY', 2);
107 CREATE INDEX idx_search_name_centroid ON search_name USING GIST (centroid);
108 CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id);
110 drop table IF EXISTS place_addressline;
111 CREATE TABLE place_addressline (
113 address_place_id INTEGER,
117 cached_rank_address integer
119 CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id);
120 CREATE INDEX idx_place_addressline_address_place_id on place_addressline USING BTREE (address_place_id);
122 drop table IF EXISTS place_boundingbox CASCADE;
123 CREATE TABLE place_boundingbox (
132 CREATE INDEX idx_place_boundingbox_place_id on place_boundingbox USING BTREE (place_id);
133 SELECT AddGeometryColumn('place_boundingbox', 'outline', 4326, 'GEOMETRY', 2);
134 CREATE INDEX idx_place_boundingbox_outline ON place_boundingbox USING GIST (outline);
135 GRANT SELECT on place_boundingbox to "www-data" ;
136 GRANT INSERT on place_boundingbox to "www-data" ;
138 drop table IF EXISTS reverse_cache;
139 CREATE TABLE reverse_cache (
140 latlonzoomid integer,
141 country_code varchar(2),
144 GRANT SELECT on reverse_cache to "www-data" ;
145 GRANT INSERT on reverse_cache to "www-data" ;
146 CREATE INDEX idx_reverse_cache_latlonzoomid ON reverse_cache USING BTREE (latlonzoomid);
149 CREATE TABLE country (
150 country_code varchar(2),
152 country_default_language_code varchar(2)
154 SELECT AddGeometryColumn('country', 'geometry', 4326, 'POLYGON', 2);
155 insert into country select iso3166::varchar(2), 'name:en'->cntry_name, null,
156 ST_Transform(geometryn(the_geom, generate_series(1, numgeometries(the_geom))), 4326) from worldboundaries;
157 CREATE INDEX idx_country_country_code ON country USING BTREE (country_code);
158 CREATE INDEX idx_country_geometry ON country USING GIST (geometry);
161 CREATE TABLE placex (
162 place_id INTEGER NOT NULL,
163 partition varchar(10),
174 country_code varchar(2),
176 parent_place_id INTEGER,
177 linked_place_id INTEGER,
178 rank_address INTEGER,
180 indexed_status INTEGER,
181 indexed_date TIMESTAMP,
182 geometry_sector INTEGER
184 SELECT AddGeometryColumn('placex', 'geometry', 4326, 'GEOMETRY', 2);
185 CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id);
186 CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id);
187 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search);
188 CREATE INDEX idx_placex_rank_address ON placex USING BTREE (rank_address);
189 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry);
191 --CREATE INDEX idx_placex_indexed ON placex USING BTREE (indexed);
193 CREATE INDEX idx_placex_pending ON placex USING BTREE (rank_search) where indexed_status > 0;
194 CREATE INDEX idx_placex_pendingsector ON placex USING BTREE (rank_search,geometry_sector) where indexed_status > 0;
196 --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;
198 CREATE INDEX idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) where parent_place_id IS NOT NULL;
199 CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) where indexed_status > 0 and class='place' and type='houses';
201 CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id);
202 CLUSTER placex USING idx_placex_sector;
204 DROP SEQUENCE seq_place;
205 CREATE SEQUENCE seq_place start 1;
206 GRANT SELECT on placex to "www-data" ;
207 GRANT UPDATE ON placex to "www-data" ;
208 GRANT SELECT ON search_name to "www-data" ;
209 GRANT DELETE on search_name to "www-data" ;
210 GRANT INSERT on search_name to "www-data" ;
211 GRANT SELECT on place_addressline to "www-data" ;
212 GRANT INSERT ON place_addressline to "www-data" ;
213 GRANT DELETE on place_addressline to "www-data" ;
214 GRANT SELECT ON seq_word to "www-data" ;
215 GRANT UPDATE ON seq_word to "www-data" ;
216 GRANT INSERT ON word to "www-data" ;
217 GRANT SELECT ON planet_osm_ways to "www-data" ;
218 GRANT SELECT ON planet_osm_rels to "www-data" ;
219 GRANT SELECT on location_area to "www-data" ;
220 GRANT SELECT on country to "www-data" ;
222 -- insert creates the location tagbles, creates location indexes if indexed == true
223 CREATE TRIGGER placex_before_insert BEFORE INSERT ON placex
224 FOR EACH ROW EXECUTE PROCEDURE placex_insert();
226 -- update insert creates the location tables
227 CREATE TRIGGER placex_before_update BEFORE UPDATE ON placex
228 FOR EACH ROW EXECUTE PROCEDURE placex_update();
230 -- diff update triggers
231 CREATE TRIGGER placex_before_delete AFTER DELETE ON placex
232 FOR EACH ROW EXECUTE PROCEDURE placex_delete();
233 CREATE TRIGGER place_before_delete BEFORE DELETE ON place
234 FOR EACH ROW EXECUTE PROCEDURE place_delete();
235 CREATE TRIGGER place_before_insert BEFORE INSERT ON place
236 FOR EACH ROW EXECUTE PROCEDURE place_insert();
238 alter table placex add column geometry_sector INTEGER;
239 alter table placex add column indexed_status INTEGER;
240 alter table placex add column indexed_date TIMESTAMP;
242 update placex set geometry_sector = geometry_sector(geometry);
243 drop index idx_placex_pendingbylatlon;
244 drop index idx_placex_interpolation;
245 drop index idx_placex_sector;
246 CREATE INDEX idx_placex_pendingbylatlon ON placex USING BTREE (geometry_index(geometry_sector,indexed,name),rank_search)
247 where geometry_index(geometry_sector,indexed,name) IS NOT NULL;
248 CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) where indexed = false and class='place' and type='houses';
249 CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id);