This is a amazing! Thanks Denes.
On Monday, 19 March 2012 19:24:09 UTC-5, DenesL wrote: > > 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. >
