[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
Geospatial support is definitely an external project in any case. As soon as something goes in the core, it's now linked to our release process, I'm getting the tickets and complaints personally, etc., theres no way a huge subject like geo support belongs there. I agree with you that it has to be an external project. I do not want to bog down SQLAlchemy's release process. I'm just looking for smooth integration with PostGIS and other geospatial SQL extensions.If we decide my observation that GeoDjango has done a lot of work that needs to be re-done isn't really valid, and everything they've done is only useful for Django web applications, then that idea should be scrapped, and a simple library which builds upon SQLAlchemy expression constructs should be created. We need the SQLAlchemy extension to Spatialite in the lab now, so we'll get to work on that first. 1. As you suggested, we'll first try looking into making GeoDjango work as a plugin, in which case we'll get a lot of stuff for free. 2. Otherwise, we'll write our own stripped-down version for Spatialite and PostGIS. I'm aiming for a release of the plugin or extension by the end of May. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
Michael, Do you know if anyone is working on a PostGIS extension for SQLAlchemy? If not, I'd like to take a crack at it. RHH On Mon, Mar 16, 2009 at 1:47 PM, Michael Bayer mike...@zzzcomputing.com wrote: Roy H. Han wrote: On Feb 20, 6:52 pm, Michael Bayer mike...@zzzcomputing.com wrote: Also I've built an ORM extension forpostgiswhich is incomplete but demonstrates how to round trip and createPostGISexpressions in a clean way, thats in the distribution in examples/postgis/postgis.py . Thanks for this contribution, Michael. Will you be at the PyCon SQLAlchemy sprint? I would like to work on getting PostGIS support hardcoded in the trunk. ill be there monday and tuesday. PostGIS is a PG extension though so support for it should remain separateid most like an external tool along the lines of geodjango. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
Do you know if anyone is working on a PostGIS extension for SQLAlchemy? If not, I'd like to take a crack at it. My suggestions regarding this are of course building off of the postgis.py example, and also perhaps, if its feasable (not sure if it is), building the whole thing as a plugin to GeoDjango, which works with many different databases besides Postgres, and seems to have a lot of non-django-specific functionality. If the Django ORM bindings in GeoDjango could be replaced with SQLAlchemy bindings in an efficient way (where effiicient means, we're not taking Django ORM constructs and then translating to SQLA), we could leverage all the work they've done. Hmm. I was hoping I could add geospatial support to SQLAlchemy itself rather than for a specific web framework. I think GeoDjango wraps the geospatial C++ libraries (GDAL, GEOS, PROJ) with PostGIS but otherwise most of the core functionality seems to be in the geospatial C++ libraries. I have also been tinkering with Alessandro Furieri's Spatialite for SQLite3 and I think it would be a real boon if one could add geospatial support to SQLAlchemy via Spatialite as well as PostGIS. You are saying that we can save work by writing it as a plugin to GeoDjango, but I really like Pylons. Certainly, I can start with the postgis.py example and use the GeoDjango API and MapFish API as references. The MapFish community seems to have integrated PostGIS with Pylons already and maybe I can ask them how they feel about a geospatial extension for SQLAlchemy? We have a need for this at the lab because most of our custom desktop GIS applications use SQLite and SQLAlchemy seems like a great way to glue them all together. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
On Apr 19, 2009, at 4:05 PM, Roy Hyunjin Han wrote: Do you know if anyone is working on a PostGIS extension for SQLAlchemy? If not, I'd like to take a crack at it. My suggestions regarding this are of course building off of the postgis.py example, and also perhaps, if its feasable (not sure if it is), building the whole thing as a plugin to GeoDjango, which works with many different databases besides Postgres, and seems to have a lot of non-django-specific functionality. If the Django ORM bindings in GeoDjango could be replaced with SQLAlchemy bindings in an efficient way (where effiicient means, we're not taking Django ORM constructs and then translating to SQLA), we could leverage all the work they've done. Hmm. I was hoping I could add geospatial support to SQLAlchemy itself rather than for a specific web framework. I didn't see anything in GeoDjango that is specific to Django except its bindings to the ORM, which is the part I'd want to replace, but I havent looked closely. If it is in fact tightly bound to only running in a Django web application then we'd have to scrap the idea. I of course am a Pylons user as well. geospatial support is definitely an external project in any case. SQLAlchemy is a core toolkit, but its not batteries included. Utilities that build upon it like Migrate, FormAlchemy and such are separate projects. As soon as something goes in the core, its now linked to our release process, I'm getting the tickets and complaints personally, etc., theres no way a huge subject like geo support belongs there. I can't think of what advantage there would be to it being delivered in the core. I think GeoDjango wraps the geospatial C++ libraries (GDAL, GEOS, PROJ) with PostGIS but otherwise most of the core functionality seems to be in the geospatial C++ libraries. it appears to support geometric schemas for several databases. Certainly, I can start with the postgis.py example and use the GeoDjango API and MapFish API as references. The MapFish community seems to have integrated PostGIS with Pylons already and maybe I can ask them how they feel about a geospatial extension for SQLAlchemy? I dont know much about MapFish but it appears to have a client/server, calls itself a web applciation framework, and seems completely complicated compared to just we'd like to use PostGIS with SQLAlchemy. I'm just looking for smooth integration with PostGIS and other geospatial SQL extensions.If we decide my observation that GeoDjango has done a lot of work that needs to be re-done isn't really valid, and everything they've done is only useful for Django web applications, then that idea should be scrapped, and a simple library which builds upon SQLAlchemy expression constructs should be created. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
On Sun, Apr 19, 2009 at 10:17 PM, Michael Bayer
mike...@zzzcomputing.com wrote:
I dont know much about MapFish but it appears to have a client/server,
calls itself a web applciation framework, and seems completely
complicated compared to just we'd like to use PostGIS with
SQLAlchemy. I'm just looking for smooth integration with PostGIS
and other geospatial SQL extensions. If we decide my observation
that GeoDjango has done a lot of work that needs to be re-done isn't
really valid, and everything they've done is only useful for Django
web applications, then that idea should be scrapped, and a simple
library which builds upon SQLAlchemy expression constructs should be
created.
Hi
MapFish's python package [1] basically provides the following:
1- a paster_create_template entry point [2] for creating MapFish
applications with paster create -t mapfish. MapFish applications are
basically Pylons applications with an extra command (paster mf-layer)
for creating RESTful web services relying on PostGIS tables
2- a Geometry sqlalchemy.types.TypeEngine [3]
# example:
table = Table('postgis_table_name', metadata,
Column('gid', Integer, primary_key=True),
Column('the_geom', Geometry(4326))
)
Geometries read from PostGIS are converted into Shapely [4] geometries.
3- the implementation of the MapFish RESTful protocol for reading,
creating, updating and deleting geographic objects [5]
We're interested in feedback and collaboration on that.Thanks!
[1] https://trac.mapfish.org/trac/mapfish/browser/trunk/MapFish/server/python
[2]
https://trac.mapfish.org/trac/mapfish/browser/trunk/MapFish/server/python/mapfish/util.py
[3]
https://trac.mapfish.org/trac/mapfish/browser/trunk/MapFish/server/python/mapfish/sqlalchemygeom.py
[4] http://trac.gispython.org/lab/wiki/Shapely
[5]
https://trac.mapfish.org/trac/mapfish/browser/trunk/MapFish/server/python/mapfish/lib/protocol.py
--
Eric Lemoine
Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac, Cedex
Tel : 00 33 4 79 44 44 96
Mail : eric.lemo...@camptocamp.com
http://www.camptocamp.com
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
On Apr 19, 2009, at 5:12 PM, Eric Lemoine wrote:
On Sun, Apr 19, 2009 at 10:17 PM, Michael Bayer
mike...@zzzcomputing.com wrote:
I dont know much about MapFish but it appears to have a client/
server,
calls itself a web applciation framework, and seems completely
complicated compared to just we'd like to use PostGIS with
SQLAlchemy. I'm just looking for smooth integration with PostGIS
and other geospatial SQL extensions.If we decide my observation
that GeoDjango has done a lot of work that needs to be re-done isn't
really valid, and everything they've done is only useful for Django
web applications, then that idea should be scrapped, and a simple
library which builds upon SQLAlchemy expression constructs should be
created.
Hi
MapFish's python package [1] basically provides the following:
1- a paster_create_template entry point [2] for creating MapFish
applications with paster create -t mapfish. MapFish applications are
basically Pylons applications with an extra command (paster mf-layer)
for creating RESTful web services relying on PostGIS tables
2- a Geometry sqlalchemy.types.TypeEngine [3]
# example:
table = Table('postgis_table_name', metadata,
Column('gid', Integer, primary_key=True),
Column('the_geom', Geometry(4326))
)
Geometries read from PostGIS are converted into Shapely [4]
geometries.
3- the implementation of the MapFish RESTful protocol for reading,
creating, updating and deleting geographic objects [5]
how easily can the SQLAlchemy constructs be used independently of the
Pylons/RESTful stuff ? do you include comparator objects like those
demonstrated in the postgis.py example ? e.g.:
print
session
.query(Road).filter(Road.road_geom.intersects(r1.road_geom)).all()
session.query(Road).filter(Road.road_geom == 'LINESTRING(189412
252431,189631 259122)').one()
the GeomFromText function is called implicitly with the above
examples.
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
On Sun, Apr 19, 2009 at 11:37 PM, Michael Bayer mike...@zzzcomputing.com wrote: how easily can the SQLAlchemy constructs be used independently of the Pylons/RESTful stuff ? By installing the package and only import mapfish.sqlalchemygeom. But a separate python package may make sense, and I guess what we could do that if people are interested. do you include comparator objects like those demonstrated in the postgis.py example ? e.g.: No, but I'd be interested in looking into that more closely. print session .query(Road).filter(Road.road_geom.intersects(r1.road_geom)).all() session.query(Road).filter(Road.road_geom == 'LINESTRING(189412 252431,189631 259122)').one() the GeomFromText function is called implicitly with the above examples. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
On Feb 20, 6:52 pm, Michael Bayer mike...@zzzcomputing.com wrote: Also I've built an ORM extension forpostgiswhich is incomplete but demonstrates how to round trip and createPostGISexpressions in a clean way, thats in the distribution in examples/postgis/postgis.py . Thanks for this contribution, Michael. Will you be at the PyCon SQLAlchemy sprint? I would like to work on getting PostGIS support hardcoded in the trunk. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
Roy H. Han wrote: On Feb 20, 6:52 pm, Michael Bayer mike...@zzzcomputing.com wrote: Also I've built an ORM extension forpostgiswhich is incomplete but demonstrates how to round trip and createPostGISexpressions in a clean way, thats in the distribution in examples/postgis/postgis.py . Thanks for this contribution, Michael. Will you be at the PyCon SQLAlchemy sprint? I would like to work on getting PostGIS support hardcoded in the trunk. ill be there monday and tuesday. PostGIS is a PG extension though so support for it should remain separateid most like an external tool along the lines of geodjango. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
the func. call is a SQL expression which can't be bound to a bind
parameter. that has to stay in the values() clause. e.g.
table.insert().values(location=f, id=97).execute(), or
table.insert().values(location=f).execute(id=97).
if you wanted everything inside of 'f' to be bound:
table.insert().values(location=func.GeomFromText(bindparam('a'),
bindparam('b')).execute(id=97, a='POINT(2,3)', b=4326).
Also I've built an ORM extension for postgis which is incomplete but
demonstrates how to round trip and create PostGIS expressions in a
clean way, thats in the distribution in examples/postgis/postgis.py .
On Feb 20, 2009, at 4:03 PM, quaker4lyf wrote:
Hello,
I need to query, insert, update and delete from already existing
PostGIS tables.
After much trial and error, nothing worked. Then I came across this
message:
http://groups.google.com/group/sqlalchemy/msg/424d9aa10d30abaf
Following that, I've confirmed that the following works:
f = func.GeomFromText('POINT(-118.0 34.0)',4326)
ins = tbl_test.insert().values(id=97, location=f)
conn = engine.connect()
trans = conn.begin()
conn.execute(ins)
trans.commit()
conn.close()
which is nice, but I like to work with connectionless or implicit
execution (as described in SQLAlchemy 0.5.3 Documentation). So I
tried:
tbl_test.insert().execute(id=42, location=f)
but it doesn't work. The error returned was:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt
'INSERT INTO tbl_test (id, location) VALUES (%(id)s, %(location)
s)' {'id': 42, 'location': sqlalchemy.sql.expression.Function at
0x830dd8c; GeomFromText}
So I suspect this has to do with how slqalchemy understands my table
schema?
I currently am declaring the table as
tbl_test = Table('tbl_test', self.metadata, Column('event_id',
Integer), Column('location', Geometry(2, 4326), nullable=False))
where Geometry is copied and pasted from some post found using Google:
class Geometry(TypeEngine):
Base PostGIS Geometry column type
name = 'GEOMETRY'
def __init__(self, dimension, srid=-1):
self.dimension = dimension
self.srid = srid
def bind_processor(self, dialect):
def process(value):
return value
return process
def result_processor(self, dialect): #not used yet
def process(value):
return value
return process
Can anyone help? According to Michael Bayer in that old post, the
connectionless statement should work.
Thank you
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
