· 7 years ago · Nov 13, 2018, 08:08 PM
1DROP TABLE IF EXISTS import_property_master;
2
3CREATE TABLE import_property_master (
4 `FORM CODE` BOOL,
5 `PARCEL ID` VARCHAR(15) NOT NULL,
6 `LOCATION` VARCHAR(30) NOT NULL,
7 `LOC DESC` VARCHAR(60),
8 `MUNICIPALITY` VARCHAR(20),
9 `LOT NUMBER` DECIMAL(38, 1),
10 `SUBDIVISION` VARCHAR(14),
11 `SUBDIV DESC` VARCHAR(30),
12 `ALTERNATE PARCEL ID` VARCHAR(10),
13 `OWNER ID` DECIMAL(38, 0) NOT NULL,
14 `OWNER NAME` VARCHAR(40) NOT NULL,
15 `OWNER ADDR1` VARCHAR(33) NOT NULL,
16 `OWNER ADDR2` VARCHAR(32) NOT NULL,
17 `OWNER ADDR3` VARCHAR(25),
18 `PROP USE CODE` VARCHAR(3),
19 `PROP USE DESC` VARCHAR(15),
20 `USE MEMO` VARCHAR(60),
21 `ZONE CODE` VARCHAR(6),
22 `ZONE DESC` VARCHAR(30),
23 `ZONING MEMO` VARCHAR(25),
24 `STATUS` VARCHAR(8) NOT NULL,
25 `BOOK-PAGE` VARCHAR(11),
26 `RENTAL` BOOL NOT NULL,
27 `CORNER LOT` BOOL NOT NULL,
28 `VACANT LOT` BOOL NOT NULL,
29 `GOVT OWNED` BOOL NOT NULL,
30 `TOTAL UNITS` DECIMAL(38, 0) NOT NULL,
31 `RESTR-HZD CODE1` VARCHAR(4),
32 `RESTR-HZD DESC1` VARCHAR(30),
33 `RESTR-HZD CODE2` VARCHAR(4),
34 `RESTR-HZD DESC2` VARCHAR(30),
35 `RESTR-HZD CODE3` VARCHAR(4),
36 `RESTR-HZD DESC3` VARCHAR(27),
37 `RESTR-HZD CODE4` VARCHAR(4),
38 `RESTR-HZD DESC4` VARCHAR(26),
39 `RESTR-HZD CODE5` VARCHAR(4),
40 `RESTR-HZD DESC5` VARCHAR(25),
41 `RESTR-HZD CODE6` BOOL,
42 `RESTR-HZD DESC6` BOOL,
43 `RESTR-HZD CODE7` BOOL,
44 `RESTR-HZD DESC7` BOOL,
45 `RESTR-HZD CODE8` BOOL,
46 `RESTR-HZD DESC8` BOOL,
47 `RESTR-HZD CODE9` BOOL,
48 `RESTR-HZD DESC9` BOOL,
49 `RESTR-HZD CODE10` BOOL,
50 `RESTR-HZD DESC10` BOOL,
51 `SUB ADDR UNIT1` VARCHAR(5),
52 `SUB ADDR REFERENCE1` VARCHAR(13),
53 `SUB ADDR UNIT2` VARCHAR(5),
54 `SUB ADDR REFERENCE2` VARCHAR(10),
55 `SUB ADDR UNIT3` VARCHAR(5),
56 `SUB ADDR REFERENCE3` VARCHAR(10),
57 `SUB ADDR UNIT4` VARCHAR(5),
58 `SUB ADDR REFERENCE4` BOOL,
59 `SUB ADDR UNIT5` VARCHAR(5),
60 `SUB ADDR REFERENCE5` BOOL,
61 `SUB ADDR UNIT6` VARCHAR(5),
62 `SUB ADDR REFERENCE6` BOOL,
63 `SUB ADDR UNIT7` VARCHAR(5),
64 `SUB ADDR REFERENCE7` BOOL,
65 `SUB ADDR UNIT8` VARCHAR(5),
66 `SUB ADDR REFERENCE8` VARCHAR(10),
67 `SUB ADDR UNIT9` VARCHAR(5),
68 `SUB ADDR REFERENCE9` BOOL,
69 `SUB ADDR UNIT10` VARCHAR(5),
70 `SUB ADDR REFERENCE10` BOOL,
71 `GEO-DIST CODE1` BOOL,
72 `GEO-DIST DESC1` BOOL,
73 `GEO-DIST CODE2` BOOL,
74 `GEO-DIST DESC2` BOOL,
75 `GEO-DIST CODE3` BOOL,
76 `GEO-DIST DESC3` BOOL,
77 `GEO-DIST CODE4` BOOL,
78 `GEO-DIST DESC4` BOOL,
79 `GEO-DIST CODE5` BOOL,
80 `GEO-DIST DESC5` BOOL,
81 `GEO-DIST CODE6` BOOL,
82 `GEO-DIST DESC6` BOOL,
83 `GEO-DIST CODE7` BOOL,
84 `GEO-DIST DESC7` BOOL,
85 `GEO-DIST CODE8` BOOL,
86 `GEO-DIST DESC8` BOOL,
87 `GEO-DIST CODE9` BOOL,
88 `GEO-DIST DESC9` BOOL,
89 `GEO-DIST CODE10` BOOL,
90 `GEO-DIST DESC10` BOOL,
91 `SIGN1 HEIGHT` DECIMAL(38, 1) NOT NULL,
92 `SIGN1 WIDTH` DECIMAL(38, 1) NOT NULL,
93 `SIGN1 TOTAL HEIGHT` DECIMAL(38, 0) NOT NULL,
94 `SIGN1 SQFT` DECIMAL(38, 0) NOT NULL,
95 `SIGN1 MATERIAL` VARCHAR(29),
96 `SIGN1 ATTACHED/FREESTND` VARCHAR(1),
97 `SIGN1 DIST FROM ROOF TO TOP` DECIMAL(38, 0) NOT NULL,
98 `SIGN1 SETBACK FROM STREET` DECIMAL(38, 0) NOT NULL,
99 `SIGN1 PROJ BEYOND BLDG` DECIMAL(38, 0) NOT NULL,
100 `SIGN1 ILLUMINATED` BOOL,
101 `SIGN1 UNDERGRND WIRING` BOOL,
102 `SIGN1 FLASHING` BOOL,
103 `SIGN1 EMITS NOISE` BOOL,
104 `SIGN1 CLEARANCE ABOVE GROUND` DECIMAL(38, 0) NOT NULL,
105 `SIGN1 NO OF SIGNS` VARCHAR(3),
106 `SIGN1 WORDING LN1` VARCHAR(53),
107 `SIGN1 WORDING LN2` BOOL,
108 `SIGN1 SIZE/LOC EXISTING SIGN` VARCHAR(60),
109 `SIGN1 DATE TO REMOVE` DATE,
110 `SIGN2 HEIGHT` DECIMAL(38, 0) NOT NULL,
111 `SIGN2 WIDTH` DECIMAL(38, 1) NOT NULL,
112 `SIGN2 TOTAL HEIGHT` DECIMAL(38, 0) NOT NULL,
113 `SIGN2 SQFT` DECIMAL(38, 0) NOT NULL,
114 `SIGN2 MATERIAL` VARCHAR(23),
115 `SIGN2 ATTACHED/FREESTND` VARCHAR(1),
116 `SIGN2 DIST FROM ROOF TO TOP` DECIMAL(38, 0) NOT NULL,
117 `SIGN2 SETBACK FROM STREET` DECIMAL(38, 0) NOT NULL,
118 `SIGN2 PROJ BEYOND BLDG` DECIMAL(38, 0) NOT NULL,
119 `SIGN2 ILLUMINATED` BOOL,
120 `SIGN2 UNDERGRND WIRING` BOOL,
121 `SIGN2 FLASHING` BOOL,
122 `SIGN2 EMITS NOISE` BOOL,
123 `SIGN2 CLEARANCE ABOVE GROUND` DECIMAL(38, 0) NOT NULL,
124 `SIGN2 NO OF SIGNS` VARCHAR(29),
125 `SIGN2 WORDING LN1` VARCHAR(49),
126 `SIGN2 WORDING LN2` BOOL,
127 `SIGN2 SIZE/LOC EXISTING SIGN` VARCHAR(59),
128 `SIGN2 DATE TO REMOVE` DATE,
129 `SIGN3 HEIGHT` DECIMAL(38, 1) NOT NULL,
130 `SIGN3 WIDTH` DECIMAL(38, 1) NOT NULL,
131 `SIGN3 TOTAL HEIGHT` DECIMAL(38, 0) NOT NULL,
132 `SIGN3 SQFT` DECIMAL(38, 0) NOT NULL,
133 `SIGN3 MATERIAL` VARCHAR(28),
134 `SIGN3 ATTACHED/FREESTND` VARCHAR(1),
135 `SIGN3 DIST FROM ROOF TO TOP` DECIMAL(38, 0) NOT NULL,
136 `SIGN3 SETBACK FROM STREET` DECIMAL(38, 0) NOT NULL,
137 `SIGN3 PROJ BEYOND BLDG` DECIMAL(38, 0) NOT NULL,
138 `SIGN3 ILLUMINATED` BOOL,
139 `SIGN3 UNDERGRND WIRING` BOOL,
140 `SIGN3 FLASHING` BOOL,
141 `SIGN3 EMITS NOISE` BOOL,
142 `SIGN3 CLEARANCE ABOVE GROUND` DECIMAL(38, 0) NOT NULL,
143 `SIGN3 NO OF SIGNS` BOOL,
144 `SIGN3 WORDING LN1` VARCHAR(47),
145 `SIGN3 WORDING LN2` VARCHAR(20),
146 `SIGN3 SIZE/LOC EXISTING SIGN` VARCHAR(39),
147 `SIGN3 DATE TO REMOVE` BOOL,
148 `SIGN4 HEIGHT` DECIMAL(38, 0) NOT NULL,
149 `SIGN4 WIDTH` DECIMAL(38, 1) NOT NULL,
150 `SIGN4 TOTAL HEIGHT` DECIMAL(38, 1) NOT NULL,
151 `SIGN4 SQFT` DECIMAL(38, 0) NOT NULL,
152 `SIGN4 MATERIAL` VARCHAR(22),
153 `SIGN4 ATTACHED/FREESTND` VARCHAR(1),
154 `SIGN4 DIST FROM ROOF TO TOP` DECIMAL(38, 0) NOT NULL,
155 `SIGN4 SETBACK FROM STREET` DECIMAL(38, 0) NOT NULL,
156 `SIGN4 PROJ BEYOND BLDG` DECIMAL(38, 0) NOT NULL,
157 `SIGN4 ILLUMINATED` BOOL,
158 `SIGN4 UNDERGRND WIRING` BOOL,
159 `SIGN4 FLASHING` BOOL,
160 `SIGN4 EMITS NOISE` BOOL,
161 `SIGN4 CLEARANCE ABOVE GROUND` DECIMAL(38, 0) NOT NULL,
162 `SIGN4 NO OF SIGNS` VARCHAR(22),
163 `SIGN4 WORDING LN1` VARCHAR(32),
164 `SIGN4 WORDING LN2` BOOL,
165 `SIGN4 SIZE/LOC EXISTING SIGN` BOOL,
166 `SIGN4 DATE TO REMOVE` BOOL,
167 `SIGN5 HEIGHT` DECIMAL(38, 1) NOT NULL,
168 `SIGN5 WIDTH` DECIMAL(38, 1) NOT NULL,
169 `SIGN5 TOTAL HEIGHT` DECIMAL(38, 0) NOT NULL,
170 `SIGN5 SQFT` DECIMAL(38, 0) NOT NULL,
171 `SIGN5 MATERIAL` VARCHAR(19),
172 `SIGN5 ATTACHED/FREESTND` VARCHAR(1),
173 `SIGN5 DIST FROM ROOF TO TOP` DECIMAL(38, 0) NOT NULL,
174 `SIGN5 SETBACK FROM STREET` DECIMAL(38, 0) NOT NULL,
175 `SIGN5 PROJ BEYOND BLDG` DECIMAL(38, 0) NOT NULL,
176 `SIGN5 ILLUMINATED` BOOL,
177 `SIGN5 UNDERGRND WIRING` BOOL,
178 `SIGN5 FLASHING` BOOL,
179 `SIGN5 EMITS NOISE` BOOL,
180 `SIGN5 CLEARANCE ABOVE GROUND` DECIMAL(38, 0) NOT NULL,
181 `SIGN5 NO OF SIGNS` VARCHAR(26),
182 `SIGN5 WORDING LN1` VARCHAR(32),
183 `SIGN5 WORDING LN2` BOOL,
184 `SIGN5 SIZE/LOC EXISTING SIGN` VARCHAR(40),
185 `SIGN5 DATE TO REMOVE` DATE,
186 `PROPERTY MANAGER ID` DECIMAL(38, 0) NOT NULL,
187 `PROPERTY MANAGER NAME` VARCHAR(38),
188 `PROPERTY MANAGER ADDRESS 1` VARCHAR(25),
189 `PROPERTY MANAGER ADDRESS 2` VARCHAR(30),
190 `PROPERTY MANAGER ADDRESS 3` VARCHAR(20),
191 `FLOOD ZONE CODE` BOOL,
192 `FLOOD ZONE DESC` BOOL,
193 `FLOOD ELEVATION` DECIMAL(38, 0) NOT NULL,
194 `SECTION` BOOL,
195 `SUBDIV PHASE` BOOL,
196 `LOC LONG` VARCHAR(30) NOT NULL,
197 `LOC STATE` VARCHAR(2),
198 `LOC ZIP` DECIMAL(38, 0),
199 `LOC TOWN` VARCHAR(20),
200 `FRONT SETBACK` DECIMAL(38, 0) NOT NULL,
201 `FRONT DIRECTION` BOOL,
202 `BACK SETBACK` DECIMAL(38, 0) NOT NULL,
203 `BACK DIRECTION` BOOL,
204 `LEFT SETBACK` DECIMAL(38, 0) NOT NULL,
205 `LEFT DIRECTION` BOOL,
206 `RIGHT SETBACK` DECIMAL(38, 0) NOT NULL,
207 `RIGHT DIRECTION` BOOL,
208 `WIDTH FRONT` DECIMAL(38, 2) NOT NULL,
209 `WIDTH BACK` DECIMAL(38, 2) NOT NULL,
210 `DEPTH LEFT` DECIMAL(38, 2) NOT NULL,
211 `DEPTH RIGHT` DECIMAL(38, 2) NOT NULL,
212 `LOT SQUARE FEET` DECIMAL(38, 0) NOT NULL,
213 `LOT ACRES` DECIMAL(38, 3) NOT NULL,
214 `FRONT FOOTAGE 1` DECIMAL(38, 0) NOT NULL,
215 `FRONT FOOTAGE 2` BOOL NOT NULL,
216 `ALLOWED LOT PERCENT` BOOL NOT NULL,
217 `ALLOWED BLDG PERCENT` DECIMAL(38, 0) NOT NULL,
218 `IMPERVIOUS SURFACE` DECIMAL(38, 0) NOT NULL,
219 `WATERFRONT CODE` BOOL,
220 `WATERFRONT DESC` BOOL,
221 `WATERFRONT FOOTAGE` BOOL NOT NULL,
222 `LEGAL DESCRIPTION` VARCHAR(223),
223 CHECK (`FORM CODE` IN (0, 1)),
224 CHECK (`RENTAL` IN (0, 1)),
225 CHECK (`CORNER LOT` IN (0, 1)),
226 CHECK (`VACANT LOT` IN (0, 1)),
227 CHECK (`GOVT OWNED` IN (0, 1)),
228 CHECK (`RESTR-HZD CODE6` IN (0, 1)),
229 CHECK (`RESTR-HZD DESC6` IN (0, 1)),
230 CHECK (`RESTR-HZD CODE7` IN (0, 1)),
231 CHECK (`RESTR-HZD DESC7` IN (0, 1)),
232 CHECK (`RESTR-HZD CODE8` IN (0, 1)),
233 CHECK (`RESTR-HZD DESC8` IN (0, 1)),
234 CHECK (`RESTR-HZD CODE9` IN (0, 1)),
235 CHECK (`RESTR-HZD DESC9` IN (0, 1)),
236 CHECK (`RESTR-HZD CODE10` IN (0, 1)),
237 CHECK (`RESTR-HZD DESC10` IN (0, 1)),
238 CHECK (`SUB ADDR REFERENCE4` IN (0, 1)),
239 CHECK (`SUB ADDR REFERENCE5` IN (0, 1)),
240 CHECK (`SUB ADDR REFERENCE6` IN (0, 1)),
241 CHECK (`SUB ADDR REFERENCE7` IN (0, 1)),
242 CHECK (`SUB ADDR REFERENCE9` IN (0, 1)),
243 CHECK (`SUB ADDR REFERENCE10` IN (0, 1)),
244 CHECK (`GEO-DIST CODE1` IN (0, 1)),
245 CHECK (`GEO-DIST DESC1` IN (0, 1)),
246 CHECK (`GEO-DIST CODE2` IN (0, 1)),
247 CHECK (`GEO-DIST DESC2` IN (0, 1)),
248 CHECK (`GEO-DIST CODE3` IN (0, 1)),
249 CHECK (`GEO-DIST DESC3` IN (0, 1)),
250 CHECK (`GEO-DIST CODE4` IN (0, 1)),
251 CHECK (`GEO-DIST DESC4` IN (0, 1)),
252 CHECK (`GEO-DIST CODE5` IN (0, 1)),
253 CHECK (`GEO-DIST DESC5` IN (0, 1)),
254 CHECK (`GEO-DIST CODE6` IN (0, 1)),
255 CHECK (`GEO-DIST DESC6` IN (0, 1)),
256 CHECK (`GEO-DIST CODE7` IN (0, 1)),
257 CHECK (`GEO-DIST DESC7` IN (0, 1)),
258 CHECK (`GEO-DIST CODE8` IN (0, 1)),
259 CHECK (`GEO-DIST DESC8` IN (0, 1)),
260 CHECK (`GEO-DIST CODE9` IN (0, 1)),
261 CHECK (`GEO-DIST DESC9` IN (0, 1)),
262 CHECK (`GEO-DIST CODE10` IN (0, 1)),
263 CHECK (`GEO-DIST DESC10` IN (0, 1)),
264 CHECK (`SIGN1 ILLUMINATED` IN (0, 1)),
265 CHECK (`SIGN1 UNDERGRND WIRING` IN (0, 1)),
266 CHECK (`SIGN1 FLASHING` IN (0, 1)),
267 CHECK (`SIGN1 EMITS NOISE` IN (0, 1)),
268 CHECK (`SIGN1 WORDING LN2` IN (0, 1)),
269 CHECK (`SIGN2 ILLUMINATED` IN (0, 1)),
270 CHECK (`SIGN2 UNDERGRND WIRING` IN (0, 1)),
271 CHECK (`SIGN2 FLASHING` IN (0, 1)),
272 CHECK (`SIGN2 EMITS NOISE` IN (0, 1)),
273 CHECK (`SIGN2 WORDING LN2` IN (0, 1)),
274 CHECK (`SIGN3 ILLUMINATED` IN (0, 1)),
275 CHECK (`SIGN3 UNDERGRND WIRING` IN (0, 1)),
276 CHECK (`SIGN3 FLASHING` IN (0, 1)),
277 CHECK (`SIGN3 EMITS NOISE` IN (0, 1)),
278 CHECK (`SIGN3 NO OF SIGNS` IN (0, 1)),
279 CHECK (`SIGN3 DATE TO REMOVE` IN (0, 1)),
280 CHECK (`SIGN4 ILLUMINATED` IN (0, 1)),
281 CHECK (`SIGN4 UNDERGRND WIRING` IN (0, 1)),
282 CHECK (`SIGN4 FLASHING` IN (0, 1)),
283 CHECK (`SIGN4 EMITS NOISE` IN (0, 1)),
284 CHECK (`SIGN4 WORDING LN2` IN (0, 1)),
285 CHECK (`SIGN4 SIZE/LOC EXISTING SIGN` IN (0, 1)),
286 CHECK (`SIGN4 DATE TO REMOVE` IN (0, 1)),
287 CHECK (`SIGN5 ILLUMINATED` IN (0, 1)),
288 CHECK (`SIGN5 UNDERGRND WIRING` IN (0, 1)),
289 CHECK (`SIGN5 FLASHING` IN (0, 1)),
290 CHECK (`SIGN5 EMITS NOISE` IN (0, 1)),
291 CHECK (`SIGN5 WORDING LN2` IN (0, 1)),
292 CHECK (`FLOOD ZONE CODE` IN (0, 1)),
293 CHECK (`FLOOD ZONE DESC` IN (0, 1)),
294 CHECK (`SECTION` IN (0, 1)),
295 CHECK (`SUBDIV PHASE` IN (0, 1)),
296 CHECK (`FRONT DIRECTION` IN (0, 1)),
297 CHECK (`BACK DIRECTION` IN (0, 1)),
298 CHECK (`LEFT DIRECTION` IN (0, 1)),
299 CHECK (`RIGHT DIRECTION` IN (0, 1)),
300 CHECK (`FRONT FOOTAGE 2` IN (0, 1)),
301 CHECK (`ALLOWED LOT PERCENT` IN (0, 1)),
302 CHECK (`WATERFRONT CODE` IN (0, 1)),
303 CHECK (`WATERFRONT DESC` IN (0, 1)),
304 CHECK (`WATERFRONT FOOTAGE` IN (0, 1))
305);