
-- Table that combines both police reports tables 
DROP TABLE IF EXISTS public.dallaspoliceall;
CREATE TABLE public.dallaspoliceall 
DISTRIBUTE BY HASH(offenseservicenumber) AS
SELECT dp.*,  
       extract('hour' from dp.offensestarttime)::int offensehour,  
       extract('dow' from dp.offensedate)::int offensedow,
       extract('day' from dp.offensedate)::int offenseday,
       dpn.offensenarrative 
  FROM public.dallaspolice dp JOIN
       public.dallaspolicenarrative dpn ON (dp.offenseservicenumber = dpn.offenseservicenumber);

-- Enhance building permits data
ALTER TABLE public.dallasbuildingpermits 
   ADD COLUMN Value numeric, 
   ADD COLUMN Address1 varchar(1000),
   ADD COLUMN Address2 varchar(1000),
   ADD COLUMN Lon numeric,
   ADD COLUMN Lat numeric;
UPDATE public.dallasbuildingpermits 
   SET Value = substr(ValueStr, 2)::numeric,
       Address1 = substring(geolocation from '#"%#"\n%\n%' for '#'),
       Address2 = substring(geolocation from '%\n#"%#"\n%' for '#'),
       Lat = trim(leading '(' from substring(geolocation from '%\n%\n#"%#",%' for '#'))::numeric,
       Lon = trim(trailing ')' from substring(geolocation from '%\n%\n%,#"%#"' for '#'))::numeric;

-- Unpivot for distance measure function
DROP TABLE IF EXISTS public.dallasbuildingpermits_unpivot;
CREATE TABLE public.dallasbuildingpermits_unpivot 
DISTRIBUTE BY HASH(PermitNo)
AS
select permitno, axis, max(val::numeric) val from Unpivot(
  on public.dallasbuildingpermits
  colsToUnpivot('lon','lat')
  colsToAccumulate('permitno')
  ATTRIBUTECOLUMNNAME('axis')
  VALUECOLUMNNAME('val')
)
  where val is not null
 group by permitno, axis;


/**
DROP TABLE IF EXISTS public.dallasbuildingpermits_dist;
CREATE TABLE public.dallasbuildingpermits_dist 
DISTRIBUTE BY HASH(target_permitno)
AS
select * from VectorDistance(
  on public.dallasbuildingpermits_unpivot as target partition by permitno
  on public.dallasbuildingpermits_unpivot as ref dimension
  targetIdColumns('permitno')
  targetFeatureColumn('axis')
  targetValueColumn('val')
  measure('Euclidean')
  --threashold('0.07','0.5')
);
**/
