[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] puzzling outerjoin in the mapper
Hi, i'm playing with outerjoin defined in the mapper. I'm getting results different from what I expected, so that I would like to understand which is the underline logic. Where a Query w/ outerjoin SELECT has in the backend n rows and would have m rows in a simple join, I only get m rows plus one 'None' for all the others. I would have thought to get one instance for each output of the query, am I wrong? tanks in advance sandro *:-) The example: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, ForeignKey, text, func from sqlalchemy.types import * from sqlalchemy.orm import relation, sessionmaker, mapper, column_property from sqlalchemy.orm.interfaces import SessionExtension from datetime import datetime, timedelta Base = declarative_base() Base.metadata.bind = 'sqlite://' Session = sessionmaker(bind=Base.metadata.bind) sess = Session() class Entry(Base): __tablename__ = 'calendar_entry' # Todo and Events id = Column(Integer, primary_key=True) summary = Column(String(100)) dtstart = Column(DateTime(timezone=False), nullable=False, index=True) class Alarm(Base): __tablename__ = 'calendar_alarm' ida = Column(Integer, primary_key=True) trigger = Column(Interval, nullable=False) # o2m entry_id = Column(ForeignKey(Entry.id), nullable=False) entry = relation(Entry, backref='alarm', lazy=True) Base.metadata.create_all() e1 = Entry(summary=sum1, dtstart=datetime.now()) e2 = Entry(summary=sum2, dtstart=datetime.now()) a = Alarm(trigger=timedelta(days=1)) ea1 = Entry(summary=entry w/ alarm, dtstart=datetime.now(),alarm=[a]) for e in (e1, e2, ea1, a): sess.add(e) sess.commit() entry_table = Entry.__table__ alarm_table = Alarm.__table__ class MyJoin(object): def __str__(self): return %s % self.dtstart m = mapper(MyJoin, entry_table.outerjoin(alarm_table), ) q = sess.query(m) print q.count() for r in q.all(): print r --- with result -- 3 # count for the matches None??? 2009-04-19 20:34:04.188442 # the only joined entry (entry w/ alarm) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
