def setup_metadata(self, tablename=None):
metadata = sa.MetaData(schema='vector_layer' if tablename else None)
geom_fldtype = _GEOM_TYPE_2_DB[self.geometry_type]
class model(object):
def __init__(self, **kwargs):
for k, v in kwargs.iteritems():
setattr(self, k, v)
table = sa.Table(
tablename if tablename else ('lvd_' + str(uuid.uuid4().hex)),
metadata, sa.Column('id', sa.Integer, primary_key=True),
ga.GeometryExtensionColumn(
'geom', _GEOM_TYPE_2_GA[geom_fldtype](2, srid=self.srs_id)),
*map(
lambda
(fld): sa.Column(fld.key, _FIELD_TYPE_2_DB[fld.datatype]),
self.fields))
ga.GeometryDDL(table)
orm.mapper(model, table)
self.metadata = metadata
self.table = table
self.model = model
def upgrade():
t = op.create_table(
'ride_tracks',
sa.Column('ride_id',sa.BigInteger, sa.ForeignKey('rides.id', ondelete="cascade"), primary_key=True, autoincrement=False),
ga.GeometryExtensionColumn('gps_track', ga.LineString(2), nullable=False)
)
ga.GeometryDDL(t)
op.add_column('rides', sa.Column('track_fetched', sa.Boolean, default=None, nullable=True))
op.execute('update rides set track_fetched = false where manual = false')
ride_rain = sa.Column(sa.Boolean, default=False, nullable=False)
ride_snow = sa.Column(sa.Boolean, default=False, nullable=False)
day_temp_min = sa.Column(sa.Float, nullable=True)
day_temp_max = sa.Column(sa.Float, nullable=True)
sunrise = sa.Column(sa.Time, nullable=True)
sunset = sa.Column(sa.Time, nullable=True)
def __repr__(self):
return '<{0} ride_id={1}>'.format(self.__class__.__name__, self.id,
self.segment_name)
# Setup Geometry columns
ga.GeometryDDL(RideGeo.__table__)
ga.GeometryDDL(RideTrack.__table__)
# Setup a Pool event to get MySQL to use strict SQL mode ...
# (This is the default now in 5.7+, so not necessary.)
def _set_sql_mode(dbapi_con, connection_record):
# dbapi_con.cursor().execute("SET sql_mode = 'STRICT_TRANS_TABLES';")
pass
sa.event.listen(Pool, 'connect', _set_sql_mode)
# Create VIEWS that may be helpful.
_v_daily_scores_create = sa.DDL("""
area_geojson = db.column_property(db.func.AsGeoJSON(db.literal_column('area')), deferred=True)
@classmethod
def containing(cls, coords):
"""
Return CoveredAreas containing point (lat,lon)
"""
return cls.query.filter(
cls.area != None,
cls.area.gcontains(db.func.MakePoint(coords[1], coords[0])) == 1
)
def __repr__(self):
return u'<CoveredArea %r>' % (self.name,)
geo.GeometryDDL(CoveredArea.__table__)
class RegisteredOffice(db.Model):
__tablename__ = 'registered_offices'
id = db.Column(db.Integer, primary_key=True)
isp_id = db.Column(db.Integer, db.ForeignKey('isp.id'))
point = geo.GeometryColumn(geo.Point(0))
geo.GeometryDDL(RegisteredOffice.__table__)
@event.listens_for(db.metadata, 'before_create')
def init_spatialite_metadata(target, conn, **kwargs):
conn.execute('SELECT InitSpatialMetaData(1)')
class Imagery(Base):
"""
Table for image locations/geodata
"""
__tablename__ = "imagery"
id = Column(Integer, primary_key=True)
timestamp = Column(DateTime) # Store this for validation
path = Column(String)
matched_to = Column(
Integer, ForeignKey('shapedata.id')) # Store matches for later use
geom = geoalchemy.GeometryColumn(
geoalchemy.Polygon(2)) # We will store bounding boxes here
class Shapes(Base):
"""
Table for image locations/geodata
"""
__tablename__ = "shapedata"
id = Column(Integer, primary_key=True)
timestamp = Column(DateTime) # Store this for validation
path = Column(String)
geom = geoalchemy.GeometryColumn(
geoalchemy.Polygon(2)) # Actual polygon from filesystem
geoalchemy.GeometryDDL(Imagery.__table__)
geoalchemy.GeometryDDL(Shapes.__table__)
'email': orm.column_property(sms_addresses_table.c.email, comparator_factory=LowerCaseComparator),
})
orm.mapper(Feature, features_table, properties={
'owner': orm.relation(Person, backref='features'),
'geometry': geoalchemy.GeometryColumn(features_table.c.geometry, comparator=geoalchemy.postgis.PGComparator),
'tags': orm.relation(Tag, secondary=feature_tags_table, backref='features'),
})
orm.mapper(Tag, tags_table, properties={
'text': orm.column_property(tags_table.c.text, comparator_factory=LowerCaseComparator),
})
orm.mapper(Map, maps_table)
# DDLs
geoalchemy.GeometryDDL(features_table)
# Helpers
def validateSRID(srid):
'Make sure we have a valid SRID'
if not srid:
raise GeoRegistryError('Must specify spatial reference srid')
try:
srid = int(srid)
except ValueError:
raise GeoRegistryError('Could not parse srid=%s as an integer' % srid)
result = Session.execute('SELECT proj4text FROM spatial_ref_sys WHERE srid=:srid', dict(srid=srid)).fetchone()
if not result:
raise GeoRegistryError('Could not recognize srid=%s' % srid)