Spatial / GIS support (in latest trunk) ===================== Sponsored by AidIQ for use by Sahana Eden
available for (so far) ++++++++++++++++++++++ 1) Postgres + PostGIS http://postgis.refractions.net/docs/ 2) MS SQL http://msdn.microsoft.com/en-us/library/ff848797.aspx SQLite + Spatialite next. Both geometry and geography fields are supported. supported functions (so far) ++++++++++++++++++++++++++++ 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 Some MS SQL examples ++++++++++++++++++++ dbm = DAL(r"mssql://user:pass@host:db") sp = dbm.define_table('spatial', Field('geo1','geometry()') ) # a point sp.insert(geo1="POINT (1 2)") 1 # a line sp.insert(geo1="LINESTRING (100 100, 20 180, 180 180)") 2 # a polygon (a square in this case) sp.insert(geo1="POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))") 3 rr=dbm(sp.id>0).select() print rr spatial.id,spatial.geo1 1, 2, 3, # why doesn't it display geo1? # field geo1 has an undisplayable internal representation in MS SQL # e.g. the first record's POINT(1 2) is stored as # 0x00000000010C000000000000F03F0000000000000040 # to visualize as WKT use function st_astext rr=dbm(sp.id>0).select(sp.id, sp.geo1.st_astext()) print rr spatial.id,spatial.geo1.STAsText() 1,POINT (1 2) 2,"LINESTRING (100 100, 20 180, 180 180)" 3,"POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))" # or using an alias rr=dbm(sp.id>0).select(sp.id, sp.geo1.st_astext().with_alias('g1')) for r in rr: print r.spatial.id, r.g1 ... 1 POINT (1 2) 2 LINESTRING (100 100, 20 180, 180 180) 3 POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0)) # other functions # STContains rr=dbm(sp.geo1.st_contains("POINT(1 1)")).select(sp.id,sp.geo1.st_astext()) print rr spatial.id,spatial.geo1.STAsText() 3,"POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))" # STDistance rr=dbm(sp.id>0).select(sp.id, sp.geo1.st_distance("POINT(-1 2)").with_alias('d')) for r in rr: print r.spatial.id, r.d ... 1 2.0 2 140.714249456 3 1.0 # STIntersects rr=dbm(sp.geo1.st_intersects("LINESTRING(20 120,60 160)")).select(sp.id,sp.geo1.st_astext()) print rr spatial.id,spatial.geo1.STAsText() 2,"LINESTRING (100 100, 20 180, 180 180)" 3,"POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))" Some PostGIS examples +++++++++++++++++++++ #pg is the postgres db connection pg.define_table('franchise', Field('code', 'string', length=1), Field('name'), ) pg.define_table('fastfood', Field('franchise', 'reference franchise'), Field('lat', 'double'), Field('lon', 'double'), Field('geom', 'geometry()'), ) # parameters for geom fields are: schema, srid and dimension # e.g. Field('geom', 'geometry('',4326,2)') # hard-coded defaults: srid=4326, dimension=2 # migration works, fields are added/dropped as required. pg.franchise.insert(code='b', name='Burger Joint') pg.fastfood.insert(franchise=1,lat=25.8092,lon=-80.24,geom='POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))') pg.commit() ff=pg.fastfood q=ff.geom.st_overlaps('POLYGON((1 1,5 1,5 5,1 5,1 1))') print pg(q).select() fastfood.id,fastfood.franchise,fastfood.lat,fastfood.lon,fastfood.geom # no records, there are no overlaps q=ff.geom.st_overlaps('POLYGON((1 1,11 1,11 11,11 1,1 1))') print pg(q).select() fastfood.id,fastfood.franchise,fastfood.lat,fastfood.lon,fastfood.geom 1,1,25.8092,-80.24,0103000020E61000000100000005000000000000000000000000000000000 00000000000000000244000000000000000000000000000002440000000000000244000000000000 00000000000000000244000000000000000000000000000000000 print pg(ff.id>0).select(ff.id,ff.franchise,ff.lat,ff.lon,ff.geom.st_simplify(1).st_astext()) fastfood2.id,fastfood2.franchise,fastfood2.lat,fastfood2.lon,"ST_AsText(ST_Simplify(fastfood2.geom, 1.0))" 1,1,25.8092,-80.24,"POLYGON((0 0,10 0,10 10,0 10,0 0))" # geography example pg.define_table('airport', Field('code','string',3), Field('geog','geography()'), ) a=pg.airport a.insert(code='LAX', geog='POINT(-118.4079 33.9434)') 1 a.insert(code='CDG', geog='POINT(2.5559 49.0083)') 2 a.insert(code='REK', geog='POINT(-21.8628 64.1286)') 3 lax="POINT(-118.4079 33.9434)" rr=pg(a.id>0).select(a.code,a.geog.st_distance(lax).with_alias('from_lax')) for r in rr: print r.airport.code, r.from_lax ... LAX,0.0 CDG,9124665.26917 REK,6969660.54628 # results are in meters rr=pg(a.id>0).select(a.code,a.geog.st_asgeojson().with_alias('geojson')) for r in rr: print r.airport.code, r.geojson ... LAX {"type":"Point","coordinates":[-118.4079,33.943399999999997]} CDG {"type":"Point","coordinates":[2.5559,49.008299999999998]} REK {"type":"Point","coordinates":[-21.8628,64.128600000000006]} More to come... Denes Lengyel.
