On 9/21/06, dmiller <[EMAIL PROTECTED]> wrote:
>
> On Sep 21, 2006, at 2:16 AM, Wyatt Baldwin wrote:
>
> > I am attempting to use SQLAlchemy with PostGIS (spatial extensions for
> > Postgres). I'm wondering if anyone out there has any experience with
> > this.
> >
> >
> > I was considering looking into the SQLAlchemy types and seeing if I
> > could hack together a geometry type. One thing that looks tricky is
> > that, as far as I can tell, the geometry column must be added after
> > the table is created. The PostGIS docs say to do this:
> >
> > CREATE TABLE "raw"."portlandor" (
> > [normal columns here]
> > )
> >
> > and then in a separate step:
> >
> > SELECT AddGeometryColumn
> > ('raw','portlandor','the_geom','2913','MULTILINESTRING',2);
> >
> >
> > A google search for postgis + sqlalchemy[1] doesn't turn up anything
> > useful that I can see. The 2nd item there is a post on my own site,
> > which just happens to mention both of postgis and sqlalchemy.
> >
> > Any ideas?
>
> It is possible to use SA without using the DDL management features. I
> usually write a separate DDL script that creates all my tables,
> indexes, constraints, triggers, etc. and then simply run SA against
> the database created by that script. It's usually not that much work
> to write separate DDL and keep it in sync with my SA tables and mappers.
>
> However, as I was writing that I realized that what you need to do
> should be possible with SA right now. Try this (untested code):
>
>
> portlandor_table = Table("portlandor", metadata,
> <normal column definitions here>
> )
>
> def create_all(metadata=metadata):
> metadata.create_all()
> engine = metadata.engine
>
> # add geometry columns after tables are created
> engine.func.AddGeometryColumn('raw', 'portlandor', 'the_geom',
> '2913', 'MULTILINESTRING', 2).execute()
> portlandor_table.append_column(Column("the_geom", Geometry))
>
>
> Then just use the custom create_all() function to create your tables.
> Of course you will need to create the "Geometry" column type as well
> since it is not provided by SA.
>
> ~ Daniel
Thanks, Daniel. I tried what you suggested and it partly worked, at
least in that it didn't raise any errors, but I couldn't get it to
actually add the column to the table, even when doing a commit after.
I'm doing this for now:
# psycopg2 connection and cursor
connection = engine.raw_connection()
cursor = connection.cursor()
db_schema = 'portlandor'
SRID = 2913
metadata.create_all()
# Add geometry columns after tables are created
Q = "SELECT AddGeometryColumn('%s', '%s', 'geom', %s, '%s', 2)"
tables = ('layer_streets', 'layer_nodes')
types = ('MULTILINESTRING', 'POINT')
for table, type_ in zip(tables, types):
cursor.execute(Q % (db_schema, table, SRID, type_) )
# Add gist INDEXes to geometry columns
Q = ('CREATE INDEX "%s_the_geom_gist" ON "%s"."%s" '
'using gist ("geom" gist_geometry_ops)')
for table in tables:
cursor.execute(Q % (table, db_schema, table))
# Save all that
connection.commit()
# The above added cols to the DB; this adds them to SQLAlchemy table defs
# addGeometryColumns calls append_column for each table that has geometry
addGeometryColumns(tables)
I also started on a Geometry type. It's pretty basic but seems to be
working somewhat. Before, I had created a Geometry class that was just
a subclass of the Binary type, but when I tried to copy geometries
from one table to another, the geometries got mangled.
It seems like when creating instances of a Geometry type, the
AddGeometryColumn could/should happen then, but I'm not sure (yet) how
to do that.
class Geometry(sqlalchemy.types.TypeEngine):
"""PostGIS Geometry Type."""
def __init__(self, srid, type_, dimension):
"""
``srid`` `int` -- Spatial Reference ID
``type_`` `string` -- Geometry type (POINT, LINESTRING, etc)
``dimension`` `int` -- Geometry dimensions (2D, 3D, etc)
From PostGIS documentation:
AddGeometryColumn(
<schema_name>, <table_name>, <column_name>, <srid>, <type>,
<dimension>
)
Example:
AddGeometryColumn(
'portlandor', 'layer_streets', 'geom', 2913, 'MULTILINESTRING', 2
)
First three params are given; we need to get the last three from the
users of this class.
"""
self.srid = srid
self.type = type_.upper()
self.dimension = dimension
def get_col_spec(self):
"""What exactly is this supposed to return?"""
return "%s %s" % (self.type, self.srid)
--
Wyatt Baldwin
byCycle.org
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
