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
On 2/25/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
> 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()
>
>
> On Feb 25, 2007, at 11:02 AM, Allen Bierbaum wrote:
>
> >
> > 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
> >
> >
> > On 2/23/07, Allen <[EMAIL PROTECTED]> wrote:
> >>
> >> 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/sqlalchemy-
> >> [EMAIL PROTECTED]/msg03371.html
> >> [2] http://bycycle.org/2007/01/29/using-postgis-with-sqlalchemy/
> >>
> >>
> >>>
> >>
> >
> > >
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
