V1_2__CHANGE_ADDRESS_TABLE.sql 4.01 KB
Newer Older
dietricf's avatar
dietricf committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
-----------------------------------------------------------------------------------
-- *******************************************************************************
-- * Copyright (c) 2019 Contributors to the Eclipse Foundation
-- *
-- * See the NOTICE file(s) distributed with this work for additional
-- * information regarding copyright ownership.
-- *
-- * This program and the accompanying materials are made available under the
-- * terms of the Eclipse Public License v. 2.0 which is available at
-- * http://www.eclipse.org/legal/epl-2.0.
-- *
-- * SPDX-License-Identifier: EPL-2.0
-- *******************************************************************************
-----------------------------------------------------------------------------------



-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- Tables ADDRESSIMPORT
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------

DROP TABLE IF EXISTS public.TBL_GFI_ADDRESS CASCADE;
DROP SEQUENCE IF EXISTS public.TBL_GFI_ADDRESS_ID_SEQ;

DROP TABLE IF EXISTS public.TBL_ADDRESS CASCADE;
DROP SEQUENCE IF EXISTS public.TBL_ADDRESS_ID_SEQ;

-- ---------------------------------------------
-- TABLE TBL_ADDRESS
-- ---------------------------------------------
CREATE SEQUENCE public.TBL_GFI_ADDRESS_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.TBL_GFI_ADDRESS_ID_SEQ
  OWNER TO GFI_SERVICE;

CREATE TABLE public.TBL_GFI_ADDRESS
(
  ID integer NOT NULL DEFAULT nextval('TBL_GFI_ADDRESS_ID_SEQ'::regclass),
  UUID uuid NOT NULL,
  SDOX1 numeric(9,2) NOT NULL,
  SDOY1 numeric(10,2) NOT NULL,
  G3EFID numeric,
  POSTCODE character varying(30),
  COMMUNITY character varying(255),
  DISTRICT character varying(255),
  STREET character varying(255),
  HOUSENUMBER character varying(30),
  WATER_CONNECTION boolean,
  WATER_GROUP character varying(255),
  GAS_CONNECTION boolean,
  GAS_GROUP character varying(255),
  POWER_CONNECTION boolean,
  DISTRICTHEATING_CONNECTION boolean,
  TELECOMM_CONNECTION boolean,
  STATION_ID character varying(30),
  LONGITUDE numeric(9,6),
  LATITUDE numeric(9,6),
  CONSTRAINT TBL_ADDRESS_PKEY PRIMARY KEY (id)
);

ALTER TABLE public.TBL_GFI_ADDRESS
  OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.TBL_GFI_ADDRESS TO GFI_SERVICE;

CREATE INDEX idx_tbl_gfiaddress_g3efid ON public.TBL_GFI_ADDRESS ( G3EFID );
CREATE INDEX idx_tbl_gfiaddress_postcode ON public.TBL_GFI_ADDRESS ( POSTCODE );
CREATE INDEX idx_tbl_gfiaddress_community ON public.TBL_GFI_ADDRESS ( COMMUNITY );
CREATE INDEX idx_tbl_gfiaddress_district ON public.TBL_GFI_ADDRESS ( DISTRICT );
CREATE INDEX idx_tbl_gfiaddress_street ON public.TBL_GFI_ADDRESS ( STREET );
CREATE INDEX idx_tbl_gfiaddress_station_id ON public.TBL_GFI_ADDRESS ( STATION_ID );


-- ---------------------------------------------
-- TABLE TBL_STATION
-- ---------------------------------------------

DROP TABLE IF EXISTS public.TBL_STATION CASCADE;
DROP SEQUENCE IF EXISTS public.TBL_STATION_ID_SEQ;

CREATE SEQUENCE public.TBL_STATION_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.TBL_STATION_ID_SEQ
  OWNER TO GFI_SERVICE;

CREATE TABLE public.TBL_STATION
(
  ID integer NOT NULL DEFAULT nextval('TBL_STATION_ID_SEQ'::regclass),
  UUID uuid NOT NULL,
  SDOX1 numeric(9,2) NOT NULL,
  SDOY1 numeric(10,2) NOT NULL,
  G3EFID numeric,
  STATION_ID character varying(30),
  STATION_NAME character varying(255),
  LONGITUDE numeric(9,6),
  LATITUDE numeric(9,6),
  CONSTRAINT TBL_STATION_PKEY PRIMARY KEY (id)
);

ALTER TABLE public.TBL_STATION
  OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.TBL_STATION TO GFI_SERVICE;

CREATE INDEX idx_tbl_station_g3efid ON public.TBL_STATION ( G3EFID );
CREATE INDEX idx_tbl_station_station_id ON public.TBL_STATION ( STATION_ID );
CREATE INDEX idx_tbl_station_uuid ON public.TBL_STATION ( UUID );