As of January 1, 2020 this library no longer supports Python 2 on the latest released version. Library versions released prior to that date will continue to be available. For more information please visit Python 2 support on Google Cloud.

Working with Geographic data

BigQuery provides a GEOGRAPHY data type for working with geographic data, including:

  • Points,

  • Linestrings,

  • Polygons, and

  • Collections of points, linestrings, and polygons.

Geographic data uses the WGS84 coordinate system.

To define a geography column, use the GEOGRAPHY data type imported from the sqlalchemy_bigquery module:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String
from sqlalchemy_bigquery import GEOGRAPHY

Base = declarative_base()

class Lake(Base):
    __tablename__ = "lakes"

    name = Column(String, primary_key=True)
    geog = Column(GEOGRAPHY)

BigQuery has a variety of SQL geographic functions for working with geographic data. Among these are functions for converting between SQL geometry objects and standard text (WKT) and binary (WKB) representations.

Geography data is typically represented in Python as text strings in WKT format or as WKB objects, which contain binary data in WKB format. Querying geographic data returns WKB objects and WKB objects may be used in queries. When calling spatial functions that expect geographic arguments, text arguments are automatically coerced to geography.

Inserting data

When inserting geography data, you can pass WKT strings, WKT objects, or WKB objects:

from sqlalchemy.orm import sessionmaker
from sqlalchemy_bigquery import WKT

Session = sessionmaker(bind=engine)
session = Session()

lake = Lake(name="Majeur", geog="POLYGON((0 0,1 0,1 1,0 1,0 0))")
lake2 = Lake(name="Garde", geog=WKT("POLYGON((1 0,3 0,3 2,1 2,1 0))"))
b = WKT("POLYGON((3 0,6 0,6 3,3 3,3 0))").wkb
lake3 = Lake(name="Orta", geog=b)

session.add_all((lake, lake2, lake3))

Note that in the lake3 example, we got a WKB object by creating a WKT object and getting its wkb property. Normally, we’d get WKB objects as results of previous queries.


When performing spacial queries, and geography objects are expected, you can to pass WKB or WKT objects:

from sqlalchemy import func

lakes_touching_lake2 = list(
    session.query(Lake).filter(func.ST_Touches(Lake.geog, lake2.geog))

In this example, we passed the geog attribute of lake2, which is a WKB object.

Or you can pass strings in WKT format:

lakes_containing = list(
    session.query(Lake).filter(func.ST_Contains(Lake.geog, "POINT(4 1)"))

Installing geography support

To get geography support, you need to install sqlalchemy-bigquery with the geography extra, or separately install GeoAlchemy2 and shapely.

pip install 'sqlalchemy-bigquery[geography]'