GeoAlchemy2 Documentation

GeoAlchemy2 allows you to perform spatial relationship queries (like ST_Contains, ST_Intersects, etc.) using two patterns:

1. Standard SQLAlchemy func pattern: func.ST_Contains(column, geometry).
2. GeoAlchemy2 concise pattern: column.ST_Contains(geometry). This pattern applies the function directly to the column object and is more readable.

Note that GeoAlchemy2 operator functions (like .intersects()) do not include the ST_ prefix, whereas the standard PostGIS functions do.

from sqlalchemy import select, func

# Using func.ST_Contains
s = select(lake_table).where(
    func.ST_Contains(lake_table.c.geom, 'POINT(4 1)')
)

# Using GeoAlchemy2 concise syntax
s = select(lake_table).where(
    lake_table.c.geom.ST_Contains('POINT(4 1)')
)

# Using an operator function (no ST_ prefix)
s = select(lake_table).where(
    lake_table.c.geom.intersects('LINESTRING(2 1,4 1)')
)

GeoAlchemy 2 replaces several legacy spatial element classes with two primary classes: WKTElement and WKBElement.

WKTElement

Use geoalchemy2.elements.WKTElement in expressions when you need to specify a geometry using a Well-Known Text (WKT) string with a specific SRID. If no SRID is required, you can pass a WKT string directly to the spatial function.

Example with SRID:

from geoalchemy2.elements import WKTElement
Lake.geom.ST_Touches(WKTElement('POINT(1 1)', srid=4326))

Example without SRID:

Lake.geom.ST_Touches('POINT(1 1)')

WKBElement

geoalchemy2.elements.WKBElement is the type used for geometry values read from the database. When you load an object from the database, its geometry attributes will be instances of WKBElement. This replaces the legacy PersistentSpatialElement and PGPersistentSpatialElement classes.

from geoalchemy2.elements import WKTElement

# Using WKTElement with a specific SRID
Lake.geom.ST_Touches(WKTElement('POINT(1 1)', srid=4326))

# Using a raw WKT string when SRID is not needed
Lake.geom.ST_Touches('POINT(1 1)')

You can define spatial relationships (e.g., finding all 'Treasures' contained within a 'Lake') using SQLAlchemy's relationship() function.

Because spatial relationships rely on SQL functions (like ST_Contains) rather than simple equality, you must use the .as_comparison(1, 2) method on the function in the primaryjoin argument. This is required for SQLAlchemy to correctly handle the function in a join condition.

Key parameters for spatial relationships:

• primaryjoin: The SQL condition for the join, using func.ST_... and .as_comparison().
• viewonly=True: Recommended for spatial relationships to ensure they are used for loading data and not for attempting to persist changes via the relationship.
• backref: Provides the inverse relationship on the related model.

from sqlalchemy.orm import relationship, backref
from sqlalchemy import func

class Lake(Base):
    __tablename__ = 'lake'
    id = Column(Integer, primary_key=True)
    geom = Column(Geometry('POLYGON'))
    
    treasures = relationship(
        'Treasure',
        primaryjoin='func.ST_Contains(foreign(Lake.geom), Treasure.geom).as_comparison(1, 2)',
        backref=backref('lake', uselist=False),
        viewonly=True,
        uselist=True,
    )

To ensure Alembic's --autogenerate feature works correctly with GeoAlchemy 2 (handling imports, spatial operations, and ignoring internal spatial tables), you should configure the env.py file with geoalchemy2.alembic_helpers.

Pass the following three functions to context.configure() in both run_migrations_offline() and run_migrations_online():

1. include_object=alembic_helpers.include_object: Ignores internal tables managed by spatial extensions.
2. process_revision_directives=alembic_helpers.writer: Adds specific spatial operations (like create_geospatial_table, add_geospatial_column, etc.) to the migration scripts.
3. render_item=alembic_helpers.render_item: Automatically adds geoalchemy2 imports to the generated migration scripts.

from geoalchemy2 import alembic_helpers

# In run_migrations_offline and run_migrations_online:
def run_migrations_online():
    # ...
    context.configure(
        # ...
        include_object=alembic_helpers.include_object,
        process_revision_directives=alembic_helpers.writer,
        render_item=alembic_helpers.render_item,
    )

If you use custom SQLAlchemy types (e.g., TheCustomType defined in my_package.custom_types), Alembic's autogenerate won't automatically import them. You can extend alembic_helpers.render_item to handle this.

Create a custom render_item function that calls the default alembic_helpers.render_item first, then checks for your custom type to inject the necessary import statement into autogen_context.imports.

from geoalchemy2 import alembic_helpers
from my_package.custom_types import TheCustomType

def render_item(obj_type, obj, autogen_context):
    # Try default spatial rendering first
    spatial_type = alembic_helpers.render_item(obj_type, obj, autogen_context)
    if spatial_type:
        return spatial_type

    # Handle custom type imports
    if obj_type == 'type' and isinstance(obj, TheCustomType):
        import_name = obj.__class__.__name__
        autogen_context.imports.add(f"from my_package.custom_types import {import_name}")
        return "%r" % obj

    return False

# Use this in context.configure(render_item=render_item, ...)