用SQL求出重叠的矩形！

题目：只要有一个矩形的点，在另一个矩形中，就算重叠。可以是多个矩形的点，在一个矩形内

--具体的测试数据及SQL如下：

--create table!

create table retangle (name varchar2(10),p1x number(8,2),p1y number(8,2),
p2x number(8,2),p2y number(8,2),p3x number(8,2),p3y number(8,2),p4x number(8,2),p4y number(8,2));

--insert into !
insert into retangle values('矩形一',1,2,2,2,2,1,1,1);
insert into retangle values('矩形二',3,4,4,4,4,2,3,2);
insert into retangle values('矩形三',4,3,5,3,5,1,3,1);  

insert into retangle values('矩形四',1,2,2,2,2,1,1,1);
insert into retangle values('矩形五',1,1,1,2,2,2,2,1);
insert into retangle values('矩形六',4,2,4,4,6,4,6,2);

 

方法一：用点到矩形四边的距离（以下的SQL还是有问题的！）
我的思路大概是这样的：有矩形A、B，如果矩形B中的任意一个顶点满足以下两个条件，则可证明该顶点在矩形A内
--1、顶点到矩形A的两条宽边的距离等于矩形A的长；
--2、顶点到矩形A的两条长边的距离等于矩形A的宽；

其实如果满足了以上两个条件后，矩形B不一定仅仅局限于矩形，也可以是不规则的四边形。

 

--具体的测试数据及SQL如下：

 

with
t as (select rownum rn,r.* from retangle r),
tt as(select t1.name,t1.p1x,t1.p1y,t1.p2x,t1.p2y,t1.p3x,t1.p3y,t1.p4x,t1.p4y,
t2.name name1,t2.p1x p1x1,t2.p1y p1y1,t2.p2x p2x1,t2.p2y p2y1,t2.p3x p3x1,t2.p3y p3y1,t2.p4x p4x1,t2.p4y p4y1,
decode(t1.p1x-t1.p2x,0,999999,(t1.p1y-t1.p2y)/(t1.p1x-t1.p2x)) k1,decode(t1.p1x-t1.p2x,0,t1.p1x,(t1.p1y-(t1.p1y-t1.p2y)/(t1.p1x-t1.p2x)*t1.p1x)) b1,
decode(t1.p2x-t1.p3x,0,999999,(t1.p2y-t1.p3y)/(t1.p2x-t1.p3x)) k2,decode(t1.p2x-t1.p3x,0,t1.p2x,(t1.p2y-(t1.p2y-t1.p3y)/(t1.p2x-t1.p3x)*t1.p2x)) b2,
decode(t1.p3x-t1.p4x,0,999999,(t1.p3y-t1.p4y)/(t1.p3x-t1.p4x)) k3,decode(t1.p3x-t1.p4x,0,t1.p3x,(t1.p3y-(t1.p3y-t1.p4y)/(t1.p3x-t1.p4x)*t1.p3x)) b3,
decode(t1.p4x-t1.p1x,0,999999,(t1.p4y-t1.p1y)/(t1.p4x-t1.p1x)) k4,decode(t1.p4x-t1.p1x,0,t1.p4x,(t1.p4y-(t1.p4y-t1.p1y)/(t1.p4x-t1.p1x)*t1.p4x)) b4
from t t1,t t2
where t1.rnttt as (select tt.name,tt.p1x,tt.p1y,tt.p2x,tt.p2y,tt.p3x,tt.p3y,tt.p4x,tt.p4y,
tt.name1,tt.k1,tt.b1,tt.k2,tt.b2,tt.k3,tt.b3,tt.k4,tt.b4,
decode(tt.k1,999999,abs(tt.p1x1-b1),abs(k1*tt.p1x1-tt.p1y1+b1)/power(1+k1*k1,0.5)) p1_p1p2,
decode(tt.k2,999999,abs(tt.p1x1-b2),abs(k2*tt.p1x1-tt.p1y1+b2)/power(1+k2*k2,0.5)) p1_p2p3,
decode(tt.k3,999999,abs(tt.p1x1-b3),abs(k3*tt.p1x1-tt.p1y1+b3)/power(1+k3*k3,0.5)) p1_p3p4,
decode(tt.k4,999999,abs(tt.p1x1-b4),abs(k4*tt.p1x1-tt.p1y1+b4)/power(1+k4*k4,0.5)) p1_p4p1,
decode(tt.k1,999999,abs(tt.p2x1-b1),abs(k1*tt.p2x1-tt.p2y1+b1)/power(1+k1*k1,0.5)) p2_p1p2,
decode(tt.k2,999999,abs(tt.p2x1-b2),abs(k2*tt.p2x1-tt.p2y1+b2)/power(1+k2*k2,0.5)) p2_p2p3,
decode(tt.k3,999999,abs(tt.p2x1-b3),abs(k3*tt.p2x1-tt.p2y1+b3)/power(1+k3*k3,0.5)) p2_p3p4,
decode(tt.k4,999999,abs(tt.p2x1-b4),abs(k4*tt.p2x1-tt.p2y1+b4)/power(1+k4*k4,0.5)) p2_p4p1,
decode(tt.k1,999999,abs(tt.p3x1-b1),abs(k1*tt.p3x1-tt.p3y1+b1)/power(1+k1*k1,0.5)) p3_p1p2,
decode(tt.k2,999999,abs(tt.p3x1-b2),abs(k2*tt.p3x1-tt.p3y1+b2)/power(1+k2*k2,0.5)) p3_p2p3,
decode(tt.k3,999999,abs(tt.p3x1-b3),abs(k3*tt.p3x1-tt.p3y1+b3)/power(1+k3*k3,0.5)) p3_p3p4,
decode(tt.k4,999999,abs(tt.p3x1-b4),abs(k4*tt.p3x1-tt.p3y1+b4)/power(1+k4*k4,0.5)) p3_p4p1,
decode(tt.k1,999999,abs(tt.p4x1-b1),abs(k1*tt.p4x1-tt.p4y1+b1)/power(1+k1*k1,0.5)) p4_p1p2,
decode(tt.k2,999999,abs(tt.p4x1-b2),abs(k2*tt.p4x1-tt.p4y1+b2)/power(1+k2*k2,0.5)) p4_p2p3,
decode(tt.k3,999999,abs(tt.p4x1-b3),abs(k3*tt.p4x1-tt.p4y1+b3)/power(1+k3*k3,0.5)) p4_p3p4,
decode(tt.k4,999999,abs(tt.p4x1-b4),abs(k4*tt.p4x1-tt.p4y1+b4)/power(1+k4*k4,0.5)) p4_p4p1,
power((tt.p1x-tt.p2x)*(tt.p1x-tt.p2x)+(tt.p1y-tt.p2y)*(tt.p1y-tt.p2y),0.5) p1p2,
power((tt.p1x-tt.p4x)*(tt.p1x-tt.p4x)+(tt.p1y-tt.p4y)*(tt.p1y-tt.p4y),0.5) p1p4
from tt)
select ttt.name,ttt.name1 from ttt
where
(ttt.p1_p1p2+ttt.p1_p3p4=ttt.p1p4 and ttt.p1_p2p3+ttt.p1_p4p1=ttt.p1p2)
or
(ttt.p2_p1p2+ttt.p2_p3p4=ttt.p1p4 and ttt.p2_p2p3+ttt.p2_p4p1=ttt.p1p2)
or
(ttt.p3_p1p2+ttt.p3_p3p4=ttt.p1p4 and ttt.p3_p2p3+ttt.p3_p4p1=ttt.p1p2)
or
(ttt.p4_p1p2+ttt.p4_p3p4=ttt.p1p4 and ttt.p4_p2p3+ttt.p4_p4p1=ttt.p1p2)

解释一下：
k为斜率，b为x=0时的y值，即直线为y=kx+b
当k趋于正无穷大时，我这里设为999999，即此时的直线方程为x=常数
p1_p1p2表示矩形B的点p1到矩形A的p1p2这条直线的距离；

方法二：用点到矩形四个顶点的距离法（这个方法简单易懂）：
为了证明矩形A、B是否“重叠”（即有一个矩形的点在另一个矩形中），下面给出两个引理；
设矩形A的长为L，宽为W
引理一：若点p(x,y)在矩形内或在矩形A的边上，那么该点p到矩形A的四个顶点的距离之和L1满足：2*SQRT(L*L+W*W)=引理二：若点p（x,y）不在矩形A的内或矩形A的边上，那么该点p到矩形A的四个顶点的距离之和L1满足：L1>W+L+SQRT(L*L+W*W)；

限于篇幅以及时间有限，我就不一一罗列出引理一和引理二的证明过程了。

证明的办法有好多种：一是等式两边不断平方，然后约去相同项，利用根号大于等于0的性质即可证明；
                              二是用拉格朗日乘数法，求其最大最小值；

 

基于以上的引理，可以很简单的写出证明两个矩形是否有重叠的SQL：

 

with t as
(select rownum rn,t.* from retangle t),
tt as (select t1.name,t1.p1x,t1.p1y,t1.p2x,t1.p2y,t1.p3x,t1.p3y,t1.p4x,t1.p4y,t2.name name1,
t2.p1x p1x1,t2.p1y p1y1,t2.p2x p2x1,t2.p2y p2y1,t2.p3x p3x1,t2.p3y p3y1,t2.p4x p4x1,t2.p4y p4y1 from t t1,t t2 where t1.name<>t2.name),
ttt as (select tt.name,tt.name1,
power((tt.p1x1-tt.p1x)*(tt.p1x1-tt.p1x)+(tt.p1y1-tt.p1y)*(tt.p1y1-tt.p1y),0.5) p1_p1,
power((tt.p1x1-tt.p2x)*(tt.p1x1-tt.p2x)+(tt.p1y1-tt.p2y)*(tt.p1y1-tt.p2y),0.5) p1_p2,
power((tt.p1x1-tt.p3x)*(tt.p1x1-tt.p3x)+(tt.p1y1-tt.p3y)*(tt.p1y1-tt.p3y),0.5) p1_p3,
power((tt.p1x1-tt.p4x)*(tt.p1x1-tt.p4x)+(tt.p1y1-tt.p4y)*(tt.p1y1-tt.p4y),0.5) p1_p4,
power((tt.p2x1-tt.p1x)*(tt.p2x1-tt.p1x)+(tt.p2y1-tt.p1y)*(tt.p2y1-tt.p1y),0.5) p2_p1,
power((tt.p2x1-tt.p2x)*(tt.p2x1-tt.p2x)+(tt.p2y1-tt.p2y)*(tt.p2y1-tt.p2y),0.5) p2_p2,
power((tt.p2x1-tt.p3x)*(tt.p2x1-tt.p3x)+(tt.p2y1-tt.p3y)*(tt.p2y1-tt.p3y),0.5) p2_p3,
power((tt.p2x1-tt.p4x)*(tt.p2x1-tt.p4x)+(tt.p2y1-tt.p4y)*(tt.p2y1-tt.p4y),0.5) p2_p4,
power((tt.p3x1-tt.p1x)*(tt.p3x1-tt.p1x)+(tt.p3y1-tt.p1y)*(tt.p3y1-tt.p1y),0.5) p3_p1,
power((tt.p3x1-tt.p2x)*(tt.p3x1-tt.p2x)+(tt.p3y1-tt.p2y)*(tt.p3y1-tt.p2y),0.5) p3_p2,
power((tt.p3x1-tt.p3x)*(tt.p3x1-tt.p3x)+(tt.p3y1-tt.p3y)*(tt.p3y1-tt.p3y),0.5) p3_p3,
power((tt.p3x1-tt.p4x)*(tt.p3x1-tt.p4x)+(tt.p3y1-tt.p4y)*(tt.p3y1-tt.p4y),0.5) p3_p4,
power((tt.p4x1-tt.p1x)*(tt.p4x1-tt.p1x)+(tt.p4y1-tt.p1y)*(tt.p4y1-tt.p1y),0.5) p4_p1,
power((tt.p4x1-tt.p2x)*(tt.p4x1-tt.p2x)+(tt.p4y1-tt.p2y)*(tt.p4y1-tt.p2y),0.5) p4_p2,
power((tt.p4x1-tt.p3x)*(tt.p4x1-tt.p3x)+(tt.p4y1-tt.p3y)*(tt.p4y1-tt.p3y),0.5) p4_p3,
power((tt.p4x1-tt.p4x)*(tt.p4x1-tt.p4x)+(tt.p4y1-tt.p4y)*(tt.p4y1-tt.p4y),0.5) p4_p4,
2*power((tt.p1x-tt.p3x)*(tt.p1x-tt.p3x)+(tt.p1y-tt.p3y)*(tt.p1y-tt.p3y),0.5) min_E,
(power((tt.p1x-tt.p3x)*(tt.p1x-tt.p3x)+(tt.p1y-tt.p3y)*(tt.p1y-tt.p3y),0.5))+
(power((tt.p1x-tt.p2x)*(tt.p1x-tt.p2x)+(tt.p1y-tt.p2y)*(tt.p1y-tt.p2y),0.5))+
(power((tt.p2x-tt.p3x)*(tt.p2x-tt.p3x)+(tt.p2y-tt.p3y)*(tt.p2y-tt.p3y),0.5)) max_E
from tt)
select ttt.*,ttt.name,ttt.name1 from ttt
where
(p1_p2+p1_p3+p1_p3+p1_p4>=min_E and p1_p2+p1_p3+p1_p3+p1_p4<=max_E)
or
(p2_p1+p2_p2+p2_p3+p2_p4>=min_E and p2_p1+p2_p2+p2_p3+p2_p4<=max_E)
or
(p3_p1+p3_p2+p3_p3+p3_p4>=min_E and p3_p1+p3_p2+p3_p3+p3_p4<=max_E)
or
(p4_p1+p4_p2+p4_p3+p4_p4>=min_E and p4_p1+p4_p2+p4_p3+p4_p4<=max_E)

点评：我下班后在地铁上想到的方法，个人很喜欢方法二，毕竟花了2个小时才想出来的引理！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26977915/viewspace-733838/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26977915/viewspace-733838/