Spatial and DateTime join in SQL BigQuery [closed]

Question

I have 2 tables in BQ; one of them includes a geography column PointWKT (gps POINT) and second table includes a geography column BoundaryWKT (POLYGONS & MULTIPOLYGONS). Both tables also include DateTime column.

I want to do a spatial join so each PointWKT will be assigned to corresponding BoundaryWKT. Until today, i was using GeoPandas but now i have to switch to SQL.

Is there any way in SQL BigQuery that would substitute geopandas .sjoin like:

join_df = gpd.sjoin(table1, table2, how="inner", op='within')

ANSWER to this question:

SELECT * FROM table1, table2 WHERE ST_Contains (table1.geom, table2.geom)

Another question is, if it is possible to include another condition for joining?

Besides the spatial join from geography columns i also need to include join on DateTime column from both tables.

Is this even possible?

Answer 1

This is definitely possible, but the performance might vary depending on cardinality of various intersections.

You can include various conditions in WHERE clause, including condition on DateTime.

Say you can do something like

SELECT * FROM table1, table2 WHERE ST_Contains (table1.geom, table2.geom) 
  AND (table1.datetime BETWEEN DATETIME_SUB(table2.datetime, INTERVAL 10 MINUTE)
                           AND DATETIME_ADD(table2.datetime, INTERVAL 10 MINUTE))