MySQL geom 偏移测试

Question

我正在编写一个偏移函数来测试移动物体是否接触空间中的其他可移动和固定物体。
为了测试这一点，我需要查看它们的边界矩形是否相交。

我想测试两个可移动物体是否像这样接触: table 车
- id:整数(autoinc 主)
- 矩形:线串
- ...

table 位置
- id:整数(autoinc 主)
- car_id: 整数
- car_date:日期
- car_pos:点
- ...


FUNCTION MyDB.OffsetRect(pLineString GEOMETRY, pOffset POINT)
RETURNS geometry
BEGIN<br/>
declare Result LineString;<br/>
declare P1 Point;<br/>
declare P2 point;<br/>
declare P3 point;<br/>
declare P4 point;<br/>
set Result = ENVELOPE(pLineString); /<em>Make sure we are dealing with a rect</em>/<br/>
set P1 = POINTN(Result,1);<br/>
set p1 = Point(X(p1)+X(pOffset),Y(p1)+Y(pOffset));<br/>
set P2 = POINTN(Result,2);<br/>
set p2 = Point(X(p2)+X(pOffset),Y(p2)+Y(pOffset));<br/>
set P3 = POINTN(Result,3);<br/>
set p3 = Point(X(p3)+X(pOffset),Y(p3)+Y(pOffset));<br/>
set P4 = POINTN(Result,4);<br/>
set p4 = Point(X(p4)+X(pOffset),Y(p4)+Y(pOffset));<br/>
set Result = LineString(p1,p2,p3,p4);<br/>
RETURN Result;<br/>
END

但是我不知道使用什么查询来查看两辆车是否及时相交。类似的东西。

SELECT location.id, location2.id FROM location
INNER JOIN car ON (car.id = location.car_id)
INNER JOIN location location2 ON (location.id <> location2.id)
INNER JOIN car car2 ON (car2.id = location2.car_id AND car.id <> car2.id)
WHERE location.car_date BETWEEN date_sub(now(),INTERVAL 1 DAY) AND date_add(now(),INTERVAL 1 DAY)
AND location2.car_date BETWEEN date_sub(now(),INTERVAL 1 DAY) and date_add(now(),INTERVAL 1 DAY)
AND      MBRIntersects(OffsetRect(car.rect,location.car_pos),OffsetRect(car2.rect,location2.car_pos));

但是这不起作用，出了什么问题？

Answer 1

相交..您的意思是两者都停在相距 48 小时内的同一个地方(您的时间范围是 48 小时)。

WHERE location.car_date BETWEEN date_sub(now(),INTERVAL 1 DAY) AND date_add(now(),INTERVAL 1 DAY)
AND location.car_date BETWEEN date_sub(now(),INTERVAL 1 DAY) and date_add(now(),INTERVAL 1 DAY)

您连续两次使用同一个别名