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 SELECT ON search_name to "www-data" ;
 216 GRANT SELECT on place_addressline to "www-data" ;
 217 GRANT SELECT ON seq_word to "www-data" ;
 218 GRANT SELECT ON planet_osm_ways to "www-data" ;
 219 GRANT SELECT ON planet_osm_rels to "www-data" ;
 220 GRANT SELECT on location_area to "www-data" ;
 221 GRANT SELECT on country to "www-data" ;
 222
 223 -- insert creates the location tagbles, creates location indexes if indexed == true
 224 CREATE TRIGGER placex_before_insert BEFORE INSERT ON placex
 225     FOR EACH ROW EXECUTE PROCEDURE placex_insert();
 226
 227 -- update insert creates the location tables
 228 CREATE TRIGGER placex_before_update BEFORE UPDATE ON placex
 229     FOR EACH ROW EXECUTE PROCEDURE placex_update();
 230
 231 -- diff update triggers
 232 CREATE TRIGGER placex_before_delete AFTER DELETE ON placex
 233     FOR EACH ROW EXECUTE PROCEDURE placex_delete();
 234 CREATE TRIGGER place_before_delete BEFORE DELETE ON place
 235     FOR EACH ROW EXECUTE PROCEDURE place_delete();
 236 CREATE TRIGGER place_before_insert BEFORE INSERT ON place
 237     FOR EACH ROW EXECUTE PROCEDURE place_insert();
 238
 239 drop index idx_placex_sector;
 240 CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id);
 241
 242 DROP SEQUENCE seq_postcodes;
 243 CREATE SEQUENCE seq_postcodes start 1;
 244
 245 drop table import_polygon_error;
 246 CREATE TABLE import_polygon_error (
 247   osm_type char(1),
 248   osm_id INTEGER,
 249   class TEXT NOT NULL,
 250   type TEXT NOT NULL,
 251   name HSTORE,
 252   country_code varchar(2),
 253   updated timestamp,
 254   errormessage text
 255   );
 256 SELECT AddGeometryColumn('import_polygon_error', 'prevgeometry', 4326, 'GEOMETRY', 2);
 257 SELECT AddGeometryColumn('import_polygon_error', 'newgeometry', 4326, 'GEOMETRY', 2);
 258 CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id);
 259 GRANT SELECT ON import_polygon_error TO "www-data";
 260
 261 drop table import_polygon_delete;
 262 CREATE TABLE import_polygon_delete (
 263   osm_type char(1),
 264   osm_id INTEGER,
 265   class TEXT NOT NULL,
 266   type TEXT NOT NULL
 267   );
 268 CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id);
 269 GRANT SELECT ON import_polygon_delete TO "www-data";
 270
 271 drop sequence file;
 272 CREATE SEQUENCE file start 1;
 273
 274 -- null table so it won't error
 275 -- deliberately no drop - importing the table is expensive and static, if it is already there better to avoid removing it
 276 CREATE TABLE wikipedia_article (
 277     language text NOT NULL,
 278     title text NOT NULL,
 279     langcount integer,
 280     othercount integer,
 281     totalcount integer,
 282     lat double precision,
 283     lon double precision,
 284     importance double precision,
 285     osm_type character(1),
 286     osm_id bigint
 287 );
 288 ALTER TABLE ONLY wikipedia_article ADD CONSTRAINT wikipedia_article_pkey PRIMARY KEY (language, title);
 289 CREATE INDEX idx_wikipedia_article_osm_id ON wikipedia_article USING btree (osm_type, osm_id);
 290
 291 CREATE TABLE wikipedia_redirect (
 292     language text,
 293     from_title text,
 294     to_title text
 295 );
 296 ALTER TABLE ONLY wikipedia_redirect ADD CONSTRAINT wikipedia_redirect_pkey PRIMARY KEY (language, from_title);
 297