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? Thanks a lot!

  • 2
    One Question only please, you can ask two separate questions.
    – Mapperz
    Jun 17 '20 at 12:56

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))
  • Thanks for sharing this! it doesn't exactly solve my problem but you definitely showed a way to go!
    – Kuki
    Jun 18 '20 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.