# 四邊形修正邏輯
## 公式
$$
\begin{split}
&w為寬，l為長\\
&A，B，C，D為原始輸入座標\\
&A'，B'，C'，D'為修正後座標\\
&I為\triangle ABD重心\\
&J為\triangle BCD重心\\
&L為\square ABCD重心\\\\\\
&\color{#0000ff}{先計算\square ABCD重心}\\
&x_{I}={x_{A}+x_{B}+x_{D} \over 3} \\
&y_{I}={y_{A}+y_{B}+y_{D} \over 3} \\
&x_{J}={x_{B}+x_{C}+x_{D} \over 3} \\
&y_{J}={y_{B}+y_{C}+y_{D} \over 3} \\
&\triangle ABD=\frac{1}{2}[(x_{A}y_{D}+x_{D}y_{B}+x_{B}y_{A})-(x_{D}y_{A}+x_{B}y_{D}+x_{A}y_{B})]\\
&\triangle BCD=\frac{1}{2}[(x_{B}y_{D}+x_{D}y_{C}+x_{C}y_{B})-(x_{D}y_{B}+x_{C}y_{D}+x_{B}y_{C})]\\
&\begin{bmatrix}
x_{L}\\y_{L}
\end{bmatrix}={\triangle ABD \over \triangle ABD + \triangle BCD}\begin{bmatrix}
x_{I}\\y_{I}
\end{bmatrix}+{\triangle BCD \over \triangle ABD + \triangle BCD}\begin{bmatrix}
x_{J}\\y_{J}
\end{bmatrix}\\\\\\
&\color{#0000ff}{接著計算長寬之單位向量\vec{n}，\vec{m}}\\
&\vec{AD}=\begin{bmatrix}
x_{D}-x_{A}\\y_{D}-y_{A}
\end{bmatrix}\\
&\vec{n}=\frac{\vec{AD}}{\overline{AD}}\\
&\vec{m}=\begin{bmatrix}
\cos{\theta} & -\sin{\theta} \\
\sin{\theta} & \cos{\theta}
\end{bmatrix}\vec{n}，\theta=90^\circ可根據傾斜角度做調整\\\\\\
&\color{#0000ff}{最後求得A'，B'，C'，D'}\\
&\begin{bmatrix}
x'_{A}\\y'_{A}
\end{bmatrix}=\begin{bmatrix}
x_{L}\\y_{L}
\end{bmatrix}-\frac{w}{2}\vec{n}-\frac{l}{2}\vec{m}\\
&\begin{bmatrix}
x'_{B}\\y'_{B}
\end{bmatrix}=\begin{bmatrix}
x'_{A}\\y'_{A}
\end{bmatrix}+l\vec{m}\\
&\begin{bmatrix}
x'_{C}\\y'_{C}
\end{bmatrix}=\begin{bmatrix}
x'_{A}\\y'_{A}
\end{bmatrix}+w\vec{n}+l\vec{m}\\
&\begin{bmatrix}
x'_{D}\\y'_{D}
\end{bmatrix}=\begin{bmatrix}
x'_{A}\\y'_{A}
\end{bmatrix}+w\vec{n}
\end{split}
$$

## SQL程式碼
未用質心算法(會導致車格沒對齊)

CREATE OR REPLACE FUNCTION ST_TurnArray(point_A geometry, point_B geometry, point_C geometry,point_D geometry,length double precision,width double precision)
    RETURNS geometry AS
$BODY$
SELECT ST_Polygon(CONCAT('LINESTRING(',ST_X(g.pL)-$5/2*ST_X(g.vM)-$6/2*ST_X(g.vN),' ',ST_Y(g.pL)-$5/2*ST_Y(g.vM)-$6/2*ST_Y(g.vN),','
						 ,ST_X(g.pL)+$5/2*ST_X(g.vM)-$6/2*ST_X(g.vN),' ',ST_Y(g.pL)+$5/2*ST_Y(g.vM)-$6/2*ST_Y(g.vN),','
						 ,ST_X(g.pL)+$5/2*ST_X(g.vM)+$6/2*ST_X(g.vN),' ',ST_Y(g.pL)+$5/2*ST_Y(g.vM)+$6/2*ST_Y(g.vN),','
						 ,ST_X(g.pL)-$5/2*ST_X(g.vM)+$6/2*ST_X(g.vN),' ',ST_Y(g.pL)-$5/2*ST_Y(g.vM)+$6/2*ST_Y(g.vN),','
						 ,ST_X(g.pL)-$5/2*ST_X(g.vM)-$6/2*ST_X(g.vN),' ',ST_Y(g.pL)-$5/2*ST_Y(g.vM)-$6/2*ST_Y(g.vN),')')::geometry,3826)	--以L為中心定義出修正後四邊形位置
FROM(SELECT
    ST_Centroid(ST_Polygon(CONCAT('LINESTRING(',f.pAx,' ',f.pAy,',',f.pBx,' ',f.pBy,',',f.pCx,' ',f.pCy,',',f.pDx,' ',f.pDy,',',f.pAx,' ',f.pAy,')')::geometry,3826)) AS pL,
    case
        when f.dAD>f.dAB then ST_MakePoint((f.pDx-f.pAx)/f.dAD,(f.pDy-f.pAy)/f.dAD)
        when f.dAD<=f.dAB then ST_MakePoint((f.pBx-f.pAx)/f.dAB,(f.pBy-f.pAy)/f.dAB)
        END AS vM,
    case
        when f.dAB>f.dAD then ST_Rotate(ST_MakePoint((f.pBx-f.pAx)/f.dAB,(f.pBy-f.pAy)/f.dAB),f.r)
        when f.dAB<=f.dAD then ST_Rotate(ST_MakePoint((f.pDx-f.pAx)/f.dAD,(f.pDy-f.pAy)/f.dAD),f.r)
        END AS vN
    FROM(SELECT
        ST_X($1) AS pAx, ST_Y($1) AS pAy,
        ST_X($2) AS pBx, ST_Y($2) AS pBy,
        ST_X($3) AS pCx, ST_Y($3) AS pCy,
        ST_X($4) AS pDx, ST_Y($4) AS pDy,
        ST_Distance($1,$4) AS dAD,
        ST_Distance($1,$2) AS dAB,
        case
            when $5 < 0 AND $6 > 0 then pi()/3
            when $6 < 0 AND $5 > 0 then pi()*2/3
            else pi()/2
            END AS r
        FROM public.pk_mark) AS f) AS g
$BODY$
LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION pk_mark_fixed() RETURNS trigger 
AS $$
BEGIN
    --UPDATE
    IF (TG_OP = 'UPDATE' AND OLD.* IS DISTINCT FROM NEW.*) THEN
        NEW.geom = st_turnarray(ST_AsText(ST_PointN(ST_Boundary(NEW.geom),1)),
								ST_AsText(ST_PointN(ST_Boundary(NEW.geom),2)),
								ST_AsText(ST_PointN(ST_Boundary(NEW.geom),3)),
								ST_AsText(ST_PointN(ST_Boundary(NEW.geom),4)),
								NEW.length,
								NEW.width);
    --INSERT	
    ELSEIF(TG_OP = 'INSERT' AND OLD.* is NULL)THEN
        NEW.geom = st_turnarray(ST_AsText(ST_PointN(ST_Boundary(NEW.geom),1)),
								ST_AsText(ST_PointN(ST_Boundary(NEW.geom),2)),
								ST_AsText(ST_PointN(ST_Boundary(NEW.geom),3)),
								ST_AsText(ST_PointN(ST_Boundary(NEW.geom),4)),
								NEW.length,
								NEW.width);
    END IF;	
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
										  
										  
DROP TRIGGER IF EXISTS pk_mark_fixed_update ON public.pk_mark;
CREATE TRIGGER pk_mark_fixed_update 
BEFORE UPDATE ON public.pk_mark
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.* AND NEW.length is not NULL AND NEW.width is not NULL AND NEW.length <> 0 AND NEW.width <> 0)
EXECUTE PROCEDURE public.pk_mark_fixed(); 

DROP TRIGGER IF EXISTS pk_mark_fixed_insert ON public.pk_mark;
CREATE TRIGGER pk_mark_fixed_insert 
BEFORE INSERT ON public.pk_mark
FOR EACH ROW
WHEN (NEW.length is not NULL AND NEW.width is not NULL AND NEW.length <> 0 AND NEW.width <> 0)
EXECUTE PROCEDURE public.pk_mark_fixed();

###### tags: `實習` `SQL`