It would be useful if geoalchemy would support the PostGIS geography type, which allows for correct distance / intersection / etc. calculations on a sphere such as the earth.

functions._within_distance causes an exception when used within a has. E.g.:

q = session.query(Model) \
           .filter(Model.ue.has(
                       functions._within_distance(UE.geom, 'POINT(1 1)', 5)))

Hi, I've stumbled across a weird problem where SQLAlchemy or GeoAlchemy generates invalid SQL for PostGIS. It appears related to GeoAlchemy, since updates work for columns that are not geometries.

I have this small example:

from geoalchemy import *
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, mapper, relationship

class Geom(object):
    def __init__(self, name, wkt):
        self.name = name
        self.geometry = WKTSpatialElement(wkt, srid=3006)

def setup(connection_string):
    engine = create_engine(connection_string, echo=True)
    metadata = MetaData(engine)

    geometry_table = Table('Geom', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', String),
        GeometryExtensionColumn('geometry', Geometry(srid=3006)),
    )

    mapper(Geom, geometry_table)
    GeometryDDL(geometry_table)

    metadata.create_all()
    return sessionmaker(bind=engine)

if __name__ == '__main__':
    from sys import argv
    session = setup(argv[1])

    # Create row
    s = session()
    s.add(Geom('One', 'POINT(0 0)'))
    s.commit()

    # Update name of row
    s = session()
    geoms = s.query(Geom)
    geoms[0].name = 'Two'
    s.commit()

    # Update geometry of row
    s = session()
    geoms = s.query(Geom)
    geoms[0].geometry = WKTSpatialElement('POINT(1 1)', srid=3006)
    s.commit()

Running this creates a new row, updates the name of the row, but fails to update the geometry (the last session/transaction).

The problem is that the issued UPDATE statement includes the table name in the SET part, while Postgresql's documentation explicitly says you should not:

Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid. (http://www.postgresql.org/docs/9.1/static/sql-update.html)

A log when running shows the problem quite clearly:

2013-03-13 11:50:50,614 INFO sqlalchemy.engine.base.Engine select version()
2013-03-13 11:50:50,614 INFO sqlalchemy.engine.base.Engine {}
2013-03-13 11:50:50,617 INFO sqlalchemy.engine.base.Engine select current_schema()
2013-03-13 11:50:50,617 INFO sqlalchemy.engine.base.Engine {}
2013-03-13 11:50:50,621 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
2013-03-13 11:50:50,621 INFO sqlalchemy.engine.base.Engine {'name': u'Geom'}
2013-03-13 11:50:50,630 INFO sqlalchemy.engine.base.Engine
CREATE TABLE "Geom" (
        id SERIAL NOT NULL,
        name VARCHAR,
        PRIMARY KEY (id)
)


2013-03-13 11:50:50,630 INFO sqlalchemy.engine.base.Engine {}
2013-03-13 11:50:50,710 INFO sqlalchemy.engine.base.Engine COMMIT
2013-03-13 11:50:50,727 INFO sqlalchemy.engine.base.Engine SELECT AddGeometryColumn(%(AddGeometryColumn_2)s, %(AddGeometryColumn_3)s, %(AddGeometryColumn_4)s, %(AddGeometryColumn_5)s, %(AddGeometryCol
umn_6)s, %(AddGeometryColumn_7)s) AS "AddGeometryColumn_1"
2013-03-13 11:50:50,729 INFO sqlalchemy.engine.base.Engine {'AddGeometryColumn_3': 'Geom', 'AddGeometryColumn_2': 'public', 'AddGeometryColumn_5': 3006, 'AddGeometryColumn_4': 'geometry', 'AddGeometry
Column_7': 2, 'AddGeometryColumn_6': 'GEOMETRY'}
2013-03-13 11:50:50,736 INFO sqlalchemy.engine.base.Engine COMMIT
2013-03-13 11:50:50,760 INFO sqlalchemy.engine.base.Engine CREATE INDEX "idx_Geom_geometry" ON "public"."Geom" USING GIST (geometry)
2013-03-13 11:50:50,760 INFO sqlalchemy.engine.base.Engine {}
2013-03-13 11:50:50,766 INFO sqlalchemy.engine.base.Engine COMMIT
2013-03-13 11:50:50,779 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-03-13 11:50:50,779 INFO sqlalchemy.engine.base.Engine INSERT INTO "Geom" (name, geometry) VALUES (%(name)s, GeomFromText(%(GeomFromText_1)s, %(GeomFromText_2)s)) RETURNING "Geom".id
2013-03-13 11:50:50,780 INFO sqlalchemy.engine.base.Engine {'GeomFromText_2': 3006, 'name': 'One', 'GeomFromText_1': 'POINT(0 0)'}
2013-03-13 11:50:50,783 INFO sqlalchemy.engine.base.Engine COMMIT
2013-03-13 11:50:50,795 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-03-13 11:50:50,796 INFO sqlalchemy.engine.base.Engine SELECT "Geom".id AS "Geom_id", "Geom".name AS "Geom_name", ST_AsBinary("Geom".geometry) AS "Geom_geometry"
FROM "Geom"
 LIMIT %(param_1)s
2013-03-13 11:50:50,796 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
2013-03-13 11:50:50,799 INFO sqlalchemy.engine.base.Engine UPDATE "Geom" SET name=%(name)s WHERE "Geom".id = %(Geom_id)s
2013-03-13 11:50:50,799 INFO sqlalchemy.engine.base.Engine {'name': 'Two', 'Geom_id': 1}
2013-03-13 11:50:50,801 INFO sqlalchemy.engine.base.Engine COMMIT
2013-03-13 11:50:50,811 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-03-13 11:50:50,812 INFO sqlalchemy.engine.base.Engine SELECT "Geom".id AS "Geom_id", "Geom".name AS "Geom_name", ST_AsBinary("Geom".geometry) AS "Geom_geometry"
FROM "Geom"
 LIMIT %(param_1)s
2013-03-13 11:50:50,812 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
2013-03-13 11:50:50,815 INFO sqlalchemy.engine.base.Engine UPDATE "Geom" SET "Geom".geometry=GeomFromText(%(GeomFromText_1)s, %(GeomFromText_2)s) WHERE "Geom".id = %(Geom_id)s
2013-03-13 11:50:50,815 INFO sqlalchemy.engine.base.Engine {'GeomFromText_1': 'POINT(1 1)', 'GeomFromText_2': 3006, 'Geom_id': 1}
2013-03-13 11:50:50,816 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "test.py", line 42, in <module>
    s.commit()
  File "D:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 693, in commit
    self.transaction.commit()
  File "D:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 332, in commit
    self._prepare_impl()
  File "D:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 311, in _prepare_impl
    self.session.flush()
  File "D:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 1788, in flush
    self._flush(objects)
  File "D:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 1870, in _flush
    flush_context.execute()
  File "D:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 372, in execute
    rec.execute(self)
  File "D:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 525, in execute
    uow
  File "D:\Python27\lib\site-packages\sqlalchemy\orm\persistence.py", line 58, in save_obj
    mapper, table, update)
  File "D:\Python27\lib\site-packages\sqlalchemy\orm\persistence.py", line 488, in _emit_update_statements
    params)
  File "D:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 664, in execute
    params)
  File "D:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 764, in _execute_clauseelement
    compiled_sql, distilled_params
  File "D:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 878, in _execute_context
    context)
  File "D:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 871, in _execute_context
    context)
  File "D:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 320, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "Geom" of relation "Geom" does not exist
LINE 1: UPDATE "Geom" SET "Geom".geometry=GeomFromText('POINT(1 1)',...
                          ^
 'UPDATE "Geom" SET "Geom".geometry=GeomFromText(%(GeomFromText_1)s, %(GeomFromText_2)s) WHERE "Geom".id = %(Geom_id)s' {'GeomFromText_1': 'POINT(1 1)', 'GeomFromText_2': 3006, 'Geom_id': 1}

Is there something I'm missing? Why is GeoAlchemy generating invalid SQL?

I'm using GeoAlchemy 0.7.1 and SQLAlchemy 0.8.0b2, and running Python 2.7. I'm using PostgreSQL 9.1.8.

Geoalchemy2 doesn't work well with Alembic's autogenerated migration because it creates spatial_indexes by default and doesn't add those indexes to the SQLAlchemy schema object:

https://github.com/geoalchemy/geoalchemy2/blob/67da25751026ab86fa288777261f2220db0ac0c6/geoalchemy2/types.py#L67-L96

It isn't so much the default creation of them as it is that it creates them by executing raw SQL: https://github.com/geoalchemy/geoalchemy2/blob/67da25751026ab86fa288777261f2220db0ac0c6/geoalchemy2/init.py#L88-L93

And doesn't add the index to the SQLAlchemy schema metadata object. Alembic's autogeneration of migrations depends on the presence of indexes in the schema object so that it can diff the model/column code against the db schema:

https://github.com/zzzeek/alembic/blob/6d69285f3833b1bdb1d0005756ffcdcad97502f1/alembic/autogenerate/compare.py#L1 https://github.com/zzzeek/alembic/blob/6d69285f3833b1bdb1d0005756ffcdcad97502f1/alembic/autogenerate/compare.py#L147

Because of this, all of Alembic's auto-generated migrations will contain a migration to remove all of the spatial_indexes that were automatically created by the models with Geometry columns :(

As a workaround we are declaring Geometry columns with spatial_index=False and then creating the index via a __table_args__ function:

footprint_geometry = Column(
    # Alembic doesn't know about the autogenerated spatial_index.
    # Set it false here and create it in __table_args__
    Geometry(geometry_type='GEOMETRY', srid=SRID, spatial_index=False),
    doc='The footprint of the structure'
)

@declared_attr
def __table_args__(cls):
    return (Index(
        'idx_{}_footprint_geometry'.format(cls.__tablename__), 'footprint_geometry', postgres_using='gist'
    ))

When pydoc is reflecting into the a SpatialElement instance it will ask for internal attributes such as name which has non-callable values. This fix makes sure the attributes are callable before returning.

I've added a feature to set GeoAlchemy to use WKT internally with PostGIS. This allows use of coords, geom_type and geom_wkt without additional queries to the database.

The feature is enabled by adding the option wkt_internal=True in the GeometryColumn definition. e.g.

lake_geom = GeometryColumn(Geometry(2), comparator=PGComparator, wkt_internal=True)

When using non PostGIS dialect, during a query compile a warning is raised (SAWarning) and defaults back to WKB, so shouldn't break compatibility with other dialects.

I've changed the PostGIS tests so the the Lake mapper uses wkt_internal=True, added a test for geom_type and geom_wkt(as part of test_wkt).

I've also added a bit to the documentation.

sqlalchemy.sql.expression.Function does not exist in the most recent versions of SQLAlchemy. Thus attempting to import them in geoalchemy/functions.py throws an ImportError.

I'm using GeoAlchemy 0.6 along with SQLAlchemy 0.6.2 under Python 2.6, I'm using PostgreSQL 9.1 + PostGIS 2.0 (beta), while generating the schema I generally get an error about GIST Indexes:

operator class "gist_geometry_ops" does not accept data type geography

In recent versions of PostGIS gist_geometry_ops is being deprecated and no longer needed to be added explicitly, my workaround was to subclass GeometryDDL and change on _ _ call _ _ the line:

bind.execute("CREATE INDEX idx_%s_%s ON %s USING GIST (%s gist_geometry_ops)" % (table.name, c.name, table.name, c.name))

to simply:

bind.execute("CREATE INDEX idx_%s_%s ON %s USING GIST (%s)" % (table.name, c.name, table.name, c.name))

It would be good if we first tried to create the index and if we get a ProgrammingError exception try again without the gist_geometry_ops

Hello, I need to use the spatial database in a way that is possible to use either a Geographic coordinate or a Geometric coordinate. When I create the geometry with SRID, works fine. But without SRID the geometry object is never stored, even despite the WKTSpatialElement object is created successfully without an SRID number. There is any way to workaround that?
I did instance WKTSpatialElement with srid=None

Travis only tests against the PostGIS 1.5 database and the generic tests so far. MySQL commands are included but failed with The used table type doesn't support SPATIAL indexes. PostGIS 2.x can be enabled one the remaining error is fixed.

• Added functionality to check geometry is Null using '==' syntax
• Added '!=' operator to call 'NOT ST_Equal' and 'IS NOT NULL' I've added tests for these two additions, but unfortunately for postgis only, as that is all I have available to test with currently.

Idea came from google groups question from Will Furnass and work around from Tobias. https://groups.google.com/d/topic/geoalchemy/dc1yYfd5zLg/discussion

I'm using Oracle11g and have model like this:

from sqlalchemy.ext.declarative import declarative_base from geoalchemy import * from sqlalchemy import * from sqlalchemy.orm import * from geoalchemy.oracle import ORACLE_NULL_GEOMETRY

engine = create_engine("oracle://THREED:THREED@localhost:1521/orcl", echo=False, case_sensitive=False) Session = sessionmaker(bind=engine) session = Session()

metadata = MetaData(engine) Base = declarative_base(metadata=metadata)

class DT(Base): tablename = "DT" OBJECTID = Column(NUMBER,primary_key=True) SHAPE = GeometryColumn(Point(3))

when I insert data like this: aa = DT(OBJECTID=1,SHAPE=WKTSpatialElement("point(1 1 0)",srid=4326)) session.add(aa) session.commit()

error like: u'ORA-29532: Java 调用被未捕获的 Java 异常错误终止: java.sql.SQLException: -2

if I use pl/sql insert a 3D data,I use geoalchemy to query,like this: aa = session.query(DT).all() print aa

the error is: DatabaseError: (DatabaseError) ORA-13199: 3D geometries are not supported by geometry WKB/WKT generation.

Could it be that geoalchemy does not support 3D geometries? I need help,thank you!

I made changes to support Python3. Since it's not backward compatible with python2, I have increased the version no. and sqlalchemy dependency version.

This is to resolve #41

Hi Does geoalchemy work with python 3.x ? After i installed it, i've got this error on import:

>>> from geoalchemy import *
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/mehdi/Programming/Python/virtualenvs/measurment-app/lib/python3.4/site-packages/GeoAlchemy-0.7.3.dev0-py3.4.egg/geoalchemy/__init__.py", line 2, in <module>
    from geoalchemy.base import *
  File "/home/mehdi/Programming/Python/virtualenvs/measurment-app/lib/python3.4/site-packages/GeoAlchemy-0.7.3.dev0-py3.4.egg/geoalchemy/base.py", line 7, in <module>
    from utils import from_wkt
ImportError: No module named 'utils'

I'm working with an oracle database, so i think geoalchemy is the only package that i can use. isn't it? Thanks.

I pulled chokoswitch's branch and noticed that I was running into another issue with compatibility. I was getting a TypeError when instantiating ColumnCollection. It turns out in Sqlalchemy 0.9.4, passing in *args into ColumnCollection was removed (https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_4/lib/sqlalchemy/sql/base.py#L438). To get around this, I made the collection first and added the columns to it afterwards.

I think this is a problem only around 0.9.4. Version 1.0.0 and version 0.9.7 both added back the *args. That said, this should work for all version as add() has always been part of the public interface.