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"
>>> <http://127.0.0.1:8000/admin/default/edit/bikend/models/db.py>, line 585,
>>> in <module>
>>> 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"
>>> <http://127.0.0.1:8000/admin/default/edit/bikend/models/db.py>, line 585,
>>> in <module>
>>> 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"
>>> <http://127.0.0.1:8000/admin/default/edit/bikend/models/db.py>, line 583,
>>> in <module>
>>> 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"
>>>>>> <http://127.0.0.1:8000/admin/default/edit/bikend/models/db.py>, line
>>>>>> 586, in <module>
>>>>>> 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)
>>>>>> <span st...
>>>>>> Show original
>>>>>> <https://groups.google.com/group/web2py/msg/c15e58bfcfa50047?dmode=source&output=gplain&noredirect>
>>>>>>
>>>>>>
--
