joshlk

-- See: https://stackoverflow.com/questions/48770517/take-first-second-third-last-value-and-selecting-rows-window-function-wit
----- N = 1
-- State transition function
-- agg_state: the current state, el: new element
create or replace function lag_agg_sfunc_1(agg_state point, el float)
    returns point
    immutable
    language plpgsql
    as $$
declare

joshlk

-- https://wiki.postgresql.org/wiki/First/last_(aggregate)
-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT $1;
$$;
 
-- And then wrap an aggregate around it
CREATE AGGREGATE public.FIRST (
        sfunc    = public.first_agg,

joshlk

#!/usr/bin/env python

import warnings
import numbers
import time

import numpy as np

from sklearn.base import is_classifier, clone
from sklearn.utils import indexable

joshlk

Parsing and splitting UK postcodes

You can use this regex to split the postcodes into its constituent parts:area (capture groups a1, a2 ... a5), district (d1...), sector (s1...) and unit (u1...). Adapted from wikipedia:

^(?:(?P<a1>[Gg][Ii][Rr])(?P<d1>) (?P<s1>0)(?P<u1>[Aa]{2}))|(?:(?:(?:(?P<a2>[A-Za-z])(?P<d2>[0-9]{1,2}))|(?:(?:(?P<a3>[A-Za-z][A-Ha-hJ-Yj-y])(?P<d3>[0-9]{1,2}))|(?:(?:(?P<a4>[A-Za-z])(?P<d4>[0-9][A-Za-z]))|(?:(?P<a5>[A-Za-z][A-Ha-hJ-Yj-y])(?P<d5>[0-9]?[A-Za-z]))))) (?P<s2>[0-9])(?P<u2>[A-Za-z]{2}))$

Demonstrated in this Regex101.

Postgres and SQL

joshlk

class OneHotEncode(Embedding):
    """
    One-hot-encode layer eg. [[1, 3]] -> [[[0,1,0,0],[0,0,0,1]]]
    
    input_dim: Number of categories to one-hot encode
    input_length: length of X vecotrs (optional)
    mask_zero: treat 0 as masking
    """
    
    def __init__(self, input_dim, input_length=None, mask_zero=False):

joshlk

CREATE OR REPLACE FUNCTION query(
   in_tb character varying,
   out_tb character varying
)
RETURNS integer
LANGUAGE plpgsql
AS $function$
BEGIN
EXECUTE
    'CREATE TABLE '

joshlk

import dataiku
import dataikuapi
from dataikuapi import SQLQueryRecipeCreator

### Creare SQL recipy
builder = SQLQueryRecipeCreator('compute_output', project)
builder.with_input('input')
builder.with_new_output('output', 'connection')
recipe = builder.build()

joshlk

-- Convert a lat,long pair to a projected (British grid system) coordiantes
-- SRID 4326: WGS84 GPS coordiantes
-- SRID 27700: British National Grid
SELECT
   ST_X(ST_Transform(ST_SetSRID(ST_Point(long, lat),4326), 27700)) as x
  ,ST_Y(ST_Transform(ST_SetSRID(ST_Point(long, lat),4326), 27700)) as y
;                     
                     
-- Number of points from another within X meters (for BSG)
SELECT

joshlk

# SSH port forwarding/tunnel
# If I want to be able to access port 6006 on a remote server locally
ssh -L 6006:localhost:6006 user@remote

joshlk