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()
> 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
-~----------~----~----~----~------~----~------~--~---
