531The Shapely Alchemist

Following the example at byCycle.org I've figured out how to use Shapely geometries with SQLAlchemy and PostGIS. Here's the custom type:

from sqlalchemy import types
from shapely.wkb import loads

class Geometry(types.TypeEngine):

    def __init__(self, srid, geom_type, dims=2):
        super(Geometry, self).__init__()
        self.srid = srid
        self.geom_type = geom_type
        self.dims = dims

    def get_col_spec(self):
        return 'GEOMETRY()'

    def convert_bind_param(self, value, engine):
        if value is None:
            return None
        else:
            return "SRID=%s;%s" \
            % (self.srid, value.wkb.encode('hex'))

    def convert_result_value(self, value, engine):
        if value is None:
            return None
        else:
            return loads(value.decode('hex'))

Example:

>>> db = create_engine("postgres://localhost/the_db")
>>> metadata = MetaData(db)
>>> places = Table("places", metadata,
...     Column("the_geom", Geometry(4326, "POINT"))
...     )
>>> result = places.select().execute()
>>> row = result.fetchone()
>>> row
(<shapely.geometry.point.Point object at 0xb771490c>,)
>>> row.keys()
['the_geom']
>>> row.the_geom
<shapely.geometry.point.Point object at 0xb771482c>
>>> row.the_geom.wkt
'POINT (-106.0000000000000000 40.0000000000000000)'
>>> row.the_geom.x
-106.0
>>> row.the_geom.y
40.0