Skip to content

Latest commit

 

History

History
87 lines (66 loc) · 2.96 KB

geography.rst

File metadata and controls

87 lines (66 loc) · 2.96 KB

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:

.. literalinclude:: samples/snippets/geography.py
   :language: python
   :dedent: 4
   :start-after: [START bigquery_sqlalchemy_create_table_with_geography]
   :end-before: [END bigquery_sqlalchemy_create_table_with_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:

.. literalinclude:: samples/snippets/geography.py
   :language: python
   :dedent: 4
   :start-after: [START bigquery_sqlalchemy_insert_geography]
   :end-before: [END bigquery_sqlalchemy_insert_geography]

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:

.. literalinclude:: samples/snippets/geography.py
   :language: python
   :dedent: 4
   :start-after: [START bigquery_sqlalchemy_query_geography_wkb]
   :end-before: [END bigquery_sqlalchemy_query_geography_wkb]

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

Or you can pass strings in WKT format:

.. literalinclude:: samples/snippets/geography.py
   :language: python
   :dedent: 4
   :start-after: [START bigquery_sqlalchemy_query_geography_text]
   :end-before: [END bigquery_sqlalchemy_query_geography_text]

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]'