geometry field /postgis/srid conversion

[Pierre]

Hi all,

here is the table def:

db.define_table('geom',
                Field('loc','geometry()'))


I gave up on this :

I need to convert a Point(longitude,latitude) from srid 4326 to srid 2154 before insertion into the geometry field (or keep 2 versions of the same point one in each spatial system)

this does the conversion:

def trs():
    q = "SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(5. 43.1)',4326),2154));"
    pt = db.executesql(q)
    return locals()

  

but this brings up an error:


def tru():
    query ="INSERT INTO geom(loc) VALUES  (ST_GeomFromText(ST_AsText(ST_Transform(ST_GeomFromText('POINT(5. 43.1)',4326),2154)),2154));"
    result = db.executesql(query)   
    return locals() 

Ticket ID

127.0.0.1.2016-01-06.23-03-36.6324202b-9fab-47da-8e52-f1bab32d87e3

<class 'psycopg2.DataError'> Geometry SRID (2154) does not match column SRID (4326)

Version

web2py™	Version 2.12.3-stable+timestamp.2015.08.19.00.18.03

Traceback

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19.

Traceback (most recent call last): File "/opt/web2py/gluon/restricted.py", line 227, in restricted exec ccode in environment File "/opt/web2py/applications/proxima/controllers/default.py", line 118, in <module> File "/opt/web2py/gluon/globals.py", line 412, in <lambda> self._caller = lambda f: f() File "/opt/web2py/applications/proxima/controllers/default.py", line 34, in trs result = db.executesql(query) File "/opt/web2py/gluon/packages/dal/pydal/base.py", line 1000, in executesql adapter.execute(query) File "/opt/web2py/gluon/packages/dal/pydal/adapters/postgres.py", line 360, in execute return BaseAdapter.execute(self, *a, **b) File "/opt/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1378, in execute return self.log_execute(*a, **b) File "/opt/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1372, in log_execute ret = self.cursor.execute(command, *a[1:], **b) DataError: Geometry SRID (2154) does not match column SRID (4326)

[Pierre]

As I understand it web2py geometry field uses SRID 4326 by default. Is there a way to tell it to use another SRID ?

or do I have to first create the table with a define_table and then the correct geometry field with a raw SQL addcolumn command == a lot of gesticulation for a simple task

Am I wasting my time trying to do GIS in web2py ?

[Paolo Valleri]

Hi Pierre,

try to define the table as follows

db.define_table('geom',
                Field('loc','geometry(public, 2154, 2)'))

By doing that you define the column loc with srid=2154. Notice that 'public' is the schema, and 2 is the dimension.

Paolo

[Pierre]

Thanks Paolo,

this seem to work:

db.define_table('geom',
                Field('loc','geometry('',2154,2)'))

I don't understand the role of the first argument: schema/public. Why public instead of '' ?  what does schema/public relate to ?

I guess you are a web2py expert. Do you think it's wise to build a GIS App in Web2py ? I mean coding GIS queries in raw sql, passing the arguments via placeholders.... This doesn't sound very good to me.....

[Paolo Valleri]

Postgres allows the developer to define tables under different schemas, the 'common' schema is 'public'. I recommend you to use it as well.

Web2py have few bindings for GIS queries, have a look at http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#PostGIS--SpatiaLite--and-MS-Geo--experimental-

 Paolo

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/Ad7ICaNy5Dk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Pierre]

ok now that I have a correct field definition :

db.define_table('geom',
                Field('loc','geometry(public,2154,2)'))

given a Point(longitude,latitude) how do I insert a meaningful value into that field ?

I can do it like that in sql :

def trb():
    p = 'POINT(5. 43.1)'
    query = "INSERT INTO geom(loc) VALUES (ST_GeomFromText(ST_AsText(ST_Transform(ST_GeomFromText(%s,4326),2154)),2154));"
    res = db.executesql(query, (p,))
    return locals()

which is not handy  

can I do it with a geoPoint in a dal expression ?