class PredictionTile(db.Model):
""" Store individual tile predictions """
__tablename__ = 'prediction_tiles'
id = db.Column(db.Integer, primary_key=True)
prediction_id = db.Column(db.BigInteger,
db.ForeignKey('predictions.id',
name='fk_predictions'),
nullable=False)
quadkey = db.Column(db.String, nullable=False)
centroid = db.Column(Geometry('POINT', srid=4326))
predictions = db.Column(postgresql.JSONB, nullable=False)
prediction_tiles_quadkey_idx = db.Index(
'prediction_tiles_quadkey_idx',
'prediction_tiles.quadkey',
postgresql_ops={'quadkey': 'text_pattern_ops'})
@staticmethod
def get_tiles_by_quadkey(prediction_id: int, quadkeys: tuple, zoom: int):
return db.session.query(
func.substr(PredictionTile.quadkey, 1, zoom).label('qaudkey'),
func.avg(
cast(
cast(PredictionTile.predictions['ml_prediction'],
sqlalchemy.String),
sqlalchemy.Float)).label('ml_prediction'),
func.avg(
cast(
cast(PredictionTile.predictions['osm_building_area'],
sqlalchemy.String),
sqlalchemy.Float)).label('osm_building_area')).filter(
PredictionTile.prediction_id == prediction_id).filter(
func.substr(PredictionTile.quadkey, 1,
zoom).in_(quadkeys)).group_by(
func.substr(
PredictionTile.quadkey, 1,
zoom)).all()
@staticmethod
def get_aggregate_for_polygon(prediction_id: int, polygon: str):
return db.session.query(
func.avg(
cast(
cast(PredictionTile.predictions['ml_prediction'],
sqlalchemy.String),
sqlalchemy.Float)).label('ml_prediction'),
func.avg(
cast(
cast(PredictionTile.predictions['osm_building_area'],
sqlalchemy.String),
sqlalchemy.Float)).label('osm_building_area')).filter(
PredictionTile.prediction_id == prediction_id).filter(
ST_Within(PredictionTile.centroid,
ST_GeomFromText(polygon)) ==
'True').one()
class AOI(db.Model):
__tablename__ = "model_aoi"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String, nullable=False)
model_id = db.Column(db.BigInteger,
db.ForeignKey("projects.id", name="fk_projects"),
nullable=False)
pred_id = db.Column(
db.BigInteger,
db.ForeignKey("predictions.id", name="fk_predictions"),
nullable=False,
)
bounds = db.Column(Geometry("POLYGON", srid=4326), nullable=False)
def create(self, payload: dict):
self.model_id = payload["model_id"]
self.pred_id = payload["pred_id"]
self.name = payload["name"]
bounds = payload["bounds"].split(",")
self.bounds = (
"SRID=4326;POLYGON(({0} {1},{0} {3},{2} {3},{2} {1},{0} {1}))".
format(bounds[0], bounds[1], bounds[2], bounds[3]))
db.session.add(self)
db.session.commit()
return self
def list(model_id: int, pred_id: int):
filters = [AOI.model_id == model_id]
if pred_id is not None:
filters.append(AOI.pred_id == pred_id)
return AOI.query.filter(*filters)
def as_dto(self):
dto = AOIDTO()
dto.id = self.id
dto.pred_id = self.pred_id
dto.model_id = self.model_id
dto.name = self.name
dto.bounds = ",".join(
map(str,
shapely.wkb.loads(self.bounds.desc, hex=True).bounds))
return dto
class Task(db.Model):
__tablename__ = 'tasks'
id = db.Column(db.Integer, primary_key=True)
pred_id = db.Column(
db.BigInteger,
db.ForeignKey('predictions.id', name='fk_predictions'),
nullable=False
)
type = db.Column(
db.String,
nullable=False
)
created = db.Column(db.DateTime, default=timestamp, nullable=False)
batch_id = db.Column(db.String)
def create(self, pred_id: int, type: str, batch_id: str):
self.pred_id = pred_id
self.type = type
self.batch_id = batch_id
db.session.add(self)
db.session.commit()
return self
def delete(self):
db.session.delete(self)
db.session.commit()
def get(task_id: int):
return Task.query.get(task_id)
def list(pred_id: int, task_type: str):
filters = []
if pred_id is not None:
filters.append(Task.pred_id == pred_id)
if type is not None:
filters.append(Task.type == task_type)
return Task.query.filter(*filters)
def as_dto(self):
task_dto = TaskDTO()
task_dto.id = self.id
task_dto.pred_id = self.pred_id
task_dto.type = self.type
task_dto.created = self.created
task_dto.batch_id = self.batch_id
return task_dto
class User(UserMixin, db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String, unique=True)
password = db.Column(db.String)
name = db.Column(db.String)
def password_check(self, test):
results = db.session.execute(
text('''
SELECT
password = crypt(:test, password)
FROM
users
WHERE
id = :uid
'''), {
'test': test,
'uid': self.id
}).fetchall()
return results[0][0]
class Project(db.Model):
""" Describes an ML model registered with the service """
__tablename__ = "projects"
id = db.Column(db.Integer, primary_key=True)
created = db.Column(db.DateTime, default=timestamp, nullable=False)
tags = db.Column(MutableList.as_mutable(postgresql.JSONB), nullable=False)
name = db.Column(db.String, unique=True)
source = db.Column(db.String)
archived = db.Column(db.Boolean)
project_url = db.Column(db.String)
access = db.Column(db.String)
notes = db.Column(db.String)
predictions = db.relationship(Prediction,
backref="projects",
cascade="all, delete-orphan",
lazy="dynamic")
def create(self, dto: ProjectDTO):
""" Creates and saves the current model to the DB """
self.name = dto.name
self.source = dto.source
self.archived = False
self.tags = dto.tags
self.access = dto.access
self.project_url = dto.project_url
self.notes = dto.notes
db.session.add(self)
db.session.commit()
return self
def save(self):
""" Save changes to db"""
db.session.commit()
@staticmethod
def get(model_id: int):
"""
Gets specified ML Model
:param model_id: ml model ID in scope
:return ML Model if found otherwise None
"""
return Project.query.get(model_id)
@staticmethod
def get_all(uid: int, model_filter: str, model_archived: bool):
"""
Get all models in the database
"""
return Project.query.filter(
Project.name.ilike(model_filter + "%"),
Project.archived == model_archived,
or_(
Project.access == "public",
and_(ProjectAccess.uid == uid,
ProjectAccess.model_id == Project.id),
),
).all()
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
def as_dto(self, users=None):
"""
Convert the model to it's dto
"""
dto = ProjectDTO()
dto.model_id = self.id
dto.name = self.name
dto.tags = self.tags
dto.created = self.created
dto.source = self.source
dto.archived = self.archived
dto.project_url = self.project_url
dto.access = self.access
dto.notes = self.notes
if users is not None:
dto.users = users
return dto
def update(self, dto: ProjectDTO):
""" Updates an ML model """
self.id = dto.model_id
self.name = dto.name
self.source = dto.source
self.project_url = dto.project_url
self.archived = dto.archived
self.tags = dto.tags
self.access = dto.access
self.notes = dto.notes
db.session.commit()
class ProjectAccess(db.Model):
__tablename__ = "projects_access"
id = db.Column(db.Integer, primary_key=True)
model_id = db.Column(db.BigInteger,
db.ForeignKey("projects.id", name="fk_projects"),
nullable=False)
uid = db.Column(db.BigInteger,
db.ForeignKey("users.id", name="fk_users"),
nullable=False)
access = db.Column(db.String, nullable=False)
@staticmethod
def get(access_id: int):
"""
Gets specified ML Model
:param access_id: access object to get
:return ML Model if found otherwise None
"""
return ProjectAccess.query.get(access_id)
@staticmethod
def get_uid(model_id: int, access_id: int):
"""
Gets specified ML Model
:param access_id: access object to get
:return ML Model if found otherwise None
"""
return ProjectAccess.query.filter(
ProjectAccess.id == access_id,
ProjectAccess.model_id == model_id).one_or_none()
@staticmethod
def list_update(model_id: int, current_users: list, new_users: list):
uids = []
for user in new_users:
user["model_id"] = model_id
# Update all new users
for user in new_users:
uids.append(int(user.get("uid")))
user["model_id"] = model_id
access = ProjectAccess.get_uid(model_id, user.get("id"))
if not access:
access = ProjectAccess()
access.create(user)
else:
access.update(user)
for user in current_users:
if user.get("uid") not in uids:
access = ProjectAccess.get_uid(model_id, user.get("id"))
access.delete()
@staticmethod
def list(model_id: int):
query = db.session.query(
ProjectAccess.id,
ProjectAccess.uid,
User.name,
ProjectAccess.model_id,
ProjectAccess.access,
).filter(ProjectAccess.model_id == model_id,
User.id == ProjectAccess.uid)
users = []
for access in query.all():
users.append({
"id": access[0],
"uid": access[1],
"name": access[2],
"model_id": access[3],
"access": access[4],
})
return users
def create(self, dto: ProjectAccessDTO):
""" Creates and saves the current project access dto to the DB """
self.model_id = dto.get("model_id")
self.uid = dto.get("uid")
self.access = dto.get("access")
db.session.add(self)
db.session.commit()
return self
def update(self, dto: ProjectAccessDTO):
""" Updates an project access """
self.access = dto["access"]
db.session.commit()
def delete(self):
""" Deletes the current project access from the DB """
db.session.delete(self)
db.session.commit()
class PredictionTile(db.Model):
""" Store individual tile predictions """
__tablename__ = "prediction_tiles"
id = db.Column(db.Integer, primary_key=True)
prediction_id = db.Column(
db.BigInteger,
db.ForeignKey("predictions.id", name="fk_predictions"),
nullable=False,
)
quadkey = db.Column(db.String, nullable=True)
geom = db.Column(Geometry("POLYGON", srid=4326), nullable=False)
predictions = db.Column(postgresql.JSONB, nullable=False)
validity = db.Column(MutableDict.as_mutable(postgresql.JSONB),
nullable=True)
prediction_tiles_quadkey_idx = db.Index(
"prediction_tiles_quadkey_idx",
"prediction_tiles.quadkey",
postgresql_ops={"quadkey": "text_pattern_ops"},
)
@staticmethod
def get(predictiontile_id: int):
db.session.query(
PredictionTile.id,
PredictionTile.prediction_id,
PredictionTile.validity,
).filter(PredictionTile.id == predictiontile_id)
return PredictionTile.query.get(predictiontile_id)
def update(self, validity):
self.validity = validity
db.session.commit()
@staticmethod
def inferences(prediction_id: int):
results = db.session.execute(
text("""
SELECT
DISTINCT jsonb_object_keys(predictions)
FROM
prediction_tiles
WHERE
prediction_id = :pred
"""),
{
"pred": prediction_id
},
).fetchall()
inferences = []
for res in results:
inferences.append(res[0])
return inferences
@staticmethod
def count(prediction_id: int):
return (db.session.query(
func.count(PredictionTile.geom).label("count")).filter(
PredictionTile.prediction_id == prediction_id).one())
@staticmethod
def bbox(prediction_id: int):
result = db.session.execute(
text("""
SELECT
ST_Extent(geom)
FROM
prediction_tiles
WHERE
prediction_id = :pred
"""),
{
"pred": prediction_id
},
).fetchone()
bbox = []
for corners in result[0].replace("BOX(", "").replace(")",
"").split(" "):
for corner in corners.split(","):
bbox.append(float(corner))
return bbox
def mvt(prediction_id: int, z: int, x: int, y: int):
grid = mercantile.xy_bounds(x, y, z)
result = db.session.execute(
text("""
SELECT
ST_AsMVT(q, 'data', 4096, 'geom', 'id') AS mvt
FROM (
SELECT
p.id AS id,
quadkey AS quadkey,
predictions || COALESCE(v.validity, '{}'::JSONB) AS props,
ST_AsMVTGeom(geom, ST_Transform(ST_MakeEnvelope(:minx, :miny, :maxx, :maxy, 3857), 4326), 4096, 256, false) AS geom
FROM
prediction_tiles AS p
LEFT JOIN (
SELECT
id,
JSONB_Object_Agg('v_'||key, value) AS validity
FROM
prediction_tiles,
jsonb_each(validity)
GROUP BY
id
) AS v ON p.id = v.id
WHERE
p.prediction_id = :pred
AND ST_Intersects(p.geom, ST_Transform(ST_MakeEnvelope(:minx, :miny, :maxx, :maxy, 3857), 4326))
) q
"""),
{
"pred": prediction_id,
"minx": grid[0],
"miny": grid[1],
"maxx": grid[2],
"maxy": grid[3],
},
).fetchone()
return bytes(result.values()[0])
@staticmethod
def get_tiles_by_quadkey(prediction_id: int, quadkeys: tuple, zoom: int):
return (db.session.query(
func.substr(PredictionTile.quadkey, 1, zoom).label("qaudkey"),
func.avg(
cast(
cast(
PredictionTile.predictions["ml_prediction"],
sqlalchemy.String,
),
sqlalchemy.Float,
)).label("ml_prediction"),
func.avg(
cast(
cast(
PredictionTile.predictions["osm_building_area"],
sqlalchemy.String,
),
sqlalchemy.Float,
)).label("osm_building_area"),
).filter(PredictionTile.prediction_id == prediction_id).filter(
func.substr(PredictionTile.quadkey, 1,
zoom).in_(quadkeys)).group_by(
func.substr(PredictionTile.quadkey, 1,
zoom)).all())
class User(UserMixin, db.Model):
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String, unique=True)
password = db.Column(db.String)
name = db.Column(db.String)
access = db.Column(db.String)
def create(self, dto: UserDTO):
self.name = dto.name
self.email = dto.email
self.access = dto.access
self.password = dto.password
results = db.session.execute(
text("""
INSERT INTO users (name, email, access, password) VALUES (
:name,
:email,
:access,
crypt(:password, gen_salt('bf', 10))
) RETURNING id
"""),
{
"name": self.name,
"email": self.email,
"access": self.access,
"password": self.password,
},
).fetchall()
db.session.commit()
self.id = results[0][0]
return self
def list(user_filter: str, limit: int, page: int):
"""
Get all users in the database
"""
results = db.session.execute(
text("""
SELECT
count(*) OVER() AS count,
id,
name,
access,
email
FROM
users
WHERE
name iLIKE '%'||:filter||'%'
OR email iLIKE '%'||:filter||'%'
ORDER BY
id ASC
LIMIT
:limit
OFFSET
:page
"""),
{
"limit": limit,
"page": page * limit,
"filter": user_filter
},
).fetchall()
return {
"total":
results[0][0] if len(results) > 0 else 0,
"users": [{
"id": u[1],
"name": u[2],
"access": u[3],
"email": u[4]
} for u in results],
}
return results
def password_check(self, test):
results = db.session.execute(
text("""
SELECT
password = crypt(:test, password)
FROM
users
WHERE
id = :uid
"""),
{
"test": test,
"uid": self.id
},
).fetchall()
return results[0][0]
class Prediction(db.Model):
""" Predictions from a model at a given time """
__tablename__ = "predictions"
id = db.Column(db.Integer, primary_key=True)
created = db.Column(db.DateTime, default=timestamp, nullable=False)
# One of 'prediction' or 'training' - On the backend these are essentially the same
# but on the frontend, a training hint will not prompt for model upload
hint = db.Column(db.String, nullable=False)
imagery_id = db.Column(db.BigInteger, nullable=True)
model_id = db.Column(db.BigInteger,
db.ForeignKey("projects.id", name="fk_models"),
nullable=False)
version = db.Column(db.String, nullable=False)
docker_url = db.Column(db.String)
tile_zoom = db.Column(db.Integer, nullable=False)
log_link = db.Column(db.String)
model_link = db.Column(db.String)
docker_link = db.Column(db.String)
save_link = db.Column(db.String)
tfrecord_link = db.Column(db.String)
checkpoint_link = db.Column(db.String)
inf_list = db.Column(db.String)
inf_type = db.Column(db.String)
inf_binary = db.Column(db.Boolean)
inf_supertile = db.Column(db.Boolean)
def create(self, prediction_dto: PredictionDTO):
""" Creates and saves the current model to the DB """
self.imagery_id = prediction_dto.imagery_id
self.model_id = prediction_dto.model_id
self.hint = prediction_dto.hint
self.version = prediction_dto.version
self.docker_url = prediction_dto.docker_url
self.tile_zoom = prediction_dto.tile_zoom
self.inf_list = prediction_dto.inf_list
self.inf_type = prediction_dto.inf_type
self.inf_binary = prediction_dto.inf_binary
self.inf_supertile = prediction_dto.inf_supertile
db.session.add(self)
db.session.commit()
def link(self, update: dict):
""" Update prediction to include asset links """
if update.get("logLink") is not None:
self.log_link = update["logLink"]
if update.get("modelLink") is not None:
self.model_link = update["modelLink"]
if update.get("dockerLink") is not None:
self.docker_link = update["dockerLink"]
if update.get("saveLink") is not None:
self.save_link = update["saveLink"]
if update.get("tfrecordLink") is not None:
self.tfrecord_link = update["tfrecordLink"]
if update.get("checkpointLink") is not None:
self.checkpoint_link = update["checkpointLink"]
db.session.commit()
def save(self):
""" Save changes to db"""
db.session.commit()
def export(self):
return (db.session.query(
PredictionTile.id,
PredictionTile.quadkey,
ST_AsGeoJSON(PredictionTile.geom).label("geometry"),
PredictionTile.predictions,
PredictionTile.validity,
).filter(PredictionTile.prediction_id == self.id).yield_per(100))
@staticmethod
def get(prediction_id: int):
"""
Get prediction with the given ID
:param prediction_id
:return prediction if found otherwise None
"""
db.session.query(
Prediction.id,
Prediction.hint,
Prediction.created,
Prediction.docker_url,
Prediction.model_id,
Prediction.tile_zoom,
Prediction.version,
Prediction.log_link,
Prediction.model_link,
Prediction.docker_link,
Prediction.save_link,
Prediction.tfrecord_link,
Prediction.checkpoint_link,
Prediction.inf_list,
Prediction.inf_type,
Prediction.inf_binary,
Prediction.inf_supertile,
Prediction.imagery_id,
).filter(Prediction.id == prediction_id)
return Prediction.query.get(prediction_id)
@staticmethod
def get_predictions_by_model(model_id: int):
"""
Gets predictions for a specified ML Model
:param model_id: ml model ID in scope
:return predictions if found otherwise None
"""
query = db.session.query(
Prediction.id,
Prediction.hint,
Prediction.created,
Prediction.docker_url,
Prediction.model_id,
Prediction.tile_zoom,
Prediction.version,
Prediction.log_link,
Prediction.model_link,
Prediction.docker_link,
Prediction.save_link,
Prediction.tfrecord_link,
Prediction.checkpoint_link,
Prediction.inf_list,
Prediction.inf_type,
Prediction.inf_binary,
Prediction.inf_supertile,
Prediction.imagery_id,
).filter(Prediction.model_id == model_id)
return query.all()
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
@staticmethod
def as_dto(prediction):
""" Static method to convert the prediction result as a schematic """
prediction_dto = PredictionDTO()
prediction_dto.prediction_id = prediction[0]
prediction_dto.hint = prediction[1]
prediction_dto.created = prediction[2]
prediction_dto.docker_url = prediction[3]
prediction_dto.model_id = prediction[4]
prediction_dto.tile_zoom = prediction[5]
prediction_dto.version = prediction[6]
prediction_dto.log_link = prediction[7]
prediction_dto.model_link = prediction[8]
prediction_dto.docker_link = prediction[9]
prediction_dto.save_link = prediction[10]
prediction_dto.tfrecord_link = prediction[11]
prediction_dto.checkpoint_link = prediction[12]
prediction_dto.inf_list = prediction[13]
prediction_dto.inf_type = prediction[14]
prediction_dto.inf_binary = prediction[15]
prediction_dto.inf_supertile = prediction[16]
prediction_dto.imagery_id = prediction[17]
return prediction_dto
class Integration(db.Model):
""" Store an integration for a given model """
__tablename__ = 'integration'
id = db.Column(db.Integer, primary_key=True)
model_id = db.Column(db.BigInteger,
db.ForeignKey('ml_models.id', name='fk_models'),
nullable=False)
integration = db.Column(db.String, nullable=False)
name = db.Column(db.String, nullable=False)
url = db.Column(db.String, nullable=False)
auth = db.Column(db.String, nullable=True)
def create(self, model_id: int, integration: dict):
""" Creates and saves the current model to the DB """
self.model_id = model_id
self.integration = integration.get("integration")
self.auth = integration.get("auth")
self.name = integration.get("name")
self.url = integration.get("url")
db.session.add(self)
db.session.commit()
return self
def as_dto(self):
integration_dto = IntegrationDTO()
integration_dto.id = self.id
integration_dto.model_id = self.model_id
integration_dto.integration = self.integration
integration_dto.name = self.name
integration_dto.url = self.url
return integration_dto
def get(integration_id: int):
query = db.session.query(
Integration.id, Integration.name, Integration.integration,
Integration.url,
Integration.model_id).filter(Integration.id == integration_id)
return Integration.query.get(integration_id)
def get_secrets(integration_id: int):
query = db.session.query(
Integration.id, Integration.auth, Integration.name,
Integration.integration, Integration.url,
Integration.model_id).filter(Integration.id == integration_id)
return Integration.query.get(integration_id)
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
def list(model_id: int):
query = db.session.query(
Integration.id, Integration.name, Integration.url,
Integration.integration).filter(Integration.model_id == model_id)
integrations = []
for integration in query.all():
integrations.append({
"id": integration[0],
"name": integration[1],
"url": integration[2],
"integration": integration[3]
})
return integrations
def update(self, update: dict):
if update.get("name") is not None:
self.name = update["name"]
if update.get("auth") is not None:
self.auth = update["auth"]
if update.get("url") is not None:
self.url = update["url"]
db.session.commit()
class MLModel(db.Model):
""" Describes an ML model registered with the service """
__tablename__ = 'ml_models'
id = db.Column(db.Integer, primary_key=True)
created = db.Column(db.DateTime, default=timestamp, nullable=False)
tags = db.Column(MutableList.as_mutable(postgresql.JSONB), nullable=False)
name = db.Column(db.String, unique=True)
source = db.Column(db.String)
archived = db.Column(db.Boolean)
project_url = db.Column(db.String)
predictions = db.relationship(Prediction,
backref='ml_models',
cascade='all, delete-orphan',
lazy='dynamic')
def create(self, ml_model_dto: MLModelDTO):
""" Creates and saves the current model to the DB """
self.name = ml_model_dto.name
self.source = ml_model_dto.source
self.archived = False
self.tags = ml_model_dto.tags
self.project_url = ml_model_dto.project_url
db.session.add(self)
db.session.commit()
return self
def save(self):
""" Save changes to db"""
db.session.commit()
@staticmethod
def get(model_id: int):
"""
Gets specified ML Model
:param model_id: ml model ID in scope
:return ML Model if found otherwise None
"""
return MLModel.query.get(model_id)
@staticmethod
def get_all(model_filter: str, model_archived: bool):
"""
Get all models in the database
"""
return MLModel.query.filter(MLModel.name.ilike(model_filter + '%'),
MLModel.archived == model_archived).all()
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
def as_dto(self):
"""
Convert the model to it's dto
"""
model_dto = MLModelDTO()
model_dto.model_id = self.id
model_dto.name = self.name
model_dto.tags = self.tags
model_dto.created = self.created
model_dto.source = self.source
model_dto.archived = self.archived
model_dto.project_url = self.project_url
return model_dto
def update(self, updated_ml_model_dto: MLModelDTO):
""" Updates an ML model """
self.id = updated_ml_model_dto.model_id
self.name = updated_ml_model_dto.name
self.source = updated_ml_model_dto.source
self.project_url = updated_ml_model_dto.project_url
self.archived = updated_ml_model_dto.archived
self.tags = updated_ml_model_dto.tags
db.session.commit()
class PredictionTile(db.Model):
""" Store individual tile predictions """
__tablename__ = 'prediction_tiles'
id = db.Column(db.Integer, primary_key=True)
prediction_id = db.Column(db.BigInteger,
db.ForeignKey('predictions.id',
name='fk_predictions'),
nullable=False)
quadkey = db.Column(db.String, nullable=False)
quadkey_geom = db.Column(Geometry('POLYGON', srid=4326), nullable=False)
centroid = db.Column(Geometry('POINT', srid=4326))
predictions = db.Column(postgresql.JSONB, nullable=False)
validity = db.Column(MutableDict.as_mutable(postgresql.JSONB),
nullable=True)
prediction_tiles_quadkey_idx = db.Index(
'prediction_tiles_quadkey_idx',
'prediction_tiles.quadkey',
postgresql_ops={'quadkey': 'text_pattern_ops'})
@staticmethod
def get(predictiontile_id: int):
query = db.session.query(
PredictionTile.id,
PredictionTile.prediction_id,
PredictionTile.validity,
).filter(PredictionTile.id == predictiontile_id)
return PredictionTile.query.get(predictiontile_id)
def update(self, validity):
self.validity = validity
db.session.commit()
@staticmethod
def inferences(prediction_id: int):
results = db.session.execute(
text('''
SELECT
DISTINCT jsonb_object_keys(predictions)
FROM
prediction_tiles
WHERE
prediction_id = :pred
'''), {
'pred': prediction_id
}).fetchall()
inferences = []
for res in results:
inferences.append(res[0])
return inferences
@staticmethod
def count(prediction_id: int):
return db.session.query(
func.count(PredictionTile.quadkey).label("count")).filter(
PredictionTile.prediction_id == prediction_id).one()
@staticmethod
def bbox(prediction_id: int):
result = db.session.execute(
text('''
SELECT
ST_Extent(quadkey_geom)
FROM
prediction_tiles
WHERE
prediction_id = :pred
'''), {
'pred': prediction_id
}).fetchone()
bbox = []
for corners in result[0].replace('BOX(', '').replace(')',
'').split(' '):
for corner in corners.split(','):
bbox.append(float(corner))
return bbox
def mvt(prediction_id: int, z: int, x: int, y: int):
grid = mercantile.xy_bounds(x, y, z)
result = db.session.execute(
text('''
SELECT
ST_AsMVT(q, 'data', 4096, 'geom', 'id') AS mvt
FROM (
SELECT
p.id AS id,
quadkey AS quadkey,
predictions || COALESCE(v.validity, '{}'::JSONB) AS props,
ST_AsMVTGeom(quadkey_geom, ST_Transform(ST_MakeEnvelope(:minx, :miny, :maxx, :maxy, 3857), 4326), 4096, 256, false) AS geom
FROM
prediction_tiles AS p
LEFT JOIN (
SELECT
id,
JSONB_Object_Agg('v_'||key, value) AS validity
FROM
prediction_tiles,
jsonb_each(validity)
GROUP BY
id
) AS v ON p.id = v.id
WHERE
p.prediction_id = :pred
AND ST_Intersects(p.quadkey_geom, ST_Transform(ST_MakeEnvelope(:minx, :miny, :maxx, :maxy, 3857), 4326))
) q
'''), {
'pred': prediction_id,
'minx': grid[0],
'miny': grid[1],
'maxx': grid[2],
'maxy': grid[3]
}).fetchone()
return bytes(result.values()[0])
@staticmethod
def get_tiles_by_quadkey(prediction_id: int, quadkeys: tuple, zoom: int):
return db.session.query(
func.substr(PredictionTile.quadkey, 1, zoom).label('qaudkey'),
func.avg(
cast(
cast(PredictionTile.predictions['ml_prediction'],
sqlalchemy.String),
sqlalchemy.Float)).label('ml_prediction'),
func.avg(
cast(
cast(PredictionTile.predictions['osm_building_area'],
sqlalchemy.String),
sqlalchemy.Float)).label('osm_building_area')).filter(
PredictionTile.prediction_id == prediction_id).filter(
func.substr(PredictionTile.quadkey, 1,
zoom).in_(quadkeys)).group_by(
func.substr(
PredictionTile.quadkey, 1,
zoom)).all()
@staticmethod
def get_aggregate_for_polygon(prediction_id: int, polygon: str):
return db.session.query(
func.avg(
cast(
cast(PredictionTile.predictions['ml_prediction'],
sqlalchemy.String),
sqlalchemy.Float)).label('ml_prediction'),
func.avg(
cast(
cast(PredictionTile.predictions['osm_building_area'],
sqlalchemy.String),
sqlalchemy.Float)).label('osm_building_area')).filter(
PredictionTile.prediction_id == prediction_id).filter(
ST_Within(PredictionTile.centroid,
ST_GeomFromText(polygon)) ==
'True').one()
class Imagery(db.Model):
""" Store an imagery source for a given model """
__tablename__ = "imagery"
id = db.Column(db.Integer, primary_key=True)
model_id = db.Column(db.BigInteger,
db.ForeignKey("projects.id", name="fk_projects"),
nullable=False)
name = db.Column(db.String, nullable=False)
url = db.Column(db.String, nullable=False)
fmt = db.Column(db.String, nullable=False)
def create(self, model_id: int, imagery: dict):
""" Creates and saves the current model to the DB """
self.model_id = model_id
self.name = imagery.get("name")
self.url = imagery.get("url")
self.fmt = imagery.get("fmt")
db.session.add(self)
db.session.commit()
return self
def as_dto(self):
imagery_dto = ImageryDTO()
imagery_dto.id = self.id
imagery_dto.model_id = self.model_id
imagery_dto.name = self.name
imagery_dto.url = self.url
imagery_dto.fmt = self.fmt
return imagery_dto
def get(imagery_id: int):
db.session.query(Imagery.id, Imagery.name, Imagery.url, Imagery.fmt,
Imagery.model_id).filter(Imagery.id == imagery_id)
return Imagery.query.get(imagery_id)
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
def list(model_id: int):
query = db.session.query(
Imagery.id, Imagery.name, Imagery.url,
Imagery.fmt).filter(Imagery.model_id == model_id)
imagery = []
for img in query.all():
imagery.append({
"id": img[0],
"name": img[1],
"url": img[2],
"fmt": img[3]
})
return imagery
def update(self, update: dict):
if update.get("name") is not None:
self.name = update["name"]
if update.get("url") is not None:
self.url = update["url"]
if update.get("fmt") is not None:
self.fmt = update["fmt"]
db.session.commit()
class Token(db.Model):
""" Store an api token for a given user """
__tablename__ = "users_tokens"
id = db.Column(db.Integer, primary_key=True)
uid = db.Column(db.BigInteger,
db.ForeignKey("users.id", name="fk_uid"),
nullable=False)
name = db.Column(db.String, nullable=False)
token = db.Column(db.String, nullable=False)
created = created = db.Column(db.DateTime,
default=timestamp,
nullable=False)
def create(self, token: dict):
""" Creates and saves the current token to the DB """
self.uid = token.get("uid")
self.name = token.get("name")
self.token = "%030x" % random.randrange(16**60)
db.session.add(self)
db.session.commit()
return self
def as_dto(self):
dto = TokenDTO()
dto.id = self.id
dto.uid = self.uid
dto.name = self.name
dto.created = self.created
dto.token = self.token
return dto
def get(uid: int, token_id: int):
db.session.query(
Token.id,
Token.uid,
Token.name,
Token.created,
).filter(Token.uid == uid).filter(Token.id == token_id)
return Token.query.all()[0]
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
def list(uid: int):
query = db.session.query(Token.id, Token.uid, Token.name,
Token.created).filter(Token.uid == uid)
tokens = []
for t in query.all():
tokens.append({
"id": t[0],
"uid": t[1],
"name": t[2],
"created": t[3]
})
return tokens
class Prediction(db.Model):
""" Predictions from a model at a given time """
__tablename__ = 'predictions'
id = db.Column(db.Integer, primary_key=True)
created = db.Column(db.DateTime, default=timestamp, nullable=False)
model_id = db.Column(db.BigInteger,
db.ForeignKey('ml_models.id', name='fk_models'),
nullable=False)
version_id = db.Column(db.Integer,
db.ForeignKey('ml_model_versions.id',
name='ml_model_versions_fk'),
nullable=False)
dockerhub_hash = db.Column(db.String)
bbox = db.Column(Geometry('POLYGON', srid=4326))
tile_zoom = db.Column(db.Integer, nullable=False)
def create(self, prediction_dto: PredictionDTO):
""" Creates and saves the current model to the DB """
self.model_id = prediction_dto.model_id
self.version_id = prediction_dto.version_id
self.dockerhub_hash = prediction_dto.dockerhub_hash
self.bbox = ST_GeomFromText(bbox_to_polygon_wkt(prediction_dto.bbox),
4326)
self.tile_zoom = prediction_dto.tile_zoom
db.session.add(self)
db.session.commit()
def save(self):
""" Save changes to db"""
db.session.commit()
@staticmethod
def get(prediction_id: int):
"""
Get prediction with the given ID
:param prediction_id
:return prediction if found otherwise None
"""
query = db.session.query(
Prediction.id, Prediction.created, Prediction.dockerhub_hash,
ST_AsGeoJSON(ST_Envelope(Prediction.bbox)).label('bbox'),
Prediction.model_id, Prediction.tile_zoom,
Prediction.version_id).filter(Prediction.id == prediction_id)
return query.one()
@staticmethod
def get_predictions_by_model(model_id: int):
"""
Gets predictions for a specified ML Model
:param model_id: ml model ID in scope
:return predictions if found otherwise None
"""
query = db.session.query(
Prediction.id, Prediction.created, Prediction.dockerhub_hash,
ST_AsGeoJSON(ST_Envelope(Prediction.bbox)).label('bbox'),
Prediction.model_id, Prediction.tile_zoom,
Prediction.version_id).filter(Prediction.model_id == model_id)
return query.all()
@staticmethod
def get_latest_predictions_in_bbox(model_id: int, version_id: int,
bbox: list):
"""
Fetch latest predictions for the specified model intersecting
the given bbox
:param model_id, version_id, bbox
:return list of predictions
"""
query = db.session.query(
Prediction.id, Prediction.created, Prediction.dockerhub_hash,
ST_AsGeoJSON(ST_Envelope(Prediction.bbox)).label('bbox'),
Prediction.model_id, Prediction.tile_zoom,
Prediction.version_id).filter(
Prediction.model_id == model_id).filter(
Prediction.version_id == version_id).filter(
ST_Intersects(
Prediction.bbox,
ST_MakeEnvelope(
bbox[0], bbox[1], bbox[2], bbox[3],
4326))).order_by(
Prediction.created.desc()).limit(1)
return query.all()
def get_all_predictions_in_bbox(model_id: int, bbox: list):
"""
Fetch all predictions for the specified model intersecting the given
bbox
:param model_id, bbox
:return list of predictions
"""
query = db.session.query(
Prediction.id, Prediction.created, Prediction.dockerhub_hash,
ST_AsGeoJSON(ST_Envelope(Prediction.bbox)).label('bbox'),
Prediction.model_id, Prediction.tile_zoom,
Prediction.version_id).filter(
Prediction.model_id == model_id).filter(
ST_Intersects(
Prediction.bbox,
ST_MakeEnvelope(bbox[0], bbox[1], bbox[2], bbox[3],
4326)))
return query.all()
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
@staticmethod
def as_dto(prediction):
""" Static method to convert the prediction result as a schematic """
prediction_dto = PredictionDTO()
version = MLModelVersion.get(prediction[6])
version_dto = version.as_dto()
prediction_dto.prediction_id = prediction[0]
prediction_dto.created = prediction[1]
prediction_dto.dockerhub_hash = prediction[2]
prediction_dto.bbox = geojson_to_bbox(prediction[3])
prediction_dto.model_id = prediction[4]
prediction_dto.tile_zoom = prediction[5]
prediction_dto.version_id = prediction[6]
prediction_dto.version_string = f'{version_dto.version_major}.{version_dto.version_minor}.{version_dto.version_patch}'
return prediction_dto
class MLModelVersion(db.Model):
""" Stores versions of all subscribed ML Models """
__tablename__ = 'ml_model_versions'
id = db.Column(db.Integer, primary_key=True)
created = db.Column(db.DateTime, default=timestamp, nullable=False)
model_id = db.Column(db.BigInteger,
db.ForeignKey('ml_models.id',
name='fk_models_versions'),
nullable=False)
version_major = db.Column(db.Integer, nullable=False)
version_minor = db.Column(db.Integer, nullable=False)
version_patch = db.Column(db.Integer, nullable=False)
def create(self, version_dto: MLModelVersionDTO):
""" Creates a new version of an ML model """
self.model_id = version_dto.model_id
self.version_major = version_dto.version_major
self.version_minor = version_dto.version_minor
self.version_patch = version_dto.version_patch
db.session.add(self)
db.session.commit()
return self
def save(self):
""" Save changes to the db """
db.session.commit()
@staticmethod
def get(version_id: int):
"""
Get a version using the id
:param version_id
:return version or None
"""
return MLModelVersion.query.get(version_id)
@staticmethod
def get_version(model_id: int, version_major: int, version_minor: int,
version_patch: int):
"""
Get a version object for the supplied and corresponding semver
:param model_id, version_major, version_minor, version_patch
:return version or None
"""
return MLModelVersion.query.filter_by(
model_id=model_id,
version_major=version_major,
version_minor=version_minor,
version_patch=version_patch).one()
@staticmethod
def get_latest_version(model_id: int):
"""
Get the latest version of a given model
:param model_id
:return version or None
"""
return MLModelVersion.query.filter_by(model_id=model_id).order_by(
MLModelVersion.version_major.desc(),
MLModelVersion.version_minor.desc(),
MLModelVersion.version_patch.desc()).first()
def as_dto(self):
"""
Convert the version object to it's DTO
"""
version_dto = MLModelVersionDTO()
version_dto.version_id = self.id
version_dto.model_id = self.model_id
version_dto.created = self.created
version_dto.version_major = self.version_major
version_dto.version_minor = self.version_minor
version_dto.version_patch = self.version_patch
return version_dto