aoc-2020-day12-postgresql.sql

CREATE TEMP TABLE raw_input (
    line TEXT,
    line_id SERIAL PRIMARY KEY
);

\COPY raw_input (line) FROM ~/Downloads/input12.txt

CREATE TEMP TABLE directions AS
    SELECT LEFT(line, 1) AS command_type,
           SUBSTRING(line FROM 2)::INTEGER AS command_amount,
           line_id
    FROM raw_input;

ALTER TABLE directions ADD CONSTRAINT directions_pk PRIMARY KEY (line_id);

-- CREATE TEMP TABLE t AS
WITH RECURSIVE travels AS (
    SELECT 0 AS relative_latitude,
           0 AS relative_longitude,
           0 AS line_id,
           90 AS direction,
           '' AS next_cmd,
           0 AS next_amt
    UNION ALL
    SELECT relative_latitude + 
           CASE WHEN command_type = 'E' OR (command_type = 'F' AND direction = 90)
                THEN command_amount
                WHEN command_type = 'W' OR (command_type = 'F' AND direction = 270)
                THEN 0 - command_amount
                ELSE 0
           END AS relative_latitude,
           relative_longitude + 
           CASE WHEN command_type = 'N' OR (command_type = 'F' AND direction = 0)
                THEN command_amount
                WHEN command_type = 'S' OR (command_type = 'F' AND direction = 180)
                THEN 0 - command_amount
                ELSE 0
           END AS relative_longitude,
           directions.line_id,
           CASE WHEN command_type = 'L'
                THEN (direction + 360 - command_amount) % 360
                WHEN command_type = 'R'
                THEN (direction + 360 + command_amount) % 360
                ELSE direction
           END AS direction,
           directions.command_type,
           directions.command_amount
    FROM directions
        JOIN travels
            ON travels.line_id + 1 = directions.line_id
)
SELECT ABS(relative_latitude) + ABS(relative_longitude)
    FROM travels
    ORDER BY line_id DESC
    LIMIT 1;


-- Part 2

WITH RECURSIVE travels AS (
    SELECT 0 AS relative_latitude,
           0 AS relative_longitude,
           0 AS line_id,
           10 AS waypoint_latitude,
           1 AS waypoint_longitude,
           '' AS line_cmd,
           0 AS line_amt
    UNION ALL
    SELECT relative_latitude +
           CASE WHEN command_type = 'F'
                THEN command_amount * waypoint_latitude
                ELSE 0
           END AS relative_latitude,
           relative_longitude +
           CASE WHEN command_type = 'F'
                THEN command_amount * waypoint_longitude
                ELSE 0
           END AS relative_longitude,
           directions.line_id,
           CASE WHEN command_type = 'E'
                THEN waypoint_latitude + command_amount
                WHEN command_type = 'W'
                THEN waypoint_latitude - command_amount
                WHEN command_type = 'R'
                THEN CASE command_amount % 360
                        WHEN 0
                        THEN waypoint_latitude
                        WHEN 90
                        THEN waypoint_longitude
                        WHEN 180
                        THEN 0 - waypoint_latitude
                        WHEN 270
                        THEN 0 -waypoint_longitude
                    END
                WHEN command_type = 'L'
                THEN CASE command_amount % 360
                        WHEN 0
                        THEN waypoint_latitude
                        WHEN 90
                        THEN 0 - waypoint_longitude
                        WHEN 180
                        THEN 0 - waypoint_latitude
                        WHEN 270
                        THEN waypoint_longitude
                    END
                ELSE waypoint_latitude
           END AS waypoint_latitude,
           CASE WHEN command_type = 'N'
                THEN waypoint_longitude + command_amount
                WHEN command_type = 'S'
                THEN waypoint_longitude - command_amount
                WHEN command_type = 'R'
                THEN CASE command_amount % 360
                        WHEN 0
                        THEN waypoint_longitude
                        WHEN 90
                        THEN 0 - waypoint_latitude
                        WHEN 180
                        THEN 0 - waypoint_longitude
                        WHEN 270
                        THEN waypoint_latitude
                    END
                WHEN command_type = 'L'
                THEN CASE command_amount % 360
                        WHEN 0
                        THEN waypoint_longitude
                        WHEN 90
                        THEN waypoint_latitude
                        WHEN 180
                        THEN 0 - waypoint_longitude
                        WHEN 270
                        THEN 0 - waypoint_latitude
                    END
                ELSE waypoint_longitude
           END AS waypoint_longitude,
           directions.command_type,
           directions.command_amount
    FROM directions
        JOIN travels
            ON travels.line_id + 1 = directions.line_id
)
SELECT ABS(relative_latitude) + ABS(relative_longitude)
    FROM travels
    ORDER BY line_id DESC
    LIMIT 1;