lib-sql/partition-tables.src.sql

   1 drop table IF EXISTS search_name_blank CASCADE;
   2 CREATE TABLE search_name_blank (
   3   place_id BIGINT,
   4   address_rank smallint,
   5   name_vector integer[],
   6   centroid GEOMETRY(Geometry, 4326)
   7   );
   8
   9
  10 {% for partition in db.partitions %}
  11   CREATE TABLE location_area_large_{{ partition }} () INHERITS (location_area_large) {{db.tablespace.address_data}};
  12   CREATE INDEX idx_location_area_large_{{ partition }}_place_id ON location_area_large_{{ partition }} USING BTREE (place_id) {{db.tablespace.address_index}};
  13   CREATE INDEX idx_location_area_large_{{ partition }}_geometry ON location_area_large_{{ partition }} USING GIST (geometry) {{db.tablespace.address_index}};
  14
  15   CREATE TABLE search_name_{{ partition }} () INHERITS (search_name_blank) {{db.tablespace.address_data}};
  16   CREATE INDEX idx_search_name_{{ partition }}_place_id ON search_name_{{ partition }} USING BTREE (place_id) {{db.tablespace.address_index}};
  17   CREATE INDEX idx_search_name_{{ partition }}_centroid_street ON search_name_{{ partition }} USING GIST (centroid) {{db.tablespace.address_index}} where address_rank between 26 and 27;
  18   CREATE INDEX idx_search_name_{{ partition }}_centroid_place ON search_name_{{ partition }} USING GIST (centroid) {{db.tablespace.address_index}} where address_rank between 2 and 25;
  19
  20   DROP TABLE IF EXISTS location_road_{{ partition }};
  21   CREATE TABLE location_road_{{ partition }} (
  22     place_id BIGINT,
  23     partition SMALLINT,
  24     country_code VARCHAR(2),
  25     geometry GEOMETRY(Geometry, 4326)
  26     ) {{db.tablespace.address_data}};
  27   CREATE INDEX idx_location_road_{{ partition }}_geometry ON location_road_{{ partition }} USING GIST (geometry) {{db.tablespace.address_index}};
  28   CREATE INDEX idx_location_road_{{ partition }}_place_id ON location_road_{{ partition }} USING BTREE (place_id) {{db.tablespace.address_index}};
  29
  30 {% endfor %}