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