-- Create Dallas Open Data tables
-- https://www.dallasopendata.com/browse

-- Police Bulk Data table
-- Permalink: https://www.dallasopendata.com/Police/Police-Bulk-Data/ftja-9jxd
DROP TABLE IF EXISTS public.dallaspolice;
CREATE TABLE public.dallaspolice  (
AgencyORI varchar(20),
AgencyName varchar(100),
offenseservicenumber varchar(100),
offensedate date,
offensereporteddate date,
offensedescription varchar(1000),
offensestarttime time,
offensestoptime time,
offensetimedispatched interval,
offensebeat integer,
offensereportingarea integer,
offensewatch varchar(5),
offensesignal1 varchar(5),
offensesignal2 varchar(5),
offensename varchar(1000),
offenserace varchar(10),
offensegender varchar(10),
offenseage varchar(10),
offenseblock varchar(20),
offensedirection varchar(5),
offensestreet varchar(1000),
offenseapartment varchar(20),
offensecity varchar(100),
offensestate varchar(10),
offensezip varchar(5),
offensebusinessblock varchar(20),
offensebusinessdirection varchar(5),
offensebusinessstreet varchar(1000),
offensebusinesscity varchar(100),
offensepropertyattackcode varchar(5),
offensepremises varchar(100),
offensedateofoccurence1 date,
offensetimeofoccurence1 time,
offensedateofoccurence2 date,
offensetimeofoccurence2 time,
offenseucr1 varchar(20),
offenseucr2 varchar(20),
offensemethodofoffense varchar(1000),
offenseweather varchar(100),
offensefamilyviolence varchar(5),
offensegangacitivty varchar(5),
offensereportofficerbadge1 varchar(20),
offensereportingofficerbadge2 varchar(20),
offenserecorddate timestamp,
offensestatus varchar(100)
) DISTRIBUTE BY HASH(offenseservicenumber);


-- Bulk Police Narrative table
-- Permalink: https://www.dallasopendata.com/Police/Bulk-Police-Narrative/inke-qqax
DROP TABLE IF EXISTS public.dallaspolicenarrative;
CREATE TABLE public.dallaspolicenarrative  (
AgencyORI varchar(20),
AgencyName varchar(100),
offenseservicenumber varchar(100),
offensenarrative varchar(10000),
offensedate date,
offenserecorddate timestamp
) DISTRIBUTE BY HASH(offenseservicenumber);


-- Building Inspection Master Permits
-- Permalink: https://www.dallasopendata.com/dataset/Building-Inspection-Master-Permits/ks9j-qkj8
DROP TABLE IF EXISTS public.dallasbuildingpermits;
CREATE TABLE public.dallasbuildingpermits (
PermitNo varchar(10),
PermitType varchar(200),
Issued date,
Mapsco varchar(100),
Contractor varchar(200),
ValueStr varchar(20),
Area numeric,
WorkDescription varchar(20000),
LandUse varchar(100),
Address varchar(200),
GeoLocation varchar(300),
ZipCode varchar(100)
) DISTRIBUTE BY HASH(PermitNo);


