Re: [web2py] geodal

[paolo.vall...@gmail.com]

Done,
http://code.google.com/p/web2py/issues/detail?id=1259&thanks=1259&ts=1357666735
Thanks

Paolo


2013/1/8 Massimo Di Pierro 

> Please open a ticket so I will look into it asap. I would add the st_x/y
> to all GIS adapters.
>
>
> On Tuesday, January 8, 2013 9:36:57 AM UTC-6, Paolo valleri wrote:
>>
>> Hi Massimo, good to know that the patch is in trunk but I it is only for
>> the postgres adapter, I can try to define the same functions for the other
>> adapter but then someone has to check if they works or not.
>>
>> For what concern the other issue I will open I ticket, the main problem
>> is that web2py makes all the common fields name lower while it store the
>> 'gis' fields as is. I have just tried this:
>> db.define_table('test_geo1_**name',
>> Field('name_P', 'string'),
>> Field('location_P','geometry()**'),
>> )
>> In the db I have the field *name_p* and *location_P* respectively. To
>> fix it, I added in dal.py .lower() at line 832:
>> ftype = ftype % dict(schema=schema,
>>  tablename=tablename,
>>  fieldname=field_name.lower(), srid=srid,
>>  dimension=dimension)
>> And now in the db I have the field *name_p* and *location_p* respectively
>>
>> Paolo
>>
>>
>>
>>
>>
>> On Tuesday, January 8, 2013 3:48:56 PM UTC+1, Massimo Di Pierro wrote:
>>>
>>> In trunk. How about the previous issue? Is that still open? If so, can
>>> you please open a ticket? Thanks.
>>>
>>> On Tuesday, 8 January 2013 05:31:28 UTC-6, Paolo valleri wrote:

 Hi all, I have a simple problem, how I can extract values of a single
 point?
 I found that there are the functions ST_X, and ST_Y.
 Please find attached a patch that add them to dal and to the postgres
 adapter, I haven't put them neither to MSSQLAdapter nor to
 SQLITESpatiaLiteAdapter because I don't have those db to run tests.
 Now you can do something like:
 print db(db.city).select(db.city.lat**lng,
  db.city.latlng.st_x(),
  db.city.latlng.st_y())

 city.latlng,ST_X(city.latlng),**ST_Y(city.latlng)
 POINT(51.507335 -0.127683),51.507335,-0.127683
 POINT(41.901514 12.460774),41.901514,12.460774
 POINT(40.851775 14.268124),40.851775,14.268124
 The geometry field MUST contain only point otherwise it raises an error.

 Hope it helps

 Paolo



 On Sunday, January 6, 2013 5:29:52 PM UTC+1, Paolo valleri wrote:
>
> Hi Massimo, I found an other strange behavior. I tried to capitalize
> the name of a field, as follows:
> db.define_table('test_geo',
> Field('loc_Test','geometry()')**,
> )
> and I get this error:
>
> Traceback (most recent call last):
>
>   File "/home/paolo/Dropbox/git/**web2py/gluon/restricted.py", line 212, 
> in restricted
>
> exec ccode in environment
>
>   File 
> "/home/paolo/Dropbox/git/**web2py/applications/bikend/**models/db.py" 
> , line 585, 
> in 
>
> db.test_geo.insert(loc_Test=ge**oPoint(45.89096,11.0401399))
>
>   File "/home/paolo/Dropbox/git/**web2py/gluon/dal.py", line 7977, in 
> insert
>
> ret =  self._db._adapter.insert(self**,self._listify(fields))
>
>   File "/home/paolo/Dropbox/git/**web2py/gluon/dal.py", line 1175, in 
> insert
>
> raise e
> ProgrammingError: column "loc_test" of relation "test_geo" does not exist
>
> LINE 1: INSERT INTO test_geo(loc_Test) VALUES (ST_GeomFromText('POIN...
>
> and so I tried to insert without the capitalize and I get this error:
>
> Traceback (most recent call last):
>
>   File "/home/paolo/Dropbox/git/**web2py/gluon/restricted.py", line 212, 
> in restricted
>
> exec ccode in environment
>
>   File 
> "/home/paolo/Dropbox/git/**web2py/applications/bikend/**models/db.py" 
> , line 585, 
> in 
>
> db.test_geo.insert(loc_test=ge**oPoint(45.89096,11.0401399))
>
>   File "/home/paolo/Dropbox/git/**web2py/gluon/dal.py", line 7977, in 
> insert
>
> ret =  self._db._adapter.insert(self**,self._listify(fields))
>
>   File "/home/paolo/Dropbox/git/**web2py/gluon/dal.py", line 7915, in 
> _listify
>
> 'Field %s does not belong to the table' % name)
> SyntaxError: Field loc_test does not belong to the table
>
>
> finally, I redefined the table as was before, (without any capital
> latter) and I get this:
>
> Traceback (most recent call last):
>
>   File "/home/paolo/Dropbox/git/**web2py/gluon/restricted.py", line 212, 
> in restricted
>
> exec ccode in environment
>
>   File 
> "/home/paolo/Dropbox/git/**web2py/applications/bikend/**m

Re: [web2py] geodal

[Massimo Di Pierro]

Please open a ticket so I will look into it asap. I would add the st_x/y to 
all GIS adapters.

On Tuesday, January 8, 2013 9:36:57 AM UTC-6, Paolo valleri wrote:
>
> Hi Massimo, good to know that the patch is in trunk but I it is only for 
> the postgres adapter, I can try to define the same functions for the other 
> adapter but then someone has to check if they works or not.
>
> For what concern the other issue I will open I ticket, the main problem is 
> that web2py makes all the common fields name lower while it store the 'gis' 
> fields as is. I have just tried this:
> db.define_table('test_geo1_name', 
> Field('name_P', 'string'),
> Field('location_P','geometry()'),
> )
> In the db I have the field *name_p* and *location_P* respectively. To fix 
> it, I added in dal.py .lower() at line 832:
> ftype = ftype % dict(schema=schema,
>  tablename=tablename,
>  fieldname=field_name.lower(), srid=srid,
>  dimension=dimension)
> And now in the db I have the field *name_p* and *location_p* respectively
>
> Paolo
>
>
>
>
>
> On Tuesday, January 8, 2013 3:48:56 PM UTC+1, Massimo Di Pierro wrote:
>>
>> In trunk. How about the previous issue? Is that still open? If so, can 
>> you please open a ticket? Thanks.
>>
>> On Tuesday, 8 January 2013 05:31:28 UTC-6, Paolo valleri wrote:
>>>
>>> Hi all, I have a simple problem, how I can extract values of a single 
>>> point?
>>> I found that there are the functions ST_X, and ST_Y.
>>> Please find attached a patch that add them to dal and to the postgres 
>>> adapter, I haven't put them neither to MSSQLAdapter nor to 
>>> SQLITESpatiaLiteAdapter because I don't have those db to run tests.
>>> Now you can do something like:
>>> print db(db.city).select(db.city.latlng,
>>>  db.city.latlng.st_x(), 
>>>  db.city.latlng.st_y())
>>>
>>> city.latlng,ST_X(city.latlng),ST_Y(city.latlng)
>>> POINT(51.507335 -0.127683),51.507335,-0.127683
>>> POINT(41.901514 12.460774),41.901514,12.460774
>>> POINT(40.851775 14.268124),40.851775,14.268124
>>> The geometry field MUST contain only point otherwise it raises an error.
>>>
>>> Hope it helps
>>>
>>> Paolo
>>>
>>>
>>>
>>> On Sunday, January 6, 2013 5:29:52 PM UTC+1, Paolo valleri wrote:

 Hi Massimo, I found an other strange behavior. I tried to capitalize 
 the name of a field, as follows:
 db.define_table('test_geo', 
 Field('loc_Test','geometry()'),
 )
 and I get this error:

 Traceback (most recent call last):
   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
 restricted
 exec ccode in environment
   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
 , line 585, 
 in 
 db.test_geo.insert(loc_Test=geoPoint(45.89096,11.0401399))
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7977, in insert
 ret =  self._db._adapter.insert(self,self._listify(fields))
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1175, in insert
 raise e
 ProgrammingError: column "loc_test" of relation "test_geo" does not exist
 LINE 1: INSERT INTO test_geo(loc_Test) VALUES (ST_GeomFromText('POIN...

 and so I tried to insert without the capitalize and I get this error:

 Traceback (most recent call last):
   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
 restricted
 exec ccode in environment
   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
 , line 585, 
 in 
 db.test_geo.insert(loc_test=geoPoint(45.89096,11.0401399))
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7977, in insert
 ret =  self._db._adapter.insert(self,self._listify(fields))
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7915, in 
 _listify
 'Field %s does not belong to the table' % name)
 SyntaxError: Field loc_test does not belong to the table


 finally, I redefined the table as was before, (without any capital 
 latter) and I get this:

 Traceback (most recent call last):
   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
 restricted
 exec ccode in environment
   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
 , line 583, 
 in 
 db.test_geo.insert(Name='paolo')
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7313, in 
 __getattr__
 return self.lazy_define_table(tablename,*fields,**args)
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7281, in 
 lazy_define_table
 polymodel=polymodel)
   Fi

Re: [web2py] geodal

[Paolo valleri]

Hi Massimo, good to know that the patch is in trunk but I it is only for 
the postgres adapter, I can try to define the same functions for the other 
adapter but then someone has to check if they works or not.

For what concern the other issue I will open I ticket, the main problem is 
that web2py makes all the common fields name lower while it store the 'gis' 
fields as is. I have just tried this:
db.define_table('test_geo1_name', 
Field('name_P', 'string'),
Field('location_P','geometry()'),
)
In the db I have the field *name_p* and *location_P* respectively. To fix 
it, I added in dal.py .lower() at line 832:
ftype = ftype % dict(schema=schema,
 tablename=tablename,
 fieldname=field_name.lower(), srid=srid,
 dimension=dimension)
And now in the db I have the field *name_p* and *location_p* respectively

Paolo





On Tuesday, January 8, 2013 3:48:56 PM UTC+1, Massimo Di Pierro wrote:
>
> In trunk. How about the previous issue? Is that still open? If so, can you 
> please open a ticket? Thanks.
>
> On Tuesday, 8 January 2013 05:31:28 UTC-6, Paolo valleri wrote:
>>
>> Hi all, I have a simple problem, how I can extract values of a single 
>> point?
>> I found that there are the functions ST_X, and ST_Y.
>> Please find attached a patch that add them to dal and to the postgres 
>> adapter, I haven't put them neither to MSSQLAdapter nor to 
>> SQLITESpatiaLiteAdapter because I don't have those db to run tests.
>> Now you can do something like:
>> print db(db.city).select(db.city.latlng,
>>  db.city.latlng.st_x(), 
>>  db.city.latlng.st_y())
>>
>> city.latlng,ST_X(city.latlng),ST_Y(city.latlng)
>> POINT(51.507335 -0.127683),51.507335,-0.127683
>> POINT(41.901514 12.460774),41.901514,12.460774
>> POINT(40.851775 14.268124),40.851775,14.268124
>> The geometry field MUST contain only point otherwise it raises an error.
>>
>> Hope it helps
>>
>> Paolo
>>
>>
>>
>> On Sunday, January 6, 2013 5:29:52 PM UTC+1, Paolo valleri wrote:
>>>
>>> Hi Massimo, I found an other strange behavior. I tried to capitalize the 
>>> name of a field, as follows:
>>> db.define_table('test_geo', 
>>> Field('loc_Test','geometry()'),
>>> )
>>> and I get this error:
>>>
>>> Traceback (most recent call last):
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
>>> restricted
>>> exec ccode in environment
>>>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
>>> , line 585, 
>>> in 
>>> db.test_geo.insert(loc_Test=geoPoint(45.89096,11.0401399))
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7977, in insert
>>> ret =  self._db._adapter.insert(self,self._listify(fields))
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1175, in insert
>>> raise e
>>> ProgrammingError: column "loc_test" of relation "test_geo" does not exist
>>> LINE 1: INSERT INTO test_geo(loc_Test) VALUES (ST_GeomFromText('POIN...
>>>
>>> and so I tried to insert without the capitalize and I get this error:
>>>
>>> Traceback (most recent call last):
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
>>> restricted
>>> exec ccode in environment
>>>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
>>> , line 585, 
>>> in 
>>> db.test_geo.insert(loc_test=geoPoint(45.89096,11.0401399))
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7977, in insert
>>> ret =  self._db._adapter.insert(self,self._listify(fields))
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7915, in _listify
>>> 'Field %s does not belong to the table' % name)
>>> SyntaxError: Field loc_test does not belong to the table
>>>
>>>
>>> finally, I redefined the table as was before, (without any capital 
>>> latter) and I get this:
>>>
>>> Traceback (most recent call last):
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
>>> restricted
>>> exec ccode in environment
>>>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
>>> , line 583, 
>>> in 
>>> db.test_geo.insert(Name='paolo')
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7313, in 
>>> __getattr__
>>> return self.lazy_define_table(tablename,*fields,**args)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7281, in 
>>> lazy_define_table
>>> polymodel=polymodel)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 965, in 
>>> create_table
>>> fake_migrate=fake_migrate)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1068, in 
>>> migrate_table
>>> self.execute(sub_query)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 17

Re: [web2py] geodal

[Massimo Di Pierro]

In trunk. How about the previous issue? Is that still open? If so, can you 
please open a ticket? Thanks.

On Tuesday, 8 January 2013 05:31:28 UTC-6, Paolo valleri wrote:
>
> Hi all, I have a simple problem, how I can extract values of a single 
> point?
> I found that there are the functions ST_X, and ST_Y.
> Please find attached a patch that add them to dal and to the postgres 
> adapter, I haven't put them neither to MSSQLAdapter nor to 
> SQLITESpatiaLiteAdapter because I don't have those db to run tests.
> Now you can do something like:
> print db(db.city).select(db.city.latlng,
>  db.city.latlng.st_x(), 
>  db.city.latlng.st_y())
>
> city.latlng,ST_X(city.latlng),ST_Y(city.latlng)
> POINT(51.507335 -0.127683),51.507335,-0.127683
> POINT(41.901514 12.460774),41.901514,12.460774
> POINT(40.851775 14.268124),40.851775,14.268124
> The geometry field MUST contain only point otherwise it raises an error.
>
> Hope it helps
>
> Paolo
>
>
>
> On Sunday, January 6, 2013 5:29:52 PM UTC+1, Paolo valleri wrote:
>>
>> Hi Massimo, I found an other strange behavior. I tried to capitalize the 
>> name of a field, as follows:
>> db.define_table('test_geo', 
>> Field('loc_Test','geometry()'),
>> )
>> and I get this error:
>>
>> Traceback (most recent call last):
>>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
>> restricted
>> exec ccode in environment
>>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
>> , line 585, in 
>> 
>> db.test_geo.insert(loc_Test=geoPoint(45.89096,11.0401399))
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7977, in insert
>> ret =  self._db._adapter.insert(self,self._listify(fields))
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1175, in insert
>> raise e
>> ProgrammingError: column "loc_test" of relation "test_geo" does not exist
>> LINE 1: INSERT INTO test_geo(loc_Test) VALUES (ST_GeomFromText('POIN...
>>
>> and so I tried to insert without the capitalize and I get this error:
>>
>> Traceback (most recent call last):
>>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
>> restricted
>> exec ccode in environment
>>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
>> , line 585, in 
>> 
>> db.test_geo.insert(loc_test=geoPoint(45.89096,11.0401399))
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7977, in insert
>> ret =  self._db._adapter.insert(self,self._listify(fields))
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7915, in _listify
>> 'Field %s does not belong to the table' % name)
>> SyntaxError: Field loc_test does not belong to the table
>>
>>
>> finally, I redefined the table as was before, (without any capital 
>> latter) and I get this:
>>
>> Traceback (most recent call last):
>>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
>> restricted
>> exec ccode in environment
>>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
>> , line 583, in 
>> 
>> db.test_geo.insert(Name='paolo')
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7313, in 
>> __getattr__
>> return self.lazy_define_table(tablename,*fields,**args)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7281, in 
>> lazy_define_table
>> polymodel=polymodel)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 965, in 
>> create_table
>> fake_migrate=fake_migrate)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1068, in 
>> migrate_table
>> self.execute(sub_query)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in execute
>> return self.log_execute(*a, **b)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in 
>> log_execute
>> ret = self.cursor.execute(*a, **b)
>> ProgrammingError: syntax error at or near "SELECT"
>> LINE 1: ALTER TABLE test_geo ADD loc_test__tmp SELECT AddGeometryCol...
>>
>>
>>
>> Hope it helps,
>> Paolo
>>
>>
>>
>>
>>
>>
>> I changed the name of the field:
>>
>>
>> On Sunday, January 6, 2013 5:01:32 PM UTC+1, Paolo valleri wrote:
>>>
>>> well done! All the former tests worked. I will investigate more the 
>>> others functions as soon as possible.
>>>
>>> Paolo
>>>
>>>
>>> On Sunday, January 6, 2013 4:28:13 PM UTC+1, Massimo Di Pierro wrote:

 One more try please. 

 On Sunday, 6 January 2013 02:11:09 UTC-6, Paolo valleri wrote:
>
> Hi Massimo, I've tried the same 3 tests.
> The first one failed, it was working before though, the ticket:
>
> Traceback (most recent call last):
>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
> restricte

Re: [web2py] geodal

[Paolo valleri]

Hi all, I have a simple problem, how I can extract values of a single point?
I found that there are the functions ST_X, and ST_Y.
Please find attached a patch that add them to dal and to the postgres 
adapter, I haven't put them neither to MSSQLAdapter nor to 
SQLITESpatiaLiteAdapter because I don't have those db to run tests.
Now you can do something like:
print db(db.city).select(db.city.latlng,
 db.city.latlng.st_x(), 
 db.city.latlng.st_y())

city.latlng,ST_X(city.latlng),ST_Y(city.latlng)
POINT(51.507335 -0.127683),51.507335,-0.127683
POINT(41.901514 12.460774),41.901514,12.460774
POINT(40.851775 14.268124),40.851775,14.268124
The geometry field MUST contain only point otherwise it raises an error.

Hope it helps

Paolo



On Sunday, January 6, 2013 5:29:52 PM UTC+1, Paolo valleri wrote:
>
> Hi Massimo, I found an other strange behavior. I tried to capitalize the 
> name of a field, as follows:
> db.define_table('test_geo', 
> Field('loc_Test','geometry()'),
> )
> and I get this error:
>
> Traceback (most recent call last):
>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
> restricted
> exec ccode in environment
>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
> , line 585, in 
> 
> db.test_geo.insert(loc_Test=geoPoint(45.89096,11.0401399))
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7977, in insert
> ret =  self._db._adapter.insert(self,self._listify(fields))
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1175, in insert
> raise e
> ProgrammingError: column "loc_test" of relation "test_geo" does not exist
> LINE 1: INSERT INTO test_geo(loc_Test) VALUES (ST_GeomFromText('POIN...
>
> and so I tried to insert without the capitalize and I get this error:
>
> Traceback (most recent call last):
>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
> restricted
> exec ccode in environment
>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
> , line 585, in 
> 
> db.test_geo.insert(loc_test=geoPoint(45.89096,11.0401399))
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7977, in insert
> ret =  self._db._adapter.insert(self,self._listify(fields))
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7915, in _listify
> 'Field %s does not belong to the table' % name)
> SyntaxError: Field loc_test does not belong to the table
>
>
> finally, I redefined the table as was before, (without any capital latter) 
> and I get this:
>
> Traceback (most recent call last):
>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
> restricted
> exec ccode in environment
>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
> , line 583, in 
> 
> db.test_geo.insert(Name='paolo')
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7313, in 
> __getattr__
> return self.lazy_define_table(tablename,*fields,**args)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7281, in 
> lazy_define_table
> polymodel=polymodel)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 965, in 
> create_table
> fake_migrate=fake_migrate)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1068, in 
> migrate_table
> self.execute(sub_query)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in execute
> return self.log_execute(*a, **b)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in 
> log_execute
> ret = self.cursor.execute(*a, **b)
> ProgrammingError: syntax error at or near "SELECT"
> LINE 1: ALTER TABLE test_geo ADD loc_test__tmp SELECT AddGeometryCol...
>
>
>
> Hope it helps,
> Paolo
>
>
>
>
>
>
> I changed the name of the field:
>
>
> On Sunday, January 6, 2013 5:01:32 PM UTC+1, Paolo valleri wrote:
>>
>> well done! All the former tests worked. I will investigate more the 
>> others functions as soon as possible.
>>
>> Paolo
>>
>>
>> On Sunday, January 6, 2013 4:28:13 PM UTC+1, Massimo Di Pierro wrote:
>>>
>>> One more try please. 
>>>
>>> On Sunday, 6 January 2013 02:11:09 UTC-6, Paolo valleri wrote:

 Hi Massimo, I've tried the same 3 tests.
 The first one failed, it was working before though, the ticket:

 Traceback (most recent call last):
   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
 restricted
 exec ccode in environment
   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
 , line 586, 
 in 
 print db(query)._select(db.test_geo.id, db.test_geo.loc_test)
   File "/home/paolo/Dropbox/git/web2py/

Re: [web2py] geodal

[Paolo valleri]

well done! All the former tests worked. I will investigate more the others 
functions as soon as possible.

Paolo


On Sunday, January 6, 2013 4:28:13 PM UTC+1, Massimo Di Pierro wrote:
>
> One more try please. 
>
> On Sunday, 6 January 2013 02:11:09 UTC-6, Paolo valleri wrote:
>>
>> Hi Massimo, I've tried the same 3 tests.
>> The first one failed, it was working before though, the ticket:
>>
>> Traceback (most recent call last):
>>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
>> restricted
>> exec ccode in environment
>>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
>> , line 586, in 
>> 
>> print db(query)._select(db.test_geo.id, db.test_geo.loc_test)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8928, in _select
>> return adapter._select(self.query,fields,attributes)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1478, in _select
>> sql_f = ', '.join(map(geoexpand, fields))
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1477, in geoexpand
>> return self.expand(field)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1312, in expand
>> return op(first, second)
>> TypeError: ST_ASTEXT() takes exactly 2 arguments (3 given)
>>
>>
>> The other two test worked, but than the application failed raising the 
>> same ticket as the first test.
>> Hope it helps,
>>
>> Paolo
>>
>> On Sunday, January 6, 2013 1:46:00 AM UTC+1, Massimo Di Pierro wrote:
>>>
>>> If something still fails, can you place post the result of print db(...) 
>>> _select(...)
>>>
>>> On Saturday, 5 January 2013 18:45:26 UTC-6, Massimo Di Pierro wrote:

 I made some changes in trunk. Can you please try again?

 On Saturday, 5 January 2013 07:05:48 UTC-6, Paolo valleri wrote:
>
> Hi, I made a few more tests:
> from gluon.dal import geoPoint, geoLine, geoPolygon
> db.define_table('test_geo', 
> Field('loc_test','geometry()'))
> db.test_geo.insert(loc_test=geoPoint(45.89096,11.0401399))
> db.test_geo.insert(loc_test=geoPolygon((0,0),(150,0),(150,150),(0,150
> ),(0,0)))
>
> query = db.test_geo.loc_test.st_contains(geoPoint(1,1))
> print db(query).select(db.test_geo.id, db.test_geo.loc_test)
>
> dist = db.test_geo.loc_test.st_distance(geoPoint(45.0,11.0)).
> with_alias('dist')
> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>
> dist = db.test_geo.loc_test.st_distance(geoPoint(45.0,11.0))
> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
> The results are the following:
>
> The first query worked
>
> The second query failed with the following error:
> Traceback (most recent call last):
>
>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
> restricted
> exec ccode in environment
>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
> , line 589, 
> in 
> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8975, in select
> return adapter.select(self.query,fields,attributes)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1636, in select
> return self._select_aux(sql,fields,attributes)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1601, in 
> _select_aux
> self.execute(sql)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in 
> execute
> return self.log_execute(*a, **b)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in 
> log_execute
> ret = self.cursor.execute(*a, **b)
> ProgrammingError: syntax error at or near "AS"
> LINE 1: ...GeomFromText('POINT (45.00 11.00)',4326)) AS dist) F...
>
>
> The third one failed with the following error:
>
> Traceback (most recent call last):
>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
> restricted
> exec ccode in environment
>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
> , line 586, 
> in 
> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8975, in select
> return adapter.select(self.query,fields,attributes)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1636, in select
> return self._select_aux(sql,fields,attributes)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1601, in 
> _select_aux
> self.execute(sql)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in 
> execute
> 

Re: [web2py] geodal

[Massimo Di Pierro]

One more try please. 

On Sunday, 6 January 2013 02:11:09 UTC-6, Paolo valleri wrote:
>
> Hi Massimo, I've tried the same 3 tests.
> The first one failed, it was working before though, the ticket:
>
> Traceback (most recent call last):
>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
> restricted
> exec ccode in environment
>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
> , line 586, in 
> 
> print db(query)._select(db.test_geo.id, db.test_geo.loc_test)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8928, in _select
> return adapter._select(self.query,fields,attributes)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1478, in _select
> sql_f = ', '.join(map(geoexpand, fields))
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1477, in geoexpand
> return self.expand(field)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1312, in expand
> return op(first, second)
> TypeError: ST_ASTEXT() takes exactly 2 arguments (3 given)
>
>
> The other two test worked, but than the application failed raising the 
> same ticket as the first test.
> Hope it helps,
>
> Paolo
>
> On Sunday, January 6, 2013 1:46:00 AM UTC+1, Massimo Di Pierro wrote:
>>
>> If something still fails, can you place post the result of print db(...) 
>> _select(...)
>>
>> On Saturday, 5 January 2013 18:45:26 UTC-6, Massimo Di Pierro wrote:
>>>
>>> I made some changes in trunk. Can you please try again?
>>>
>>> On Saturday, 5 January 2013 07:05:48 UTC-6, Paolo valleri wrote:

 Hi, I made a few more tests:
 from gluon.dal import geoPoint, geoLine, geoPolygon
 db.define_table('test_geo', 
 Field('loc_test','geometry()'))
 db.test_geo.insert(loc_test=geoPoint(45.89096,11.0401399))
 db.test_geo.insert(loc_test=geoPolygon((0,0),(150,0),(150,150),(0,150
 ),(0,0)))

 query = db.test_geo.loc_test.st_contains(geoPoint(1,1))
 print db(query).select(db.test_geo.id, db.test_geo.loc_test)

 dist = db.test_geo.loc_test.st_distance(geoPoint(45.0,11.0)).with_alias
 ('dist')
 print db(db.test_geo.id>0).select(db.test_geo.id, dist)

 dist = db.test_geo.loc_test.st_distance(geoPoint(45.0,11.0))
 print db(db.test_geo.id>0).select(db.test_geo.id, dist)
 The results are the following:

 The first query worked

 The second query failed with the following error:
 Traceback (most recent call last):

   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
 restricted
 exec ccode in environment
   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
 , line 589, 
 in 
 print db(db.test_geo.id>0).select(db.test_geo.id, dist)
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8975, in select
 return adapter.select(self.query,fields,attributes)
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1636, in select
 return self._select_aux(sql,fields,attributes)
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1601, in 
 _select_aux
 self.execute(sql)
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in execute
 return self.log_execute(*a, **b)
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in 
 log_execute
 ret = self.cursor.execute(*a, **b)
 ProgrammingError: syntax error at or near "AS"
 LINE 1: ...GeomFromText('POINT (45.00 11.00)',4326)) AS dist) F...


 The third one failed with the following error:

 Traceback (most recent call last):
   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
 restricted
 exec ccode in environment
   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
 , line 586, 
 in 
 print db(db.test_geo.id>0).select(db.test_geo.id, dist)
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8975, in select
 return adapter.select(self.query,fields,attributes)
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1636, in select
 return self._select_aux(sql,fields,attributes)
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1601, in 
 _select_aux
 self.execute(sql)
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in execute
 return self.log_execute(*a, **b)
   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in 
 log_execute
 ret = self.cursor.execute(*a, **b)
 ProgrammingError: function st_astext(double precision) does not exist
 LINE 1: SELECT  test_geo.id, ST_AsText(ST_Distance(test_geo>>> st

Re: [web2py] geodal

[Paolo valleri]

Hi Massimo, I've tried the same 3 tests.
The first one failed, it was working before though, the ticket:

Traceback (most recent call last):
  File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
restricted
exec ccode in environment
  File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
, line 586, in 

print db(query)._select(db.test_geo.id, db.test_geo.loc_test)
  File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8928, in _select
return adapter._select(self.query,fields,attributes)
  File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1478, in _select
sql_f = ', '.join(map(geoexpand, fields))
  File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1477, in geoexpand
return self.expand(field)
  File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1312, in expand
return op(first, second)
TypeError: ST_ASTEXT() takes exactly 2 arguments (3 given)


The other two test worked, but than the application failed raising the same 
ticket as the first test.
Hope it helps,

Paolo

On Sunday, January 6, 2013 1:46:00 AM UTC+1, Massimo Di Pierro wrote:
>
> If something still fails, can you place post the result of print db(...) 
> _select(...)
>
> On Saturday, 5 January 2013 18:45:26 UTC-6, Massimo Di Pierro wrote:
>>
>> I made some changes in trunk. Can you please try again?
>>
>> On Saturday, 5 January 2013 07:05:48 UTC-6, Paolo valleri wrote:
>>>
>>> Hi, I made a few more tests:
>>> from gluon.dal import geoPoint, geoLine, geoPolygon
>>> db.define_table('test_geo', 
>>> Field('loc_test','geometry()'))
>>> db.test_geo.insert(loc_test=geoPoint(45.89096,11.0401399))
>>> db.test_geo.insert(loc_test=geoPolygon((0,0),(150,0),(150,150),(0,150),(
>>> 0,0)))
>>>
>>> query = db.test_geo.loc_test.st_contains(geoPoint(1,1))
>>> print db(query).select(db.test_geo.id, db.test_geo.loc_test)
>>>
>>> dist = db.test_geo.loc_test.st_distance(geoPoint(45.0,11.0)).with_alias(
>>> 'dist')
>>> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>>>
>>> dist = db.test_geo.loc_test.st_distance(geoPoint(45.0,11.0))
>>> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>>> The results are the following:
>>>
>>> The first query worked
>>>
>>> The second query failed with the following error:
>>> Traceback (most recent call last):
>>>
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
>>> restricted
>>> exec ccode in environment
>>>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
>>> , line 589, 
>>> in 
>>> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8975, in select
>>> return adapter.select(self.query,fields,attributes)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1636, in select
>>> return self._select_aux(sql,fields,attributes)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1601, in 
>>> _select_aux
>>> self.execute(sql)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in execute
>>> return self.log_execute(*a, **b)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in 
>>> log_execute
>>> ret = self.cursor.execute(*a, **b)
>>> ProgrammingError: syntax error at or near "AS"
>>> LINE 1: ...GeomFromText('POINT (45.00 11.00)',4326)) AS dist) F...
>>>
>>>
>>> The third one failed with the following error:
>>>
>>> Traceback (most recent call last):
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
>>> restricted
>>> exec ccode in environment
>>>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
>>> , line 586, 
>>> in 
>>> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8975, in select
>>> return adapter.select(self.query,fields,attributes)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1636, in select
>>> return self._select_aux(sql,fields,attributes)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1601, in 
>>> _select_aux
>>> self.execute(sql)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in execute
>>> return self.log_execute(*a, **b)
>>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in 
>>> log_execute
>>> ret = self.cursor.execute(*a, **b)
>>> ProgrammingError: function st_astext(double precision) does not exist
>>> LINE 1: SELECT  test_geo.id, ST_AsText(ST_Distance(test_geo>> style="color: #660;"...
>>> Show original 
>>> 
>>>
>>>

-- 

Re: [web2py] geodal

[Massimo Di Pierro]

If something still fails, can you place post the result of print db(...) 
_select(...)

On Saturday, 5 January 2013 18:45:26 UTC-6, Massimo Di Pierro wrote:
>
> I made some changes in trunk. Can you please try again?
>
> On Saturday, 5 January 2013 07:05:48 UTC-6, Paolo valleri wrote:
>>
>> Hi, I made a few more tests:
>> from gluon.dal import geoPoint, geoLine, geoPolygon
>> db.define_table('test_geo', 
>> Field('loc_test','geometry()'))
>> db.test_geo.insert(loc_test=geoPoint(45.89096,11.0401399))
>> db.test_geo.insert(loc_test=geoPolygon((0,0),(150,0),(150,150),(0,150),(0
>> ,0)))
>>
>> query = db.test_geo.loc_test.st_contains(geoPoint(1,1))
>> print db(query).select(db.test_geo.id, db.test_geo.loc_test)
>>
>> dist = db.test_geo.loc_test.st_distance(geoPoint(45.0,11.0)).with_alias(
>> 'dist')
>> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>>
>> dist = db.test_geo.loc_test.st_distance(geoPoint(45.0,11.0))
>> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>> The results are the following:
>>
>> The first query worked
>>
>> The second query failed with the following error:
>> Traceback (most recent call last):
>>
>>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
>> restricted
>> exec ccode in environment
>>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
>> , line 589, in 
>> 
>> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8975, in select
>> return adapter.select(self.query,fields,attributes)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1636, in select
>> return self._select_aux(sql,fields,attributes)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1601, in 
>> _select_aux
>> self.execute(sql)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in execute
>> return self.log_execute(*a, **b)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in 
>> log_execute
>> ret = self.cursor.execute(*a, **b)
>> ProgrammingError: syntax error at or near "AS"
>> LINE 1: ...GeomFromText('POINT (45.00 11.00)',4326)) AS dist) F...
>>
>>
>> The third one failed with the following error:
>>
>> Traceback (most recent call last):
>>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
>> restricted
>> exec ccode in environment
>>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
>> , line 586, in 
>> 
>> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8975, in select
>> return adapter.select(self.query,fields,attributes)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1636, in select
>> return self._select_aux(sql,fields,attributes)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1601, in 
>> _select_aux
>> self.execute(sql)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in execute
>> return self.log_execute(*a, **b)
>>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in 
>> log_execute
>> ret = self.cursor.execute(*a, **b)
>> ProgrammingError: function st_astext(double precision) does not exist
>> LINE 1: SELECT  test_geo.id, ST_AsText(ST_Distance(test_geo> style="color: #660;"...
>> Show original 
>> 
>>
>>

-- 

Re: [web2py] geodal

[Massimo Di Pierro]

I made some changes in trunk. Can you please try again?

On Saturday, 5 January 2013 07:05:48 UTC-6, Paolo valleri wrote:
>
> Hi, I made a few more tests:
> from gluon.dal import geoPoint, geoLine, geoPolygon
> db.define_table('test_geo', 
> Field('loc_test','geometry()'))
> db.test_geo.insert(loc_test=geoPoint(45.89096,11.0401399))
> db.test_geo.insert(loc_test=geoPolygon((0,0),(150,0),(150,150),(0,150),(0,
> 0)))
>
> query = db.test_geo.loc_test.st_contains(geoPoint(1,1))
> print db(query).select(db.test_geo.id, db.test_geo.loc_test)
>
> dist = db.test_geo.loc_test.st_distance(geoPoint(45.0,11.0)).with_alias(
> 'dist')
> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>
> dist = db.test_geo.loc_test.st_distance(geoPoint(45.0,11.0))
> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
> The results are the following:
>
> The first query worked
>
> The second query failed with the following error:
> Traceback (most recent call last):
>
>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
> restricted
> exec ccode in environment
>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
> , line 589, in 
> 
> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8975, in select
> return adapter.select(self.query,fields,attributes)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1636, in select
> return self._select_aux(sql,fields,attributes)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1601, in 
> _select_aux
> self.execute(sql)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in execute
> return self.log_execute(*a, **b)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in 
> log_execute
> ret = self.cursor.execute(*a, **b)
> ProgrammingError: syntax error at or near "AS"
> LINE 1: ...GeomFromText('POINT (45.00 11.00)',4326)) AS dist) F...
>
>
> The third one failed with the following error:
>
> Traceback (most recent call last):
>   File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in 
> restricted
> exec ccode in environment
>   File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py" 
> , line 586, in 
> 
> print db(db.test_geo.id>0).select(db.test_geo.id, dist)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8975, in select
> return adapter.select(self.query,fields,attributes)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1636, in select
> return self._select_aux(sql,fields,attributes)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1601, in 
> _select_aux
> self.execute(sql)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in execute
> return self.log_execute(*a, **b)
>   File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in 
> log_execute
> ret = self.cursor.execute(*a, **b)
> ProgrammingError: function st_astext(double precision) does not exist
> LINE 1: SELECT  test_geo.id, ST_AsText(ST_Distance(test_geo style="color: #660;"...
> Show original 
> 
>
>

-- 

Re: [web2py] geodal

[Alec Taylor]

Thanks, will test it tomorrow.

Examples with long/lat inserts, extracts and distance comparisons would
also be handy for the book.

On Sun, Dec 23, 2012 at 4:50 AM, Massimo Di Pierro <
massimo.dipie...@gmail.com> wrote:

> I made some changes to the DAL geo APIs. This is an experimental feature
> so we are allowed to make changes. Anyway, the changes should be backward
> compatible. The only issue is that I am documenting the geodal api in the
> book and I would not want to change them later.
>
> Can you please check them:
>
> The DAL supports geographical APIs using PostGIS (for PostgreSQL),
> spatialite (for SQLite), and MSSQL and Spatial Extensions.
>
> DAL provides geometry and geography fields types and the following
> functions:
>
> st_asgeojson (PostGIS only)
> st_astext
> st_contained
> st_contains
> st_distance
> st_equals
> st_intersects
> st_overlaps
> st_simplify (PostGIS only)
> st_touches
> st_within
>
> examples:
>
> >>> from gluon.dal import DAL, Field, geoPoint, geoLine, geoPolygon
> >>> db = DAL("mssql://user:pass@host:db")
> >>> sp = db.define_table('spatial',Field('loc','geometry()'))
>
> >>> sp.insert(loc=geoPoint(1,1))
> >>> sp.insert(loc=geoLine((100,100),(20,180),(180,180)))
> >>> sp.insert(loc=geoPolygon((0,0),(150,0),(150,150),(0,150),(0,0)))
>
>
> >>> print db(sp.id>0).select()
> spatial.id,spatial.loc
> 1, POINT (1 2)"
> 2, LINESTRING (100 100, 20 180, 180 180)
> 3, POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))
>
> >>> query = sp.loc.st_intersects(geoLine((20,120),(60,160)))
> >>> query =
> sp.loc.st_overlaps(geoPolygon((1,1),(11,1),(11,11),(11,1),(1,1)))
> >>> query = sp.loc.st_contains(geoPoint(1,1))
> >>> print db(query).select(sp.id,sp.loc)
> spatial.id,spatial.loc
> 3,"POLYGNON ((0 0, 150 0, 150 150, 0 150, 0 0))"
>
> >>> dist = sp.loc.st_distance(geoPoint(-1,2)).with_alias('dist')
> >>> print db(sp.id>0).select(sp.id, dist)
> spatial.id, dist
> 1 2.0
> 2 140.714249456
> 3 1.0
>
>  --
>
>
>
>

-- 

Re: [web2py] geodal

[Paolo valleri]

Hi Massimo, 
I have just tried a few examples but there is a bug in the getPoint 
function, please find attached a simple patch to fix it.
Paolo

On Monday, December 24, 2012 7:21:12 PM UTC+1, Massimo Di Pierro wrote:
>
> Thank you Denes. Would you please check my changes. I do not think I broke 
> anything but I tried to make it so that it always returns st_astext if not 
> specified and created Python functions to generate 
> geoPoint/geoLine/geoPolygon. I think it makes code more readable. 
>
> On Monday, 24 December 2012 12:13:39 UTC-6, DenesL wrote:
>>
>>
>> The original post (Spatial / GIS support in DAL):
>> https://groups.google.com/d/topic/web2py/feh1ksfdkGk/discussion
>>
>>
>>

-- 



diff --git a/gluon/dal.py b/gluon/dal.py
index e0bf056..00db5f0 100644
--- a/gluon/dal.py
+++ b/gluon/dal.py
@@ -9736,7 +9736,7 @@ DAL.Table = Table  # was necessary in gluon/globals.py session.connect
 # Geodal utils
 
 
-def geoPoint(*line):
+def geoPoint(x, y):
 return "POINT (%f %f)" % (x,y)
 
 def geoLine(*line):

Re: [web2py] geodal

[Massimo Di Pierro]

Thank you Denes. Would you please check my changes. I do not think I broke 
anything but I tried to make it so that it always returns st_astext if not 
specified and created Python functions to generate 
geoPoint/geoLine/geoPolygon. I think it makes code more readable. 

On Monday, 24 December 2012 12:13:39 UTC-6, DenesL wrote:
>
>
> The original post (Spatial / GIS support in DAL):
> https://groups.google.com/d/topic/web2py/feh1ksfdkGk/discussion
>
>
>

-- 

Re: [web2py] geodal

[DenesL]

The original post (Spatial / GIS support in DAL):
https://groups.google.com/d/topic/web2py/feh1ksfdkGk/discussion


-- 

Re: [web2py] geodal

[Massimo Di Pierro]

For the record, this feature has been in since summer 2011. It was 
sponsored by the Sahana project and implemented by Denes. It was only 
documented in an email from Denes. I have now added it to the manual and 
changed the APIs a little to make more human friendly. The API could use 
more work but the problem is that I do not fully understand differences 
between geo engines and therefore I am reluctant to change them too much.

On Saturday, 22 December 2012 12:26:48 UTC-6, rochacbruno wrote:
>
> Excelent!
>
> I did not know DAL has its support, In my new project I will use this with 
> Postgres, trying to test it today.
>
> Thank you again.
>
>

-- 

Re: [web2py] geodal

[Bruno Rocha]

Excelent!

I did not know DAL has its support, In my new project I will use this with
Postgres, trying to test it today.

Thank you again.

-- 

[web2py] geodal

[Massimo Di Pierro]

I made some changes to the DAL geo APIs. This is an experimental feature so 
we are allowed to make changes. Anyway, the changes should be backward 
compatible. The only issue is that I am documenting the geodal api in the 
book and I would not want to change them later.

Can you please check them:

The DAL supports geographical APIs using PostGIS (for PostgreSQL), 
spatialite (for SQLite), and MSSQL and Spatial Extensions.

DAL provides geometry and geography fields types and the following 
functions:

st_asgeojson (PostGIS only)
st_astext
st_contained
st_contains
st_distance
st_equals
st_intersects
st_overlaps
st_simplify (PostGIS only)
st_touches
st_within

examples:

>>> from gluon.dal import DAL, Field, geoPoint, geoLine, geoPolygon
>>> db = DAL("mssql://user:pass@host:db")
>>> sp = db.define_table('spatial',Field('loc','geometry()'))

>>> sp.insert(loc=geoPoint(1,1))
>>> sp.insert(loc=geoLine((100,100),(20,180),(180,180)))
>>> sp.insert(loc=geoPolygon((0,0),(150,0),(150,150),(0,150),(0,0)))


>>> print db(sp.id>0).select()
spatial.id,spatial.loc
1, POINT (1 2)"
2, LINESTRING (100 100, 20 180, 180 180)
3, POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))

>>> query = sp.loc.st_intersects(geoLine((20,120),(60,160)))
>>> query = 
sp.loc.st_overlaps(geoPolygon((1,1),(11,1),(11,11),(11,1),(1,1)))
>>> query = sp.loc.st_contains(geoPoint(1,1))
>>> print db(query).select(sp.id,sp.loc)
spatial.id,spatial.loc
3,"POLYGNON ((0 0, 150 0, 150 150, 0 150, 0 0))"

>>> dist = sp.loc.st_distance(geoPoint(-1,2)).with_alias('dist')
>>> print db(sp.id>0).select(sp.id, dist)
spatial.id, dist
1 2.0
2 140.714249456
3 1.0

--