geoalchemy / geoalchemy2

Geospatial extension to SQLAlchemy

GeoAlchemy

WARNING: This package is deprecated and was replaced by GeoAlchemy2 .

GIS Support for SQLAlchemy.

Introduction

GeoAlchemy is an extension of SQLAlchemy. It provides support for Geospatial data types at the ORM layer using SQLAlchemy. It aims to support spatial operations and relations specified by the Open Geospatial Consortium (OGC). The project started under Google Summer of Code Program under the mentorship of Mark Ramm-Christensen.

Requirements

Requires SQLAlchemy > 0.6. Supported on Python 2.5 and Python 2.6. Should also work with Python 2.4 but has not been tested. It also requires a supported spatial database.

Supported Spatial Databases

At present PostGIS, Spatialite, MySQL, Oracle and MS SQL Server 2008 are supported.

Support

GeoAlchemy is at an early stage of development. Its mailing list is available on Google Groups. The source code can be found on GitHub. Also, feel free to email the author directly to send bugreports, feature requests, patches, etc.

Installation

To install type as usual:

$ easy_install GeoAlchemy

Or, download the package, change into geoalchemy dir and type:

$ python setup.py install

Documentation

Documentation is available online at http://geoalchemy.org. You can also generate full documentation using sphinx by doing make html in the doc dir and pointing the browser to doc/_build/index.html.

Package Contents

geoalchemy/

Source code of the project.

geoalchemy/tests/

Unittests for GeoAlchemy.

doc/

Documentation source.

examples/

A few examples demonstrating usage.

License

GeoAlchemy is released under the MIT License.

Contributors

The contributors to this project (in alphabetical order are):

• Eric Lemoine
• Frank Broniewski
• Mark Hall
• Michael Bayer
• Mike Gilligan
• Sanjiv Singh
• Stefano Costa
• Tobias Sauerwein

geoalchemy2's Issues

Transformations of WKT/WKB to other types

Is there any plan to support other conversions –other than using shapely– from WKT/WKB to for instance python natives, numpy structures or anything else? Or interest in having a codebase to do so?

Cheers,
~lweberk

minor: released tar is missing COPYING.rst

There's no license file inside the published tar
Which add unnecessary headache for packagers :-)

Missing ST_Project

The Geography - function ST_Project seems not be implemented yet.

http://postgis.refractions.net/docs/ST_Project.html

_SpatialElement assumes bytestrings no unicode

So the following code fails:

wkt = WKTElement('POLYGON((1 2,3 4))')
print(wkt)

while this is accepted:

wkt = WKTElement(b'POLYGON((1 2,3 4))')
print(wkt)

I feel that as it is possible to pass unicode strings to WKTElement it should be able to print them too

Small typo issue in TEST.rst

gid has to be replaced by gis

Inserts with shape.from_geom or WKB?

Hi,

Thanks for the great extension to SQLAlchemy, just starting to get my feet wet!

It's unclear to me if its possible to insert a (Shapely) geometry into a table using shape.from_geom, or WKB for that matter using the Core. I've tried something like this,

from shapely import geometry
geom = geometry.box(0, 0, 1, 1)

# Do some stuff...

test_table = Table('test', metadata, Column('extent', Geometry('POLYGON')))
ins = test_table.insert()
connection.execute(ins, extent=shape.from_shape(geom))

and I get a ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'WKBElement' [SQL: 'INSERT INTO test_table (extent) VALUES (ST_GeomFromEWKT(%(extent)s))']. I'm guessing this is because from_shape returns a WKBElement rather than a WKTElement.

Using this instead works,

connection.execute(ins, extent=geom.wkt)

as well as constructing a WKTElement directly and using that.

Am I just doing it wrong or is this expected behavior?

Thanks!

Using the <-> Operator

Hi

Im trying to generate this query in SQLAlchemy for performing fast distance ordering:

SELECT ST_Transform(geom, 27700) <-> ST_Transform(ST_SetSRID(ST_MakePoint(-2.21335, 53.5628), 4326), 27700) AS distance
FROM foo
ORDER BY distance
LIMIT 10

So the code should be:

distance = (ST_Transform(Foo.geom, 27000).op('<->')(ST_Transform(func.ST_SetSRID(func.ST_MakePoint(-2.21335, 53.5628), 4326), 27700))).label('distance')
db.session.query(Foo).with_entities(distance).order_by(distance).limit(10)

However the generated SQL is:

SELECT ST_AsBinary(ST_Transform(foo.geom, 27000) <-> ST_Transform(ST_SetSRID(ST_MakePoint(-2.21335, 53.5628), 4326), 27700)) AS distance
FROM foo 
ORDER BY distance
LIMIT 10

So it gets wrapped in ST_AsBinary which is incorrect and breaks the query.

Whats the best way to achieve the desired query?

Thanks,

Chris

Escape unsafe variables

Hi,
I couldn't find in the docs how to properly escape unsafe variables, e.g. while creating a point:

p = Point(geom='POINT( %s %s )' % (lng, lat))
session.add(p)

when I can't trust lng and lat. Is there a built-in mechanism?

If I missed anything in the docs or project issues, I'd be glad to be pointed at it. Thanks!

Sql server support ?

Any plan to support sql server in future ? If yes, by when ? Our project needs it.

Thanks

Can't create new table with Geometry, "Error type modeified for geometry not allowed"

I have to say I'm impressed with GeoAlchemy2. Unfortunately, when I try and create a table with a geometry column I get the following error:

"sqlalchemy.exc.ProgrammingError: (ProgrammingError) type modifier is not allowed for type "geometry"
LINE 4: the_geom geometry(GEOMETRY,-1)"

Any ideas how I can fix this?

I'm using PostGIS 1.5 (which I guess might be the problem?),PostgreSQL 9.1 and GeoAlchemy2 0.2

Thanks.

Kl

M

Update geoalchemy.org

Right now geoalchemy.org is the Sphinx doc of GeoAlchemy 0.6. We need to change that.

Can't create ST_XMax

Hello!
I'm trying to use the PostGIS function ST_XMax. I'm using the GenericFunction class as a base for my custom class

class ST_XMax(GenericFunction):
    name = 'ST_XMax'
    type = float

but when I use it I have this error

2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 997, in scalar
2014-09-17 23:54:20-0300 [-]     clause, params=params, mapper=mapper, bind=bind, **kw).scalar()
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 991, in execute
2014-09-17 23:54:20-0300 [-]     bind, close_with_result=True).execute(clause, params or {})
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute
2014-09-17 23:54:20-0300 [-]     return meth(self, multiparams, params)
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/sql/functions.py", line 66, in _execute_on_connection
2014-09-17 23:54:20-0300 [-]     return connection._execute_function(self, multiparams, params)
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 735, in _execute_function
2014-09-17 23:54:20-0300 [-]     multiparams, params)
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 819, in _execute_clauseelement
2014-09-17 23:54:20-0300 [-]     inline=len(distilled_params) > 1)
2014-09-17 23:54:20-0300 [-]   File "<string>", line 1, in <lambda>
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 492, in compile
2014-09-17 23:54:20-0300 [-]     return self._compiler(dialect, bind=bind, **kw)
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 498, in _compiler
2014-09-17 23:54:20-0300 [-]     return dialect.statement_compiler(dialect, self, **kw)
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 395, in __init__
2014-09-17 23:54:20-0300 [-]     Compiled.__init__(self, dialect, statement, **kwargs)
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 199, in __init__
2014-09-17 23:54:20-0300 [-]     self.string = self.process(self.statement, **compile_kwargs)
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 222, in process
2014-09-17 23:54:20-0300 [-]     return obj._compiler_dispatch(self, **kwargs)
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 80, in _compiler_dispatch
2014-09-17 23:54:20-0300 [-]     return meth(self, **kw)
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1521, in visit_select
2014-09-17 23:54:20-0300 [-]     for name, column in select._columns_plus_names
2014-09-17 23:54:20-0300 [-]   File "/Users/ruio/anaconda/envs/maestro/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1212, in _label_select_column
2014-09-17 23:54:20-0300 [-]     if column.type._has_column_expression and \
2014-09-17 23:54:20-0300 [-] AttributeError: 'float' object has no attribute '_has_column_expression'

Every other function I created with the type Geometry is working, but for what I understand, ST_XMax should return a float.
What do you think?

SRID is not preserved qhen fetching data from database

If a table is created with a srid-aware geometry, the inserted rows' geometry is lost when fetching.

I include a reference to a patch that solves this issue:
meteogrid@9f50ffa

I wonder whether it would be possible to use EWKB and EWKT all over the library to make all geometries srid aware, since most GIS development ends up being srid aware (remember unicode, time zones, etc.).

Add to documentation an example on how to use reflection

Hi,

i'm trying to use GeoAlchemy2 and saw in docs that DB reflection is supported. But there was no example on that :( The only thing found is tests/functional.py but it gave me no clue.

The following code snippet:

from sqlalchemy import create_engine, MetaData, Table
sql_uri = "postgresql+psycopg2://myuser:[email protected]/mydbname" 
engine  = create_engine(sql_uri)
meta.reflect(bind=engine)

shows that geometry is not recognized:

/usr/lib/python2.7/site-packages/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/dialects/postgresql/base.py:2454: SAWarning: Did not recognize type 'geometry' of column 'geometry'
/usr/lib/python2.7/site-packages/SQLAlchemy-1.0.8-py2.7-linux-x86_64.egg/sqlalchemy/dialects/postgresql/base.py:2454: SAWarning: Did not recognize type 'geometry' of column 'point'
...

which is fully clear, because one has to provide somehow the stuff from GeoAlchemy2.

You may want to add in docs that simple import like this is the only thing needed for the above SQLAlchemy reflection code snippet:

from geoalchemy2 import Geometry, Geography

ORM Style Raster Model Creation

From the docs and code, it is not clear to me if and how it is possible to have a model with a raster type created.

So far I tried:

class SourceMap(db.Model):
    id              = db.Column(db.Integer, primary_key=True)
    name            = db.Column(db.String(128), nullable=False)
    source_link     = db.Column(db.String(512), nullable=False)
    georef_raster   = db.Column(Raster)

...which does create the georef_raster column with type raster, but QGIS for example does not recognize it as a geometry column. Are there any tutorials out there?

Q: what are the recommended libraries for GIS-transformations to be used on client-side with GeoAlchemy?

Hi

Q: what are the recommended libraries for GIS-transformations and calculations to be used not on DB-side via SQL-queries, but on client-side with Python and GeoAlchemy2?

I went through the docs several times and I saw hints about shaply, but it doesn't seem to support, e.g. very important ST_Distance_Sphere.

Support Function ST_GeomFromGeoJSON

ST_GeomFromGeoJSON definition

This function would be nice as it's the inverse of ST_AsGeoJSON which is already included.

Automap and geometry objects

Hello all,

I have been giving GeoAlchemy2 a try because I need to work on some existing PostGIS data. I would like to use the automap feature from SqlAlchemy for the relevant tables:

from sqlalchemy import *

(connect...)

Base = automap_base()
Base.prepare(engine, reflect=True)

Everything seems to work, tables are accessible from Base.classes. The problem seems to be that the geometric objects are not recognized correctly, when I query the `geom' column of an element I only get a string looking like this:

0101000020E61000007C31BF8EE3432640D80C118843134740

I would prefer the actual Polygon/LineString/...
Am I doing something wrong or does the automap feature simply not work correctly with spatial data?

PostGIS Raster support

Is it possible to include support for PostGIS Raster in geoalchemy2? I think the two most important things for that would be a Raster column type and the automatic creation of the necessary ST_ConvexHull index.

Supporting spatialite

Hi, do you plan to make geoalchemy2 support spatialite?
Currently, geoalchemy (1) supports it in theory, but not on recent versions of SQLAlchemy, because of geoalchemy/geoalchemy#38 .

Replace shapely with geomet

shapely is a big dependency since it requires the GEOS library. Only WKT/WKB functions are used from it, so I would recommend switching to use geomet since it's a pure Python solution.

Use TravisCI to publish PyPI packages

I'm doing this on my own projects too, and it works pretty well.

see http://www.agmweb.ca/2015-05-08-travis-pypi

@elemoine since the package on PyPI is registered to your account you would have to install this.

Creating Geomtry columns for tables in other schemas than public doesn't work.

When I try to create a table with table_args = {'schema':'someschema'}

I get an error from PostGIS:
InternalError: (InternalError) Table '' does not occur in the search_path
CONTEXT: SQL statement "SELECT AddGeometryColumn('','',$1,$2,$3,$4,$5)"
PL/pgSQL function "addgeometrycolumn" line 5 at SQL statement
'SELECT AddGeometryColumn(%(AddGeometryColumn_2)s, %(AddGeometryColumn_3)s, %(AddGeometryColumn_4)s, %(AddGeometryColumn_5)s, %(AddGeometryColumn_6)s) AS "AddGeometryColumn_1"' {'AddGeometryColumn_3': 'geometrie', 'AddGeometryColumn_2': ', 'AddGeometryColumn_5': 'POINT', 'AddGeometryColumn_4': 28992, 'AddGeometryColumn_6': 3}.

I checked out the code, and see that in init.py nothing is done with the table.schema property. I have just forked the repos and am making a fix.

Issue with geoalchemy2 and sqlalchemy 1.0.6

I have some code that works fine for sqlalchemy 0.9.3 but is breaking on 1.0.6. I'm not sure if it is a geoalchemy2 issue or a sqlalchemy issue - thought I would start here.

I have a object that has an attribute defined as
geom = Column(geoalchemy2.Geometry('POLYGON'))

In the test code, I create a valid geometry for this attribute. But, then I want to test what happens if there is no geometry. So, I do the following:

image.geom = None
db.db_session.commit()

With geoalchemy2 0.2.4 and sqlalchemy 0.9.3 this works fine. But, with geoalchemy 0.2.4 and slqalchemy 1.0.6 I get the following trace:

/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/scoping.py:150: in do
return getattr(self.registry(), name)(_args, *_kwargs)
/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py:790: in commit
self.transaction.commit()
/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py:392: in commit
self._prepare_impl()
/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py:372: in _prepare_impl
self.session.flush()
/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py:2004: in flush
self._flush(objects)
/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py:2122: in _flush
transaction.rollback(_capture_exception=True)
/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py:60: in exit
compat.reraise(exc_type, exc_value, exc_tb)
/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py:2086: in _flush
flush_context.execute()
/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py:373: in execute
rec.execute(self)
/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py:532: in execute
uow
/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py:170: in save_obj
mapper, table, update)
/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py:630: in _emit_update_statements
lambda rec: (
/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py:459: in _collect_update_commands
value, state.committed_state[propkey]):

---

self = <sqlalchemy.sql.elements.BinaryExpression object at 0x56141d0>

def __bool__(self):
    if self.operator in (operator.eq, operator.ne):
        return self.operator(hash(self._orig[0]), hash(self._orig[1]))
    else:

      raise TypeError("Boolean value of this clause is not defined")

E TypeError: Boolean value of this clause is not defined

There was a similar sqlalchemy issue (https://bitbucket.org/zzzeek/sqlalchemy/issue/3402/new-stack-trace-when-combining-non-eq) that was supposedly fixed in 1.0.3

My question is:

• should I not ever be assigning geom = None?
• is there an issue with geoalchmeny2 and support for sqlalchemy 1.0?
• or does this look like a regression in sqlalchemy?

Attribute ColumnComparator not found.

A statement:
from geoalchemy.base import *

raises this exception:
AttributeError: type object 'ColumnProperty' has no attribute 'ColumnComparator'

insert different format support

Is it also possible to support different GeomFrom* instead of only ST_GeomFromEWKT when doing a session.add(entity).

Or does some has an idea how to do that?

Question about Geography-Type

Hello,

I am working with geoalchemy2 at the moment, and it works quite nicely.
But now I stumbled upon a problem for which I have not found a solution in days.

My database contains two tables (lets call them A and B) which each contain one Geography-Column with type POINT.
My goal is to walk through table A point by point and search for all points in B in the vicinity of current point A.
Using ST_DWithin works like a charm, but I need to do the same with a rectangle.
Now I planned to create a POLYGON on the fly and use ST_Intersects.

My problem is now the creation of the POLYGON-Rectangle, since it has to be located with respect to the current A.POINT

I tried something like (with more points of course to create a rectangle. This is just a shortened triangle-version):

polygon = "POLYGON(" + source.geo.ST_X() + " " + source.geo.ST_Y() + "," +
                                               source.geo.ST_X()-dX + " " + source.geo.ST_Y()-dY + "," +
                                               source.geo.ST_X() + " " + source.geo.ST_Y()) + ")"

result = query(source, target).filter(target.geo.intersects((polygon)))

but this does not work, since ST_X is not available for Geography-Type. And I don't know how to make the ST_X(geo::geometry) - cast within Geoalchemy2.

So I tried to make this one long string:

polygon = sqlalchemy.func.concat("'POLYGON(('," +
        "(ST_X(" + source.__tablename__ + ".geo::geometry)+(" + str(-dX) + ")),' '," +
        "(ST_Y(" + source.__tablename__ + ".geo::geometry)+(" + str(-dY) + ")),','," +
        ..... etc.

But now the string will created within the SELECT-statement, so ST_Intersect(geography, text) will be called which produces of course an error.
To make this work, I would need to call ST_GeogFromText with this string, but I could not figure out how to call this function explicitly from within Geoalchemy2.

I could of course just launch the whole select-statement as a string, but since i have many other filters (which are dynamically added or not added) this wouldn't work very easily.

Do you have any ideas how to solve this problem? (Maybe even with a completely different approach)

Any help would be welcomed!

Thank you,
Martin

Update Gravatar

The gravatar image of the GeoAlchemy organization should be changed to the new logo:

Error while creating tables with SQLAlchemy 0.9.4

I'm getting an error calling create_all() under SQLAlchemy 0.9.4. Looks like the __init__ to sqlalchemy.sql.expression.ColumnCollection no longer takes tables as arguments. Looks like maybe you'll have to .add() each of them in, rather than giving them to the constructor?

I mentioned this in #sqlalchemy, and they referenced this commit and this issue.

Cast Geography to Geometry type ::geometry ::geography

How to instantiate a value with casting to ::geometry or ::geography?

Example:

class Point(db.Model):
    geom = db.Column(Geometry(geometry_type = 'POINT', srid = 4326))

p1 = Point(geom = "'POINT(-33.82656 -58.44279)'::geometry")

db.session.add(p1)
db.commit()

Doing that, I gave an error.

There is way to cast geography value to geometry whit geoalchemy2?

WKTElement always gives a Geometry

When i create an element with srid=4326 i expect it to be of type Geography and not Geometry. However, It generates a ST_GeomFromText statement. Subsequent usage of this element in func.ST_distance gives unexpected results (in planar degrees) instead of in meters.

>>> from geoalchemy2 import WKTElement
>>> el = WKTElement("SRID=4326; POINT(4.1,52.0)")
>>> el
<WKTElement at 0x2326990; 'SRID=4326; POINT(4.1,52.0)'>
>>> print el.select()
SELECT ST_GeomFromText(:ST_GeomFromText_2, :ST_GeomFromText_3) AS "ST_GeomFromText_1"
>>> 

I kind of would expect WKTElement to return a geography if srid=4326. Otherwise i would expect a WKTGeographyElement with default srid=4326 and using ST_GeographyFromText.

If you like i can make a pull request to provide a WKTGeographyElement class.

How to insert raster data from an array?

I'm trying to use geoalchemy to store values in a numpy array as Raster data - I have the data needed to map my 2D array onto a geographical grid (its just wgs84 lat/long) - but the docs don't say anything about how to insert raw data into a Raster type. I tried simply supplying my numpy array as an argument to INSERT but got:

(ProgrammingError) can't adapt type 'numpy.ndarray'

Then I tried converting it to a python list of lists and got:

(ProgrammingError) column "temp" is of type raster but expression is of type numeric[]

How do I insert Raster data using geoalchemy? I don't have geotiff (or anything similar) as source files, just 3million-odd arrays to go in the DB which implies doing a round-trip via flat files is out.

ST_Contains not working with Geography multipolygon+point

I'm finding that ST_contains is only working if I cast to geometry types from geography.

For background, I've created a table Catalog with a column footprint that is defined as

footprint = Column(Geography(geometry_type='MULTIPOLYGON', srid=4326))

If I define a point, e.g., point = 'POINT(9.3889166667 40.0101944444)' and want to query for all footprint polygons that cover the point:

q = s.query(Catalog)\
    .filter(func.ST_Contains(Catalog.footprint,
                             func.ST_GeographyFromText(point)))

I get an error:

ERROR: ProgrammingError: (ProgrammingError) function st_contains(geography, geography) does not exist
LINE 3: WHERE ST_Contains(catalog.footprint, ST_GeographyFromText('P...
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 'SELECT catalog.id AS catalog_id, catalog.name AS catalog_name, catalog.instrument AS catalog_instrument, ST_AsBinary(catalog.footprint) AS catalog_footprint, catalog.meta AS catalog_meta, catalog.created_at AS catalog_created_at, catalog.updated_at AS catalog_updated_at \nFROM catalog \nWHERE ST_Contains(catalog.footprint, ST_GeographyFromText(%(ST_GeographyFromText_1)s))' {'ST_GeographyFromText_1': 'POINT(9.3889166667 40.0101944444)'} [sqlalchemy.engine.default]
ERROR:astropy:ProgrammingError: (ProgrammingError) function st_contains(geography, geography) does not exist
LINE 3: WHERE ST_Contains(catalog.footprint, ST_GeographyFromText('P...
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 'SELECT catalog.id AS catalog_id, catalog.name AS catalog_name, catalog.instrument AS catalog_instrument, ST_AsBinary(catalog.footprint) AS catalog_footprint, catalog.meta AS catalog_meta, catalog.created_at AS catalog_created_at, catalog.updated_at AS catalog_updated_at \nFROM catalog \nWHERE ST_Contains(catalog.footprint, ST_GeographyFromText(%(ST_GeographyFromText_1)s))' {'ST_GeographyFromText_1': 'POINT(9.3889166667 40.0101944444)'}

However if I cast everything to geometry types, it works:

q = session.query(Catalog)\
    .filter(func.ST_Contains(func.Geometry(Catalog.footprint),
                             func.Geometry(func.ST_GeographyFromText(point))))

Is this something wrong with my naïve understanding of PostGIS/SQL, or is there a gap in geoalchemy2's support for geography types here?

Invalid SQL with load_only, order_by and limit

When running the example below, the following, invalid SQL query is generated:

SELECT anon_1.venue_id              AS anon_1_venue_id, 
    St_asbinary(anon_1.venue_location)  AS anon_1_venue_location, 
    St_asbinary(anon_1.anon_2)      AS anon_1_anon_2, 
    label_1.id              AS label_1_id 
FROM    (
    SELECT venue.id AS venue_id, 
               venue.location AS venue_location, 
               venue.location <-> St_geomfromtext(:ST_GeomFromText_1, 
                  :ST_GeomFromText_2) AS anon_2 
    FROM   venue 
    ORDER  BY venue.location <-> St_geomfromtext(:ST_GeomFromText_1, 
                     :ST_GeomFromText_2) 
    LIMIT  :param_1
    ) AS anon_1 
LEFT OUTER JOIN (
    venue_to_label AS venue_to_label_1 
    JOIN label AS label_1 
    ON label_1.id = venue_to_label_1.label_id) 
ON anon_1.venue_id = venue_to_label_1.venue_id 
ORDER  BY anon_1.anon_2

The problem is that St_asbinary is applied to anon_1.anon_2. I'd expect the line either not to be generated or at least without the "St_asbinary". I'm pretty sure that this is the fault of GeoAlchemy2. Can anyone comment on that assumption?

Any idea how to best resolve this issue? It seems pretty fundamental unfortunately. We're trying to use the code in a big project and any help is welcome!

The (minimal) example below assumes a local PostgreSQL database "tmp" with GIS extension installed.

import unittest
from geoalchemy2 import WKTElement, Geometry
from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import joinedload, relationship, load_only
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = (
    'postgres://postgres:password@localhost:5432/tmp')
db = SQLAlchemy(app)

Base = declarative_base()

# many (venue) <-> many (label) mapping table
venue_to_label = Table(
    'venue_to_label', db.metadata,
    Column('venue_id', Integer, ForeignKey('venue.id'), primary_key=True),
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True)
)


class Label(db.Model):
    __tablename__ = 'label'
    id = Column(Integer, primary_key=True, nullable=False)


class Venue(db.Model):
    id = Column(Integer, primary_key=True, nullable=False)
    labels = relationship(Label, secondary=venue_to_label)
    location = Column(Geometry(geometry_type="POINT"), nullable=False)

db.create_all()


class TestGeoAlchemy2Bug(unittest.TestCase):

    def test_geo_alchemy2_bug(self):
        point = WKTElement("POINT(0 0)")

        query = Venue.query
        query = query.options(joinedload(*['labels']).load_only(*['id']))
        query = query.order_by(Venue.location.distance_centroid(point))
        query = query.limit(10)

        print query
        print query.all()

Can't insert from select

Without the Geometry type, I can INSERT ... SELECT a geometry column:

q = session.query(a.geom)
ins = insert(b).from_select((b.geom,), q)
session.execute(ins)

> str(ins)
'INSERT INTO public.b (geom) SELECT public.a.geom AS public_a_geom \nFROM public.a'

With the Geometry type, the column_expression method wraps the SELECT with an ST_AsBinary call. ST_AsBinary (WKB, not EWKB) doesn't include the SRID, so the insert fails:

DataError: (DataError) Geometry SRID (0) does not match column SRID (4326)
'INSERT INTO public.b (geom) SELECT ST_AsBinary(public.a.geom) AS public_a_geom \nFROM public.a' {}

As a workaround, I can call func.ST_AsEWKB, which includes the SRID, in the query. Ideally, if possible, it would be nice to prevent the column expression from unnecessarily wrapping the query.

[ask] qlalchemy.schema.CreateTable do not render corect datatype public.geography

How can I render it as geo-dll compatible with psql ?

I have bellow code:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from geoalchemy2.types import Geography

class OrderMetadata(declarative_base()):    
    __tablename__ = "order_metadata"
    __table_args__ = {"schema": "taxi"} 
    end_point = Column("end_point", Geography)

    @classmethod
    def show_create_table(cls, connection, echo=True):
        engine = create_engine(connection, echo=echo)
        return CreateTable(cls.__table__, bind=engine)

When I execute, I recevie this dll:

>>> print OrderMetadata.show_create_table(postgre) # doctest: +NORMALIZE_WHITESPACE
CREATE TABLE taxi.order_metadata (      
    end_point geography(GEOMETRY,-1)
)

When I try to use this on psql, I receive error:

test_ada=# CREATE TABLE taxi.order_metadata (     
test_ada(#     end_point geography(GEOMETRY,-1)
test_ada(#     );
ERROR:  type "geography" does not exist
LINE 2:     end_point geography(GEOMETRY,-1)
                      ^

edit: actually it is created after, but i dont like to see error:

test_ada=# \d+ taxi.order_metadata;
                                 Table "taxi.order_metadata"
    Column     |           Type           | Modifiers | Storage  | Stats target | Description 
---------------+--------------------------+-----------+----------+--------------+-------------
 end_point     | geography(Geometry,4326) |           | main     |              | 
Has OIDs: no

But if I use postgre public.geography data type all looks ok:

test_ada=# CREATE TABLE taxi.order_metadata (     
test_ada(#         end_point public.geography
test_ada(#         );
CREATE TABLE

P.S. there is CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;

How can I render it as geo-dll compatible with psql ?

deepcopy of WKB objects

It seems that the WKBElement class doesn't work with deepcopy. When trying to make a deep copy of a WKBElement I get the error shown below. Having WKBElement objects support the deepcopy protocol would be a very useful feature.

File "/opt/anaconda3/lib/python3.4/copy.py", line 155, in deepcopy
y = copier(x, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 246, in _deepcopy_dict
y[deepcopy(key, memo)] = deepcopy(value, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 182, in deepcopy
y = _reconstruct(x, rv, 1, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 300, in _reconstruct
state = deepcopy(state, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 155, in deepcopy
y = copier(x, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 246, in _deepcopy_dict
y[deepcopy(key, memo)] = deepcopy(value, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 182, in deepcopy
y = _reconstruct(x, rv, 1, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 300, in _reconstruct
state = deepcopy(state, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 155, in deepcopy
y = copier(x, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 246, in _deepcopy_dict
y[deepcopy(key, memo)] = deepcopy(value, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 182, in deepcopy
y = _reconstruct(x, rv, 1, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 300, in _reconstruct
state = deepcopy(state, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 155, in deepcopy
y = copier(x, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 246, in _deepcopy_dict
y[deepcopy(key, memo)] = deepcopy(value, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 155, in deepcopy
y = copier(x, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 219, in _deepcopy_list
y.append(deepcopy(a, memo))
File "/opt/anaconda3/lib/python3.4/copy.py", line 182, in deepcopy
y = _reconstruct(x, rv, 1, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 300, in _reconstruct
state = deepcopy(state, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 155, in deepcopy
y = copier(x, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 246, in _deepcopy_dict
y[deepcopy(key, memo)] = deepcopy(value, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 182, in deepcopy
y = _reconstruct(x, rv, 1, memo)
File "/opt/anaconda3/lib/python3.4/copy.py", line 295, in _reconstruct
y = callable(*args)
File "/opt/anaconda3/lib/python3.4/copyreg.py", line 88, in newobj
return cls.new(cls, *args)
TypeError: Required argument 'object' (pos 1) not found

dimension support

It tried to define a geometry attribute using 3 dimension. But when I create the tables, it always creates a 2 dimension geometry. Could that be a bug?

class Boundary(Base):
    __tablename__ = 'boundaries'
    ...
    geom = Column(Geometry(geometry_type='MULTIPOLYGON', dimension=3, spatial_index=True), nullable=False)
    …

and

Base.metadata.create_all(create_engine(self.uri, echo=False), checkfirst=True)

build_sphinx results in maximum recursion depth exceeded in Mock()

If I run "python3 ./setup.py build_sphinx" I get a traceback that ends like this:

  File "conf.py", line 231, in __getattr__
  File "/usr/lib/python3.5/unittest/mock.py", line 1802, in __init__
    self._mock_set_magics()  # make magic work for kwargs in init
  File "/usr/lib/python3.5/unittest/mock.py", line 1810, in _mock_set_magics
    if getattr(self, "_mock_methods", None) is not None:
  File "conf.py", line 231, in __getattr__
RecursionError: maximum recursion depth exceeded while calling a Python object

Here is the problem code from doc/conf.py:

class Mock(MagicMock):
    @classmethod
    def __getattr__(cls, name):
        return Mock()

Reflected Geometry does not detect srid

When I use reflection, the srid is not detected. If I override the field in my model definition there is no problem. Is this expected?

Python 3.x support

Since this is a rewrite it maybe a good time to finally introduce tests to make sure GeoAlchemy2 supports Python 3.

Consider defining explicit Index() object instead of creating by hand

geoalchemy creates spatial indexes by default, which I love

This causes an unfortunate interplay with alembic when using the --autogenerate command. Basically, alembic is expecting an Index instance to be defined somewhere to record the presence of an index.

This can be an explicit declaration or the one created by passing index=True to a Column. In both these cases, the underlying metadata gets updated that the Index exists.

geoalchemy uses sqlalchemy's event system to create the index in pure SQL. As a result there is no updating of metadata and alembic's --autogenerate will behave inappropriately.

The workaround is pretty simple once you figure it out

1. Update calls to geometry e.g., point = Geometry('POINT', spatial_index=False)
2. Explicitly create the index - Index('idx_tablename_point', OrmObject.__table__.c.point, postgresql_using='gist')

Arguably this is a problem of alembic, or of the user of alembic (who is urged to manually review and edit all --autogenerate output before executing). Alternatively, you might feel that the index creation should be updated to use explicit Index constructs

• If you think the problem is alembic, what are your thoughts on adding a for Alembic users section to the documentation?
• If you think changing geoalchemy2 to create an explicit Index is appropriate, what are you thoughts on accomplishing that? I believe listening for the 'before-create' event and creating the Index as described above would be sufficient.

Thank you for your time,

Mike

Travis CI Can't Run PostGIS Version 1.5 Unit Tests Anymore

The PostGIS version 1.5 tests are failing due to a missing debian package for it. Travis CI is raising the error in ".travis.yml" (I'm not skilled with Travis CI & can't fix this).

MISSING PACKAGES: The versions don't match anything in the Debian repo GitHub uses.

1. postgresql-9.1-postgis=1.5.3-2
2. postgis=1.5.3-2

See this error near line #50 of the Travis CI error log:

install:

Install PostGIS 1.5

if [[ "$POSTGIS_VERSION" == "1.5" ]]; then sudo apt-get install -y --force-yes postgresql-9.1-postgis=1.5.3-2 postgis=1.5.3-2; fi

Syntax error when using mysql

I am using geoalchemy2 0.2.5, sqlalchemy 1.0.5, pymysql 0.6.6, and mysql 5.6.26. Copying the Lake class from the ORM tutorial and calling create_all() gives the following SQL syntax error:

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(POLYGON,-1), \n\tPRIMARY KEY (id)\n)' at line 4") [SQL: '\nCREATE TABLE lake (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tname VARCHAR(255), \n\tgeom geometry(POLYGON,-1), \n\tPRIMARY KEY (id)\n)\n\n']

According to the mysql docs a column with spatial data should be created like this:

CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g)) ENGINE=MyISAM;

Inserting large geometry values into an indexed column fails with an OperationalError

Sumary

Inserting a large geometry value into an indexed geometry column fails with:

sqlalchemy.exc.OperationalError: (OperationalError) index row requires N bytes, maximum size is 8191

Where N is large, and related to the size of the geometry being inserted, N > 8191.

Test case

The following test case reliably reproduces the problem for me (hint: pipe the output of the Python script into less).

CREATE USER test;
CREATE DATABASE test OWNER test;
\c test
CREATE EXTENSION postgis; -- on database test

from geoalchemy2 import Geometry
from sqlalchemy import Column, create_engine, types
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Row(Base):
    __tablename__ = 'row'
    id = Column(types.Integer, primary_key=True)
    geom = Column(Geometry('LINESTRING'), index=True)

engine = create_engine('postgresql://test:test@localhost/test')
Base.metadata.create_all(engine)

session = sessionmaker(bind=engine)()

r = Row(id=1)
# create a big geometry value...
r.geom = 'LINESTRING(' + ', '.join('%f %f' % (x, y) for x in xrange(-180, 180) for y in xrange(-90, 90)) + ')'
# ...add it to the session...
session.add(r)
# ...and commit ... boom!
session.commit()

Further information

This might be a limitation in PostgresSQL, see http://postgresql.1045698.n5.nabble.com/index-row-requires-10040-bytes-maximum-size-is-8191-td3262861.html .

However, if it is a limitation in PostgresSQL then geoalchemy2 should support inserting geometries in more compact formats (e.g. WKB, which isn't much smaller, but does compress well).

Please don't hesitate to close this issue if I've misunderstood something.

Read the Docs build is broken

It looks like the Read the Docs build is broken. See https://readthedocs.org/projects/geoalchemy-2/builds/3392197/.

Add pep8 validation to Travis build process

Full python 3 support

I saw on #3 and #21 that python 3 is at least partially supported. The problem was with the shapely library which now fully supports python 3. See https://pypi.python.org/pypi/Shapely/1.5.8 I also see that #86 aims to fix a bug with python 3.

Can you give me an update about python 3 support? I am really interested in the support of python 3. If you need some help to complete the port, please let me know.

types need process_literal_param() to compile sql

Will be best to have process_literal_param defined in types to support compile sql with param

http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html
http://docs.sqlalchemy.org/en/latest/core/custom_types.html#sqlalchemy.types.TypeDecorator.process_literal_param

at the moment if I compile the target sql expression.compile(pool, compile_kwargs={"literal_binds": True}), I get

SELECT poi.lng, poi.lat
FROM poi
WHERE ST_Intersects(poi.geometry, ST_GeomFromEWKB(NULL))

will be best if the NULL part could be wkt or wkb