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"
>>>> <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>
>>>>>>>
>>>>>>>
--
