]> git.openstreetmap.org Git - nominatim.git/blob - sql/partition-tables.src.sql
Merge pull request #782 from lonvia/rework-postcodes
[nominatim.git] / sql / partition-tables.src.sql
1 drop type if exists nearplace cascade;
2 create type nearplace as (
3   place_id BIGINT
4 );
5
6 drop type if exists nearfeature cascade;
7 create type nearfeature as (
8   place_id BIGINT,
9   keywords int[],
10   rank_address smallint,
11   rank_search smallint,
12   distance float,
13   isguess boolean
14 );
15
16 drop type if exists nearfeaturecentr cascade;
17 create type nearfeaturecentr as (
18   place_id BIGINT,
19   keywords int[],
20   rank_address smallint,
21   rank_search smallint,
22   distance float,
23   isguess boolean,
24   postcode TEXT,
25   centroid GEOMETRY
26 );
27
28 drop table IF EXISTS search_name_blank CASCADE;
29 CREATE TABLE search_name_blank (
30   place_id BIGINT,
31   search_rank smallint,
32   address_rank smallint,
33   name_vector integer[],
34   centroid GEOMETRY(Geometry, 4326)
35   );
36
37
38 CREATE TABLE location_area_country () INHERITS (location_area_large) {ts:address-data};
39 CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) {ts:address-index};
40
41 CREATE TABLE search_name_country () INHERITS (search_name_blank) {ts:address-data};
42 CREATE INDEX idx_search_name_country_place_id ON search_name_country USING BTREE (place_id) {ts:address-index};
43 CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector) WITH (fastupdate = off) {ts:address-index};
44
45 -- start
46 CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large) {ts:address-data};
47 CREATE INDEX idx_location_area_large_-partition-_place_id ON location_area_large_-partition- USING BTREE (place_id) {ts:address-index};
48 CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large_-partition- USING GIST (geometry) {ts:address-index};
49
50 CREATE TABLE search_name_-partition- () INHERITS (search_name_blank) {ts:address-data};
51 CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id) {ts:address-index};
52 CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid) {ts:address-index};
53 CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector) WITH (fastupdate = off) {ts:address-index};
54
55 CREATE TABLE location_road_-partition- (
56   place_id BIGINT,
57   partition SMALLINT,
58   country_code VARCHAR(2),
59   geometry GEOMETRY(Geometry, 4326)
60   ) {ts:address-data};
61 CREATE INDEX idx_location_road_-partition-_geometry ON location_road_-partition- USING GIST (geometry) {ts:address-index};
62 CREATE INDEX idx_location_road_-partition-_place_id ON location_road_-partition- USING BTREE (place_id) {ts:address-index};
63
64 -- end