
-- New Address Base Premium dataset

-- auto-generated definition
create table adhoc.tbl_abp_import
(
    source_id                         varchar(20),
    uprn                              bigint not null
        constraint tbl_abp_import_pk
            primary key,
    usrn                              bigint,
    address_logical_status            smallint,
    address_logical_status_desc       varchar(200),
    building_logical_status           smallint,
    building_logical_status_desc      varchar(200),
    building_status                   smallint,
    building_status_desc              varchar(200),
    building_classification_code      varchar(25),
    building_classification_code_desc varchar(200),
    organisation_name                 varchar(100),
    sao                               varchar(105),
    sao_text                          varchar(90),
    sao_start_number                  smallint,
    sao_start_suffix                  varchar(2),
    sao_end_number                    smallint,
    sao_end_suffix                    varchar(2),
    pao                               varchar(105),
    pao_text                          varchar(90),
    pao_start_number                  smallint,
    pao_start_suffix                  varchar(2),
    pao_end_number                    smallint,
    pao_end_suffix                    varchar(2),
    administrative_area               varchar(30),
    street_description                varchar(110),
    postcode                          varchar(8),
    postcode_area                     varchar(2),
    postcode_district                 varchar(4),
    postcode_sector                   varchar(6),
    locality_name                     varchar(40),
    town_name                         varchar(30),
    parent_uprn                       bigint,
    multi_occ_count                   smallint,
    x_coordinate                      varchar(15),
    y_coordinate                      varchar(15),
    wgs84_longitude                   varchar(20),
    wgs84_latitude                    varchar(20),
    rpc                               smallint,
    postal_address                    varchar(1),
    address_start_date_time           timestamp,
    address_end_date_time             timestamp,
    last_update_date_time             timestamp,
    entry_date_time                   timestamp,
    os_topo_toid                      varchar(20),
    building_premise_count            integer,
    valid_address_flag                varchar(1),
    dna_address_type                  varchar(20),
    building_type                     varchar(20),
    iud_indicator                     varchar(1),
    row_start_date_time               timestamp,
    row_end_date_time                 timestamp,
    row_is_current                    varchar(1)
);

alter table adhoc.tbl_abp_import
    owner to postgres;

create index tbl_abp_import_postcode_idx
    on adhoc.tbl_abp_import (postcode);

-- Existing abp dataset.

-- auto-generated definition
create table basedata.abp
(
    uprn                   bigint,
    classification_code    varchar, -- building_classification_code
    class_description      varchar, --building_classification_desc
    parent_uprn            bigint,
    block                  text,
    postcode               varchar(8),
    address                text, --combination of sao,pao etc.
    business_name          text,
    borough                varchar(50),
    main_proprietor        text,
    title_no               text,
    property_type          text,
    cross_reference        varchar(50), --os_topo_toid
    udprn                  bigint,
    umprn                  integer,
    geom                   geometry(Point, 27700),
    sprn                   integer,
    portfolio_id           smallint, --not used/important
    bd_status              smallint, --not used/important
    leasehold_portfolio_id integer, --not used/important
    wayleave_date          date, --not used/important
    mdu_id                 bigint, --not used/important
    class_type             text,
    category_code          smallint,
    wayleave_portfolio_id  smallint, --not used/important
    main_leaseholder       text, --not used/important
    leaseholder_managed    boolean, --not used/important
    usrn                   bigint,
    provider               integer,
    postcode_sector        varchar(7),
    street_name            varchar(42),
    street_authority       varchar(42)
);

alter table basedata.abp
    owner to postgres;

create index abp_uprn_idx
    on basedata.abp (uprn);

create index abp_geom_idx
    on basedata.abp using gist (geom);

create index abp_postcode_idx
    on basedata.abp (postcode);

create index abp_uprn_toid_idx
    on basedata.abp (uprn, cross_reference);

create index abp_uprn_postcode_address_idx
    on basedata.abp (uprn, postcode, address);

create index idx_abp_address_gin
    on basedata.abp using gin (to_tsvector('simple'::regconfig, address));

create index idx_abp_postcode_text_pattern
    on basedata.abp (postcode text_pattern_ops);

create index idx_abp_address_trgm
    on basedata.abp using gin (address public.gin_trgm_ops);

create index idx_abp_trgm
    on basedata.abp using gin (upper(address) public.gin_trgm_ops);

create index abp_borough_upper_idx
    on basedata.abp (upper(borough::text));

create index abp_borough_idx
    on basedata.abp (borough);

create index idx_abp_postcode_sector
    on basedata.abp (postcode_sector);

create index abp_usrn_idx
    on basedata.abp (usrn);

create index abp_street_name_idx
    on basedata.abp (street_name);

