def setup_spatial_table(package_extent_class, db_srid=None):
if legacy_geoalchemy:
package_extent_table = Table(
'package_extent', meta.metadata,
Column('package_id', types.UnicodeText, primary_key=True),
GeometryExtensionColumn('the_geom', Geometry(2, srid=db_srid))
)
meta.mapper(
package_extent_class,
package_extent_table,
properties={'the_geom':
GeometryColumn(package_extent_table.c.the_geom,
comparator=PGComparator)}
)
GeometryDDL(package_extent_table)
else:
# PostGIS 1.5 requires management=True when defining the Geometry
# field
management = (postgis_version()[:1] == '1')
package_extent_table = Table(
'package_extent', meta.metadata,
Column('package_id', types.UnicodeText, primary_key=True),
Column('the_geom', Geometry('GEOMETRY', srid=db_srid,
management=management)),
)
meta.mapper(package_extent_class, package_extent_table)
return package_extent_table
def _create_table(self, tablename):
""" Test functions use this function to create a table object.
Each test function should call this function only once. And
there should not be two test functions that call this function
with the same ptable_name value.
"""
import sqlahelper
from sqlalchemy import Table, Column, ForeignKey, types
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy import GeometryExtensionColumn, GeometryDDL
from geoalchemy import (Point, LineString, Polygon, MultiPoint,
MultiLineString, MultiPolygon)
engine = sqlahelper.get_engine()
Base = declarative_base(bind=engine)
ctable = Table('%s_child' % tablename,
Base.metadata,
Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode),
schema='public')
ctable.create()
ptable = Table(tablename,
Base.metadata,
Column('id', types.Integer, primary_key=True),
Column('child1_id', types.Integer,
ForeignKey('public.%s_child.id' % tablename)),
Column('child2_id', types.Integer,
ForeignKey('public.%s_child.id' % tablename)),
GeometryExtensionColumn('point', Point),
GeometryExtensionColumn('linestring', LineString),
GeometryExtensionColumn('polygon', Polygon),
GeometryExtensionColumn('multipoint', MultiPoint),
GeometryExtensionColumn('multilinestring',
MultiLineString),
GeometryExtensionColumn('multipolygon', MultiPolygon),
schema='public')
GeometryDDL(ptable)
ptable.create()
self.metadata = Base.metadata
def define_spatial_tables(db_srid=None):
global package_extent_table
if not db_srid:
db_srid = int(config.get('ckan.spatial.srid', DEFAULT_SRID))
else:
db_srid = int(db_srid)
package_extent_table = Table('package_extent', meta.metadata,
Column('package_id', types.UnicodeText, primary_key=True),
GeometryExtensionColumn('the_geom', Geometry(2,srid=db_srid)))
meta.mapper(PackageExtent, package_extent_table, properties={
'the_geom': GeometryColumn(package_extent_table.c.the_geom,
comparator=PGComparator)})
# enable the DDL extension
GeometryDDL(package_extent_table)
class Spot(object):
def __init__(self, spot_id=None, spot_height=None, spot_location=None):
self.spot_id = spot_id
self.spot_height = spot_height
self.spot_location = spot_location
mapper(Spot, spots_table, properties={
'spot_location': GeometryColumn(spots_table.c.spot_location,
comparator=MySQLComparator)})
# enable the DDL extension, which allows CREATE/DROP operations
# to work correctly. This is not needed if working with externally
# defined tables.
GeometryDDL(Road.__table__)
GeometryDDL(Lake.__table__)
GeometryDDL(spots_table)
class TestGeometry(TestCase):
def setUp(self):
metadata.drop_all()
metadata.create_all()
# Add objects. We can use strings...
session.add_all([
Road(road_name=u'Jeff Rd', road_geom='LINESTRING(-88.9139332929936 42.5082802993631,-88.8203027197452 42.5985669235669,-88.7383759681529 42.7239650127389,-88.6113059044586 42.9680732929936,-88.3655256496815 43.1402866687898)'),
Road(road_name=u'Peter Rd', road_geom='LINESTRING(-88.9139332929936 42.5082802993631,-88.8203027197452 42.5985669235669,-88.7383759681529 42.7239650127389,-88.6113059044586 42.9680732929936,-88.3655256496815 43.1402866687898)'),
Road(road_name=u'Geordie Rd', road_geom='LINESTRING(-89.2232485796178 42.6420382611465,-89.2449842484076 42.9179140573248,-89.2316084522293 43.106847178344,-89.0710987261147 43.243949044586,-89.092834566879 43.2957802993631,-89.092834566879 43.2957802993631,-89.0309715095541 43.3175159681529)'),
reader = csv.reader(open(associations_filename), delimiter='\t')
reader.next()
records = [line for line in reader]
for id, key_area_id in records:
# Определим ключевоq уч-к по его id
key_a = dbsession.query(Key_area).filter_by(
id=key_area_id).one()
# Определим полигон по его id
square = dbsession.query(Squares).filter_by(id=id).one()
square.key_areas.append(key_a)
@staticmethod
def export_to_file(filename):
from nextgisbio.utils.dump_to_file import dump
fieldnames = ['square_id', 'key_area_id']
squares_from_db = DBSession().query(Squares).join(
Squares.key_areas).order_by(Squares.id).all()
squares = []
for square in squares_from_db:
for key_area in square.key_areas:
squares.append([square.id, key_area.id])
dump(filename, fieldnames, squares, is_array=True)
GeometryDDL(Squares.__table__)
ride_windchill_end = Column(Float, nullable=True)
ride_windchill_avg = Column(Float, nullable=True)
ride_precip = Column(Float, nullable=True) # In inches
ride_rain = Column(Boolean, default=False, nullable=False)
ride_snow = Column(Boolean, default=False, nullable=False)
day_temp_min = Column(Float, nullable=True)
day_temp_max = Column(Float, nullable=True)
sunrise = Column(Time, nullable=True)
sunset = Column(Time, nullable=True)
def __repr__(self):
return "<{0} ride_id={1}>".format(self.__class__.__name__, self.id,
self.segment_name)
# Setup Geometry columns
GeometryDDL(RideGeo.__table__)
GeometryDDL(RideTrack.__table__)
# Opting for a more explicit approach to specifyign which tables are to be managed by SA.
#
# _MANAGED_TABLES = [obj.__table__ for name, obj in inspect.getmembers(sys.modules[__name__])
# if inspect.isclass(obj) and (issubclass(obj, Base) and obj is not Base)
# and hasattr(obj, '__table__')
# and not issubclass(obj, _SqlView)]
#
# register_managed_tables(_MANAGED_TABLES)
shape_id = Column(Integer, primary_key=True)
shape_name = Column(String)
shape_geom = GeometryColumn(GeometryCollection(2))
class Overlap(Base):
__tablename__ = 'overlap'
id = Column(Integer, primary_key=True)
geom = GeometryColumn(Polygon(2))
# enable the DDL extension, which allows CREATE/DROP operations
# to work correctly. This is not needed if working with externally
# defined tables.
GeometryDDL(Road.__table__)
GeometryDDL(Lake.__table__)
GeometryDDL(Spot.__table__)
GeometryDDL(Shape.__table__)
GeometryDDL(Overlap.__table__)
metadata.drop_all()
metadata.create_all()
# Add objects. We can use strings...
session.add_all([
Road(
road_name='Jeff Rd',
road_geom=
'LINESTRING(-88.9139332929936 42.5082802993631,-88.8203027197452 42.5985669235669,-88.7383759681529 42.7239650127389,-88.6113059044586 42.9680732929936,-88.3655256496815 43.1402866687898)'
),
owner_id = Column(Integer, ForeignKey('users.id'))
owner = relationship('User',
cascade='all, delete',
primaryjoin='User.id==Device.owner_id')
class Ride(Base, ResourceMixin):
__tablename__ = 'rides'
time_started = Column(DateTime)
time_ended = Column(DateTime)
owner_id = Column(Integer, ForeignKey('users.id'))
owner = relationship('User',
cascade='all, delete',
primaryjoin='User.id==Ride.owner_id')
class Trace(Base, ResourceMixin):
__tablename__ = 'traces'
geometry = GeometryColumn(Point(2))
altitude = Column(Float)
device_timestamp = Column(DateTime) # The time from the device
ride_id = Column(Integer, ForeignKey('rides.id'))
ride = relationship('Ride',
cascade='all, delete',
primaryjoin='Ride.id==Trace.ride_id')
GeometryDDL(Trace.__table__)
def test_shapefile_ingestor(self):
Base = declarative_base()
class TestClass(Base):
__tablename__ = 'testclass'
id = Column(Integer, primary_key=True)
attr1 = Column(Integer)
attr2 = Column(String)
geom = GeometryColumn(MultiPolygon(2))
GeometryDDL(TestClass.__table__)
schema = {'sources': {'TestClass': TestClass}}
Base.metadata.create_all(self.connection)
dao = ORM_DAO(session=self.session, schema=schema)
shapedir = tempfile.mkdtemp()
shapefile = os.path.join(shapedir, "test.shp")
schema = {
'geometry': 'MultiPolygon',
'properties': {
'S_ATTR1': 'int',
'S_ATTR2': 'str',
}
}
records = []
for i in range(5):
coords = [[dg.generate_polygon_coords(x=i, y=i)]]
records.append({
'id': i,
'geometry': {
'type': 'MultiPolygon',
'coordinates': coords
},
'properties': {
'S_ATTR1': i,
'S_ATTR2': str(i),
}
})
writer = shapefile_util.get_shapefile_writer(
shapefile=shapefile,
crs='EPSG:4326',
schema=schema,
)
for record in records:
writer.write(record)
writer.close()
mappings = [{
'source': 'S_ATTR1',
'target': 'attr1',
'processor': lambda value: int(value) * 10
}, {
'source': 'S_ATTR2',
'target': 'attr2',
}, {
'source': '__shape',
'target': 'geom',
'processor': gis_util.shape_to_wkt,
}]
Ingestor(reader=ShapefileReader(shp_file=shapefile),
processors=[
ClassMapper(clazz=TestClass, mappings=mappings),
DAOWriter(dao=dao, commit=False),
]).ingest()
results = dao.query({'SELECT': ['__TestClass']}).all()
for r in results:
print r.attr1, r.attr2, dao.session.scalar(r.geom.wkt)
# -*- coding: utf-8 -*-
from sqlalchemy import Column, types
from geoalchemy import GeometryColumn, LineString, GeometryDDL
from mapfish.sqlalchemygeom import GeometryTableMixIn
from testapp.model.meta import Session, Base
class Line(Base, GeometryTableMixIn):
__tablename__ = 'lines'
id = Column(types.Integer, primary_key=True)
the_geom = GeometryColumn(LineString(srid=4326))
# Triggers SQLAlchemy's DDL statement creation
GeometryDDL(Line.__table__)
if not orgid:
log.error('No organization provided')
return
shape = asShape(geojson)
extent = Session.query(OrganizationExtent)\
.filter(OrganizationExtent.organization_id == orgid).first()
if not extent:
extent = OrganizationExtent(organization_id=orgid)
extent.the_geom = WKTSpatialElement(shape.wkt, db_srid)
extent.save()
db_srid = int(config.get('ckan.spatial.srid', DEFAULT_SRID))
organization_extent_table = Table(
'organization_extent', meta.metadata,
Column('organization_id', types.UnicodeText, primary_key=True),
GeometryExtensionColumn('the_geom', Geometry(2, srid=db_srid)))
meta.mapper(OrganizationExtent,
organization_extent_table,
properties={
'the_geom':
GeometryColumn(organization_extent_table.c.the_geom,
comparator=PGComparator)
})
# enable the DDL extension
GeometryDDL(organization_extent_table)
result = dict(id=self.id,
name=self.name,
level=self.level,
parent_id=self.parent_id)
return result
class UnitPolygon(Base):
__tablename__ = 'unit_polygon'
unit_id = Column(BigInteger, ForeignKey('unit.id'), primary_key=True)
geom = GeometryColumn(MultiPolygon(2))
unit = relation(Unit, uselist=False)
GeometryDDL(UnitPolygon.__table__)
class UnitPoint(Base):
__tablename__ = 'unit_point'
unit_id = Column(BigInteger, ForeignKey('unit.id'), primary_key=True)
geom = GeometryColumn(Point(2))
unit = relation(Unit, uselist=False)
GeometryDDL(UnitPoint.__table__)
class UnitStat(Base):
__tablename__ = 'unit_stat'
host)
Base = sqlahelper.get_base()
class TestPoint(Base):
__tablename__ = 'testpoint'
__table_args__ = {'schema': 'main'}
id = Column(types.Integer, primary_key=True)
the_geom = GeometryColumn(MultiPoint(srid=21781))
name = Column(types.Unicode)
city = Column(types.Unicode)
country = Column(types.Unicode)
GeometryDDL(TestPoint.__table__)
GETFEATURE_REQUEST = u"""<?xml version='1.0' encoding="UTF-8" ?>
<wfs:GetFeature xmlns:wfs="http://www.opengis.net/wfs" service="WFS" version="1.1.0" xsi:schemaLocation="http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.1.0/wfs.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<wfs:Query typeName="feature:%(feature)s" srsName="EPSG:21781" xmlns:feature="http://mapserver.gis.umn.edu/mapserver">
<ogc:Filter xmlns:ogc="http://www.opengis.net/ogc">
<ogc:PropertyIs%(function)s matchCase="false" %(arguments)s>
<ogc:PropertyName>%(property)s</ogc:PropertyName>
<ogc:Literal>%(value)s</ogc:Literal>
</ogc:PropertyIs%(function)s>
</ogc:Filter>
</wfs:Query>
</wfs:GetFeature>"""
SUBSTITUTION_GETFEATURE_REQUEST = (GETFEATURE_REQUEST % {
'feature': u'testpoint_substitution',
from ckan.model import Package
from ckanext.publicamundi.model import Base
class CswRecord(Base):
__tablename__ = 'csw_record'
id = Column('id',
String(64),
ForeignKey(Package.id, ondelete='cascade'),
primary_key=True)
title = Column('title', String(256), nullable=True)
name = Column('name', String(64), nullable=False, index=True)
created_at = Column('created_at', DateTime(), nullable=False)
geom = GeometryColumn('geom', Geometry(2), nullable=True)
uniq_name = UniqueConstraint('name')
def __init__(self, id, name, created_at=None):
self.id = id
self.name = name
self.created_at = created_at or datetime.now()
def __unicode__(self):
return "<CswRecord \"%s\">" % (self.name)
# note: needed to generate proper AddGeometryColumn statements
GeometryDDL(CswRecord.__table__)
primaryjoin="OSRMRouteStep.edge_id == OSRMEdge.hash",
foreign_keys="OSRMEdge.hash",
uselist=False)
class OSRMEdgeFrequencies(Base):
__tablename__ = "edgefrequencies"
edge = Column(BigInteger, ForeignKey('osrmedges.hash'), primary_key=True)
forward = Column(Boolean, primary_key=True)
freq = Column(BigInteger)
geom = GeometryColumn(LineString(2), comparator=PGComparator)
edgeobj = relationship("OSRMEdge")
class OSRMRouteNode(Base):
__tablename__ = "routenodes"
osm_id = Column(Integer, ForeignKey('osrmnodes.osm_id'), primary_key=True)
n_outputs = Column(Integer)
n_inputs = Column(Integer)
sum_out = Column(Integer)
product_out = Column(BigInteger)
log_sum_out = Column(Float)
redundant = Column(Boolean)
node = relationship("OSRMNode")
GeometryDDL(OSRMNode.__table__)
GeometryDDL(OSRMEdge.__table__)
GeometryDDL(OSRMEdgeGeom.__table__)
GeometryDDL(OSRMEdgeFrequencies.__table__)
UPDATE content SET num_comments = (
SELECT count(*)
FROM content
WHERE __type__='comment' AND parent_id=tmp_parent_id
) WHERE id=tmp_parent_id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_response_count
AFTER INSERT OR UPDATE OR DELETE ON content
FOR EACH ROW EXECUTE PROCEDURE update_response_count();
""").execute_at('after-create', Content.__table__)
GeometryDDL(Content.__table__)
DDL("CREATE INDEX content_fts_idx ON content USING gin(to_tsvector('english', title || ' ' || content));"
).execute_at('after-create', Content.__table__)
class DraftContent(Content):
__tablename__ = "content_draft"
__mapper_args__ = {'polymorphic_identity': 'draft'}
id = Column(Integer(), ForeignKey('content.id'), primary_key=True)
target_type = Column(_content_type, nullable=True, default='article')
#publish_id = Column(Integer(), nullable=True, doc="if present will overwite the published content with this draft")
auto_publish_trigger_datetime = Column(DateTime(), nullable=True)
__to_dict__ = copy.deepcopy(Content.__to_dict__)
_extra_draft_fields = {
from sqlalchemy import Column, types, ForeignKey
from sqlalchemy.orm import relationship
from geoalchemy import GeometryColumn, Point, GeometryDDL
from demoapp.model.meta import engine, Base
from demoapp.model.categories import Category
class Place(Base):
__tablename__ = 'places'
id = Column(types.Integer, primary_key=True)
name = Column(types.String, nullable=False)
category_id = Column(types.Integer, ForeignKey('categories.cid'))
category = relationship(Category)
the_geom = GeometryColumn(Point(dimension=2, srid=4326))
GeometryDDL(Place.__table__)
def _create_layer(self,
public=False,
none_area=False,
attr_list=False,
exclude_properties=False):
""" This function is central for this test class. It creates
a layer with two features, and associates a restriction area
to it. """
import transaction
import sqlahelper
from sqlalchemy import func
from sqlalchemy import Column, Table, types, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy import (GeometryDDL, GeometryExtensionColumn, Point,
WKTSpatialElement)
from c2cgeoportal.models import DBSession, Layer, RestrictionArea
self.__class__._table_index = self.__class__._table_index + 1
id = self.__class__._table_index
engine = sqlahelper.get_engine()
if not self.metadata:
self.metadata = declarative_base(bind=engine).metadata
tablename = "table_%d" % id
table = Table('%s_child' % tablename,
self.metadata,
Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode),
schema='public')
table.create()
ins = table.insert().values(name=u'c1é')
c1_id = engine.connect().execute(ins).inserted_primary_key[0]
ins = table.insert().values(name=u'c2é')
c2_id = engine.connect().execute(ins).inserted_primary_key[0]
table = Table(tablename,
self.metadata,
Column('id', types.Integer, primary_key=True),
Column('child_id', types.Integer,
ForeignKey('public.%s_child.id' % tablename)),
Column('name', types.Unicode),
GeometryExtensionColumn('geom', Point(srid=21781)),
schema='public')
GeometryDDL(table)
table.create()
ins = table.insert().values(child_id=c1_id,
name='foo',
geom=func.ST_GeomFromText(
'POINT(5 45)', 21781))
engine.connect().execute(ins).inserted_primary_key[0]
ins = table.insert().values(child_id=c2_id,
name='bar',
geom=func.ST_GeomFromText(
'POINT(6 46)', 21781))
engine.connect().execute(ins).inserted_primary_key[0]
if attr_list:
ins = table.insert().values(child_id=c2_id,
name='aaa,bbb,foo',
geom=func.ST_GeomFromText(
'POINT(6 46)', 21781))
engine.connect().execute(ins).inserted_primary_key[0]
layer = Layer()
layer.id = id
layer.geoTable = tablename
layer.public = public
if exclude_properties:
layer.excludeProperties = 'name'
DBSession.add(layer)
if not public:
ra = RestrictionArea()
ra.name = u'__test_ra'
ra.layers = [layer]
ra.roles = [self.role]
ra.readwrite = True
if not none_area:
poly = 'POLYGON((4 44, 4 46, 6 46, 6 44, 4 44))'
ra.area = WKTSpatialElement(poly, srid=21781)
DBSession.add(ra)
self.layer_ids.append(self.__class__._table_index)
transaction.commit()
return id
__acl__ = [DENY_ALL]
id = Column('id', types.Integer, primary_key=True)
label = Column('label', types.Unicode)
layer_name = Column('layer_name', types.Unicode)
role_id = Column('role_id',
types.Integer,
ForeignKey(_schema + '.role.id'),
nullable=True)
role = relationship("Role")
public = Column('public', types.Boolean, server_default='true')
ts = Column('ts', TsVector)
the_geom = GeometryColumn(Geometry(srid=_srid))
params = Column('params', JSONEncodedDict, nullable=True)
GeometryDDL(FullTextSearch.__table__)
class Functionality(Base):
__label__ = _(u'functionality')
__plural__ = _(u'functionalitys')
__tablename__ = 'functionality'
__table_args__ = {'schema': _schema}
__acl__ = [
(Allow, AUTHORIZED_ROLE, ALL_PERMISSIONS),
]
id = Column(types.Integer, primary_key=True)
name = Column(types.Unicode, nullable=False, label=_(u'Name'))
value = Column(types.Unicode, nullable=False, label=_(u'Value'))
description = Column(types.Unicode)
location = GeometryColumn(Point(2), nullable=False)
user_id = Column(Integer, ForeignKey('user.id'))
def __init__(self, title, price, desc, location=''):
self.title = title
self.price = price
self.desc = desc
self.location = location
def json(self):
item_json = {
'title': self.title,
'price': self.price,
'desc': self.desc,
'user': self.user.json()
}
if deployed_on_sae:
item_json['latlng'] = {
'lat': dbsession.scalar(self.location.x),
'lng': dbsession.scalar(self.location.y)
}
return item_json
if deployed_on_sae:
GeometryDDL(Item.__table__)
#comment drop_all() after first use
#Base.metadata.drop_all()
Base.metadata.create_all()
class Item(Base):
__tablename__ = 'items'
id = Column(Integer, Sequence('item_id_seq'), primary_key=True)
title = Column(String(50), nullable=False)
price = Column(String(12), nullable=False)
desc = Column(String(200), nullable=False)
location = GeometryColumn(Point(2), nullable=False)
def __init__(self, title, price, desc, location):
self.title = title
self.price = price
self.desc = desc
self.location = location
GeometryDDL(User.__table__)
GeometryDDL(Item.__table__)
#metadata.drop_all()
metadata.create_all()
@app.route('/mainpage')
@view('static/view/index.html')
def main_page():
return {}
@app.route('/submit')
@view('static/view/submit.html')
def submit_item():
return {}
# -*- coding: utf-8 -*-
from sqlalchemy import Column, types
from geoalchemy import GeometryColumn, Point, GeometryDDL
from mapfish.sqlalchemygeom import GeometryTableMixIn
from testapp.model.meta import Session, Base
class Point(Base, GeometryTableMixIn):
__tablename__ = 'points'
id = Column(types.Integer, primary_key=True)
the_geom = GeometryColumn(Point(srid=4326))
# Triggers SQLAlchemy's DDL statement creation
GeometryDDL(Point.__table__)
from sqlalchemy import Column, types, ForeignKey
from sqlalchemy.orm import relationship
from geoalchemy import GeometryColumn, LineString, GeometryDDL
from demoapp.model.meta import Base
from demoapp.model.categories import Category
class Track(Base):
__tablename__ = 'tracks'
id = Column(types.Integer, primary_key=True)
name = Column(types.String, nullable=False)
category_id = Column(types.Integer, ForeignKey('categories.cid'))
categorie = relationship(Category)
the_geom = GeometryColumn(LineString(dimension=2, srid=4326))
GeometryDDL(Track.__table__)
from sqlalchemy import Column, types, ForeignKey
from sqlalchemy.orm import relationship
from geoalchemy import GeometryColumn, GeometryDDL
from geoalchemy import geometry
from demoapp.model.meta import Base
from demoapp.model.categories import Category
class Geometry(Base):
__tablename__ = 'geometries'
id = Column(types.Integer, primary_key=True)
name = Column(types.String, nullable=False)
category_id = Column(types.Integer, ForeignKey('categories.cid'))
category = relationship(Category)
the_geom = GeometryColumn(geometry.Geometry(dimension=2, srid=4326))
GeometryDDL(Geometry.__table__)
from sqlalchemy import Column, types, ForeignKey
from sqlalchemy.orm import relationship
from geoalchemy import GeometryColumn, Polygon, GeometryDDL
from demoapp.model.meta import Base
from demoapp.model.categories import Category
class Lake(Base):
__tablename__ = 'lakes'
id = Column(types.Integer, primary_key=True)
name = Column(types.String, nullable=False)
category_id = Column(types.Integer, ForeignKey('categories.cid'))
categorie = relationship(Category)
the_geom = GeometryColumn(Polygon(dimension=2, srid=4326))
GeometryDDL(Lake.__table__)
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy import GeometryColumn, LineString, GeometryDDL
engine = create_engine('postgres://*****:*****@localhost/gis', echo=False)
session = sessionmaker(bind=engine)()
metadata = MetaData(engine)
Base = declarative_base(metadata=metadata)
class Road(Base):
__tablename__ = 'roads'
id = Column(Integer, primary_key=True)
name = Column(Unicode, nullable=False)
width = Column(Integer)
geom = GeometryColumn(LineString(2))
GeometryDDL(Road.__table__)
key = alice.get_action_key('read article 42')
bob.send_message("Hey bob, if you want to read article 42, "+
"tell the system I gave you this key: "+key)
Bob:
api.content.show(42, auth=(alice, key))
System:
wanted_content = get_content(42)
claimed_user = get_member(alice)
if key == claimed_user.get_action_key('read article '+wanted_content.id):
print(wanted_content)
"""
return hashlib.sha1(str(self.id) + action + self.salt).hexdigest()
GeometryDDL(Member.__table__)
DDL("CREATE INDEX member_fts_idx ON member USING gin(to_tsvector('english', id || ' ' || name || ' ' || description));"
).execute_at('after-create', Member.__table__)
class User(Member):
__tablename__ = "member_user"
__mapper_args__ = {'polymorphic_identity': 'user'}
id = Column(String(32),
ForeignKey('member.id', onupdate="cascade"),
primary_key=True)
last_check = Column(
DateTime(),
nullable=False,
default=now,
sm = orm.sessionmaker(autoflush=True, autocommit=False, bind=engine)
session = orm.scoped_session(sm)
Base = declarative_base(metadata=metadata)
class Spot(Base, GeometryTableMixIn):
__tablename__ = 'spots'
spot_id = Column(Integer, primary_key=True)
spot_height = Column(Numeric(precision=10, scale=2, asdecimal=False))
spot_location = GeometryColumn(Point(2, spatial_index=False),
nullable=True)
GeometryDDL(Spot.__table__)
class Lake(Base, GeometryTableMixIn):
__tablename__ = 'lakes'
id = Column(Integer, primary_key=True)
depth = Column(Numeric(asdecimal=False))
geom = GeometryColumn(Polygon(2))
GeometryDDL(Lake.__table__)
class Test(unittest.TestCase):
def setUp(self):
class Feature(Base):
"""A GeoNames geogaphical feature.
"""
__tablename__ = "feature"
geonameid = Column(Integer, primary_key=True)
name = Column(Unicode)
asciiname = Column(Unicode)
alternatenames = Column(Unicode)
latitude = Column(Float)
longitude = Column(Float)
feature_class = Column("feature class", Unicode)
feature_code = Column("feature code", Unicode)
country_code = Column("country code", Unicode)
cc2 = Column(Unicode)
admin1_code = Column("admin1 code", Unicode)
admin2_code = Column("admin2 code", Unicode)
admin3_code = Column("admin3 code", Unicode)
admin4_code = Column("admin4 code", Unicode)
population = Column(Integer)
elevation = Column(Integer)
gtopo30 = Column(Integer)
timezone = Column(Unicode)
modification_date = Column("modification date", Date)
uf = Column(Unicode(2))
position = GeometryColumn(Point(2), comparator=SQLiteComparator)
GeometryDDL(Feature.__table__)
# -*- coding: utf-8 -*-
from sqlalchemy import Column, types
from geoalchemy import GeometryColumn, Polygon, GeometryDDL
from mapfish.sqlalchemygeom import GeometryTableMixIn
from testapp.model.meta import Session, Base
class Area(Base, GeometryTableMixIn):
__tablename__ = 'areas'
# The following fragment needed to be commented out in order to
# make table creation succeed from: paster setup-app development.ini
# Additionally, this file needs to be imported in websetup.py
#__table_args__ = {
# "autoload": True,
# "autoload_with": Session.bind
#}
# A primary key column is required by SQLAlchemy to create DDL
# statements
id = Column(types.Integer, primary_key=True)
the_geom = GeometryColumn(Polygon(srid=4326))
# Triggers SQLAlchemy's DDL statement creation
GeometryDDL(Area.__table__)
