sql/tables.sql

   1 drop table if exists import_status;
   2 CREATE TABLE import_status (
   3   lastimportdate timestamp NOT NULL
   4   );
   5 GRANT SELECT ON import_status TO "{www-user}" ;
   6
   7 drop table if exists 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 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-user}" ;
  36 GRANT INSERT ON query_log TO "{www-user}" ;
  37 GRANT UPDATE ON query_log TO "{www-user}" ;
  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-user}" ;
  53 GRANT UPDATE ON new_query_log TO "{www-user}" ;
  54 GRANT SELECT ON new_query_log TO "{www-user}" ;
  55
  56 GRANT SELECT ON TABLE country_name TO "{www-user}";
  57 GRANT SELECT ON TABLE gb_postcode TO "{www-user}";
  58
  59 create view vw_search_query_log as SELECT substr(query, 1, 50) AS query, starttime, endtime - starttime AS duration, substr(useragent, 1, 20) as
  60 useragent, language, results, ipaddress FROM new_query_log WHERE type = 'search' ORDER BY starttime DESC;
  61
  62 drop table IF EXISTS word;
  63 CREATE TABLE word (
  64   word_id INTEGER,
  65   word_token text,
  66   word text,
  67   class text,
  68   type text,
  69   country_code varchar(2),
  70   search_name_count INTEGER,
  71   operator TEXT
  72   ) {ts:search-data};
  73 CREATE INDEX idx_word_word_token on word USING BTREE (word_token) {ts:search-index};
  74 GRANT SELECT ON word TO "{www-user}" ;
  75 DROP SEQUENCE IF EXISTS seq_word;
  76 CREATE SEQUENCE seq_word start 1;
  77
  78 drop table IF EXISTS location_area CASCADE;
  79 CREATE TABLE location_area (
  80   partition integer,
  81   place_id BIGINT,
  82   country_code VARCHAR(2),
  83   keywords INTEGER[],
  84   rank_search INTEGER NOT NULL,
  85   rank_address INTEGER NOT NULL,
  86   isguess BOOL
  87   );
  88 SELECT AddGeometryColumn('location_area', 'centroid', 4326, 'POINT', 2);
  89 SELECT AddGeometryColumn('location_area', 'geometry', 4326, 'GEOMETRY', 2);
  90
  91 CREATE TABLE location_area_large () INHERITS (location_area);
  92
  93 drop table IF EXISTS location_property CASCADE;
  94 CREATE TABLE location_property (
  95   place_id BIGINT,
  96   partition integer,
  97   parent_place_id BIGINT,
  98   housenumber TEXT,
  99   postcode TEXT
 100   );
 101 SELECT AddGeometryColumn('location_property', 'centroid', 4326, 'POINT', 2);
 102
 103 CREATE TABLE location_property_aux () INHERITS (location_property);
 104 CREATE INDEX idx_location_property_aux_place_id ON location_property_aux USING BTREE (place_id);
 105 CREATE INDEX idx_location_property_aux_parent_place_id ON location_property_aux USING BTREE (parent_place_id);
 106 CREATE INDEX idx_location_property_aux_housenumber_parent_place_id ON location_property_aux USING BTREE (parent_place_id, housenumber);
 107 GRANT SELECT ON location_property_aux TO "{www-user}";
 108
 109 CREATE TABLE location_property_tiger (linegeo GEOMETRY, place_id BIGINT, partition INTEGER, parent_place_id BIGINT, startnumber INTEGER, endnumber INTEGER, interpolationtype TEXT, postcode TEXT);
 110 GRANT SELECT ON location_property_tiger TO "{www-user}";
 111
 112 CREATE TABLE location_property_osmline (
 113     linegeo GEOMETRY,
 114     place_id BIGINT NOT NULL,
 115     partition INTEGER,
 116     osm_id BIGINT,
 117     parent_place_id BIGINT,
 118     startnumber INTEGER,
 119     endnumber INTEGER,
 120     interpolationtype TEXT,
 121     admin_level INTEGER,
 122     street TEXT,
 123     postcode TEXT,
 124     calculated_country_code VARCHAR(2),
 125     geometry_sector INTEGER,
 126     indexed_status INTEGER,
 127     indexed_date TIMESTAMP);
 128 CREATE UNIQUE INDEX idx_osmline_place_id ON location_property_osmline (place_id) {ts:search-index};
 129 CREATE INDEX idx_osmline_parent_place_id ON location_property_osmline (parent_place_id) {ts:search-index};
 130 GRANT SELECT ON location_property_osmline TO "{www-user}";
 131
 132
 133 drop table IF EXISTS search_name;
 134 CREATE TABLE search_name (
 135   place_id BIGINT,
 136   search_rank integer,
 137   address_rank integer,
 138   importance FLOAT,
 139   country_code varchar(2),
 140   name_vector integer[],
 141   nameaddress_vector integer[]
 142   ) {ts:search-data};
 143 SELECT AddGeometryColumn('search_name', 'centroid', 4326, 'GEOMETRY', 2);
 144 CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id) {ts:search-index};
 145
 146 drop table IF EXISTS place_addressline;
 147 CREATE TABLE place_addressline (
 148   place_id BIGINT,
 149   address_place_id BIGINT,
 150   fromarea boolean,
 151   isaddress boolean,
 152   distance float,
 153   cached_rank_address integer
 154   ) {ts:search-data};
 155 CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id) {ts:search-index};
 156
 157 drop table if exists placex;
 158 CREATE TABLE placex (
 159   place_id BIGINT NOT NULL,
 160   partition integer,
 161   LIKE place INCLUDING CONSTRAINTS,
 162   parent_place_id BIGINT,
 163   linked_place_id BIGINT,
 164   rank_address INTEGER,
 165   rank_search INTEGER,
 166   importance FLOAT,
 167   indexed_status INTEGER,
 168   indexed_date TIMESTAMP,
 169   wikipedia TEXT, -- calculated wikipedia article name (language:title)
 170   geometry_sector INTEGER,
 171   calculated_country_code varchar(2)
 172   ) {ts:search-data};
 173 SELECT AddGeometryColumn('placex', 'centroid', 4326, 'GEOMETRY', 2);
 174 CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) {ts:search-index};
 175 CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) {ts:search-index};
 176 CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) {ts:address-index};
 177 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {ts:address-index};
 178 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {ts:search-index};
 179 CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) {ts:address-index} WHERE osm_type='N' and rank_search < 26;
 180
 181 DROP SEQUENCE IF EXISTS seq_place;
 182 CREATE SEQUENCE seq_place start 1;
 183 GRANT SELECT on placex to "{www-user}" ;
 184 GRANT SELECT ON search_name to "{www-user}" ;
 185 GRANT SELECT on place_addressline to "{www-user}" ;
 186 GRANT SELECT ON seq_word to "{www-user}" ;
 187 GRANT SELECT ON planet_osm_ways to "{www-user}" ;
 188 GRANT SELECT ON planet_osm_rels to "{www-user}" ;
 189 GRANT SELECT on location_area to "{www-user}" ;
 190
 191 -- insert creates the location tables, creates location indexes if indexed == true
 192 CREATE TRIGGER placex_before_insert BEFORE INSERT ON placex
 193     FOR EACH ROW EXECUTE PROCEDURE placex_insert();
 194
 195 -- update insert creates the location tables
 196 CREATE TRIGGER placex_before_update BEFORE UPDATE ON placex
 197     FOR EACH ROW EXECUTE PROCEDURE placex_update();
 198 CREATE TRIGGER osmline_before_update BEFORE UPDATE ON location_property_osmline
 199     FOR EACH ROW EXECUTE PROCEDURE osmline_update();
 200
 201 -- diff update triggers
 202 CREATE TRIGGER placex_before_delete AFTER DELETE ON placex
 203     FOR EACH ROW EXECUTE PROCEDURE placex_delete();
 204 CREATE TRIGGER place_before_delete BEFORE DELETE ON place
 205     FOR EACH ROW EXECUTE PROCEDURE place_delete();
 206 CREATE TRIGGER place_before_insert BEFORE INSERT ON place
 207     FOR EACH ROW EXECUTE PROCEDURE place_insert();
 208
 209 DROP SEQUENCE seq_postcodes;
 210 CREATE SEQUENCE seq_postcodes start 1;
 211
 212 drop table import_polygon_error;
 213 CREATE TABLE import_polygon_error (
 214   osm_type char(1),
 215   osm_id INTEGER,
 216   class TEXT NOT NULL,
 217   type TEXT NOT NULL,
 218   name HSTORE,
 219   country_code varchar(2),
 220   updated timestamp,
 221   errormessage text
 222   );
 223 SELECT AddGeometryColumn('import_polygon_error', 'prevgeometry', 4326, 'GEOMETRY', 2);
 224 SELECT AddGeometryColumn('import_polygon_error', 'newgeometry', 4326, 'GEOMETRY', 2);
 225 CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id);
 226 GRANT SELECT ON import_polygon_error TO "{www-user}";
 227
 228 drop table import_polygon_delete;
 229 CREATE TABLE import_polygon_delete (
 230   osm_type char(1),
 231   osm_id INTEGER,
 232   class TEXT NOT NULL,
 233   type TEXT NOT NULL
 234   );
 235 CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id);
 236 GRANT SELECT ON import_polygon_delete TO "{www-user}";
 237
 238 drop sequence file;
 239 CREATE SEQUENCE file start 1;
 240
 241 -- null table so it won't error
 242 -- deliberately no drop - importing the table is expensive and static, if it is already there better to avoid removing it
 243 CREATE TABLE wikipedia_article (
 244     language text NOT NULL,
 245     title text NOT NULL,
 246     langcount integer,
 247     othercount integer,
 248     totalcount integer,
 249     lat double precision,
 250     lon double precision,
 251     importance double precision,
 252     osm_type character(1),
 253     osm_id bigint
 254 );
 255 ALTER TABLE ONLY wikipedia_article ADD CONSTRAINT wikipedia_article_pkey PRIMARY KEY (language, title);
 256 CREATE INDEX idx_wikipedia_article_osm_id ON wikipedia_article USING btree (osm_type, osm_id);
 257
 258 CREATE TABLE wikipedia_redirect (
 259     language text,
 260     from_title text,
 261     to_title text
 262 );
 263 ALTER TABLE ONLY wikipedia_redirect ADD CONSTRAINT wikipedia_redirect_pkey PRIMARY KEY (language, from_title);
 264