AddressBase Premium Create Tables SQL

AddressBase Premium Create Tables SQL

#-------------------------------------------------------------------------------
# Name:        `AddressBase Premium Create Table SQL
# Purpose:     `This SQL code is to be used along side the Getting Started Guide
#				to setup the AddressBase Premium tables within a database
# Created:     `12/08/2014
# Copyright:   `Crown Copyright (c) Ordnance Survey 2014
# Licence:     `THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
#               "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
#               LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
#               FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
#               COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
#               INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
#               BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
#               OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
#               AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
#               OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF
#               THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE
#-------------------------------------------------------------------------------

--Create the BLPU Record Table
CREATE TABLE addressbasepremium.abp_blpu_record ( 
RECORD_IDENTIFIER bigint,
CHANGE_TYPE varchar(1) , 
PRO_ORDER integer , 
UPRN bigint NOT NULL, 
LOGICAL_STATUS integer , 
BLPU_STATE integer ,
BLPU_STATE_DATE date , 
PARENT_UPRN bigint , 
X_COORDINATE numeric(9,3) , 
Y_COORDINATE numeric(10,3) , 
RPC integer , 
LOCAL_CUSTODIAN_CODE integer , 
START_DATE date ,
END_DATE date , 
LAST_UPDATE_DATE date , 
ENTRY_DATE date , 
POSTAL_ADDRESS char(1), 
POSTCODE_LOCATOR char(8), 
MULTI_OCC_COUNT integer);
COMMIT;

--Create the Classification Table
CREATE TABLE addressbasepremium.abp_classification (
RECORD_IDENTIFIER bigint NOT NULL, 
CHANGE_TYPE varchar(1), 
PRO_ORDER integer, 
UPRN bigint NOT NULL,
CLASS_KEY varchar(14), 
CLASSIFICATION_CODE varchar(8), 
CLASS_SCHEME varchar(60), 
SCHEME_VERSION numeric(4,2), 
START_DATE date, 
END_DATE date,
LAST_UPDATE_DATE date, 
ENTRY_DATE date);
COMMIT;

--Create the Delivery Point table
CREATE TABLE addressbasepremium.abp_delivery_point ( 
RECORD_IDENTIFIER bigint, 
CHANGE_TYPE varchar(1),
PRO_ORDER integer, 
UPRN bigint, 
PARENT_ADDRESSABLE_UPRN bigint, 
RM_UDPRN bigint, 
ORGANISATION_NAME varchar(60), 
DEPARTMENT_NAME varchar(60), 
SUB_BUILDING_NAME varchar(60),
BUILDING_NAME varchar(60), 
BUILDING_NUMBER integer, 
DEPENDENT_THOROUGHFARE_NAME varchar(80), 
THROUGHFARE_NAME varchar(80),
DOUBLE_DEPENDENT_LOCALITY varchar(35), 
DEPENDENT_LOCALITY varchar(35),
POST_TOWN varchar(30), 
POSTCODE varchar(8), 
POSTCODE_TYPE varchar(5), 
WELSH_DEPENDENT_THOROUGHFARE varchar(80),
WELSH_THOROUGHFARE_NAME varchar(80),
WELSH_DOUBLE_DEPENDENT_LOCALITY varchar(35),
WELSH_DEPENDENT_LOCALITY varchar(35),
WELSH_POST_TOWN varchar(30), 
PO_BOX_NUMBER varchar(6), 
PROCESS_DATE date, 
START_DATE date, 
END_DATE date, 
LAST_UPDATE_DATE date,
ENTRY_DATE date);
COMMIT;

--Create the Cross Reference table
CREATE TABLE addressbasepremium.abp_crossref (
RECORD_IDENTIFIER bigint NOT NULL, 
CHANGE_TYPE varchar(1), 
PRO_ORDER integer, 
UPRN bigint NOT NULL,
XREF_KEY varchar(14), 
CROSS_REFERENCE varchar(20), 
VERSION integer, 
SOURCE varchar(6), 
START_DATE date, 
END_DATE date,
LAST_UPDATE_DATE date, 
ENTRY_DATE date);
COMMIT;

--Create the LPI table
CREATE TABLE addressbasepremium.abp_lpi (
RECORD_IDENTIFIER bigint NOT NULL,
CHANGE_TYPE varchar(1),
PRO_ORDER integer, 
UPRN bigint NOT NULL DEFAULT '0', 
LPI_KEY varchar(14), 
LANGUAGE varchar(3),
LOGICAL_STATUS integer,
START_DATE date, 
END_DATE date, 
LAST_UPDATE_DATE date,
ENTRY_DATE date,
SAO_START_NUMBER integer,
SAO_START_SUFFIX varchar(2),
SAO_END_NUMBER integer,
SAO_END_SUFFIX varchar(2),
SAO_TEXT varchar(90), 
PAO_START_NUMBER integer, 
PAO_START_SUFFIX varchar(2),
PAO_END_NUMBER integer,
PAO_END_SUFFIX varchar(2), 
PAO_TEXT varchar(90),
USRN bigint, 
USRN_MATCH_INDICATOR varchar(1),
AREA_NAME varchar(35), 
LEVEL char(30),
OFFICIAL_FLAG char(1));
COMMIT;

--Create the Organisation table
CREATE TABLE addressbasepremium.abp_organisation ( 
RECORD_IDENTIFIER bigint NOT NULL,
CHANGE_TYPE varchar(1), 
PRO_ORDER integer,
UPRN bigint NOT NULL,
ORG_KEY varchar(14),
ORGANISATION varchar(120), 
LEGAL_NAME varchar(60), 
START_DATE date, 
END_DATE date, 
LAST_UPDATE_DATE date, 
ENTRY_DATE date);
COMMIT;

--Create the Street table
CREATE TABLE addressbasepremium.abp_street ( 
RECORD_IDENTIFIER bigint NOT NULL, 
CHANGE_TYPE varchar(1), 
PRO_ORDER integer, 
USRN bigint NOT NULL, 
RECORD_TYPE integer, 
SWA_ORG_REF_NAMING bigint, 
STATE integer, 
STATE_DATE date,
STREET_SURFACE integer,
STREET_CLASSIFICATION integer, 
VERSION integer, 
STREET_START_DATE date,
STREET_END_DATE date, 
LAST_UPDATE_DATE date, 
RECORD_ENTRY_DATE date, 
STREET_START_X numeric(9,3),
STREET_START_Y numeric(10,3),
STREET_END_X numeric(9,3),
STREET_END_Y numeric(10,3), 
STREET_TOLERANCE integer);
COMMIT;

--Create the Street Descriptor table
CREATE TABLE addressbasepremium.abp_street_descriptor ( 
RECORD_IDENTIFIER bigint NOT NULL, 
CHANGE_TYPE varchar(1), 
PRO_ORDER integer, 
USRN bigint NOT NULL, 
STREET_DESCRIPTOR varchar(100), 
LOCALITY_NAME varchar(35),
TOWN_NAME varchar(30), 
ADMINISTRATIVE_AREA varchar(30), 
LANGUAGE varchar(3));
COMMIT;

--Create the Successor table
CREATE TABLE addressbasepremium.abp_successor (
RECORD_IDENTIFIER smallint,
CHANGE_TYPE varchar(1),
PRO_ORDER integer,
UPRN bigint NOT NULL,
SUCC_KEY varchar(14),
START_DATE date,
END_DATE date,
LAST_UPDATE_DATE date,
ENTRY_DATE date,
SUCCESSOR bigint);
COMMIT;

--Create the Header table
CREATE TABLE addressbasepremium.abp_header (
RECORD_IDENTIFIER bigint NOT NULL, 
CUSTODIAN_NAME varchar(40),
LOCAL_CUSTODIAN_NAME integer,
PROCESS_DATE date,
VOLUME_NUMBER integer,
ENTRY_DATE date,
TIME_STAMP time,
VERSION varchar(7),
FILE_TYPE varchar(1));
COMMIT;


--Create the Metadata table
CREATE TABLE addressbasepremium.abp_metadata (
RECORD_IDENTIFIER bigint NOT NULL, 
GAZ_NAME varchar(60),
GAZ_SCOPE varchar(60),
TER_OF_USE varchar(60),
LINKED_DATA varchar(100),
GAZ_OWNER varchar(15),
NGAZ_FREQ varchar(1),
CUSTODIAN_NAME varchar(40),
CUSTODIAN_UPRN bigint,
LOCAL_CUSTODIAN_CODE integer,
CO_ORD_SYSTEM varchar(40),
CO_ORD_UNIT varchar(10),
META_DATE date,
CLASS_SCHEME varchar(60),
GAZ_DATE date,
LANGUAGE varchar(3),
CHARACTER_SET varchar(30));
COMMIT;

--Create the Trailer table
CREATE TABLE addressbasepremium.abp_trailer (
RECORD_IDENTIFIER bigint NOT NULL,
NEXT_VOLUME_NUMBER integer,
RECORD_COUNT integer,
ENTRY_DATE date,
TIME_STAMP time);
COMMIT;