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 import_npi_log;
  17 CREATE TABLE import_npi_log (
  18   npiid integer,
  19   batchend timestamp,
  20   batchsize integer,
  21   starttime timestamp,
  22   endtime timestamp,
  23   event text
  24   );
  25
  26 --drop table IF EXISTS query_log;
  27 CREATE TABLE query_log (
  28   starttime timestamp,
  29   query text,
  30   ipaddress text,
  31   endtime timestamp,
  32   results integer
  33   );
  34 CREATE INDEX idx_query_log ON query_log USING BTREE (starttime);
  35 GRANT SELECT ON query_log TO "www-data" ;
  36 GRANT INSERT ON query_log TO "www-data" ;
  37 GRANT UPDATE ON query_log TO "www-data" ;
  38
  39 CREATE TABLE new_query_log (
  40   type text,
  41   starttime timestamp,
  42   ipaddress text,
  43   useragent text,
  44   language text,
  45   query text,
  46   endtime timestamp,
  47   results integer,
  48   format text,
  49   secret text
  50   );
  51 CREATE INDEX idx_new_query_log_starttime ON new_query_log USING BTREE (starttime);
  52 GRANT INSERT ON new_query_log TO "www-data" ;
  53 GRANT UPDATE ON new_query_log TO "www-data" ;
  54 GRANT SELECT ON new_query_log TO "www-data" ;
  55
  56 create view vw_search_query_log as SELECT substr(query, 1, 50) AS query, starttime, endtime - starttime AS duration, substr(useragent, 1, 20) as
  57 useragent, language, results, ipaddress FROM new_query_log WHERE type = 'search' ORDER BY starttime DESC;
  58
  59 --drop table IF EXISTS report_log;
  60 CREATE TABLE report_log (
  61   starttime timestamp,
  62   ipaddress text,
  63   query text,
  64   description text,
  65   email text
  66   );
  67 GRANT INSERT ON report_log TO "www-data" ;
  68
  69 drop table IF EXISTS word;
  70 CREATE TABLE word (
  71   word_id INTEGER,
  72   word_token text,
  73   word text,
  74   class text,
  75   type text,
  76   country_code varchar(2),
  77   search_name_count INTEGER,
  78   operator TEXT
  79   );
  80 CREATE INDEX idx_word_word_token on word USING BTREE (word_token);
  81 GRANT SELECT ON word TO "www-data" ;
  82 DROP SEQUENCE seq_word;
  83 CREATE SEQUENCE seq_word start 1;
  84
  85 drop table IF EXISTS location_area CASCADE;
  86 CREATE TABLE location_area (
  87   partition integer,
  88   place_id BIGINT,
  89   country_code VARCHAR(2),
  90   keywords INTEGER[],
  91   rank_search INTEGER NOT NULL,
  92   rank_address INTEGER NOT NULL,
  93   isguess BOOL
  94   );
  95 SELECT AddGeometryColumn('location_area', 'centroid', 4326, 'POINT', 2);
  96 SELECT AddGeometryColumn('location_area', 'geometry', 4326, 'GEOMETRY', 2);
  97
  98 CREATE TABLE location_area_large () INHERITS (location_area);
  99 CREATE TABLE location_area_roadnear () INHERITS (location_area);
 100 CREATE TABLE location_area_roadfar () INHERITS (location_area);
 101
 102 drop table IF EXISTS location_property CASCADE;
 103 CREATE TABLE location_property (
 104   place_id BIGINT,
 105   partition integer,
 106   parent_place_id BIGINT,
 107   housenumber TEXT,
 108   postcode TEXT
 109   );
 110 SELECT AddGeometryColumn('location_property', 'centroid', 4326, 'POINT', 2);
 111
 112 CREATE TABLE location_property_aux () INHERITS (location_property);
 113 CREATE INDEX idx_location_property_aux_place_id ON location_property_aux USING BTREE (place_id);
 114 CREATE INDEX idx_location_property_aux_parent_place_id ON location_property_aux USING BTREE (parent_place_id);
 115 CREATE INDEX idx_location_property_aux_housenumber_parent_place_id ON location_property_aux USING BTREE (parent_place_id, housenumber);
 116 GRANT SELECT ON location_property_aux TO "www-data";
 117
 118 CREATE TABLE location_property_tiger () INHERITS (location_property);
 119 CREATE INDEX idx_location_property_tiger_place_id ON location_property_tiger USING BTREE (place_id);
 120 CREATE INDEX idx_location_property_tiger_parent_place_id ON location_property_tiger USING BTREE (parent_place_id);
 121 CREATE INDEX idx_location_property_tiger_housenumber_parent_place_id ON location_property_tiger USING BTREE (parent_place_id, housenumber);
 122 GRANT SELECT ON location_property_tiger TO "www-data";
 123
 124 drop table IF EXISTS search_name;
 125 CREATE TABLE search_name (
 126   place_id BIGINT,
 127   search_rank integer,
 128   address_rank integer,
 129   importance FLOAT,
 130   country_code varchar(2),
 131   name_vector integer[],
 132   nameaddress_vector integer[]
 133   );
 134 SELECT AddGeometryColumn('search_name', 'centroid', 4326, 'GEOMETRY', 2);
 135 CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id);
 136
 137 drop table IF EXISTS place_addressline;
 138 CREATE TABLE place_addressline (
 139   place_id BIGINT,
 140   address_place_id BIGINT,
 141   fromarea boolean,
 142   isaddress boolean,
 143   distance float,
 144   cached_rank_address integer
 145   );
 146 CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id);
 147
 148 drop table IF EXISTS place_boundingbox CASCADE;
 149 CREATE TABLE place_boundingbox (
 150   place_id BIGINT,
 151   minlat float,
 152   maxlat float,
 153   minlon float,
 154   maxlon float,
 155   numfeatures integer,
 156   area float
 157   );
 158 SELECT AddGeometryColumn('place_boundingbox', 'outline', 4326, 'GEOMETRY', 2);
 159 GRANT SELECT on place_boundingbox to "www-data" ;
 160 GRANT INSERT on place_boundingbox to "www-data" ;
 161
 162 drop table IF EXISTS reverse_cache;
 163 CREATE TABLE reverse_cache (
 164   latlonzoomid integer,
 165   country_code varchar(2),
 166   place_id BIGINT
 167   );
 168 GRANT SELECT on reverse_cache to "www-data" ;
 169 GRANT INSERT on reverse_cache to "www-data" ;
 170 CREATE INDEX idx_reverse_cache_latlonzoomid ON reverse_cache USING BTREE (latlonzoomid);
 171
 172 drop table country;
 173 CREATE TABLE country (
 174   country_code varchar(2),
 175   country_name hstore,
 176   country_default_language_code varchar(2)
 177   );
 178 SELECT AddGeometryColumn('country', 'geometry', 4326, 'POLYGON', 2);
 179 insert into country select iso3166::varchar(2), 'name:en'->cntry_name, null,
 180   ST_Transform(geometryn(the_geom, generate_series(1, numgeometries(the_geom))), 4326) from worldboundaries;
 181 CREATE INDEX idx_country_country_code ON country USING BTREE (country_code);
 182 CREATE INDEX idx_country_geometry ON country USING GIST (geometry);
 183
 184 drop table placex;
 185 CREATE TABLE placex (
 186   place_id BIGINT NOT NULL,
 187   partition integer,
 188   LIKE place INCLUDING CONSTRAINTS,
 189   parent_place_id BIGINT,
 190   linked_place_id BIGINT,
 191   rank_address INTEGER,
 192   rank_search INTEGER,
 193   importance FLOAT,
 194   indexed_status INTEGER,
 195   indexed_date TIMESTAMP,
 196   wikipedia TEXT, -- calculated wikipedia article name (language:title)
 197   geometry_sector INTEGER,
 198   calculated_country_code varchar(2)
 199   );
 200 SELECT AddGeometryColumn('placex', 'centroid', 4326, 'GEOMETRY', 2);
 201 CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id);
 202 CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id);
 203 CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id);
 204 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector);
 205 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry);
 206 CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) WHERE osm_type='N' and rank_search < 26;
 207
 208 --CREATE INDEX idx_placex_indexed ON placex USING BTREE (indexed);
 209
 210 --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;
 211
 212 DROP SEQUENCE seq_place;
 213 CREATE SEQUENCE seq_place start 1;
 214 GRANT SELECT on placex to "www-data" ;
 215 GRANT UPDATE ON placex to "www-data" ;
 216 GRANT SELECT ON search_name to "www-data" ;
 217 GRANT DELETE on search_name to "www-data" ;
 218 GRANT INSERT on search_name to "www-data" ;
 219 GRANT SELECT on place_addressline to "www-data" ;
 220 GRANT INSERT ON place_addressline to "www-data" ;
 221 GRANT DELETE on place_addressline to "www-data" ;
 222 GRANT SELECT ON seq_word to "www-data" ;
 223 GRANT UPDATE ON seq_word to "www-data" ;
 224 GRANT INSERT ON word to "www-data" ;
 225 GRANT SELECT ON planet_osm_ways to "www-data" ;
 226 GRANT SELECT ON planet_osm_rels to "www-data" ;
 227 GRANT SELECT on location_area to "www-data" ;
 228 GRANT SELECT on country to "www-data" ;
 229
 230 -- insert creates the location tagbles, creates location indexes if indexed == true
 231 CREATE TRIGGER placex_before_insert BEFORE INSERT ON placex
 232     FOR EACH ROW EXECUTE PROCEDURE placex_insert();
 233
 234 -- update insert creates the location tables
 235 CREATE TRIGGER placex_before_update BEFORE UPDATE ON placex
 236     FOR EACH ROW EXECUTE PROCEDURE placex_update();
 237
 238 -- diff update triggers
 239 CREATE TRIGGER placex_before_delete AFTER DELETE ON placex
 240     FOR EACH ROW EXECUTE PROCEDURE placex_delete();
 241 CREATE TRIGGER place_before_delete BEFORE DELETE ON place
 242     FOR EACH ROW EXECUTE PROCEDURE place_delete();
 243 CREATE TRIGGER place_before_insert BEFORE INSERT ON place
 244     FOR EACH ROW EXECUTE PROCEDURE place_insert();
 245
 246 drop index idx_placex_sector;
 247 CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id);
 248
 249 DROP SEQUENCE seq_postcodes;
 250 CREATE SEQUENCE seq_postcodes start 1;
 251
 252 drop table import_polygon_error;
 253 CREATE TABLE import_polygon_error (
 254   osm_type char(1),
 255   osm_id INTEGER,
 256   class TEXT NOT NULL,
 257   type TEXT NOT NULL,
 258   name HSTORE,
 259   country_code varchar(2),
 260   updated timestamp,
 261   errormessage text
 262   );
 263 SELECT AddGeometryColumn('import_polygon_error', 'prevgeometry', 4326, 'GEOMETRY', 2);
 264 SELECT AddGeometryColumn('import_polygon_error', 'newgeometry', 4326, 'GEOMETRY', 2);
 265 CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id);
 266 GRANT SELECT ON import_polygon_error TO "www-data";
 267
 268 drop table import_polygon_delete;
 269 CREATE TABLE import_polygon_delete (
 270   osm_type char(1),
 271   osm_id INTEGER,
 272   class TEXT NOT NULL,
 273   type TEXT NOT NULL
 274   );
 275 CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id);
 276 GRANT SELECT ON import_polygon_delete TO "www-data";
 277
 278 drop sequence file;
 279 CREATE SEQUENCE file start 1;
 280
 281 -- null table so it won't error
 282 -- deliberately no drop - importing the table is expensive and static, if it is already there better to avoid removing it
 283 CREATE TABLE wikipedia_article (
 284     language text NOT NULL,
 285     title text NOT NULL,
 286     langcount integer,
 287     othercount integer,
 288     totalcount integer,
 289     lat double precision,
 290     lon double precision,
 291     importance double precision,
 292     osm_type character(1),
 293     osm_id bigint
 294 );
 295 ALTER TABLE ONLY wikipedia_article ADD CONSTRAINT wikipedia_article_pkey PRIMARY KEY (language, title);
 296 CREATE INDEX idx_wikipedia_article_osm_id ON wikipedia_article USING btree (osm_type, osm_id);
 297
 298 CREATE TABLE wikipedia_redirect (
 299     language text,
 300     from_title text,
 301     to_title text
 302 );
 303 ALTER TABLE ONLY wikipedia_redirect ADD CONSTRAINT wikipedia_redirect_pkey PRIMARY KEY (language, from_title);
 304