Spatial data (PostGIS/OGC) with SqlAlchemy

[Allen]

I would like to use SqlAlchemy with PostGIS to create, read, update,
and query spatial data. I have search around a bit and found a few
ideas of doing this [1][2] but I haven't seen a definitive best
practice by any means. It looks like all the solutions I can find
have a least some limitations.

Is anyone here using SqlAlchemy with spatial databases and if so how
are you doing it right now?

Specifically:
- How do you handle table specification with geometry types?
[1] tries to do this but it is incomplete

- Do you use custom types and if so how are you doing this?
[2] has some custom type code but it seems to be tied to binary
formats and I don't see how to create/retrieve the geometry in text
format.

- How are you handling the object mapping?
For example is there a way to map a "POINT" geometry to a python Point
class or tuple?

- Do you have any general recommendations for how to use spatial data
successfully with SqlAlchemy?

Thanks,
Allen

[1] http://www.mail-archive.com/sqlalche...@lists.sourceforge.net/msg03371.html
[2] http://bycycle.org/2007/01/29/using-postgis-with-sqlalchemy/

[Allen Bierbaum]

I have been pursuing this further on my own and one of the issues I
have run into is how to cleanly add a geometry column to a database.
The posting referenced in the first e-mail [2] talks about doing this
directly with psycopg2 cursor because the poster could not get it
working with SA. I gave it another try to see if I could get it
working and I think I have narrowed down the problem. That said, I
still don't fully understand how to fix it with SA only.

Here is my code example:

----------------------------
db = sa.create_engine(dsn_str)

# Option 1: Try using a function on the database (doesn't work)
print "Run with func"
db.func.AddGeometryColumn('','gis_entity','fpos',-1,'LINESTRING',2).execute()

# Option 2: Try directly with database statement execution (doesn't work)
print "Run on db"
r = db.execute("select
AddGeometryColumn('','gis_entity','cpos',-1,'LINESTRING',2)")
r.close()

# Option 3: use psycopg to execute directly without autocomit? (works)
print "Run with psycopg isolation level"
con = db.connect()
con.connection.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
con.execute("select
AddGeometryColumn('','gis_entity','p_pos',-1,'LINESTRING',2)")
con.close()
------------------------

So option 3 works, but I don't fully understand why it works and why I
can't use one of the other options. Option 1 is definitely my
preferred way to do this because it will make the calls look like
normal SA calls to a DB function.

Can anyone tell my whey option 3 is working and if there is a way to
do this directly with SA only?

Thanks,
Allen

[Michael Bayer]

if the function youre calling needs a transaction commit, why dont
you call an explicit transaction ?

conn = engine.connect()
trans = conn.begin()
conn.execute(func.AddGeometryColumn
('','gis_entity','fpos',-1,'LINESTRING',2))
trans.commit()

>> [1] http://www.mail-archive.com/sqlalchemy-
>> us...@lists.sourceforge.net/msg03371.html
>> [2] http://bycycle.org/2007/01/29/using-postgis-with-sqlalchemy/
>>
>>
>>>
>>
>
> >

[Allen Bierbaum]

I tried your idea but it doesn't seem to be valid code. Here is the
code I tried:

print "Trying with a transaction."
conn = db.connect()
trans = conn.begin()
conn.execute(sa.func.AddGeometryColumn('','gis_entity','fpos',-1,'LINESTRING',2))
trans.commit()
conn.close()

and here is the exception I get on the execute line:

Try with a transaction.
INFO:sqlalchemy.engine.base.Engine.0x..d0:BEGIN
Traceback (most recent call last):
File "gis_type_test.py", line 73, in ?
conn.execute(sa.func.AddGeometryColumn('','gis_entity','fpos',-1,'LINESTRING',2,
engine=db))
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
line 258, in execute
return Connection.executors[type(object).__mro__[-2]](self,
object, *multiparams, **params)
KeyError: <class 'sqlalchemy.sql._CompareMixin'>

I am now running into a new problem as well though. I can use my
previous hack to insert the column but I can't add data to it because
I have been unable to call "GeomFromText(..)" on the insert.

I tried to get this working by defining my own type:
class GeometryType(sa.types.TypeEngine):

def __init__(self, SRID, typeName, dimension):
super(GeometryType, self).__init__()
self.mSrid = SRID
self.mType = typeName.upper()
self.mDim = dimension

def __repr__(self):
return "%s:%s-%s(%s)" % (self.__class__.__name__, self.mType,
self.mDim, self.mSrid)

def get_col_spec(self):
return "GEOMETRY"

def convert_bind_param(self, value, engine):
return 'GeomFromText(\'%s\',%s)'%(value, self.mSrid)

def convert_result_value(self, value, engine):
# Not used yet
return value

When I use this with my table and datamapper code, it looks like
everything is working fine but the generated SQL insert statement
fails with a exception:

sqlalchemy.exceptions.SQLError: (ProgrammingError) parse error -
invalid geometry
'INSERT INTO gis_entity (id, name, pos) VALUES (%(mId)s, %(mName)s,
%(mPos)s)' {'mName': 'New entity', 'mId': 1L, 'mPos':
"GeomFromText('POINT(100 100)',-1)"}

I know from using sql directly in pgadmin3 that this line works correctly:

insert into gis_entity (id, name, pos) values (2, 'New entity',
GeomFromText('POINT(100 100)', -1));

Does anyone see how this varies from the sql statement issued by SA?
I have stared at it for 20 minutes and I don't see a difference.
Is there any way to see the raw SQL statement sent by SA to postgres?
I have turned up the debug output level to full but I still only can
see the format string and parameters used for making the sql
statement:

INFO:sqlalchemy.engine.base.Engine.0x..50:INSERT INTO gis_entity (id,
name, pos) VALUES (%(mId)s, %(mName)s, %(mPos)s)
INFO:sqlalchemy.engine.base.Engine.0x..50:{'mName': 'New entity',
'mId': 1L, 'mPos': "GeomFromText('POINT(100 100)',-1)"}

Does anyone see what I am doing wrong here?

Once I get this working, then I have to figure out how to get the data
back out from postgis. What I want to end up with here is an SQL
statement like this:

select id, name, AsText(pos) as pos from gis_entity;

Note that pos is retrieved through a server side function ('AsText')
that unpacks the binary representation from the database into a string
representation. Is there any way to do this with SA so the system
will always create queries in this form whenever it tries to retrieve
the value of pos?

I don't know if this makes a solution easier, but I am using mappers
for all my tables. What I would really like to have is a mapper that
would just automatically know to wrap all references to "pos" in
INSERT calls with a call to the server-side function 'GeomFromText'
and correspondingly wrap all references to "pos" in SELECT calls with
a call to the server-side function 'AsText'. Is it possible to do
this at the mapper level? If it is, then that could greatly simplify
everything I am trying to do here.

Thanks for you help.

-Allen

[Allen Bierbaum]

[snip]

> When I use this with my table and datamapper code, it looks like
> everything is working fine but the generated SQL insert statement
> fails with a exception:
>
> sqlalchemy.exceptions.SQLError: (ProgrammingError) parse error -
> invalid geometry
> 'INSERT INTO gis_entity (id, name, pos) VALUES (%(mId)s, %(mName)s,
> %(mPos)s)' {'mName': 'New entity', 'mId': 1L, 'mPos':
> "GeomFromText('POINT(100 100)',-1)"}
>
> I know from using sql directly in pgadmin3 that this line works correctly:
>
> insert into gis_entity (id, name, pos) values (2, 'New entity',
> GeomFromText('POINT(100 100)', -1));
>
> Does anyone see how this varies from the sql statement issued by SA?

By looking at the postgres log I figured out what was causing the
error, but I still don't know how to fix it.

The problem is that SA considers "GeomFromText('POINT(100 100)', -1)"
to be a string so it puts it in single quotes when creating the SQL
command to execute. This causes problems because them postgres doesn't
know it could be calling a method instead. I have tried returning an
sqlalchemy.func object but this doesn't work either.

Any ideas?

-Allen

[Allen Bierbaum]

I just wanted to give an update. I have found a solution that works
ok for now but I had to hack a bit. I ended up having to use
psycopg2's ability to override how python classes are converted
to/from a database. psycopg2 seems to have more complete support for
this then SA, so it let me convert to an exact string representation
for my type (ie. GeomFromText('POINT(1 1)') ). I combined this with
the GeoTypes library to represent geometry types and to read them back
from the OGC binary format.

The relevant code looks like this:

import sqlalchemy as sa
import psycopg2.extensions
import logging
pe = psycopg2.extensions

from GeoTypes import (OGGeoTypeFactory, WKBParser,
OGGeometry, OGPoint, OGPolygon, OGLineString)

class PostGisWKBFactory(object):
def __init__(self):
pass

def __call__(self, s=None):
factory = OGGeoTypeFactory()
parser = WKBParser(factory)
parser.parseGeometry(s)
return factory.getGeometry()

class GeometryType(sa.types.TypeEngine):
def __init__(self, SRID, typeName, dimension):
super(GeometryType, self).__init__()
self.mSrid = SRID
self.mType = typeName.upper()
self.mDim = dimension

self.bfact = PostGisWKBFactory()

def __repr__(self):
return "%s:%s-%s(%s)" % (self.__class__.__name__, self.mType,
self.mDim, self.mSrid)

def get_col_spec(self):
return "GEOMETRY"

def convert_bind_param(self, value, engine):
# Could be used to make the type _conform_
#return pe.AsIs(str(value))
return value

def convert_result_value(self, value, engine):
geom_obj = self.bfact(binascii.a2b_hex(value))
geom_obj._srid = self.mSrid # set directly
return geom_obj

class GeometryPOINT(GeometryType):
def __init__(self, srid):
super(GeometryPOINT,self).__init__(srid, "POINT", 2)

class GeometryLINESTRING(GeometryType):
def __init__(self, srid):
super(GeometryPOINT,self).__init__(srid, "LINESTRING", 2)

Then I added the following method to the GeoTypes.Geometry class to
allow it to represent itself to psycopg2 correctly.

# Interface to allow psycopg2 to convert to database automatically
def getquoted(self):
return self.__str__()

def __conform__(self, proto):
# Return ourselves since we have a getquoted method
return self

I still don't see a way to handle this directly with SA, so if anyone
can tell me a way to let SA know exactly how I want the object's value
to appear in the generated SQL statement please let me know so I can
refine my code.

Thanks,
Allen

[Michael Bayer]

these are all SA bugs/possible inaccuracies in API usage. you should
be able to execute the function as I described below, you should be
able to put the function into an INSERT statement as well. Ill test
these features later today.

On Feb 25, 2007, at 3:43 PM, Allen Bierbaum wrote:

>
> I tried your idea but it doesn't seem to be valid code. Here is the
> code I tried:
>
> print "Trying with a transaction."
> conn = db.connect()
> trans = conn.begin()
> conn.execute(sa.func.AddGeometryColumn

> ('','gis_entity','fpos',-1,'LINESTRING',2))
> trans.commit()

[Michael Bayer]

OK, for the function execution, the syntax that works with release
0.3.5 is:

conn = db.connect()
trans = conn.begin()

conn.execute(func.AddGeometryColumn
('','gis_entity','fpos',-1,'LINESTRING',2).select())
trans.commit()
conn.close()

notice it just adds a select(). in changeset 2364, you dont need
the select.

inserts with functions are done like this:

# without a connection
table.insert().execute(somecolumn = func.GeometryFunction(arg1, arg2,
arg3...))

# with a connection
conn.execute(table.insert(), somecolumn = func.GeometryFunction(arg1,
arg2, arg3...))