'How to cross join in Big Query using intervals?

How can I join two tables using intervals in Google Big Query?

I have two table:

Table CarsGPS:
ID | Car | Latitude | Longitude
1  | 1   | -22.123  | -43.123
2  | 1   | -22.234  | -43.234
3  | 2   | -22.567  | -43.567
4  | 2   | -22.678  | -43.678
...

Table Areas:
ID | LatitudeMin | LatitudeMax | LongitudeMin | LongitudeMax
1  | -22.124     | -22.120     | -43.124      | -43.120
2  | -22.128     | -22.124     | -43.128      | -43.124
...

I'd like to cross join these tables to check in which areas each car has passed by using Google Big Query.

In a regular SQL server I would make:

SELECT A.ID, C.Car 
FROM Cars C, Areas A
WHERE C.Latitude BETWEEN A.LatitudeMin AND A.LatitudeMax AND
      C.Longitude BETWEEN A.LongitudeMin AND A.LongitudeMax

But Google Big Query only allows me to do joins (even JOIN EACH) using exact matches among joined tables. And the "FROM X, Y" means UNION, not JOINS.

So, this is not an option:

SELECT A.ID, C.Car 
FROM Cars C
JOIN EACH
  Areas A
  ON C.Latitude BETWEEN A.LatitudeMin AND A.LatitudeMax AND
     C.Longitude BETWEEN A.LongitudeMin AND A.LongitudeMax

Then, how can I run something similar to it to identify which cars passed inside each area?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source