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>
>>>>
>>>>
--
diff --git a/gluon/dal.py b/gluon/dal.py
index 8957c89..6c0f7b7 100644
--- a/gluon/dal.py
+++ b/gluon/dal.py
@@ -2582,6 +2582,18 @@ class PostgreSQLAdapter(BaseAdapter):
"""
return 'ST_AsText(%s)' %(self.expand(first))
+ def ST_X(self, first):
+ """
+ http://postgis.org/docs/ST_X.html
+ """
+ return 'ST_X(%s)' %(self.expand(first))
+
+ def ST_Y(self, first):
+ """
+ http://postgis.org/docs/ST_Y.html
+ """
+ return 'ST_Y(%s)' %(self.expand(first))
+
def ST_CONTAINS(self, first, second):
"""
http://postgis.org/docs/ST_Contains.html
@@ -8417,6 +8429,14 @@ class Expression(object):
db = self.db
return Expression(db, db._adapter.ST_ASTEXT, self, type='string')
+ def st_x(self):
+ db = self.db
+ return Expression(db, db._adapter.ST_X, self, type='string')
+
+ def st_y(self):
+ db = self.db
+ return Expression(db, db._adapter.ST_Y, self, type='string')
+
def st_distance(self, other):
db = self.db
return Expression(db,db._adapter.ST_DISTANCE,self,other, 'double')
