[GeoAlchemy] Ambiguous Column Reference

[bekozi]

Hi,

When running this query:

s =
session.query(BurnPerimeters.geom,BurnPoints.geom).join(BurnPoints)

this SQL is returned by the constructor:

SELECT AsBinary(geom) AS burn_perimeters_geom, AsBinary(geom) AS
burn_points_geom
FROM burn_perimeters JOIN burn_points ON burn_perimeters.id =
burn_points.id_burn_perimeters

The geom fields are not referenced to their parent tables and hence
throws an exception:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) column reference
"geom" is ambiguous
LINE 1: SELECT AsBinary(geom) AS burn_perimeters_geom,
AsBinary(geom...
^
'SELECT AsBinary(geom) AS burn_perimeters_geom, AsBinary(geom) AS
burn_points_geom \nFROM burn_perimeters JOIN burn_points ON
burn_perimeters.id = burn_points.id_burn_perimeters' {}

This situation occurs whenever two tables being queried contain
geometry columns with the same names. I am new to GeoAlchemy and
SqlAlchemy and may be missing something simple. Must geometry columns
have distinct names across the data model?

Thanks!

-Ben

[Eric Lemoine]

Hi. I'd see this as bug in GeoAlchemy. What version of GeoAlchemy are you using?

--
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.l...@camptocamp.com
http://www.camptocamp.com

[Tobias Sauerwein]

Hi!

Yes, this was a bug. You can get a fixed version from the repository: http://bitbucket.org/geoalchemy/geoalchemy/

Tobias

[bekozi]

Thanks for the quick fix! I had been using what I assume was a pre-
release 0.2 version cloned from source.

-Ben

On May 5, 3:48 am, Tobias Sauerwein <tobias.sauerw...@camptocamp.com>
wrote:

> Hi!
>
> Yes, this was a bug. You can get a fixed version from the repository:http://bitbucket.org/geoalchemy/geoalchemy/
>
> Tobias
>

> On Wed, May 5, 2010 at 7:42 AM, Eric Lemoine <eric.lemo...@camptocamp.com>wrote:

> > Mail : eric.lemo...@camptocamp.com
> >http://www.camptocamp.com

[Tobias Sauerwein]

It was a bit too quick.. ;-)

I did miss that this problem also exists when the column is used in a where clause. Now it is fixed: http://bitbucket.org/geoalchemy/geoalchemy

Tobias

[bekozi]

Fix did the trick. Smooth sailing from here.

On May 6, 3:24 am, Tobias Sauerwein <tobias.sauerw...@camptocamp.com>
wrote:

[Frank Broniewski]

Hi all,

I am testing geoalchemy a bit and I have a problem working with the
geometry attributes (x,y, wkt).

Whenever I do something like db_session.scalar(location.geom.x), I get a
segmentation fault. I tried already debugging it, but I am unable to
find the cause. My code for testing:

def test():
from database import db_session

location = db_session.query(GeoViaah).filter(GeoViaah.id==1).first()
print "Location: id %s parent_id %s type %s" % (location.id,
location.parent_id,
location.type)
print db_session.scalar(location.geom.x) # segfault here


if __name__ == '__main__':
test()


The model:
class GeoViaah(Base):
__tablename__ = 'geo_viaah'
__table_args__ = {'schema':'viaah'}

pkey = Column('pkey', Integer, primary_key=True)
id = Column('viaah_id', Integer, nullable=False)
parent_id = Column('viaah_parent_id', Integer)
type = Column('viaah_type', Integer)
east = Column('easting', Numeric)
north = Column('northing', Numeric)
geom = GeometryColumn('geom', Point(2, 4326), comparator=PGComparator)
deleted = Column('deleted', Boolean, default=False)

def __init__(self, id=None, parent_id=None, type=None):
self.id = id
self.parent_id= parent_id
self.type = type

and the database connection and session definition:
engine = create_engine('postgresql://bar:foo@localhost/geodb', echo=True)
db_session = scoped_session(sessionmaker(autocommit=False,
autoflush=False, bind=engine))

metadata = MetaData(engine)

Base = declarative_base(metadata=metadata)


Before the segfault in the console output:
2010-05-10 11:55:44,971 INFO sqlalchemy.engine.base.Engine.0x...abd0
SELECT ST_X(GeomFromWKB(%(param_1)s, %(GeomFromWKB_1)s)) AS x_1
2010-05-10 11:55:44,972 INFO sqlalchemy.engine.base.Engine.0x...abd0
{'GeomFromWKB_1': 4326, 'param_1': <read-only buffer for 0x7f88495033b0,
size 21, offset 0 at 0xafa3b0>}


My system is Ubuntu 8.04 x64 server edition and python2.5. Sqlalchemy is
0.6 and Geoalchemy is 0.2. Post

Does someone have a clue whats going on? From the tutorial this should
be easy enough?!


Many thanks

Frank





--
Frank BRONIEWSKI

METRICO s.à r.l.
géomètres
technologies d'information géographique
rue des Romains 36
L-5433 NIEDERDONVEN

tél.: +352 26 74 94 - 28
fax.: +352 26 74 94 99
http://www.metrico.lu

[Tobias Sauerwein]

Hi Frank,

your code looks ok, I was able to execute it without getting an error. Segmentation fault sounds as if there is a problem with an extension module, maybe psycopg2. Does the error message give a clue in which module the error occurs?

Tobias



This is the script which worked for me (with Python 2.6.2):

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
from geoalchemy import *
from geoalchemy.postgis import PGComparator


engine = create_engine('postgresql://gis:gis@localhost/gis', echo=True)

db_session = scoped_session(sessionmaker(autocommit=False,
                                        autoflush=False, bind=engine))
metadata = MetaData(engine)

Base = declarative_base(metadata=metadata)

class GeoViaah(Base):
   __tablename__ = 'geo_viaah'

   #__table_args__ = {'schema':'viaah'}

   pkey = Column('pkey', Integer, primary_key=True)
   id = Column('viaah_id', Integer, nullable=False)
   parent_id = Column('viaah_parent_id', Integer)
   type = Column('viaah_type', Integer)
   east = Column('easting', Numeric)
   north = Column('northing', Numeric)
   geom = GeometryColumn('geom', Point(2, 4326), comparator=PGComparator)
   deleted = Column('deleted', Boolean, default=False)

   def __init__(self, id=None, parent_id=None, type=None):
       self.id = id
       self.parent_id= parent_id
       self.type = type
      

GeometryDDL(GeoViaah.__table__)
metadata.create_all()

def test():

   spot = GeoViaah(id=1)
   spot.geom = "POINT(-81.40 38.08)"
   db_session.add(spot)
   db_session.commit()

   location = db_session.query(GeoViaah).filter(GeoViaah.id==1).first()
   print "Location: id %s parent_id %s type %s" % (location.id,
                                           location.parent_id, location.type)
   print db_session.scalar(location.geom.x)

if __name__ == '__main__':
   test()

Output:
[..]
2010-05-10 12:32:04,377 INFO sqlalchemy.engine.base.Engine.0x...b58c SELECT ST_X(GeomFromWKB(%(GeomFromWKB_1)s, %(GeomFromWKB_2)s)) AS x_1
2010-05-10 12:32:04,378 INFO sqlalchemy.engine.base.Engine.0x...b58c {'GeomFromWKB_1': <read-only buffer for 0xb76143a0, size 21, offset 0 at 0xa75d660>, 'GeomFromWKB_2': 4326}
-81.4

[Frank Broniewski]

Hello Tobias,

unfortunately all that I get is 'segmentation fault'. Btw. inserting
data/ updating data works, e.g. location.geom =
WKTSpatialElement(wkt_point, srid=2169). The installed version of
psycopg2 is the one from the ubuntu-motu repository, apt-cache shows
version 2.0.6-3. Well maybe I can get somehow more answers from the
segfault somehow ...

Many thanks so far

Frank

> self.id <http://self.id> = id

> self.parent_id= parent_id
> self.type = type
>
> GeometryDDL(GeoViaah.__table__)
> metadata.create_all()
>
> def test():
>
> spot = GeoViaah(id=1)
> spot.geom = "POINT(-81.40 38.08)"
> db_session.add(spot)
> db_session.commit()
>
> location = db_session.query(GeoViaah).filter(GeoViaah.id==1).first()
> print "Location: id %s parent_id %s type %s" % (location.id

> <http://location.id>,

> <http://location.id>,

> location.parent_id,
> location.type)
> print db_session.scalar(location.geom.x) # segfault here
>
>
> if __name__ == '__main__':
> test()
>
>
> The model:
> class GeoViaah(Base):
> __tablename__ = 'geo_viaah'
> __table_args__ = {'schema':'viaah'}
>
> pkey = Column('pkey', Integer, primary_key=True)
> id = Column('viaah_id', Integer, nullable=False)
> parent_id = Column('viaah_parent_id', Integer)
> type = Column('viaah_type', Integer)
> east = Column('easting', Numeric)
> north = Column('northing', Numeric)
> geom = GeometryColumn('geom', Point(2, 4326),
> comparator=PGComparator)
> deleted = Column('deleted', Boolean, default=False)
>
> def __init__(self, id=None, parent_id=None, type=None):

> self.id <http://self.id> = id

> self.parent_id= parent_id
> self.type = type
>
> and the database connection and session definition:
> engine = create_engine('postgresql://bar:foo@localhost/geodb',
> echo=True)
> db_session = scoped_session(sessionmaker(autocommit=False,
> autoflush=False, bind=engine))
>
> metadata = MetaData(engine)
>
> Base = declarative_base(metadata=metadata)
>
>
> Before the segfault in the console output:
> 2010-05-10 11:55:44,971 INFO sqlalchemy.engine.base.Engine.0x...abd0
> SELECT ST_X(GeomFromWKB(%(param_1)s, %(GeomFromWKB_1)s)) AS x_1
> 2010-05-10 11:55:44,972 INFO sqlalchemy.engine.base.Engine.0x...abd0
> {'GeomFromWKB_1': 4326, 'param_1': <read-only buffer for
> 0x7f88495033b0, size 21, offset 0 at 0xafa3b0>}
>
>
> My system is Ubuntu 8.04 x64 server edition and python2.5.
> Sqlalchemy is 0.6 and Geoalchemy is 0.2. Post
>
> Does someone have a clue whats going on? From the tutorial this
> should be easy enough?!
>
>
> Many thanks
>
> Frank
>
>
>
>
>
> --
> Frank BRONIEWSKI
>

> METRICO s.� r.l.
> g�om�tres
> technologies d'information g�ographique

> rue des Romains 36
> L-5433 NIEDERDONVEN
>

> t�l.: +352 26 74 94 - 28

> fax.: +352 26 74 94 99
> http://www.metrico.lu
>
>

--
Frank BRONIEWSKI

METRICO s.� r.l.
g�om�tres
technologies d'information g�ographique

rue des Romains 36
L-5433 NIEDERDONVEN

t�l.: +352 26 74 94 - 28

[Eric Lemoine]

On Mon, May 10, 2010 at 12:40 PM, Frank Broniewski <br...@metrico.lu> wrote:
> Hello Tobias,
>
> unfortunately all that I get is 'segmentation fault'. Btw. inserting data/
> updating data works, e.g. location.geom = WKTSpatialElement(wkt_point,
> srid=2169). The installed version of psycopg2 is the one from the
> ubuntu-motu repository, apt-cache shows version 2.0.6-3. Well maybe I can
> get somehow more answers from the segfault somehow ...

Have you tried a more recent version of psycopg2 (installed from
pypi.python.org with easy_install or pip)?

--
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.l...@camptocamp.com
http://www.camptocamp.com

[Frank Broniewski]

Hi,

yes, I tried that just before I read your email, Eric. And it worked.

So the issue seems to be the older psycopg2 lib from Ubuntus repository.
I would'nt have thought of that without your help, so thanks to all.

Frank

Am 10.05.2010 14:01, schrieb Eric Lemoine:
> On Mon, May 10, 2010 at 12:40 PM, Frank Broniewski<br...@metrico.lu> wrote:
>> Hello Tobias,
>>
>> unfortunately all that I get is 'segmentation fault'. Btw. inserting data/
>> updating data works, e.g. location.geom = WKTSpatialElement(wkt_point,
>> srid=2169). The installed version of psycopg2 is the one from the
>> ubuntu-motu repository, apt-cache shows version 2.0.6-3. Well maybe I can
>> get somehow more answers from the segfault somehow ...
>
> Have you tried a more recent version of psycopg2 (installed from
> pypi.python.org with easy_install or pip)?
>


--