class RideGeo(db.Model):
__tablename__ = 'ride_geo'
__table_args__ = {
'mysql_engine': 'MyISAM',
'mysql_charset': 'utf8'
} # MyISAM for spatial indexes
ride_id = sa.Column(sa.BigInteger,
sa.ForeignKey('rides.id'),
primary_key=True)
start_geo = ga.GeometryColumn(ga.Point(2), nullable=False)
end_geo = ga.GeometryColumn(ga.Point(2), nullable=False)
def __repr__(self):
return '<{0} ride_id={1} start={2}>'.format(self.__class__.__name__,
self.ride_id,
self.start_geo)
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
class Countries(Base):
"""Model for countries table"""
__tablename__ = u'countries'
gid = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100))
# Example how to map geometry column:
geom = ga.GeometryColumn(ga.Geometry(2))
def get_as_dict(self, depth=1):
return serialize.get_as_dict(self, depth=depth)
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 Feature(base):
__tablename__ = 'feature'
#__table_args__ ={ 'schema':'gazetteer'}
# Not using autoload as creates issues with geometry at the moment :-(
feat_id = Column(Integer, primary_key=True)
feat_type = Column(Unicode(4))
status = Column(Unicode(4))
description = Column(Unicode)
ref_point = ga.GeometryColumn(ga.Point(2, 4167), comparator=PGComparator)
updated_by = Column(Unicode(64))
update_date = Column(DateTime)
names = relationship('Name',
backref='feature',
cascade="all, delete-orphan")
annotations = relationship('FeatureAnnotation',
backref='feature',
cascade="all, delete-orphan")
def preferredName(self):
id = Database.scalar(
Database.func.gazetteer.gaz_preferredNameId(self.feat_id))
return Name.get(id)
@staticmethod
def get(id):
return Database.query(Feature).get(id)
def location(self, srid=4167):
if Database.session().is_modified(self):
raise RuntimeError('Cannot query location of modifed feature')
result = Database.querysql('''
select st_x(pt),st_y(pt) from
(select st_transform(ref_point,:srid) as pt from gazetteer.feature
where feat_id=:id) as ptq
''',
id=self.feat_id,
srid=srid).fetchone()
return [float(result[0]), float(result[1])]
def setLocation(self, xy, srid=4167):
wkt = Database.scalar(
'select astext(st_transform(st_setsrid(st_point(:x,:y),:srid),4167))',
x=xy[0],
y=xy[1],
srid=srid)
self.ref_point = ga.WKTSpatialElement(wkt)
def __str__(self):
return 'Feature<' + str(self.feat_id) + '>'
class LossMapData(Base):
__tablename__ = "loss_map_data"
__table_args__ = {"schema": "uiapi"}
id = sa.Column(sa.Integer, primary_key=True)
output_id = sa.Column(sa.Integer, sa.ForeignKey("uiapi.output.id"),
nullable=False)
output = relationship("Output", backref="lossmapdata_set")
location = ga.GeometryColumn(ga.Point(2), nullable=False)
value = sa.Column(sa.Float, nullable=False)
def __repr__(self):
return(":loss_map_data: %s, %s" % (
self.id, self.value))
class Cities(Base):
"""Model for cities table"""
__tablename__ = u'cities'
# Note, all models MUST have primary key column
gid = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100))
#example how to map column name to variable name that is different
country = sa.Column("sov0name", sa.String(100))
geom = ga.GeometryColumn(ga.Geometry(2))
def get_as_dict(self, depth=1):
"""Return instance as Python dictionary that is JSON serializable (easy to return in flask api)"""
return serialize.get_as_dict(self, depth=depth)
class RideTrack(db.Model):
__tablename__ = 'ride_tracks'
__table_args__ = {
'mysql_engine': 'MyISAM',
'mysql_charset': 'utf8'
} # MyISAM for spatial indexes
ride_id = sa.Column(sa.BigInteger,
sa.ForeignKey('rides.id'),
primary_key=True)
gps_track = ga.GeometryColumn(ga.LineString(2), nullable=False)
elevation_stream = sa.Column(JSONEncodedText, nullable=True)
time_stream = sa.Column(JSONEncodedText, nullable=True)
def __repr__(self):
return '<{0} ride_id={1}>'.format(self.__class__.__name__,
self.ride_id)
class Message(SQLModel):
"""Encapsulate a geolocated message."""
__tablename__ = 'message'
query = Session.query_property()
id = Column(Integer, primary_key=True)
content = Column(UnicodeText)
latitude = Column(Float, nullable=False)
longitude = Column(Float, nullable=False)
location = geoalchemy.GeometryColumn(Geography(2), comparator=PGComparator)
def update_location(self):
"""Update `self.location` with a point value derived from
`self.latitude` and `self.longitude`.
Note that the point will be `autocast`_ to geography type on saving:
> Standard geometry type data will autocast to geography if it is of
SRID 4326.
`autocast`: http://postgis.refractions.net/docs/ch04.html#Geography_Basics
"""
self.location = "POINT(%0.8f %0.8f)" % (self.longitude, self.latitude)
@classmethod
def within_clause(cls, latitude, longitude, distance):
"""Return a within clause that explicitly casts the `latitude` and
`longitude` provided to geography type.
"""
attr = '%s.location' % cls.__tablename__
point = 'POINT(%0.8f %0.8f)' % (longitude, latitude)
location = "ST_GeographyFromText(E'SRID=4326;%s')" % point
return 'ST_DWithin(%s, %s, %d)' % (attr, location, distance)
def __repr__(self):
return self.content
class CoveredArea(db.Model):
__tablename__ = 'covered_areas'
id = db.Column(db.Integer, primary_key=True)
isp_id = db.Column(db.Integer, db.ForeignKey('isp.id'))
name = db.Column(db.String)
area = geo.GeometryColumn(geo.MultiPolygon(2))
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,)
class OqParams(Base):
__tablename__ = "oq_params"
__table_args__ = {"schema": "uiapi"}
id = sa.Column(sa.Integer, primary_key=True)
job_type = sa.Column(sa.Enum("classical", "event_based", "deterministic",
native_enum=False),
nullable=False, default="classical")
upload_id = sa.Column(sa.Integer, sa.ForeignKey("uiapi.upload.id"),
nullable=False)
upload = relationship("Upload")
region_grid_spacing = sa.Column(sa.Float, nullable=False)
min_magnitude = sa.Column(sa.Float)
investigation_time = sa.Column(sa.Float)
component = sa.Column(sa.Enum("average", "gmroti50", native_enum=False),
nullable=False)
imt = sa.Column(sa.Enum("pga", "sa", "pgv", "pgd", native_enum=False),
nullable=False)
period = sa.Column(sa.Float)
truncation_type = sa.Column(sa.Enum("none", "onesided", "twosided",
native_enum=False),
nullable=False)
truncation_level = sa.Column(sa.Float, nullable=False, default=3.0)
reference_vs30_value = sa.Column(sa.Float, nullable=False)
imls = sa.Column(postgresql.ARRAY(sa.Float),
doc="Intensity measure levels")
poes = sa.Column(postgresql.ARRAY(sa.Float),
doc="Probabilities of exceedence")
realizations = sa.Column(sa.Integer, doc="Number of logic tree samples")
histories = sa.Column(sa.Integer, doc="Number of seismicity histories")
gm_correlated = sa.Column(sa.Boolean, doc="Ground motion correlation flag")
region = ga.GeometryColumn(ga.Polygon(2), nullable=False)
last_update = sa.Column(sa.DateTime, sa.FetchedValue())
def __repr__(self):
return(":params: %s, %s (:upload: %s)" % (
self.id, self.job_type, self.upload.id))
def location(self):
return geoalchemy.GeometryColumn(Geography(), comparator=PGComparator)
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))
'username': orm.column_property(people_table.c.username, comparator_factory=LowerCaseComparator),
'nickname': orm.column_property(people_table.c.nickname, comparator_factory=LowerCaseComparator),
'email': orm.column_property(people_table.c.email, comparator_factory=LowerCaseComparator),
'sms_addresses': orm.relation(SMSAddress),
})
orm.mapper(PersonCandidate, person_candidates_table, properties={
'username': orm.column_property(person_candidates_table.c.username, comparator_factory=LowerCaseComparator),
'nickname': orm.column_property(person_candidates_table.c.nickname, comparator_factory=LowerCaseComparator),
'email': orm.column_property(person_candidates_table.c.email, comparator_factory=LowerCaseComparator),
})
orm.mapper(SMSAddress, sms_addresses_table, properties={
'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
