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-users@lists.sourceforge.net/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
-~----------~----~----~----~------~----~------~--~---
