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

[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.

[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.

[DenesL]

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

[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. 

[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

[Alec Taylor]

Thanks, will test it tomorrow.

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

--
 
 
 

[Paolo valleri]

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 <module>
    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.000000 11.000000)',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 <module>
    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.loc_test...
                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I am using postgres 9.1 + postGis 1.5.3
Paolo

[Massimo Di Pierro]

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

exist
LINE 1: SELECT  test_geo.id, ST_AsText(ST_Distance(test_geo<span style="color: #660;"...
Show original

[Massimo Di Pierro]

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

[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 <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)
  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

[Massimo Di Pierro]

One more try please. 

[Paolo valleri]

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

Paolo

[Paolo valleri]

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

[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

)
  <span st...
Show original

[Massimo Di Pierro]

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

[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

[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.

[paolo....@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 <massimo....@gmail.com>

--
 
 
 

--
 Paolo

[Paolo valleri]

Hi Massimo, I am still making few examples. The last one is that, I changed the geometry dimension to 3. This allows me to store points as 3d but it has the negative drawback that all the geometry type must be 3d, as a result I I have added the function ST_Force_3D at each insert to guarantee the backwards compatibility. So far I have successfully run the following example starting from an empty db:

from gluon.dal import geoPoint, geoPolygon
db.define_table('test_3d_1',
    Field('location_test', 'geometry()'),
)
db.test_3d_1.insert(location_test=geoPoint(45.89096,11.0401399, 345))
x = db.test_3d_1.location_test.st_x().with_alias('lng')
y = db.test_3d_1.location_test.st_y().with_alias('lat')
z = db.test_3d_1.location_test.st_z().with_alias('ele')
dist = db.test_3d_1.location_test.st_distance(geoPoint(45.0,11.0)).with_alias('dist')
print db(db.test_3d_1).select(x, y, z, dist)

Please find attached a very initial patch (it add the st_z as well).

However, it seems there is a migration problem. Namely, if I've a table with a geometry field defined as 2d, changing the dimension to 3 raise the following error during the migration:

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/test_pg/models/db.py", line 72, in <module>
    Field('location_test', 'geometry()'),
  File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7337, in define_table
    table = self.lazy_define_table(tablename,*fields,**args)
  File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 7373, in lazy_define_table
    polymodel=polymodel)
  File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 967, in create_table
    fake_migrate=fake_migrate)
  File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1070, in migrate_table
    self.execute(sub_query)
  File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1716, in execute
    return self.log_execute(*a, **b)
  File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1710, in log_execute
    ret = self.cursor.execute(*a, **b)

ProgrammingError: syntax error at or near "SELECT"

LINE 1: ALTER TABLE test_2d_1 ADD location_test__tmp SELECT AddGeome...