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))
session.commit()
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.
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]'
