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.
