MySQL geom offset testing

I'm looking to write an offset function to test in a moving object touches other movable and fixed objects in space.

In order to test this I need to see if their bounding rects intersect.

I would like to test if two movable objects touch like so: Table car

- id: integer (autoinc primary)

- rect: Linestring

- ...

Table location

- id: integer (autoinc primary)

- car_id: integer

- car_date: date

- car_pos: point

- ...

FUNCTION MyDB.OffsetRect(pLineString GEOMETRY, pOffset POINT) RETURNS geometry BEGIN

declare Result LineString;

declare P1 Point;

declare P2 point;

declare P3 point;

declare P4 point;

set Result = ENVELOPE(pLineString); /Make sure we are dealing with a rect/

set P1 = POINTN(Result,1);

set p1 = Point(X(p1)+X(pOffset),Y(p1)+Y(pOffset));

set P2 = POINTN(Result,2);

set p2 = Point(X(p2)+X(pOffset),Y(p2)+Y(pOffset));

set P3 = POINTN(Result,3);

set p3 = Point(X(p3)+X(pOffset),Y(p3)+Y(pOffset));

set P4 = POINTN(Result,4);

set p4 = Point(X(p4)+X(pOffset),Y(p4)+Y(pOffset));

set Result = LineString(p1,p2,p3,p4);

RETURN Result;

END

But I'm stuck at what query to use to see if two cars intersect in time. somethin开发者_开发知识库g like.

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));

However this does not work, what's wrong?

Intersect.. you mean both parked in the same place within the space of 48 hours from each other (your time ranges are 48 hours).

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)

You have used the same alias twice in a row