Column("z_name", TEXT, unique=True, nullable=False),
schema="bra",
)
# Alpine massif, like "Maurienne", "Chablais", "Aspe ossau"
MassifTable = AbstractSpatialTable(
"massif",
metadata,
Column("m_id", UUID(as_uuid=True), primary_key=True, default=uuid.uuid4),
Column("m_name", TEXT, unique=True, nullable=False),
Column(
"m_department",
UUID(as_uuid=True),
ForeignKey("bra.department.d_id"),
nullable=False,
),
Column("the_geom", Geometry("POLYGON", srid=4326), nullable=False),
schema="bra",
)
RiskTable = AbstractTable(
"risk",
metadata,
Column("r_id", UUID(as_uuid=True), primary_key=True, default=uuid.uuid4),
Column(
"r_record_id",
UUID(as_uuid=True),
ForeignKey("bra.record.br_id"),
nullable=False,
),
Column("r_altitude_limit", TEXT),
Column("r_risk", Integer, CheckConstraint("r_risk>0 AND r_risk<=5")),
class Business(db.Model):
""" Business model. """
__tablename__ = 'businesses'
biz_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
biz_name = db.Column(db.String(100), nullable=False)
address = db.Column(db.String(100), nullable=True)
city = db.Column(db.String(64), nullable=True)
state = db.Column(db.String(64), nullable=True)
country = db.Column(db.String(64), nullable=True)
zipcode = db.Column(db.String(64), nullable=True)
phone = db.Column(db.String(64), nullable=True)
email = db.Column(db.String(64), nullable=True)
valid_email = db.Column(db.Boolean, nullable=False, default=False)
url = db.Column(db.String(100), nullable=True)
category = db.Column(db.String(64), nullable=True)
days_open = db.Column(db.String(64), nullable=True)
open_time = db.Column(db.Integer, nullable=True)
close_time = db.Column(db.Integer, nullable=True)
claimed = db.Column(db.Boolean, nullable=False, default=False)
biz_pic = db.Column(db.String(100), nullable=True)
lat = db.Column(db.Float, nullable=True)
lng = db.Column(db.Float, nullable=True)
location = db.Column(Geometry(geometry_type='POINT'), nullable=True)
__table_args__ = (db.CheckConstraint(
"email ~ '^[A-Z0-9a-z._%+-]+@[A-Z0-9a-z.-]+\.[A-Za-z]{2,}$'"), )
checkins = db.relationship('CheckIn', backref='biz')
referrals = db.relationship('Referral', backref='biz')
reviews = db.relationship('Review', backref='biz')
promos = db.relationship('Promo', backref='biz')
def __repr__(self):
""" Displays info. """
return (u'<biz_id={} biz_name={}>'.format(self.biz_id, self.biz_name))
def is_owned(self, user_id):
""" Tracks whether business has been claimed by a specific user. """
owned = UserBiz.query.filter(UserBiz.biz_id == self.biz_id,
UserBiz.user_id == user_id).first()
if owned:
return True
else:
return False
def is_claimed(self):
"""
Tracks whether business has been claimed and therefore, is available
to be claimed or not.
"""
claimed = UserBiz.query.filter_by(biz_id=self.biz_id).first()
if claimed:
return True
else:
return False
def is_checkin(self, user_id):
""" Tracks whether business has been checked into by specific user for
that day. """
today = datetime.today().date()
checkin = CheckIn.query.filter(CheckIn.biz_id == self.biz_id,
CheckIn.user_id == user_id,
CheckIn.checkin_date == today).first()
if checkin:
return True
else:
return False
def has_reviewed(self, user_id):
""" Tracks whether business has been reviewed by a specific user. """
review = Review.query.filter(Review.biz_id == self.biz_id,
Review.user_id == user_id).first()
if review:
return True
else:
return False
def visit_not_reviewed(self, user_id):
""" Tracks whether business has been checked in but not reviewed by
specific user. """
checkins_not_reviewed = (db.session.query(distinct(
CheckIn.biz_id)).filter(
CheckIn.user_id == user_id,
CheckIn.biz_id.notin_(
db.session.query(distinct(Review.biz_id)).filter(
Review.user_id == user_id))).all())
not_reviewed = False
for item in checkins_not_reviewed:
if item[0] == self.biz_id:
not_reviewed = True
return not_reviewed
def tot_checkins(self):
""" Tracks total check-ins to the business. """
total = len(self.checkins)
return total
def tot_user_checkins(self, user_id):
""" Tracks total check-ins to the business by a specific user. """
total = CheckIn.query.filter(CheckIn.user_id == user_id,
CheckIn.biz_id == self.biz_id).count()
return total
def tot_reviews(self):
""" Calculates total reviews for a business. """
count = len(self.reviews)
return count
def avg_score(self):
""" Calculates average rating of business. """
tot_score = 0
for review in self.reviews:
if review.revise_review:
tot_score += review.new_rating
else:
tot_score += review.rating
avg_rating = tot_score / self.tot_reviews()
return avg_rating
def tot_promos_red(self):
""" Calculates total promotions redeemed by consumers. """
redeemed_promos = 0
for promo in self.promos:
for item in promo.user_promos:
if item.redeemed is True:
redeemed_promos += 1
return redeemed_promos
def tot_refs(self):
""" Calculates total referrals. """
total = len(self.referrals)
return total
def tot_refs_red(self):
""" Calculates total referrals that have been redeemed. """
redeemed_refs = 0
for item in self.referrals:
if item.user_promo.redeemed is True:
redeemed_refs += 1
return redeemed_refs
def deg_of_sep(self, user2_id, degrees=6):
"""
Calculates degrees of separation, if any, between a user and business,
if claimed. If count is zero, the user2 is user1. Otherwise, count of none
means no connection. Currently set to stop at count at n degrees.
"""
if not self.users:
count = 0
return count
else:
visit = []
seen = set()
visit.append(self.users[0].user_id)
sought = user2_id
anchor = self.users[0].user_id
count = 0
while visit:
current = visit.pop(0)
if current == sought:
return count
else:
if current not in seen:
seen.add(current)
for friend in friends_lst(current, seen):
visit.append(friend)
if current == anchor:
if visit:
anchor = visit[-1]
count += 1
if count == (degrees + 1):
count = None
return count
return count
class Task(db.Model):
""" Describes an individual mapping Task """
__tablename__ = "tasks"
# Table has composite PK on (id and project_id)
id = db.Column(db.Integer, primary_key=True)
project_id = db.Column(db.Integer,
db.ForeignKey("projects.id"),
index=True,
primary_key=True)
x = db.Column(db.Integer)
y = db.Column(db.Integer)
zoom = db.Column(db.Integer)
extra_properties = db.Column(db.Unicode)
# Tasks need to be split differently if created from an arbitrary grid or were clipped to the edge of the AOI
is_square = db.Column(db.Boolean, default=True)
geometry = db.Column(Geometry("MULTIPOLYGON", srid=4326))
task_status = db.Column(db.Integer, default=TaskStatus.READY.value)
locked_by = db.Column(db.BigInteger,
db.ForeignKey("users.id", name="fk_users_locked"),
index=True)
mapped_by = db.Column(db.BigInteger,
db.ForeignKey("users.id", name="fk_users_mapper"),
index=True)
validated_by = db.Column(db.BigInteger,
db.ForeignKey("users.id",
name="fk_users_validator"),
index=True)
# Mapped objects
task_history = db.relationship(TaskHistory, cascade="all")
task_annotations = db.relationship(TaskAnnotation, cascade="all")
lock_holder = db.relationship(User, foreign_keys=[locked_by])
mapper = db.relationship(User, foreign_keys=[mapped_by])
def create(self):
""" Creates and saves the current model to the DB """
db.session.add(self)
db.session.commit()
def update(self):
""" Updates the DB with the current state of the Task """
db.session.commit()
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
@classmethod
def from_geojson_feature(cls, task_id, task_feature):
"""
Constructs and validates a task from a GeoJson feature object
:param task_id: Unique ID for the task
:param task_feature: A geoJSON feature object
:raises InvalidGeoJson, InvalidData
"""
if type(task_feature) is not geojson.Feature:
raise InvalidGeoJson("Task: Invalid GeoJson should be a feature")
task_geometry = task_feature.geometry
if type(task_geometry) is not geojson.MultiPolygon:
raise InvalidGeoJson("Task: Geometry must be a MultiPolygon")
is_valid_geojson = geojson.is_valid(task_geometry)
if is_valid_geojson["valid"] == "no":
raise InvalidGeoJson(
f"Task: Invalid MultiPolygon - {is_valid_geojson['message']}")
task = cls()
try:
task.x = task_feature.properties["x"]
task.y = task_feature.properties["y"]
task.zoom = task_feature.properties["zoom"]
task.is_square = task_feature.properties["isSquare"]
except KeyError as e:
raise InvalidData(f"Task: Expected property not found: {str(e)}")
if "extra_properties" in task_feature.properties:
task.extra_properties = json.dumps(
task_feature.properties["extra_properties"])
task.id = task_id
task_geojson = geojson.dumps(task_geometry)
task.geometry = ST_SetSRID(ST_GeomFromGeoJSON(task_geojson), 4326)
return task
@staticmethod
def get(task_id: int, project_id: int):
"""
Gets specified task
:param task_id: task ID in scope
:param project_id: project ID in scope
:return: Task if found otherwise None
"""
# LIKELY PROBLEM AREA
return Task.query.filter_by(id=task_id,
project_id=project_id).one_or_none()
@staticmethod
def get_tasks(project_id: int, task_ids: List[int]):
""" Get all tasks that match supplied list """
return Task.query.filter(Task.project_id == project_id,
Task.id.in_(task_ids)).all()
@staticmethod
def get_all_tasks(project_id: int):
""" Get all tasks for a given project """
return Task.query.filter(Task.project_id == project_id).all()
@staticmethod
def auto_unlock_delta():
return parse_duration(current_app.config["TASK_AUTOUNLOCK_AFTER"])
@staticmethod
def auto_unlock_tasks(project_id: int):
"""Unlock all tasks locked for longer than the auto-unlock delta"""
expiry_delta = Task.auto_unlock_delta()
lock_duration = (datetime.datetime.min +
expiry_delta).time().isoformat()
expiry_date = datetime.datetime.utcnow() - expiry_delta
old_locks_query = """SELECT t.id
FROM tasks t, task_history th
WHERE t.id = th.task_id
AND t.project_id = th.project_id
AND t.task_status IN (1,3)
AND th.action IN ( 'LOCKED_FOR_VALIDATION','LOCKED_FOR_MAPPING' )
AND th.action_text IS NULL
AND t.project_id = :project_id
AND th.action_date <= :expiry_date
"""
old_tasks = db.engine.execute(text(old_locks_query),
project_id=project_id,
expiry_date=str(expiry_date))
if old_tasks.rowcount == 0:
# no tasks older than the delta found, return without further processing
return
for old_task in old_tasks:
task = Task.get(old_task[0], project_id)
task.auto_unlock_expired_tasks(expiry_date, lock_duration)
def auto_unlock_expired_tasks(self, expiry_date, lock_duration):
"""Unlock all tasks locked before expiry date. Clears task lock if needed"""
TaskHistory.update_expired_and_locked_actions(self.project_id, self.id,
expiry_date,
lock_duration)
last_action = TaskHistory.get_last_locked_or_auto_unlocked_action(
self.project_id, self.id)
if last_action.action in [
"AUTO_UNLOCKED_FOR_MAPPING",
"AUTO_UNLOCKED_FOR_VALIDATION",
]:
self.clear_lock()
def is_mappable(self):
""" Determines if task in scope is in suitable state for mapping """
if TaskStatus(self.task_status) not in [
TaskStatus.READY,
TaskStatus.INVALIDATED,
]:
return False
return True
def set_task_history(self,
action,
user_id,
comment=None,
new_state=None,
mapping_issues=None):
"""
Sets the task history for the action that the user has just performed
:param task: Task in scope
:param user_id: ID of user performing the action
:param action: Action the user has performed
:param comment: Comment user has added
:param new_state: New state of the task
:param mapping_issues: Identified issues leading to invalidation
"""
history = TaskHistory(self.id, self.project_id, user_id)
if action in [
TaskAction.LOCKED_FOR_MAPPING, TaskAction.LOCKED_FOR_VALIDATION
]:
history.set_task_locked_action(action)
elif action == TaskAction.COMMENT:
history.set_comment_action(comment)
elif action == TaskAction.STATE_CHANGE:
history.set_state_change_action(new_state)
elif action in [
TaskAction.AUTO_UNLOCKED_FOR_MAPPING,
TaskAction.AUTO_UNLOCKED_FOR_VALIDATION,
]:
history.set_auto_unlock_action(action)
if mapping_issues is not None:
history.task_mapping_issues = mapping_issues
self.task_history.append(history)
return history
def lock_task_for_mapping(self, user_id: int):
self.set_task_history(TaskAction.LOCKED_FOR_MAPPING, user_id)
self.task_status = TaskStatus.LOCKED_FOR_MAPPING.value
self.locked_by = user_id
self.update()
def lock_task_for_validating(self, user_id: int):
self.set_task_history(TaskAction.LOCKED_FOR_VALIDATION, user_id)
self.task_status = TaskStatus.LOCKED_FOR_VALIDATION.value
self.locked_by = user_id
self.update()
def reset_task(self, user_id: int):
if TaskStatus(self.task_status) in [
TaskStatus.LOCKED_FOR_MAPPING,
TaskStatus.LOCKED_FOR_VALIDATION,
]:
self.record_auto_unlock()
self.set_task_history(TaskAction.STATE_CHANGE, user_id, None,
TaskStatus.READY)
self.mapped_by = None
self.validated_by = None
self.locked_by = None
self.task_status = TaskStatus.READY.value
self.update()
def clear_task_lock(self):
"""
Unlocks task in scope in the database. Clears the lock as though it never happened.
No history of the unlock is recorded.
:return:
"""
# clear the lock action for the task in the task history
last_action = TaskHistory.get_last_locked_action(
self.project_id, self.id)
last_action.delete()
# Set locked_by to null and status to last status on task
self.clear_lock()
def record_auto_unlock(self, lock_duration):
locked_user = self.locked_by
last_action = TaskHistory.get_last_locked_action(
self.project_id, self.id)
next_action = (TaskAction.AUTO_UNLOCKED_FOR_MAPPING
if last_action.action == "LOCKED_FOR_MAPPING" else
TaskAction.AUTO_UNLOCKED_FOR_VALIDATION)
self.clear_task_lock()
# Add AUTO_UNLOCKED action in the task history
auto_unlocked = self.set_task_history(action=next_action,
user_id=locked_user)
auto_unlocked.action_text = lock_duration
self.update()
def unlock_task(self,
user_id,
new_state=None,
comment=None,
undo=False,
issues=None):
""" Unlock task and ensure duration task locked is saved in History """
if comment:
self.set_task_history(
action=TaskAction.COMMENT,
comment=comment,
user_id=user_id,
mapping_issues=issues,
)
history = self.set_task_history(
action=TaskAction.STATE_CHANGE,
new_state=new_state,
user_id=user_id,
mapping_issues=issues,
)
if (new_state in [TaskStatus.MAPPED, TaskStatus.BADIMAGERY]
and TaskStatus(
self.task_status) != TaskStatus.LOCKED_FOR_VALIDATION):
# Don't set mapped if state being set back to mapped after validation
self.mapped_by = user_id
elif new_state == TaskStatus.VALIDATED:
TaskInvalidationHistory.record_validation(self.project_id, self.id,
user_id, history)
self.validated_by = user_id
elif new_state == TaskStatus.INVALIDATED:
TaskInvalidationHistory.record_invalidation(
self.project_id, self.id, user_id, history)
self.mapped_by = None
self.validated_by = None
if not undo:
# Using a slightly evil side effect of Actions and Statuses having the same name here :)
TaskHistory.update_task_locked_with_duration(
self.id, self.project_id, TaskStatus(self.task_status),
user_id)
self.task_status = new_state.value
self.locked_by = None
self.update()
def reset_lock(self, user_id, comment=None):
""" Removes a current lock from a task, resets to last status and updates history with duration of lock """
if comment:
self.set_task_history(action=TaskAction.COMMENT,
comment=comment,
user_id=user_id)
# Using a slightly evil side effect of Actions and Statuses having the same name here :)
TaskHistory.update_task_locked_with_duration(
self.id, self.project_id, TaskStatus(self.task_status), user_id)
self.clear_lock()
def clear_lock(self):
""" Resets to last status and removes current lock from a task """
self.task_status = TaskHistory.get_last_status(self.project_id,
self.id).value
self.locked_by = None
self.update()
@staticmethod
def get_tasks_as_geojson_feature_collection(
project_id,
task_ids_str: str = None,
order_by: str = None,
order_by_type: str = "ASC",
status: int = None,
):
"""
Creates a geoJson.FeatureCollection object for tasks related to the supplied project ID
:param project_id: Owning project ID
:order_by: sorting option: available values update_date and building_area_diff
:status: task status id to filter by
:return: geojson.FeatureCollection
"""
# subquery = (
# db.session.query(func.max(TaskHistory.action_date))
# .filter(
# Task.id == TaskHistory.task_id,
# Task.project_id == TaskHistory.project_id,
# )
# .correlate(Task)
# .group_by(Task.id)
# .label("update_date")
# )
query = db.session.query(
Task.id,
Task.x,
Task.y,
Task.zoom,
Task.is_square,
Task.task_status,
Task.geometry.ST_AsGeoJSON().label("geojson"),
Task.locked_by,
# subquery,
)
filters = [Task.project_id == project_id]
if task_ids_str:
tasks_filters = []
task_ids = map(int, task_ids_str.split(","))
tasks = Task.get_tasks(project_id, task_ids)
if not tasks or len(tasks) == 0:
raise NotFound()
else:
for task in tasks:
tasks_filters.append(task.id)
filters = [
Task.project_id == project_id,
Task.id.in_(tasks_filters)
]
else:
tasks = Task.get_all_tasks(project_id)
if not tasks or len(tasks) == 0:
raise NotFound()
if status:
filters.append(Task.task_status == status)
if order_by == "effort_prediction":
query = query.outerjoin(TaskAnnotation).filter(*filters)
if order_by_type == "DESC":
query = query.order_by(
desc(
cast(
cast(
TaskAnnotation.
properties["building_area_diff"], Text),
Float,
)))
else:
query = query.order_by(
cast(
cast(TaskAnnotation.properties["building_area_diff"],
Text),
Float,
))
# elif order_by == "last_updated":
# if order_by_type == "DESC":
# query = query.filter(*filters).order_by(desc("update_date"))
# else:
# query = query.filter(*filters).order_by("update_date")
else:
query = query.filter(*filters)
project_tasks = query.all()
tasks_features = []
for task in project_tasks:
task_geometry = geojson.loads(task.geojson)
task_properties = dict(
taskId=task.id,
taskX=task.x,
taskY=task.y,
taskZoom=task.zoom,
taskIsSquare=task.is_square,
taskStatus=TaskStatus(task.task_status).name,
lockedBy=task.locked_by,
)
feature = geojson.Feature(geometry=task_geometry,
properties=task_properties)
tasks_features.append(feature)
return geojson.FeatureCollection(tasks_features)
@staticmethod
def get_tasks_as_geojson_feature_collection_no_geom(project_id):
"""
Creates a geoJson.FeatureCollection object for all tasks related to the supplied project ID without geometry
:param project_id: Owning project ID
:return: geojson.FeatureCollection
"""
project_tasks = (db.session.query(
Task.id, Task.x, Task.y, Task.zoom, Task.is_square,
Task.task_status).filter(Task.project_id == project_id).all())
tasks_features = []
for task in project_tasks:
task_properties = dict(
taskId=task.id,
taskX=task.x,
taskY=task.y,
taskZoom=task.zoom,
taskIsSquare=task.is_square,
taskStatus=TaskStatus(task.task_status).name,
)
feature = geojson.Feature(properties=task_properties)
tasks_features.append(feature)
return geojson.FeatureCollection(tasks_features)
@staticmethod
def get_mapped_tasks_by_user(project_id: int):
""" Gets all mapped tasks for supplied project grouped by user"""
# Raw SQL is easier to understand that SQL alchemy here :)
sql = """select u.username, u.mapping_level, count(distinct(t.id)), json_agg(distinct(t.id)),
max(th.action_date) last_seen, u.date_registered, u.last_validation_date
from tasks t,
task_history th,
users u
where t.project_id = th.project_id
and t.id = th.task_id
and t.mapped_by = u.id
and t.project_id = :project_id
and t.task_status = 2
and th.action_text = 'MAPPED'
group by u.username, u.mapping_level, u.date_registered, u.last_validation_date"""
results = db.engine.execute(text(sql), project_id=project_id)
mapped_tasks_dto = MappedTasks()
for row in results:
user_mapped = MappedTasksByUser()
user_mapped.username = row[0]
user_mapped.mapping_level = MappingLevel(row[1]).name
user_mapped.mapped_task_count = row[2]
user_mapped.tasks_mapped = row[3]
user_mapped.last_seen = row[4]
user_mapped.date_registered = row[5]
user_mapped.last_validation_date = row[6]
mapped_tasks_dto.mapped_tasks.append(user_mapped)
return mapped_tasks_dto
@staticmethod
def get_max_task_id_for_project(project_id: int):
"""Gets the nights task id currently in use on a project"""
sql = """select max(id) from tasks where project_id = :project_id GROUP BY project_id"""
result = db.engine.execute(text(sql), project_id=project_id)
if result.rowcount == 0:
raise NotFound()
for row in result:
return row[0]
def as_dto(
self,
task_history: List[TaskHistoryDTO] = [],
last_updated: datetime.datetime = None,
):
"""Just converts to a TaskDTO"""
task_dto = TaskDTO()
task_dto.task_id = self.id
task_dto.project_id = self.project_id
task_dto.task_status = TaskStatus(self.task_status).name
task_dto.lock_holder = self.lock_holder.username if self.lock_holder else None
task_dto.task_history = task_history
if last_updated:
task_dto.last_updated = last_updated
task_dto.auto_unlock_seconds = Task.auto_unlock_delta().total_seconds()
return task_dto
def as_dto_with_instructions(self,
preferred_locale: str = "en") -> TaskDTO:
"""Get dto with any task instructions"""
task_history = []
for action in reversed(self.task_history):
history = TaskHistoryDTO()
history.history_id = action.id
history.action = action.action
history.action_text = action.action_text
history.action_date = action.action_date
history.action_by = (action.actioned_by.username
if action.actioned_by else None)
history.picture_url = (action.actioned_by.picture_url
if action.actioned_by else None)
if action.task_mapping_issues:
history.issues = [
issue.as_dto() for issue in action.task_mapping_issues
]
task_history.append(history)
task_dto = self.as_dto(task_history)
per_task_instructions = self.get_per_task_instructions(
preferred_locale)
# If we don't have instructions in preferred locale try again for default locale
task_dto.per_task_instructions = (
per_task_instructions if per_task_instructions else
self.get_per_task_instructions(self.projects.default_locale))
annotations = self.get_per_task_annotations()
task_dto.task_annotations = annotations if annotations else []
return task_dto
def get_per_task_annotations(self):
result = [ta.get_dto() for ta in self.task_annotations]
return result
def get_per_task_instructions(self, search_locale: str) -> str:
""" Gets any per task instructions attached to the project """
project_info = self.projects.project_info.all()
for info in project_info:
if info.locale == search_locale:
return self.format_per_task_instructions(
info.per_task_instructions)
def format_per_task_instructions(self, instructions) -> str:
""" Format instructions by looking for X, Y, Z tokens and replacing them with the task values """
if not instructions:
return "" # No instructions so return empty string
properties = {}
if self.x:
properties["x"] = str(self.x)
if self.y:
properties["y"] = str(self.y)
if self.zoom:
properties["z"] = str(self.zoom)
if self.extra_properties:
properties.update(json.loads(self.extra_properties))
try:
instructions = instructions.format(**properties)
except KeyError:
pass
return instructions
def copy_task_history(self) -> list:
copies = []
for entry in self.task_history:
db.session.expunge(entry)
make_transient(entry)
entry.id = None
entry.task_id = None
db.session.add(entry)
copies.append(entry)
return copies
def get_locked_tasks_for_user(user_id: int):
""" Gets tasks on project owned by specified user id"""
tasks = Task.query.filter_by(locked_by=user_id)
tasks_dto = LockedTasksForUser()
tasks_dto.locked_tasks = []
for task in tasks:
tasks_dto.locked_tasks.append(task.id)
tasks_dto.project = task.project_id
tasks_dto.task_status = TaskStatus(task.task_status).name
return tasks_dto
def get_locked_tasks_details_for_user(user_id: int):
""" Gets tasks on project owned by specified user id"""
tasks = Task.query.filter_by(locked_by=user_id)
locked_tasks = []
for task in tasks:
locked_tasks.append(task)
return locked_tasks
def add_geometry_column(cls):
if not hasattr(cls, 'geom'):
from geoalchemy2 import Geometry
cls.geom = deferred(Column(Geometry('MULTILINESTRING')))
class Estado(Base):
"""
Class to hold a brazillian state
"""
__tablename__ = 'estados'
gid = Column(Integer, Sequence('estados_gid_pkey'), primary_key=True, nullable=False)
name = Column(Unicode(80), nullable=True)
descriptio = Column(Unicode(80), nullable=True)
timestamp = Column(Date, nullable=True)
begin = Column(Date, nullable=True)
end = Column(Date, nullable=True)
altitudemo = Column(Unicode(80), nullable=True)
tessellate = Column(Numeric(10, 0), nullable=True)
extrude = Column(Numeric(10, 0), nullable=True)
visibility = Column(Numeric(10, 0), nullable=True)
draworder = Column(Numeric(10, 0), nullable=True)
icon = Column(Unicode(80), nullable=True)
# Add geometry type
geom = Column(Geometry('MULTILINESTRING'))
def __init__(self,
geom,
name=None,
descriptio=None,
timestamp=None,
begin=None,
end=None,
date=None,
altitudemo=None,
tessellate=None,
extrude=None,
visibility=None,
draworder=None,
icon=None):
"""
Building method for the Class
:param geom: MultiLineString Geometry type
:param name: Place name
:param descriptio: Description
:param timestamp:
:param begin:
:param end:
:param date:
:param altitudemo:
:param tessellate:
:param extrude:
:param visibility:
:param draworder:
:param icon:
:return:
"""
self.geom = geom
self.name = name
self.descriptio = descriptio
self.timestamp = timestamp
self.begin = begin
self.end = end
self.date = date
self.altitudemo = altitudemo
self.tessellate = tessellate
self.extrude = extrude
self.visibility = visibility
self.draworder = draworder
self.icon = icon
def __repr__(self):
"""
Método que lista os parâmetros da classe
"""
return "<Estado('%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s')>" % (
self.geom,
self.name,
self.descriptio,
self.timestamp,
self.begin,
self.end,
self.date,
self.altitudemo,
self.tessellate,
self.extrude,
self.visibility,
self.draworder,
self.icon
)
def _create_layer(
self,
public=False,
none_area=False,
attr_list=False,
exclude_properties=False,
metadatas=None,
geom_type=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
from geoalchemy2 import Geometry, WKTElement
from sqlalchemy import CheckConstraint, Column, ForeignKey, Table, types
from sqlalchemy.ext.declarative import declarative_base
from c2cgeoportal_commons.models import DBSession
from c2cgeoportal_commons.models.main import LayerWMS, OGCServer, RestrictionArea
if self._tables is None:
self._tables = []
self.__class__._table_index += 1
id = self.__class__._table_index
engine = DBSession.c2c_rw_bind
connection = engine.connect()
if not self.metadata:
self.metadata = declarative_base(bind=engine).metadata
tablename = f"table_{id:d}"
table1 = Table(
f"{tablename!s}_child",
self.metadata,
Column("id", types.Integer, primary_key=True),
Column("name", types.Unicode),
schema="public",
)
if geom_type:
table1.append_column(Column("geom", Geometry("POINT", srid=21781)))
else:
table1.append_column(Column("geom", Geometry(srid=21781)))
self._tables.append(table1)
table2 = Table(
tablename,
self.metadata,
Column("id", types.Integer, primary_key=True),
Column("child_id", types.Integer,
ForeignKey(f"public.{tablename!s}_child.id")),
Column("name", types.Unicode),
Column(
"email",
types.Unicode,
CheckConstraint(
"""email ~* '^[A-Za-z0-9._%%-]
+@[A-Za-z0-9.-]+[.][A-Za-z]+$'""",
name="proper_email",
),
),
Column("last_update_user", types.Unicode),
Column("last_update_date", types.DateTime),
schema="public",
)
if geom_type:
table2.append_column(Column("geom", Geometry("POINT", srid=21781)))
else:
table2.append_column(Column("geom", Geometry(srid=21781)))
self._tables.append(table2)
table1.drop(checkfirst=True)
table2.drop(checkfirst=True)
table1.create()
table2.create()
ins = table1.insert().values(name="c1é")
c1_id = connection.execute(ins).inserted_primary_key[0]
ins = table1.insert().values(name="c2é")
c2_id = connection.execute(ins).inserted_primary_key[0]
ins = table2.insert().values(child_id=c1_id,
name="foo",
geom=WKTElement("POINT(5 45)", 21781))
connection.execute(ins)
ins = table2.insert().values(child_id=c2_id,
name="bar",
geom=WKTElement("POINT(6 46)", 21781))
connection.execute(ins)
if attr_list:
ins = table2.insert().values(child_id=c2_id,
name="aaa,bbb,foo",
geom=WKTElement("POINT(6 46)", 21781))
connection.execute(ins)
ogc_server = DBSession.query(OGCServer).filter(
OGCServer.name == "__test_ogc_server").one()
layer = LayerWMS()
layer.id = id
layer.name = str(id)
layer.ogc_server = ogc_server
layer.geo_table = tablename
layer.public = public
layer.interface = [self.main]
if exclude_properties:
layer.exclude_properties = "name"
if metadatas:
layer.metadatas = metadatas
DBSession.add(layer)
if not public:
ra = RestrictionArea()
ra.name = "__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 = WKTElement(poly, srid=21781)
DBSession.add(ra)
transaction.commit()
self.layer_ids.append(id)
return id
class Project(db.Model):
""" Describes a HOT Mapping Project """
__tablename__ = 'projects'
# Columns
id = db.Column(db.Integer, primary_key=True)
status = db.Column(db.Integer,
default=ProjectStatus.DRAFT.value,
nullable=False)
created = db.Column(db.DateTime, default=timestamp, nullable=False)
priority = db.Column(db.Integer, default=ProjectPriority.MEDIUM.value)
default_locale = db.Column(
db.String(10), default='en'
) # The locale that is returned if requested locale not available
author_id = db.Column(db.BigInteger,
db.ForeignKey('users.id', name='fk_users'),
nullable=False)
mapper_level = db.Column(
db.Integer, default=1, nullable=False,
index=True) # Mapper level project is suitable for
enforce_mapper_level = db.Column(db.Boolean, default=False)
enforce_validator_role = db.Column(
db.Boolean,
default=False) # Means only users with validator role can validate
private = db.Column(db.Boolean,
default=False) # Only allowed users can validate
entities_to_map = db.Column(db.String)
changeset_comment = db.Column(db.String)
due_date = db.Column(db.DateTime)
imagery = db.Column(db.String)
josm_preset = db.Column(db.String)
last_updated = db.Column(db.DateTime, default=timestamp)
license_id = db.Column(db.Integer,
db.ForeignKey('licenses.id', name='fk_licenses'))
geometry = db.Column(Geometry('MULTIPOLYGON', srid=4326))
centroid = db.Column(Geometry('POINT', srid=4326))
task_creation_mode = db.Column(db.Integer,
default=TaskCreationMode.GRID.value,
nullable=False)
# Tags
mapping_types = db.Column(ARRAY(db.Integer), index=True)
organisation_tag = db.Column(db.String, index=True)
campaign_tag = db.Column(db.String, index=True)
# Stats
total_tasks = db.Column(db.Integer, nullable=False)
tasks_mapped = db.Column(db.Integer, default=0, nullable=False)
tasks_validated = db.Column(db.Integer, default=0, nullable=False)
tasks_bad_imagery = db.Column(db.Integer, default=0, nullable=False)
# Mapped Objects
tasks = db.relationship(Task,
backref='projects',
cascade="all, delete, delete-orphan",
lazy='dynamic')
project_info = db.relationship(ProjectInfo, lazy='dynamic', cascade='all')
author = db.relationship(User)
allowed_users = db.relationship(User, secondary=project_allowed_users)
priority_areas = db.relationship(PriorityArea,
secondary=project_priority_areas,
cascade="all, delete-orphan",
single_parent=True)
def create_draft_project(self, draft_project_dto: DraftProjectDTO):
"""
Creates a draft project
:param draft_project_dto: DTO containing draft project details
:param aoi: Area of Interest for the project (eg boundary of project)
"""
self.project_info.append(
ProjectInfo.create_from_name(draft_project_dto.project_name))
self.status = ProjectStatus.DRAFT.value
self.author_id = draft_project_dto.user_id
self.last_updated = timestamp()
def set_project_aoi(self, draft_project_dto: DraftProjectDTO):
""" Sets the AOI for the supplied project """
aoi_geojson = geojson.loads(
json.dumps(draft_project_dto.area_of_interest))
aoi_geometry = GridService.merge_to_multi_polygon(aoi_geojson,
dissolve=True)
valid_geojson = geojson.dumps(aoi_geometry)
self.geometry = ST_SetSRID(ST_GeomFromGeoJSON(valid_geojson), 4326)
self.centroid = ST_Centroid(self.geometry)
def set_default_changeset_comment(self):
""" Sets the default changeset comment"""
default_comment = current_app.config['DEFAULT_CHANGESET_COMMENT']
self.changeset_comment = f'{default_comment}-{self.id}'
self.save()
def create(self):
""" Creates and saves the current model to the DB """
db.session.add(self)
db.session.commit()
def save(self):
""" Save changes to db"""
db.session.commit()
@staticmethod
def clone(project_id: int, author_id: int):
""" Clone project """
cloned_project = Project.get(project_id)
# Remove clone from session so we can reinsert it as a new object
db.session.expunge(cloned_project)
make_transient(cloned_project)
# Re-initialise counters and meta-data
cloned_project.total_tasks = 0
cloned_project.tasks_mapped = 0
cloned_project.tasks_validated = 0
cloned_project.tasks_bad_imagery = 0
cloned_project.last_updated = timestamp()
cloned_project.created = timestamp()
cloned_project.author_id = author_id
cloned_project.status = ProjectStatus.DRAFT.value
cloned_project.id = None # Reset ID so we get a new ID when inserted
cloned_project.geometry = None
cloned_project.centroid = None
db.session.add(cloned_project)
db.session.commit()
# Now add the project info, we have to do it in a two stage commit because we need to know the new project id
original_project = Project.get(project_id)
for info in original_project.project_info:
db.session.expunge(info)
make_transient(
info
) # Must remove the object from the session or it will be updated rather than inserted
info.id = None
info.project_id_str = str(cloned_project.id)
cloned_project.project_info.append(info)
# Now add allowed users now we know new project id, if there are any
for user in original_project.allowed_users:
cloned_project.allowed_users.append(user)
db.session.add(cloned_project)
db.session.commit()
return cloned_project
@staticmethod
def get(project_id: int):
"""
Gets specified project
:param project_id: project ID in scope
:return: Project if found otherwise None
"""
return Project.query.get(project_id)
def update(self, project_dto: ProjectDTO):
""" Updates project from DTO """
self.status = ProjectStatus[project_dto.project_status].value
self.priority = ProjectPriority[project_dto.project_priority].value
self.default_locale = project_dto.default_locale
self.enforce_mapper_level = project_dto.enforce_mapper_level
self.enforce_validator_role = project_dto.enforce_validator_role
self.private = project_dto.private
self.mapper_level = MappingLevel[
project_dto.mapper_level.upper()].value
self.entities_to_map = project_dto.entities_to_map
self.changeset_comment = project_dto.changeset_comment
self.due_date = project_dto.due_date
self.imagery = project_dto.imagery
self.josm_preset = project_dto.josm_preset
self.last_updated = timestamp()
self.license_id = project_dto.license_id
if project_dto.organisation_tag:
org_tag = Tags.upsert_organistion_tag(project_dto.organisation_tag)
self.organisation_tag = org_tag
else:
self.organisation_tag = None # Set to none, for cases where a tag could have been removed
if project_dto.campaign_tag:
camp_tag = Tags.upsert_campaign_tag(project_dto.campaign_tag)
self.campaign_tag = camp_tag
else:
self.campaign_tag = None # Set to none, for cases where a tag could have been removed
# Cast MappingType strings to int array
type_array = []
for mapping_type in project_dto.mapping_types:
type_array.append(MappingTypes[mapping_type].value)
self.mapping_types = type_array
# Add list of allowed users, meaning the project can only be mapped by users in this list
if hasattr(project_dto, 'allowed_users'):
self.allowed_users = [
] # Clear existing relationships then re-insert
for user in project_dto.allowed_users:
self.allowed_users.append(user)
# Set Project Info for all returned locales
for dto in project_dto.project_info_locales:
project_info = self.project_info.filter_by(
locale=dto.locale).one_or_none()
if project_info is None:
new_info = ProjectInfo.create_from_dto(
dto) # Can't find info so must be new locale
self.project_info.append(new_info)
else:
project_info.update_from_dto(dto)
self.priority_areas = [
] # Always clear Priority Area prior to updating
if project_dto.priority_areas:
for priority_area in project_dto.priority_areas:
pa = PriorityArea.from_dict(priority_area)
self.priority_areas.append(pa)
db.session.commit()
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
def can_be_deleted(self) -> bool:
""" Projects can be deleted if they have no mapped work """
task_count = self.tasks.filter(
Task.task_status != TaskStatus.READY.value).count()
if task_count == 0:
return True
else:
return False
def get_locked_tasks_for_user(self, user_id: int):
""" Gets tasks on project owned by specified user id"""
tasks = self.tasks.filter_by(locked_by=user_id)
locked_tasks = []
for task in tasks:
locked_tasks.append(task.id)
return locked_tasks
def get_locked_tasks_details_for_user(self, user_id: int):
""" Gets tasks on project owned by specified user id"""
tasks = self.tasks.filter_by(locked_by=user_id)
locked_tasks = []
for task in tasks:
locked_tasks.append(task)
return locked_tasks
@staticmethod
def get_projects_for_admin(admin_id: int,
preferred_locale: str) -> PMDashboardDTO:
""" Get projects for admin """
admins_projects = Project.query.filter_by(author_id=admin_id).all()
if admins_projects is None:
raise NotFound('No projects found for admin')
admin_projects_dto = PMDashboardDTO()
for project in admins_projects:
pm_project = project.get_project_summary(preferred_locale)
project_status = ProjectStatus(project.status)
if project_status == ProjectStatus.DRAFT:
admin_projects_dto.draft_projects.append(pm_project)
elif project_status == ProjectStatus.PUBLISHED:
admin_projects_dto.active_projects.append(pm_project)
elif project_status == ProjectStatus.ARCHIVED:
admin_projects_dto.archived_projects.append(pm_project)
else:
current_app.logger.error(
f'Unexpected state project {project.id}')
return admin_projects_dto
def get_project_summary(self, preferred_locale) -> ProjectSummary:
""" Create Project Summary model for postgis project object"""
summary = ProjectSummary()
summary.project_id = self.id
summary.campaign_tag = self.campaign_tag
summary.created = self.created
summary.last_updated = self.last_updated
summary.mapper_level = MappingLevel(self.mapper_level).name
summary.organisation_tag = self.organisation_tag
summary.status = ProjectStatus(self.status).name
centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON())
summary.aoi_centroid = geojson.loads(centroid_geojson)
summary.percent_mapped = int(
((self.tasks_mapped + self.tasks_bad_imagery) / self.total_tasks) *
100)
summary.percent_validated = int(
(self.tasks_validated / self.total_tasks) * 100)
project_info = ProjectInfo.get_dto_for_locale(self.id,
preferred_locale,
self.default_locale)
summary.name = project_info.name
summary.short_description = project_info.short_description
return summary
def get_project_title(self, preferred_locale):
project_info = ProjectInfo.get_dto_for_locale(self.id,
preferred_locale,
self.default_locale)
return project_info.name
def get_aoi_geometry_as_geojson(self):
""" Helper which returns the AOI geometry as a geojson object """
aoi_geojson = db.engine.execute(self.geometry.ST_AsGeoJSON()).scalar()
return geojson.loads(aoi_geojson)
@staticmethod
@cached(active_mappers_cache)
def get_active_mappers(project_id) -> int:
""" Get count of Locked tasks as a proxy for users who are currently active on the project """
return Task.query \
.filter(Task.task_status.in_((TaskStatus.LOCKED_FOR_MAPPING.value,
TaskStatus.LOCKED_FOR_VALIDATION.value))) \
.filter(Task.project_id == project_id) \
.distinct(Task.locked_by) \
.count()
def _get_project_and_base_dto(self):
""" Populates a project DTO with properties common to all roles """
base_dto = ProjectDTO()
base_dto.project_id = self.id
base_dto.project_status = ProjectStatus(self.status).name
base_dto.default_locale = self.default_locale
base_dto.project_priority = ProjectPriority(self.priority).name
base_dto.area_of_interest = self.get_aoi_geometry_as_geojson()
base_dto.enforce_mapper_level = self.enforce_mapper_level
base_dto.enforce_validator_role = self.enforce_validator_role
base_dto.private = self.private
base_dto.mapper_level = MappingLevel(self.mapper_level).name
base_dto.entities_to_map = self.entities_to_map
base_dto.changeset_comment = self.changeset_comment
base_dto.due_date = self.due_date
base_dto.imagery = self.imagery
base_dto.josm_preset = self.josm_preset
base_dto.campaign_tag = self.campaign_tag
base_dto.organisation_tag = self.organisation_tag
base_dto.license_id = self.license_id
base_dto.created = self.created
base_dto.last_updated = self.last_updated
base_dto.author = User().get_by_id(self.author_id).username
base_dto.active_mappers = Project.get_active_mappers(self.id)
base_dto.task_creation_mode = TaskCreationMode(
self.task_creation_mode).name
if self.private:
# If project is private it should have a list of allowed users
allowed_usernames = []
for user in self.allowed_users:
allowed_usernames.append(user.username)
base_dto.allowed_usernames = allowed_usernames
if self.mapping_types:
mapping_types = []
for mapping_type in self.mapping_types:
mapping_types.append(MappingTypes(mapping_type).name)
base_dto.mapping_types = mapping_types
if self.priority_areas:
geojson_areas = []
for priority_area in self.priority_areas:
geojson_areas.append(priority_area.get_as_geojson())
base_dto.priority_areas = geojson_areas
return self, base_dto
def as_dto_for_mapping(self, locale: str) -> Optional[ProjectDTO]:
""" Creates a Project DTO suitable for transmitting to mapper users """
project, project_dto = self._get_project_and_base_dto()
project_dto.tasks = Task.get_tasks_as_geojson_feature_collection(
self.id)
project_dto.project_info = ProjectInfo.get_dto_for_locale(
self.id, locale, project.default_locale)
return project_dto
def all_tasks_as_geojson(self):
""" Creates a geojson of all areas """
project_tasks = Task.get_tasks_as_geojson_feature_collection(self.id)
return project_tasks
def as_dto_for_admin(self, project_id):
""" Creates a Project DTO suitable for transmitting to project admins """
project, project_dto = self._get_project_and_base_dto()
if project is None:
return None
project_dto.project_info_locales = ProjectInfo.get_dto_for_all_locales(
project_id)
return project_dto
class Point(Base):
__tablename__ = "point"
id = Column(Integer, primary_key=True)
geom = Column(Geometry(srid=4326, geometry_type="POINT"))
raw_geom = Column(RawGeometry(srid=4326, geometry_type="POINT"))
def data_route():
west = request.args.get('w')
east = request.args.get('e')
north = request.args.get('n')
south = request.args.get('s')
month = request.args.get('m')
nb = int(request.args.get('nb', config['max_nb_cities_at_once']))
if nb > config['max_nb_cities_at_once']:
nb = config['max_nb_cities_at_once']
if west is None or east is None or south is None or north is None:
return 'TODO 404'
rectangle = 'POLYGON(({0} {1}, {0} {2}, {3} {2}, {3} {1}, {0} {1}))' \
.format(west, south, north, east)
with session_scope() as session:
# choose N cities
sq = session.query(City) \
.filter(func.ST_Covers(
cast(rectangle, Geometry()),
func.ST_SetSRID(cast(City.location, Geometry()), 0))) \
.order_by(City.priority_index) \
.limit(nb).subquery('city')
# get their data
query = session.query(
sq.c.id,
sq.c.name,
func.ST_Y(cast(sq.c.location, Geometry())),
func.ST_X(cast(sq.c.location, Geometry())),
sq.c.population, MonthlyStat.month,
MonthlyStat.value, Stat.code, sq.c.country, sq.c.source) \
.join(MonthlyStat) \
.join(Stat) \
.filter(Stat.code.in_(['avgHigh', 'avgLow', 'precipitation',
'precipitationDays', 'monthlySunHours',
'rain', 'rainDays']))
if month is not None:
query = query.filter(MonthlyStat.month == month)
def default():
return {'month_stats': defaultdict(dict)}
cities = defaultdict(default)
# print(query)
# format what is returned from the query
for row in query:
id = row[0]
cities[id]['name'] = row[1]
cities[id]['coords'] = (row[2], row[3])
cities[id]['pop'] = row[4]
cities[id]['month_stats'][row[7]][row[5]] = row[6]
cities[id]['country'] = row[8]
cities[id]['source'] = row[9]
# changing rain to precipitation
# TODO something similar with snow?
for _,c in cities.items():
for old, new in [('rain', 'precipitation'),
('rainDays', 'precipitationDays')]:
if old in c['month_stats'] and new not in c['month_stats']:
c['month_stats'][new] = c['month_stats'].pop(old)
# from pprint import pprint
# pprint(cities)
return json.dumps(cities)
class RestrictionArea(Base):
__tablename__ = "restrictionarea"
__table_args__ = {"schema": _schema}
__colanderalchemy_config__ = {
"title": _("Restriction area"),
"plural": _("Restriction areas")
}
__c2cgeoform_config__ = {"duplicate": True}
id = Column(Integer,
primary_key=True,
info={"colanderalchemy": {
"widget": HiddenWidget()
}})
area = Column(
Geometry("POLYGON", srid=_srid),
info={
"colanderalchemy": {
"typ": ColanderGeometry("POLYGON", srid=_srid, map_srid=3857)
}
},
)
name = Column(Unicode, info={"colanderalchemy": {"title": _("Name")}})
description = Column(Unicode,
info={"colanderalchemy": {
"title": _("Description")
}})
readwrite = Column(Boolean,
default=False,
info={"colanderalchemy": {
"title": _("Read/write")
}})
# relationship with Role and Layer
roles = relationship(
"Role",
secondary=role_ra,
info={"colanderalchemy": {
"title": _("Roles"),
"exclude": True
}},
cascade="save-update,merge,refresh-expire",
backref=backref("restrictionareas",
info={
"colanderalchemy": {
"exclude": True,
"title": _("Restriction areas")
}
}),
)
layers = relationship(
"Layer",
secondary=layer_ra,
info={"colanderalchemy": {
"title": _("Layers"),
"exclude": True
}},
cascade="save-update,merge,refresh-expire",
backref=backref("restrictionareas",
info={
"colanderalchemy": {
"title": _("Restriction areas"),
"exclude": True
}
}),
)
def __init__(
self,
name: str = "",
description: str = "",
layers: List[Layer] = None,
roles: List[Role] = None,
area: Geometry = None,
readwrite: bool = False,
) -> None:
if layers is None:
layers = []
if roles is None:
roles = []
self.name = name
self.description = description
self.layers = layers
self.roles = roles
self.area = area
self.readwrite = readwrite
def __str__(self) -> str: # pragma: no cover
return self.name or ""
class Role(Base):
__tablename__ = "role"
__table_args__ = {"schema": _schema}
__colanderalchemy_config__ = {"title": _("Role"), "plural": _("Roles")}
__c2cgeoform_config__ = {"duplicate": True}
id = Column(Integer,
primary_key=True,
info={"colanderalchemy": {
"widget": HiddenWidget()
}})
name = Column(Unicode,
unique=True,
nullable=False,
info={"colanderalchemy": {
"title": _("Name")
}})
description = Column(Unicode,
info={"colanderalchemy": {
"title": _("Description")
}})
extent = Column(
Geometry("POLYGON", srid=_srid),
info={
"colanderalchemy": {
"typ": ColanderGeometry("POLYGON", srid=_srid, map_srid=3857)
}
},
)
# functionality
functionalities = relationship(
"Functionality",
secondary=role_functionality,
cascade="save-update,merge,refresh-expire",
info={
"colanderalchemy": {
"exclude": True,
"title": _("Functionalities")
}
},
)
def __init__(
self,
name: str = "",
description: str = "",
functionalities: List[Functionality] = None,
extent: Geometry = None,
) -> None:
if functionalities is None:
functionalities = []
self.name = name
self.functionalities = functionalities
self.extent = extent
self.description = description
def __str__(self) -> str:
return self.name or "" # pragma: no cover
@property
def bounds(self) -> None:
if self.extent is None:
return None
return to_shape(self.extent).bounds
class Photo(db.Model):
"""
A class representing the photo of the report and declare its attributes:
Attributes:
photo_id: Identification of the photo that the RESTful API
server generates.
report_id: Identification of the report this photo is attached to.
location: Geographic location where the photo has been taken.
accuracy: Accuracy in meters of the geographic location of the heritage.
bearing: Angle of the direction that the camera pointed to when
this photo has been taken. The bearing is the number of degrees
in the angle measured in a clockwise direction from the north line
to the line passing through the location of the camera in
the direction the camera was pointing at.
creation_time: Date and time when the photo has been registered to
the server platform.
update_time: Date and time of the most recent modification of one of
the attributes of this photo.
"""
__tablename__ = "photo"
# Identification of the photo that the RESTful API server generates.
photo_id = db.Column(UUID(as_uuid=True),
default=uuid.uuid4,
primary_key=True,
nullable=False)
# Identification of the report this photo is attached to.
report_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('report.report_id'),
nullable=False)
# Geographic location where the photo has been taken
location = db.Column(Geometry(geometry_type='POINTZ', dimension=3),
nullable=True)
# Accuracy in meters of the geographic location of the heritage
accuracy = db.Column(db.Float(), nullable=True)
# # Angle of the direction that the camera pointed to when
# # this photo has been taken.
bearing = db.Column(db.Float(), nullable=True)
# Define creation time
creation_time = db.Column(db.DateTime(timezone=True),
default=datetime.utcnow,
nullable=False)
# Define update time
update_time = db.Column(db.DateTime(timezone=True),
default=datetime.utcnow,
nullable=False)
def __init__(self, report_id, *args, **kwargs):
"""The constructor of the photo update """
super(Photo, self).__init__(report_id=report_id, *args, **kwargs)
@classmethod
def authenticate_photo(cls, photo):
"""Photo authentication: check if the provided photo is registered or not
Args:
photo (Photo Object): The provided photo which is to be authenticated
Raises:
ExistedPhotoInAnotherReportException: In case the provided photo
was uploaded in another report
Returns:
None: In case the provided photo was uploaded in the same report
Photo object: In case the provided photo have not yet uploaded
"""
image = Photo.query.filter_by(location=photo.location).first()
if image:
report = Report.query.filter_by(report_id=image.report_id)
if not report:
raise ExistedPhotoInAnotherReportException(image.report_id)
# Do not add to database if the photo was uploaded
return None
# In case photo have not yet uploaded, add photo object to database
db.session.add(photo)
db.session.commit()
# Update `update_time` of the report
report = Report.query.filter_by(report_id=photo.report_id)
report.update_time = datetime.utcnow()
db.session.commit()
return photo
def postgres(schema, tables, data_directory, psql_path, plpython, **params):
psql = local[psql_path]
data_directory = Path(data_directory)
logger.info('Initializing PostgreSQL...')
engine = init_database(
'postgresql', params, schema, isolation_level='AUTOCOMMIT'
)
engine.execute("CREATE SEQUENCE IF NOT EXISTS test_sequence;")
use_postgis = 'geo' in tables
if use_postgis:
engine.execute("CREATE EXTENSION IF NOT EXISTS POSTGIS")
if plpython:
engine.execute("CREATE EXTENSION IF NOT EXISTS PLPYTHONU")
query = "COPY {} FROM STDIN WITH (FORMAT CSV, HEADER TRUE, DELIMITER ',')"
database = params['database']
for table in tables:
src = data_directory / '{}.csv'.format(table)
# If we are loading the geo sample data, handle the data types
# specifically so that PostGIS understands them as geometries.
if table == 'geo':
if not use_postgis:
continue
from geoalchemy2 import Geometry, WKTElement
srid = 0
df = pd.read_csv(src)
df[df.columns[1:]] = df[df.columns[1:]].applymap(
lambda x: WKTElement(x, srid=srid)
)
df.to_sql(
'geo',
engine,
index=False,
dtype={
"geo_point": Geometry("POINT", srid=srid),
"geo_linestring": Geometry("LINESTRING", srid=srid),
"geo_polygon": Geometry("POLYGON", srid=srid),
"geo_multipolygon": Geometry("MULTIPOLYGON", srid=srid),
},
)
continue
load = psql[
'--host',
params['host'],
'--port',
params['port'],
'--username',
params['user'],
'--dbname',
database,
'--command',
query.format(table),
]
with local.env(PGPASSWORD=params['password']):
with src.open('r') as f:
load(stdin=f)
engine.execute('VACUUM FULL ANALYZE')
def _create_layer(self, exclude_properties=False, metadatas=None, geom_type=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
from sqlalchemy import Column, Table, types, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy2 import Geometry
from c2cgeoportal.models import DBSession, RestrictionArea, LayerWMS
self.__class__._table_index += 1
id = self.__class__._table_index
engine = DBSession.c2c_rw_bind
connection = engine.connect()
if not self.metadata:
self.metadata = declarative_base(bind=engine).metadata
tablename = "geo_table_{0:d}".format(id)
schemaname = "geodata"
table1 = Table(
"{0!s}_child".format(tablename), self.metadata,
Column("id", types.Integer, primary_key=True),
Column("name", types.Unicode),
schema=schemaname
)
self._tables.append(table1)
table2 = Table(
tablename, self.metadata,
Column("id", types.Integer, primary_key=True),
Column("child_id", types.Integer,
ForeignKey("{0!s}.{1!s}_child.id".format(schemaname, tablename))),
Column("name", types.Unicode, nullable=False),
Column("deleted", types.Boolean),
Column("last_update_user", types.Unicode),
Column("last_update_date", types.DateTime),
Column("date", types.Date),
Column("start_time", types.Time),
# Column("interval", Interval()),
Column("short_name1", types.String, nullable=True),
Column("short_name2", types.String(50), nullable=True),
Column("short_number", types.Integer, nullable=True),
Column("double_number", types.Float(precision=4)),
Column("large_binary", types.LargeBinary(length=60), nullable=True),
Column("value", types.Enum("one", "two", "three", name="an_enum_value")),
Column("numeric", types.Numeric(precision=5, scale=2), nullable=True),
Column("numeric2", types.Numeric(), nullable=True),
schema=schemaname
)
if geom_type:
table2.append_column(
Column("geom", Geometry("POINT", srid=21781))
)
else:
table2.append_column(
Column("geom", Geometry(srid=21781))
)
self._tables.append(table2)
table2.drop(checkfirst=True)
table1.drop(checkfirst=True)
table1.create()
table2.create()
ins = table1.insert().values(name="c1é")
connection.execute(ins).inserted_primary_key[0]
ins = table1.insert().values(name="c2é")
connection.execute(ins).inserted_primary_key[0]
layer = LayerWMS(name="test_WMS_1", public=True)
layer.layer = "test_wms"
layer.id = id
layer.geo_table = "{0!s}.{1!s}".format(schemaname, tablename)
layer.interfaces = [self.main]
layer.ogc_server = self.ogc_server
if exclude_properties:
layer.exclude_properties = "name"
if metadatas:
layer.metadatas = metadatas
DBSession.add(self.layer_group_1)
self.layer_group_1.children = self.layer_group_1.children + [layer]
DBSession.add(self.layer_group_1)
ra = RestrictionArea()
ra.name = "__test_ra"
ra.layers = [layer]
ra.roles = [self.role]
ra.readwrite = True
DBSession.add(ra)
transaction.commit()
self.layer_ids.append(id)
return id
class Entry(Base):
r"""Entry
The Entry is the main entity in metacatalog. An object instance models a
set of metadata needed to store and manage a datasource. The Entry is not
the actual data.
The Entry is designed to store all necessary information to be exportable
in ISO19115 in the scope of metacatalog. That means, Properties which are
always the same across metacatalog, or can be derived from the actual
implementation, are not part of an Entry.
Attributes
----------
id : int
Unique id of the record. If not specified, the database will assign it.
uuid : str
.. versionadded:: 0.1.9
Version 4 UUID string to identify the Entry across installations.
This field is read-only and will be assigned on creation. It is primarily
used to export Entry into ISO19115 metadata.
title : str
A full title (512) to describe the datasource as well as possible.
The truncated title (first 25 signs) is usually used to print an
Entry object to the console.
abstract : str
Full abstract of the datasource. The abstract should include all
necessary information that is needed to fully understand the data.
external_id : str
Any kind of OID that was used to identify the data in the first place.
Usually an unque ID field of other data-storage solutions. The
exernal_id is only stored for reference reasons.
location : str, tuple
The location as a POINT Geometry in unprojected WGS84 (EPSG: 4326).
The location is primarily used to show all Entry objects on a map, or
perform geo-searches. If the data-source needs to store more complex
Geometries, you can use the ``geom`` argument.
The location can be passed as WKT or a tuple of (x, y) coordinates.
Note that it will be returned and stored as WKB. The output value will
be reworked in a future release
geom : str
.. deprecated:: 0.1.11
The geom attribute will be reomved with version 0.2
.. warning::
The geom attribute is completely untested so far and might be
reworked or removed in a future release
It takes a WKT of any kind of OGC-conform Geometry. The return value
will be the same Geometry as WKB.
creation : datetime.datetime
Following the ISO19115 the *creation* date is referring to the creation
date of the **data resource** described by the Entry, not the Entry
itself. If creation date is not set, it is assumed, that yet no data
resource is connected to the Entry.
end : datetime.datimetime
The last date the data source described by this Entry has data for.
The end date is **not** ISO19115-compliant and will be reworked.
version : int
The version of this Entry. Usually metacatalog will handle the version
itself and there is not need to set the version manually.
latest_version_id : int
Foreign key to `Entry.id`. This key is self-referencing the another
Entry. This has to be set if the current Entry is not the latest one.
If latest_version_id is None, the Entry is the most recent one and
database operations that find multiple entries will in a future release
filter to 'version duplicates'.
is_partial : bool
.. versionadded:: 0.1.10
If an Entry is partial, it is not self-contained and **has** to be part
of a :class:`EntryGroup <metacatalog.models.EntryGroup>` of type
composite.
.. note::
To make it possbile to add partial Entrys via the models submodule,
The Entry class itself will **not** check integrity. This has to
be done on adding partial Entry records, or by checking the database
comment : str
Arbitrary free-text comment to the Entry
citation : str
.. versionadded:: 0.1.13
Citation informatio for this Entry. Note, that metacatalog does not
assign DOIs and thus a citation is only useful if the associated
data has a DOI and the bibliographic information applies to the Entry
as well.
.. note::
Metacatalog does not manage bibliography. Thus it is highly
recommended to use thrid party software for management and only
export the reference to the resource in a common citation style.
license : metacatalog.models.License
Data License associated to the data and the metadata. You can pass
the `License <metacatalog.models.License>`_ itself, or use the
license_id attribute.
license_id : int
Foreign key to the data license.
author : metacatalog.models.Person
:class:`Person <metacatalog.models.Person>` that acts as first author
for the given entry. Only one first author is possible, co-authors can
be requested from either the contributors list or the
:py:attr:`authors` property. `author` is a property and setting a
new author using this property is not supported.
authors : list
List of :class:`Person <metacatalog.models.Person>`. The first element
is the first author, see :py:attr:`~author`. The others are
:class:`Person <metacatalog.models.Person>`s associated with the
:class:`Role <metacatalog.models.PersonRole>` of ``'coAuthor' ``.
The list of authors is sorted by the `order` attribute.
`authors` is a property and setting a new list of authors using this
property is not supported.
Note
----
One Entry object instance is always described by exactly one variable.
If a datasource is a composite of many datasources, there are two
strategies. Either a new table can be implemented and an abstract
:class:`Variable <metacatalog.models.Variable>` be added. This is done with
Eddy-Covariance data. Secondly, Each variable of the datasource can be
represented by its own Entry, which get then grouped by an
:class:`EntryGroup` of :class:`EntryGroupType` ``'composite'``.
See Also
--------
`EntryGroup`
`EntryGroupType
"""
__tablename__ = 'entries'
# columns
id = Column(Integer, primary_key=True, autoincrement=True)
uuid = Column(String(36), nullable=False, default=lambda: str(uuid4()))
title = Column(String(512), nullable=False)
abstract = Column(String)
external_id = Column(String)
location = Column(Geometry(geometry_type='POINT', srid=4326),
nullable=False)
geom = Column(Geometry)
version = Column(Integer, default=1, nullable=False)
latest_version_id = Column(Integer,
ForeignKey('entries.id'),
nullable=True)
is_partial = Column(Boolean, default=False, nullable=False)
comment = Column(String, nullable=True)
citation = Column(String(2048), nullable=True)
license_id = Column(Integer, ForeignKey('licenses.id'))
variable_id = Column(Integer, ForeignKey('variables.id'), nullable=False)
datasource_id = Column(Integer, ForeignKey('datasources.id'))
embargo = Column(Boolean, default=False, nullable=False)
embargo_end = Column(DateTime, default=get_embargo_end)
publication = Column(DateTime, default=dt.utcnow)
lastUpdate = Column(DateTime, default=dt.utcnow, onupdate=dt.utcnow)
# relationships
contributors = relationship("PersonAssociation",
back_populates='entry',
cascade='all, delete, delete-orphan')
keywords = relationship("KeywordAssociation",
back_populates='entry',
cascade='all, delete, delete-orphan')
license = relationship("License", back_populates='entries')
variable = relationship("Variable", back_populates='entries')
datasource = relationship("DataSource",
back_populates='entries',
cascade='all, delete, delete-orphan',
single_parent=True)
other_versions = relationship("Entry",
backref=backref('latest_version',
remote_side=[id]))
associated_groups = relationship("EntryGroup",
secondary="nm_entrygroups",
back_populates='entries')
details = relationship("Detail", back_populates='entry')
# extensions
io_extension = None
io_interface = None
def to_dict(self, deep=False, stringify=False) -> dict:
"""To dict
Return the model as a python dictionary.
Parameters
----------
deep : bool
If True, all related objects will be included as
dictionary. Defaults to False
stringify : bool
If True, all values will be turned into a string,
to make the object serializable.
Returns
-------
obj : dict
The Model as dict
"""
# base dictionary
d = dict(id=self.id,
uuid=self.uuid,
title=self.title,
author=self.author.to_dict(deep=False),
authors=[a.to_dict(deep=False) for a in self.authors],
locationShape=self.location_shape.wkt,
location=self.location_shape.wkt,
variable=self.variable.to_dict(deep=False),
embargo=self.embargo,
embargo_end=self.embargo_end,
version=self.version,
isPartial=self.is_partial,
publication=self.publication,
lastUpdate=self.lastUpdate,
keywords=self.plain_keywords_dict())
# optional relations
if self.license is not None:
d['license'] = self.license.to_dict(deep=False)
if self.details is not None:
d['details'] = self.details_dict(full=True)
if self.datasource is not None:
d['datasource'] = self.datasource.to_dict(deep=False)
# set optional attributes
for attr in ('abstract', 'external_id', 'comment', 'citation'):
if hasattr(self, attr) and getattr(self, attr) is not None:
d[attr] = getattr(self, attr)
# lazy loading
if deep:
projects = self.projects
if len(projects) > 0:
d['projects'] = [p.to_dict(deep=False) for p in projects]
comp = self.composite_entries
if len(comp) > 0:
d['composite_entries'] = [e.to_dict(deep=False) for e in comp]
if stringify:
return serialize(d, stringify=True)
return d
@classmethod
def is_valid(cls, entry):
return isinstance(entry, Entry) and entry.id is not None
@property
def checksum(self):
"""
.. versionadded:: 0.3.8
MD5 checksum of this entry. The checksum will change if any of the linked
Metadata changes. This can be used in application built on metacatalog to
verify integrity.
"""
# get a dict_representation
d = self.to_dict(deep=True, stringify=True)
# calculate the hash
md5 = hashlib.md5(json.dumps(d).encode()).hexdigest()
return md5
@property
def is_latest_version(self):
self.latest_version_id == self.id or self.latest_version_id is None
@property
def latest_version(self):
versions = [e.version for e in self.other_versions]
# no other versions, then self is the only
if len(versions) == 1:
return self
# if more versions exist, find the highest number
latest_index = versions.index(max(versions))
return self.other_versions[latest_index]
@property
def author(self):
return [
c.person for c in self.contributors if c.role.name == 'author'
][0]
@author.setter
def author(self, new_author):
self.set_new_author(new_author)
def set_new_author(self, new_author, commit=False):
"""
Set a new first Author for this entry.
Parameters
----------
new_author : metacatalog.models.Person
The new first author. As of now the new author has to be passed as a
model instance. Passing the ID or query parameter is not yet supported.
commit : boolean
If True, the whole :class:`Entry <metacatalog.models.Entry>` will commit
and persist itself to the database.
.. note::
This will also affect other uncommited edits to the Entry.
"""
if not isinstance(new_author, models.Person):
raise AttributeError(
'The new author has to be of type metatacatalog.models.Person')
# find the association
assoc_idx = [
i for i, c in enumerate(self.contributors)
if c.role.name == 'author'
][0]
self.contributors[assoc_idx].person = new_author
if commit:
session = object_session(self)
try:
session.add(self)
session.commit()
except Exception as e:
session.rollback()
raise e
@property
def authors(self):
# get all
coAuthors = [c for c in self.contributors if c.role.name == 'coAuthor']
# order
idx = np.argsort([c.order for c in coAuthors])
# build the author list
authors = [self.author]
for i in idx:
authors.append(coAuthors[i].person)
return authors
@property
def projects(self):
return [
group for group in self.associated_groups
if group.type.name.lower() == 'project'
]
@property
def composite_entries(self):
return [
group for group in self.associated_groups
if group.type.name.lower() == 'composite'
]
@property
def location_shape(self):
return to_shape(self.location)
@location_shape.setter
def location_shape(self, shape):
self.location = from_shape(shape)
def plain_keywords_list(self):
"""Metadata Keyword list
Returns list of controlled keywords associated with this
instance of meta data.
If there are any associated values or alias of the given
keywords, use the keywords_dict function
"""
return [kw.keyword.path() for kw in self.keywords]
def plain_keywords_dict(self):
return [kw.keyword.as_dict() for kw in self.keywords]
def keywords_dict(self):
return [
dict(path=kw.keyword.full_path,
alias=kw.alias,
value=kw.associated_value) for kw in self.keywords
]
def details_dict(self, full=True):
"""
Returns the associated details as dictionary.
Parameters
----------
full : bool
If True (default) the keywords will contain the
full info including key description, ids and
stemmed key. If false, it will be truncated to a
plain key:value dict
"""
if full:
return {d.stem: d.to_dict() for d in self.details}
else:
return {d.stem: d.value for d in self.details}
def details_table(self, fmt='html'):
"""
Return the associated details as table
Parameters
----------
fmt : string
Can be one of:
* `html` to return a HTML table
* `latex` to return LaTeX table
* `markdown` to return Markdown table
"""
# get the details
details = dict()
for key, detail in self.details_dict(full=True).items():
if isinstance(detail['value'], dict):
expand = {
f'{key}.{k}': dict(value=v,
id=detail['id'],
key=detail['key'],
stem=detail['stem'])
for k, v in detail['value'].items()
}
details.update(expand)
else:
details[key] = detail
# turn into a transposed datarame
df = pd.DataFrame(details).T
# output table
if fmt.lower() == 'html':
return df.to_html()
elif fmt.lower() == 'latex':
return df.to_latex()
elif fmt.lower() == 'markdown' or fmt.lower() == 'md':
return df.to_markdown()
else:
raise ValueError("fmt has to be in ['html', 'latex', 'markdown']")
def add_details(self, details=None, commit=False, **kwargs):
"""
Adds arbitrary key-value pairs to this entry.
Parameters
----------
details : list
.. versionadded:: 0.1.8
List of dict of structure:
.. code-block::
[{
'key': '',
'value': '',
'description': ''
}]
where the ``description`` is optional and can be omitted.
If no descriptions are passed at all, you can also use `**kwargs`
to pass ``key=value`` pairs.
commit : bool
If True, the Entry session will be added to the
current session and the transaction is commited.
Can have side-effects. Defaults to False.
"""
ps = nltk.PorterStemmer()
# build entries here
detail_list = []
# parse kwargs
for k, v in kwargs.items():
detail_list.append({
'entry_id': self.id,
'key': str(k),
'stem': ps.stem(k),
'value': v
})
# parse details
if details is not None:
for detail in details:
d = {
'entry_id': self.id,
'key': detail['key'],
'stem': ps.stem(detail['key']),
'value': detail['value']
}
if 'description' in detail.keys():
d['description'] = detail['description']
detail_list.append(d)
# build the models
for detail in detail_list:
self.details.append(models.Detail(**detail))
if commit:
session = object_session(self)
try:
session.add(self)
session.commit()
except Exception as e:
session.rollback()
raise e
def export(self, path=None, fmt='JSON', **kwargs):
r"""
Export the Entry. Exports the data using a metacatalog extension.
Refer to the note below to learn more about export extensions.
Parameters
----------
path : str
If set, the export will be written into a file at the given
location.
fmt : str
Export format. Each export extension should at least support
json and XML export.
**kwargs
Any other argument given will be passed down to the actual
export function.
Notes
-----
Uses any extension prefixed with 'export-' activated, by passing
itself to the extension. If not format-specific extension is activated,
the default :class:`ExportExtension <metacatalog.ext.export.ExportExtension>`
will be used. A method of same name as ``fmt`` on the extension will be used.
If such a method is not present, the 'export' method is used and the fmt
attribute will be passed along. This can be used for format specific
extensions.
Refer to the notes about :any:`custom extensions <metacatalog.ext.base>`
to learn more about writing your own export extension.
Consider this example:
.. code-block:: Python
from metacatalog.ext import MetacatalogExtensionInterface
import json
class RawJSONExtension(MetacatalogExtensionInterface):
@classmethod
def init_extension(cls):
pass
@classmethod
def json(cls, entry, path, **kwargs):
# get the dict
data = entry.to_dict(stringify=True)
if path is None:
return data
else:
with open(path, 'w') as f:
json.dump(data, f, indent=kwargs.get('indent', 4))
You can activate and use it like:
>> from metacatalog import ext
>> ext.extension('export', RawJSONEXtension)
>> entry.export(path='testfile.json', fmt='json', indent=2)
"""
# load the extension
from metacatalog import ext
try:
Export = ext.extension(f'export-{fmt.lower()}')
except AttributeError:
try:
Export = ext.extension('export')
except AttributeError:
from metacatalog.ext.export import ExportExtension as Export
# get the export function
if hasattr(Export, fmt.lower()):
exp_function = getattr(Export, fmt.lower())
elif hasattr(Export, 'export'):
exp_function = getattr(Export, 'export')
else:
raise AttributeError(
f'The current export extension cannot export {fmt}')
# return
return exp_function(self, path=path, **kwargs)
def make_composite(self,
others=[],
title=None,
description=None,
commit=False):
"""
Create a composite EntryGroup from this Entry. A composite marks
stand-alone (:attr:`is_partial` ``= False``) entries as inseparable.
A composite can also contain a partial Entry
(:attr:`is_partial` ``= True``), whichs data only makes sense in the
context of the composite group.
Parameters
----------
others : list of Entry
The other :class:`Entries <metacatalog.models.Entry>` that
should be part of the composite.
title : str
Optional title of the composite, if applicable
description : str
Optional description of the composite if applicable
commit : bool
If True, the newly created Group will be persisted in the
database. Defaults to False.
Returns
-------
composite : metacatalog.models.EntryGroup
The newly created EntryGroup of EntryGroupType.name == 'Composite'
"""
# check type of others
if isinstance(others, Entry):
others = [others]
if not all([isinstance(e, Entry) for e in others]):
raise AttributeError("others has to be a list of Entry instances")
others.append(self)
# get a session
session = object_session(self)
type_ = api.find_group_type(session, name='Composite')[0]
composite = models.EntryGroup(type=type_,
title=title,
description=description,
entries=others)
if commit:
try:
session.add(composite)
session.commit()
except Exception as e:
session.rollback()
raise e
# return
return composite
def neighbors(self,
distance,
unit='meter',
buffer_epsg=3857,
as_sql=False,
**kwargs):
"""
Find neighboring :class:`Entries <metacatalog.models.Entry>` around the
location of this instance. You can return the result, or the sqlalchemy
Query object, which can be printed as plain SQL.
Parameters
----------
distance : int, float
The maximum distance at which another Entry is still considered to be a neighbor.
unit : str
Has to be one of ['meter', 'km', 'mile', 'nautic'] to specify the unit
of the given distance. Note that the distance will always be transformed
into meter.
buffer_epsg : int
The EPSG identification number of any projected cartesian coordinate
reference system that uses meter as unit. This CRS will be used to
apply the search distance (in meter).
.. note::
The default system is the transversal Mercartor projection, which is
a global system. Thus, it can always be applied, but may introduce
large uncertainties in small areas. Replace this attribute by a
local CRS wherever possible.
as_sql : bool
If False (default) the SQL query for neighbors will be executed and
the result is returned. Else, the SQL query itself will be returned.
kwargs : keyword arguments
Any passed keyword argument will be passed down to the
:func:`api.find_entry <metacatalog.api.find_entry>` function to further
filter the results.
See Also
--------
:func:`around <metacatalog.util.location.around>`
:func:`find_entry <metacatalog.api.find_entry>`
"""
# open a session
session = object_session(self)
# get the base filter query
kwargs['return_iterator'] = True
query = api.find_entry(session, **kwargs)
# get the area
filter_query = around(self,
distance=distance,
unit=unit,
query=query,
buffer_use_epsg=buffer_epsg)
if as_sql:
return filter_query
else:
return filter_query.all()
def create_datasource(self,
path: str,
type,
datatype,
commit=False,
**args):
"""
"""
#
if self.datasource is not None:
raise MetadataMissingError(
'Datasource already exists. You can edit that one.')
# get a session
session = object_session(self)
# load the datasource type
if isinstance(type, int):
ds_type = api.find_datasource_type(session=session,
id=type,
return_iterator=True).one()
elif isinstance(type, str):
ds_type = api.find_datasource_type(session=session,
name=type,
return_iterator=True).first()
else:
raise AttributeError('type has to be of type int or str')
# TODO need the API for DataTypes here!!
dtype = session.query(
models.DataType).filter(models.DataType.name == datatype).one()
# build the datasource object
ds = models.DataSource(type=ds_type, datatype=dtype, path=path)
# add the args
ds.save_args_from_dict(args)
# append to self
self.datasource = ds
if commit:
try:
session.add(self)
session.commit()
except Exception as e:
session.rollback()
raise e
# return
return ds
def get_data(self, **kwargs):
"""
.. versionchanged:: 0.1.12
Read the data. This is only possible if a datasource is specified and
any kind of IOExtension or IOInterface is activated. By default,
the builtin :class:`IOExtension <metacatalog.ext.io.extension.IOExtension>`
is activated since version 0.1.12.
"""
if self.datasource is None:
raise MetadataMissingError('Entry need datasource information')
try:
# check if an io_extension is set
if self.io_extension is not None:
return self.io_extension.read(**kwargs)
# if no extension instance, maybe an interface class is set
elif self.io_interface is not None:
reader = self.io_interface.get_reader(self.datasource)
return reader(self, self.datasource, **kwargs)
else:
raise IOOperationNotFoundError(
"No IO interface activated. Run metacatalog.ext.extension('io', InterfaceClass) to register"
)
except IOOperationNotFoundError as e:
print('[ERROR]: Operation not possible.\n%s' % str(e))
return None
def import_data(self, data, **kwargs):
"""
.. versionchanged:: 0.1.12
Import data. This is only possible if a datasource is specified and
any kind of IOExtension or IOInterface is activated. By default,
the builtin :class:`IOExtension <metacatalog.ext.io.extension.IOExtension>`
is activated since version 0.1.12.
For the default interface, the datasource type and data type determine
where the data will be stored and how the data has to look like.
You can easily inherit from the
:class:`IOExtension <metacatalog.ext.io.extension.IOExtension>` to
customize read and write behaviour. If you import i.e. a timeseries to
the same database as metacatalog, you will need to prepared data to
to only hold an datetime index and the data to be stored.
"""
if self.datasource is None:
raise MetadataMissingError('Entry need datasource information')
try:
# check if an io_extension is set
if self.io_extension is not None:
return self.io_extension.import_(data, **kwargs)
# if no extension instance, maybe an interface class is set
elif self.io_interface is not None:
importer = self.io_interface.get_importer(self.datasource)
return importer(self, self.datasource, data, **kwargs)
else:
raise IOOperationNotFoundError(
"No IO interface activated. Run metacatalog.ext.extension('io', InterfaceClass) to register"
)
except IOOperationNotFoundError as e:
print('[ERROR]: Operation not possible.\n%s' % str(e))
return None
def append_data(self, data, **kwargs):
"""
.. versionadded:: 0.1.12
Append data. This is only possible if a datasource is specified and
any kind of IOExtension or IOInterface is activated. By default,
the builtin :class:`IOExtension <metacatalog.ext.io.extension.IOExtension>`
is activated since version 0.1.12.
For the default interface, the datasource type and data type determine
where the data will be stored and how the data has to look like.
You can easily inherit from the
:class:`IOExtension <metacatalog.ext.io.extension.IOExtension>` to
customize read and write behaviour. If you import i.e. a timeseries to
the same database as metacatalog, you will need to prepared data to
to only hold an datetime index and the data to be stored.
"""
if self.datasource is None:
raise MetadataMissingError('Entry need datasource information')
try:
# check if an io_extension is set
if self.io_extension is not None:
return self.io_extension.append(data, **kwargs)
# if no extension instance, maybe an interface class is set
elif self.io_interface is not None:
appender = self.io_interface.get_appender(self.datasource)
return appender(self, self.datasource, data, **kwargs)
else:
raise IOOperationNotFoundError(
"No IO interface activated. Run metacatalog.ext.extension('io', InterfaceClass) to register"
)
except IOOperationNotFoundError as e:
print('[ERROR]: Operation not possible.\n%s' % str(e))
return None
def delete_data(self, delete_source=False, **kwargs):
"""
.. versionadded:: 0.1.12
Delete data. This is only possible if a datasource is specified and
any kind of IOExtension or IOInterface is activated. By default,
the builtin :class:`IOExtension <metacatalog.ext.io.extension.IOExtension>`
is activated since version 0.1.12.
For the default interface, the datasource type and data type determine
where the data is stored and how the data will be delted.
You can easily inherit from the
:class:`IOExtension <metacatalog.ext.io.extension.IOExtension>` to
customize read and write behaviour.
Parameters
----------
delete_source : bool
If True, the DataSource will be deleted as well after the data
has been deleted.
"""
if self.datasource is None:
raise MetadataMissingError('Entry need datasource information')
kwargs['delete_source'] = delete_source
try:
# check if an io_extension is set
if self.io_extension is not None:
return self.io_extension.delete(**kwargs)
# if no extension instance, maybe an interface class is set
elif self.io_interface is not None:
deleter = self.io_interface.get_deleter(self.datasource)
return deleter(self, self.datasource, **kwargs)
else:
raise IOOperationNotFoundError(
"No IO interface activated. Run metacatalog.ext.extension('io', InterfaceClass) to register"
)
except IOOperationNotFoundError as e:
print('[ERROR]: Operation not possible.\n%s' % str(e))
return None
def __str__(self):
return "<ID=%d %s [%s] >" % (self.id, self.title[:20],
self.variable.name)
class Registration(Base):
__tablename__ = 'registration'
id = Column(Integer, primary_key=True)
jid = Column(String, index=True, nullable=False)
point = Column(Geometry(geometry_type='POINT', management=True),
nullable=False)
class Location(Base):
__tablename__ = "location"
__table_args__ = (
Index("idx_location_geom", "geom", postgresql_using="gist"),
Index("idx_location_boundary", "boundary", postgresql_using="gist"),
)
id = Column(Integer, autoincrement=True, nullable=False, primary_key=True)
# station_id = Column(Integer, ForeignKey("station.id"))
# @TODO sort out this join based on this lateral query ..
# @NOTE this might not be the best way to do this as
# the closest weather station is not always the most relevant
# select
# l.id,
# l.locality,
# l.state,
# closest_station.state,
# closest_station.code,
# closest_station.dist
# from location l
# left join lateral (
# select
# code, state, ST_Distance(l.geom, bom_station.geom) / 1000 as dist
# from bom_station order by l.geom <-> bom_station.geom limit 1
# ) AS closest_station on TRUE;
# weather_station = relationship(
# "BomStation",
# primaryjoin=\
# "func.ST_ClosestPoint(remote(BomStation.geom), \
# foreign(Location.geom))",
# viewonly=True,
# uselist=True,
# lazy="joined",
# )
address1 = Column(Text)
address2 = Column(Text)
locality = Column(Text)
state = Column(Text)
postcode = Column(Text, nullable=True)
revisions = relationship("Revision", lazy="joined")
# an OSM way id such as 395531577
osm_way_id = Column(Text, nullable=True)
# Geo fields
place_id = Column(Text, nullable=True, index=True)
geocode_approved = Column(Boolean, default=False)
geocode_skip = Column(Boolean, default=False)
geocode_processed_at = Column(DateTime, nullable=True)
geocode_by = Column(Text, nullable=True)
geom = Column(Geometry("POINT", srid=4326, spatial_index=False))
boundary = Column(Geometry("POLYGON", srid=4326, spatial_index=True))
@hybrid_property
def lat(self) -> Optional[float]:
if self.geom:
return wkb.loads(bytes(self.geom.data)).y
return None
@hybrid_property
def lng(self) -> Optional[float]:
if self.geom:
return wkb.loads(bytes(self.geom.data)).x
return None
class Project(db.Model):
""" Describes a HOT Mapping Project """
__tablename__ = "projects"
# Columns
id = db.Column(db.Integer, primary_key=True)
status = db.Column(db.Integer,
default=ProjectStatus.DRAFT.value,
nullable=False)
created = db.Column(db.DateTime, default=timestamp, nullable=False)
priority = db.Column(db.Integer, default=ProjectPriority.MEDIUM.value)
default_locale = db.Column(
db.String(10), default="en"
) # The locale that is returned if requested locale not available
author_id = db.Column(db.BigInteger,
db.ForeignKey("users.id", name="fk_users"),
nullable=False)
mapper_level = db.Column(
db.Integer, default=2, nullable=False,
index=True) # Mapper level project is suitable for
mapping_permission = db.Column(db.Integer,
default=MappingPermission.ANY.value)
validation_permission = db.Column(
db.Integer, default=ValidationPermission.ANY.value
) # Means only users with validator role can validate
enforce_random_task_selection = db.Column(
db.Boolean, default=False
) # Force users to edit at random to avoid mapping "easy" tasks
private = db.Column(db.Boolean,
default=False) # Only allowed users can validate
featured = db.Column(
db.Boolean, default=False) # Only PMs can set a project as featured
entities_to_map = db.Column(db.String)
changeset_comment = db.Column(db.String)
osmcha_filter_id = db.Column(
db.String) # Optional custom filter id for filtering on OSMCha
due_date = db.Column(db.DateTime)
imagery = db.Column(db.String)
josm_preset = db.Column(db.String)
id_presets = db.Column(ARRAY(db.String))
last_updated = db.Column(db.DateTime, default=timestamp)
license_id = db.Column(db.Integer,
db.ForeignKey("licenses.id", name="fk_licenses"))
geometry = db.Column(Geometry("MULTIPOLYGON", srid=4326))
centroid = db.Column(Geometry("POINT", srid=4326))
country = db.Column(ARRAY(db.String), default=[])
task_creation_mode = db.Column(db.Integer,
default=TaskCreationMode.GRID.value,
nullable=False)
organisation_id = db.Column(
db.Integer,
db.ForeignKey("organisations.id", name="fk_organisations"),
index=True,
)
# Tags
mapping_types = db.Column(ARRAY(db.Integer), index=True)
# Editors
mapping_editors = db.Column(
ARRAY(db.Integer),
default=[
Editors.ID.value,
Editors.JOSM.value,
Editors.POTLATCH_2.value,
Editors.FIELD_PAPERS.value,
Editors.CUSTOM.value,
],
index=True,
nullable=False,
)
validation_editors = db.Column(
ARRAY(db.Integer),
default=[
Editors.ID.value,
Editors.JOSM.value,
Editors.POTLATCH_2.value,
Editors.FIELD_PAPERS.value,
Editors.CUSTOM.value,
],
index=True,
nullable=False,
)
# Stats
total_tasks = db.Column(db.Integer, nullable=False)
tasks_mapped = db.Column(db.Integer, default=0, nullable=False)
tasks_validated = db.Column(db.Integer, default=0, nullable=False)
tasks_bad_imagery = db.Column(db.Integer, default=0, nullable=False)
# Mapped Objects
tasks = db.relationship(Task,
backref="projects",
cascade="all, delete, delete-orphan",
lazy="dynamic")
project_info = db.relationship(ProjectInfo, lazy="dynamic", cascade="all")
project_chat = db.relationship(ProjectChat, lazy="dynamic", cascade="all")
author = db.relationship(User)
allowed_users = db.relationship(User, secondary=project_allowed_users)
priority_areas = db.relationship(
PriorityArea,
secondary=project_priority_areas,
cascade="all, delete-orphan",
single_parent=True,
)
custom_editor = db.relationship(CustomEditor, uselist=False)
favorited = db.relationship(User,
secondary=project_favorites,
backref="favorites")
organisation = db.relationship(Organisation, backref="projects")
campaign = db.relationship(Campaign,
secondary=campaign_projects,
backref="projects")
interests = db.relationship(Interest,
secondary=project_interests,
backref="projects")
def create_draft_project(self, draft_project_dto: DraftProjectDTO):
"""
Creates a draft project
:param draft_project_dto: DTO containing draft project details
:param aoi: Area of Interest for the project (eg boundary of project)
"""
self.project_info.append(
ProjectInfo.create_from_name(draft_project_dto.project_name))
self.status = ProjectStatus.DRAFT.value
self.author_id = draft_project_dto.user_id
self.last_updated = timestamp()
def set_project_aoi(self, draft_project_dto: DraftProjectDTO):
""" Sets the AOI for the supplied project """
aoi_geojson = geojson.loads(
json.dumps(draft_project_dto.area_of_interest))
aoi_geometry = GridService.merge_to_multi_polygon(aoi_geojson,
dissolve=True)
valid_geojson = geojson.dumps(aoi_geometry)
self.geometry = ST_SetSRID(ST_GeomFromGeoJSON(valid_geojson), 4326)
self.centroid = ST_Centroid(self.geometry)
def set_default_changeset_comment(self):
""" Sets the default changeset comment"""
default_comment = current_app.config["DEFAULT_CHANGESET_COMMENT"]
self.changeset_comment = (
f"{default_comment}-{self.id} {self.changeset_comment}"
if self.changeset_comment is not None else
f"{default_comment}-{self.id}")
self.save()
def set_country_info(self):
""" Sets the default country based on centroid"""
lat, lng = (db.session.query(
cast(ST_Y(Project.centroid), sqlalchemy.String),
cast(ST_X(Project.centroid), sqlalchemy.String),
).filter(Project.id == self.id).one())
url = "https://nominatim.openstreetmap.org/reverse?format=jsonv2&lat={0}&lon={1}".format(
lat, lng)
country_info = requests.get(url)
country_info_json = country_info.content.decode("utf8").replace(
"'", '"')
# Load the JSON to a Python list & dump it back out as formatted JSON
data = json.loads(country_info_json)
if data["address"].get("country") is not None:
self.country = [data["address"]["country"]]
else:
self.country = [data["address"]["county"]]
self.save()
def create(self):
""" Creates and saves the current model to the DB """
db.session.add(self)
db.session.commit()
def save(self):
""" Save changes to db"""
db.session.commit()
@staticmethod
def clone(project_id: int, author_id: int):
""" Clone project """
cloned_project = Project.get(project_id)
# Remove clone from session so we can reinsert it as a new object
db.session.expunge(cloned_project)
make_transient(cloned_project)
# Re-initialise counters and meta-data
cloned_project.total_tasks = 0
cloned_project.tasks_mapped = 0
cloned_project.tasks_validated = 0
cloned_project.tasks_bad_imagery = 0
cloned_project.last_updated = timestamp()
cloned_project.created = timestamp()
cloned_project.author_id = author_id
cloned_project.status = ProjectStatus.DRAFT.value
cloned_project.id = None # Reset ID so we get a new ID when inserted
cloned_project.geometry = None
cloned_project.centroid = None
db.session.add(cloned_project)
db.session.commit()
# Now add the project info, we have to do it in a two stage commit because we need to know the new project id
original_project = Project.get(project_id)
for info in original_project.project_info:
db.session.expunge(info)
make_transient(
info
) # Must remove the object from the session or it will be updated rather than inserted
info.id = None
info.project_id_str = str(cloned_project.id)
cloned_project.project_info.append(info)
# Now add allowed users now we know new project id, if there are any
for user in original_project.allowed_users:
cloned_project.allowed_users.append(user)
# Add other project metadata
cloned_project.priority = original_project.priority
cloned_project.default_locale = original_project.default_locale
cloned_project.mapper_level = original_project.mapper_level
cloned_project.mapping_permission = original_project.mapping_permission
cloned_project.validation_permission = original_project.validation_permission
cloned_project.enforce_random_task_selection = (
original_project.enforce_random_task_selection)
cloned_project.private = original_project.private
cloned_project.entities_to_map = original_project.entities_to_map
cloned_project.due_date = original_project.due_date
cloned_project.imagery = original_project.imagery
cloned_project.josm_preset = original_project.josm_preset
cloned_project.license_id = original_project.license_id
cloned_project.mapping_types = original_project.mapping_types
# We try to remove the changeset comment referencing the old project. This
# assumes the default changeset comment has not changed between the old
# project and the cloned. This is a best effort basis.
default_comment = current_app.config["DEFAULT_CHANGESET_COMMENT"]
changeset_comments = []
if original_project.changeset_comment is not None:
changeset_comments = original_project.changeset_comment.split(" ")
if f"{default_comment}-{original_project.id}" in changeset_comments:
changeset_comments.remove(
f"{default_comment}-{original_project.id}")
cloned_project.changeset_comment = " ".join(changeset_comments)
db.session.add(cloned_project)
db.session.commit()
return cloned_project
@staticmethod
def get(project_id: int):
"""
Gets specified project
:param project_id: project ID in scope
:return: Project if found otherwise None
"""
return Project.query.get(project_id)
def update(self, project_dto: ProjectDTO):
""" Updates project from DTO """
self.status = ProjectStatus[project_dto.project_status].value
self.priority = ProjectPriority[project_dto.project_priority].value
self.default_locale = project_dto.default_locale
self.enforce_random_task_selection = project_dto.enforce_random_task_selection
self.private = project_dto.private
self.mapper_level = MappingLevel[
project_dto.mapper_level.upper()].value
self.entities_to_map = project_dto.entities_to_map
self.changeset_comment = project_dto.changeset_comment
self.due_date = project_dto.due_date
self.imagery = project_dto.imagery
self.josm_preset = project_dto.josm_preset
self.id_presets = project_dto.id_presets
self.last_updated = timestamp()
self.license_id = project_dto.license_id
if project_dto.osmcha_filter_id:
# Support simple extraction of OSMCha filter id from OSMCha URL
match = re.search(r"aoi=([\w-]+)", project_dto.osmcha_filter_id)
self.osmcha_filter_id = (match.group(1) if match else
project_dto.osmcha_filter_id)
else:
self.osmcha_filter_id = None
if project_dto.organisation:
org = Organisation.get(project_dto.organisation)
if org is None:
raise NotFound("Organisation does not exist")
self.organisation = org
# Cast MappingType strings to int array
type_array = []
for mapping_type in project_dto.mapping_types:
type_array.append(MappingTypes[mapping_type].value)
self.mapping_types = type_array
# Cast Editor strings to int array
mapping_editors_array = []
for mapping_editor in project_dto.mapping_editors:
mapping_editors_array.append(Editors[mapping_editor].value)
self.mapping_editors = mapping_editors_array
validation_editors_array = []
for validation_editor in project_dto.validation_editors:
validation_editors_array.append(Editors[validation_editor].value)
self.validation_editors = validation_editors_array
self.country = project_dto.country_tag
# Add list of allowed users, meaning the project can only be mapped by users in this list
if hasattr(project_dto, "allowed_users"):
self.allowed_users = [
] # Clear existing relationships then re-insert
for user in project_dto.allowed_users:
self.allowed_users.append(user)
# Update teams and projects relationship.
self.teams = []
if hasattr(project_dto, "project_teams") and project_dto.project_teams:
for team_dto in project_dto.project_teams:
team = Team.get(team_dto.team_id)
if team is None:
raise NotFound(f"Team not found")
role = TeamRoles[team_dto.role].value
ProjectTeams(project=self, team=team, role=role)
# Set Project Info for all returned locales
for dto in project_dto.project_info_locales:
project_info = self.project_info.filter_by(
locale=dto.locale).one_or_none()
if project_info is None:
new_info = ProjectInfo.create_from_dto(
dto) # Can't find info so must be new locale
self.project_info.append(new_info)
else:
project_info.update_from_dto(dto)
self.priority_areas = [
] # Always clear Priority Area prior to updating
if project_dto.priority_areas:
for priority_area in project_dto.priority_areas:
pa = PriorityArea.from_dict(priority_area)
self.priority_areas.append(pa)
if project_dto.custom_editor:
if not self.custom_editor:
new_editor = CustomEditor.create_from_dto(
self.id, project_dto.custom_editor)
self.custom_editor = new_editor
else:
self.custom_editor.update_editor(project_dto.custom_editor)
else:
if self.custom_editor:
self.custom_editor.delete()
self.campaign = [
Campaign.query.get(c.id) for c in project_dto.campaigns
]
if project_dto.mapping_permission:
self.mapping_permission = MappingPermission[
project_dto.mapping_permission.upper()].value
if project_dto.validation_permission:
self.validation_permission = ValidationPermission[
project_dto.validation_permission.upper()].value
# Update Interests.
self.interests = []
if project_dto.interests:
self.interests = [
Interest.query.get(i.id) for i in project_dto.interests
]
db.session.commit()
def delete(self):
""" Deletes the current model from the DB """
db.session.delete(self)
db.session.commit()
def is_favorited(self, user_id: int) -> bool:
user = User.query.get(user_id)
if user not in self.favorited:
return False
return True
def favorite(self, user_id: int):
user = User.query.get(user_id)
self.favorited.append(user)
db.session.commit()
def unfavorite(self, user_id: int):
user = User.query.get(user_id)
if user not in self.favorited:
raise ValueError("Project not been favorited by user")
self.favorited.remove(user)
db.session.commit()
def set_as_featured(self):
if self.featured is True:
raise ValueError("Project is already featured")
self.featured = True
db.session.commit()
def unset_as_featured(self):
if self.featured is False:
raise ValueError("Project is not featured")
self.featured = False
db.session.commit()
def can_be_deleted(self) -> bool:
""" Projects can be deleted if they have no mapped work """
task_count = self.tasks.filter(
Task.task_status != TaskStatus.READY.value).count()
if task_count == 0:
return True
else:
return False
@staticmethod
def get_projects_for_admin(admin_id: int, preferred_locale: str,
search_dto: ProjectSearchDTO) -> PMDashboardDTO:
""" Get projects for admin """
query = Project.query.filter(Project.author_id == admin_id)
# Do Filtering Here
if search_dto.order_by:
if search_dto.order_by_type == "DESC":
query = query.order_by(desc(search_dto.order_by))
else:
query = query.order_by(search_dto.order_by)
admins_projects = query.all()
if admins_projects is None:
raise NotFound("No projects found for admin")
admin_projects_dto = PMDashboardDTO()
for project in admins_projects:
pm_project = project.get_project_summary(preferred_locale)
project_status = ProjectStatus(project.status)
if project_status == ProjectStatus.DRAFT:
admin_projects_dto.draft_projects.append(pm_project)
elif project_status == ProjectStatus.PUBLISHED:
admin_projects_dto.active_projects.append(pm_project)
elif project_status == ProjectStatus.ARCHIVED:
admin_projects_dto.archived_projects.append(pm_project)
else:
current_app.logger.error(
f"Unexpected state project {project.id}")
return admin_projects_dto
def get_project_user_stats(self, user_id: int) -> ProjectUserStatsDTO:
"""Compute project specific stats for a given user"""
stats_dto = ProjectUserStatsDTO()
stats_dto.time_spent_mapping = 0
stats_dto.time_spent_validating = 0
stats_dto.total_time_spent = 0
query = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
WHERE (action='LOCKED_FOR_MAPPING' or action='AUTO_UNLOCKED_FOR_MAPPING')
and user_id = :user_id and project_id = :project_id;"""
total_mapping_time = db.engine.execute(text(query),
user_id=user_id,
project_id=self.id)
for time in total_mapping_time:
total_mapping_time = time[0]
if total_mapping_time:
stats_dto.time_spent_mapping = total_mapping_time.total_seconds(
)
stats_dto.total_time_spent += stats_dto.time_spent_mapping
query = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
WHERE (action='LOCKED_FOR_VALIDATION' or action='AUTO_UNLOCKED_FOR_VALIDATION')
and user_id = :user_id and project_id = :project_id;"""
total_validation_time = db.engine.execute(text(query),
user_id=user_id,
project_id=self.id)
for time in total_validation_time:
total_validation_time = time[0]
if total_validation_time:
stats_dto.time_spent_validating = total_validation_time.total_seconds(
)
stats_dto.total_time_spent += stats_dto.time_spent_validating
return stats_dto
def get_project_stats(self) -> ProjectStatsDTO:
""" Create Project Stats model for postgis project object"""
project_stats = ProjectStatsDTO()
project_stats.project_id = self.id
project_area_sql = "select ST_Area(geometry, true)/1000000 as area from public.projects where id = :id"
project_area_result = db.engine.execute(text(project_area_sql),
id=self.id)
project_stats.area = project_area_result.fetchone()["area"]
project_stats.total_mappers = (db.session.query(User).filter(
User.projects_mapped.any(self.id)).count())
project_stats.total_tasks = self.total_tasks
project_stats.total_comments = (db.session.query(ProjectChat).filter(
ProjectChat.project_id == self.id).count())
project_stats.percent_mapped = Project.calculate_tasks_percent(
"mapped",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
project_stats.percent_validated = Project.calculate_tasks_percent(
"validated",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
project_stats.percent_bad_imagery = Project.calculate_tasks_percent(
"bad_imagery",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON())
project_stats.aoi_centroid = geojson.loads(centroid_geojson)
unique_mappers = (TaskHistory.query.filter(
TaskHistory.action == "LOCKED_FOR_MAPPING",
TaskHistory.project_id == self.id,
).distinct(TaskHistory.user_id).count())
unique_validators = (TaskHistory.query.filter(
TaskHistory.action == "LOCKED_FOR_VALIDATION",
TaskHistory.project_id == self.id,
).distinct(TaskHistory.user_id).count())
project_stats.total_time_spent = 0
project_stats.total_mapping_time = 0
project_stats.total_validation_time = 0
project_stats.average_mapping_time = 0
project_stats.average_validation_time = 0
query = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
WHERE (action='LOCKED_FOR_MAPPING' or action='AUTO_UNLOCKED_FOR_MAPPING')
and project_id = :project_id;"""
total_mapping_time = db.engine.execute(text(query), project_id=self.id)
for row in total_mapping_time:
total_mapping_time = row[0]
if total_mapping_time:
total_mapping_seconds = total_mapping_time.total_seconds()
project_stats.total_mapping_time = total_mapping_seconds
project_stats.total_time_spent += project_stats.total_mapping_time
if unique_mappers:
average_mapping_time = total_mapping_seconds / unique_mappers
project_stats.average_mapping_time = average_mapping_time
query = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
WHERE (action='LOCKED_FOR_VALIDATION' or action='AUTO_UNLOCKED_FOR_VALIDATION')
and project_id = :project_id;"""
total_validation_time = db.engine.execute(text(query),
project_id=self.id)
for row in total_validation_time:
total_validation_time = row[0]
if total_validation_time:
total_validation_seconds = total_validation_time.total_seconds(
)
project_stats.total_validation_time = total_validation_seconds
project_stats.total_time_spent += project_stats.total_validation_time
if unique_validators:
average_validation_time = (total_validation_seconds /
unique_validators)
project_stats.average_validation_time = average_validation_time
return project_stats
def get_project_summary(self, preferred_locale) -> ProjectSummary:
""" Create Project Summary model for postgis project object"""
summary = ProjectSummary()
summary.project_id = self.id
priority = self.priority
if priority == 0:
summary.priority = "URGENT"
elif priority == 1:
summary.priority = "HIGH"
elif priority == 2:
summary.priority = "MEDIUM"
else:
summary.priority = "LOW"
summary.author = User().get_by_id(self.author_id).username
polygon = to_shape(self.geometry)
polygon_aea = transform(
partial(
pyproj.transform,
pyproj.Proj(init="EPSG:4326"),
pyproj.Proj(proj="aea",
lat_1=polygon.bounds[1],
lat_2=polygon.bounds[3]),
),
polygon,
)
area = polygon_aea.area / 1000000
summary.area = area
summary.country_tag = self.country
summary.changeset_comment = self.changeset_comment
summary.due_date = self.due_date
summary.created = self.created
summary.last_updated = self.last_updated
summary.osmcha_filter_id = self.osmcha_filter_id
summary.mapper_level = MappingLevel(self.mapper_level).name
summary.mapping_permission = MappingPermission(
self.mapping_permission).name
summary.validation_permission = ValidationPermission(
self.validation_permission).name
summary.random_task_selection_enforced = self.enforce_random_task_selection
summary.private = self.private
summary.status = ProjectStatus(self.status).name
summary.entities_to_map = self.entities_to_map
summary.imagery = self.imagery
if self.organisation_id:
summary.organisation_name = self.organisation.name
summary.organisation_logo = self.organisation.logo
if self.campaign:
summary.campaigns = [i.as_dto() for i in self.campaign]
# Cast MappingType values to related string array
mapping_types_array = []
if self.mapping_types:
for mapping_type in self.mapping_types:
mapping_types_array.append(MappingTypes(mapping_type).name)
summary.mapping_types = mapping_types_array
if self.mapping_editors:
mapping_editors = []
for mapping_editor in self.mapping_editors:
mapping_editors.append(Editors(mapping_editor).name)
summary.mapping_editors = mapping_editors
if self.validation_editors:
validation_editors = []
for validation_editor in self.validation_editors:
validation_editors.append(Editors(validation_editor).name)
summary.validation_editors = validation_editors
if self.custom_editor:
summary.custom_editor = self.custom_editor.as_dto()
# If project is private, fetch list of allowed users
if self.private:
allowed_users = []
for user in self.allowed_users:
allowed_users.append(user.username)
summary.allowed_users = allowed_users
centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON())
summary.aoi_centroid = geojson.loads(centroid_geojson)
summary.percent_mapped = Project.calculate_tasks_percent(
"mapped",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
summary.percent_validated = Project.calculate_tasks_percent(
"validated",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
summary.percent_bad_imagery = Project.calculate_tasks_percent(
"bad_imagery",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
summary.project_teams = [
ProjectTeamDTO(
dict(
team_id=t.team.id,
team_name=t.team.name,
role=TeamRoles(t.role).name,
)) for t in self.teams
]
project_info = ProjectInfo.get_dto_for_locale(self.id,
preferred_locale,
self.default_locale)
summary.project_info = project_info
return summary
def get_project_title(self, preferred_locale):
project_info = ProjectInfo.get_dto_for_locale(self.id,
preferred_locale,
self.default_locale)
return project_info.name
@staticmethod
def get_project_total_contributions(project_id: int) -> int:
project_contributors_count = (TaskHistory.query.with_entities(
TaskHistory.user_id).filter(
TaskHistory.project_id == project_id,
TaskHistory.action != "COMMENT").distinct(
TaskHistory.user_id).count())
return project_contributors_count
def get_aoi_geometry_as_geojson(self):
""" Helper which returns the AOI geometry as a geojson object """
aoi_geojson = db.engine.execute(self.geometry.ST_AsGeoJSON()).scalar()
return geojson.loads(aoi_geojson)
def get_project_teams(self):
""" Helper to return teams with members so we can handle permissions """
project_teams = []
for t in self.teams:
project_teams.append({
"name":
t.team.name,
"role":
t.role,
"members": [m.member.username for m in t.team.members],
})
return project_teams
@staticmethod
@cached(active_mappers_cache)
def get_active_mappers(project_id) -> int:
""" Get count of Locked tasks as a proxy for users who are currently active on the project """
return (Task.query.filter(
Task.task_status.in_((
TaskStatus.LOCKED_FOR_MAPPING.value,
TaskStatus.LOCKED_FOR_VALIDATION.value,
))).filter(Task.project_id == project_id).distinct(
Task.locked_by).count())
def _get_project_and_base_dto(self):
""" Populates a project DTO with properties common to all roles """
base_dto = ProjectDTO()
base_dto.project_id = self.id
base_dto.project_status = ProjectStatus(self.status).name
base_dto.default_locale = self.default_locale
base_dto.project_priority = ProjectPriority(self.priority).name
base_dto.area_of_interest = self.get_aoi_geometry_as_geojson()
base_dto.aoi_bbox = shape(base_dto.area_of_interest).bounds
base_dto.mapping_permission = MappingPermission(
self.mapping_permission).name
base_dto.validation_permission = ValidationPermission(
self.validation_permission).name
base_dto.enforce_random_task_selection = self.enforce_random_task_selection
base_dto.private = self.private
base_dto.mapper_level = MappingLevel(self.mapper_level).name
base_dto.entities_to_map = self.entities_to_map
base_dto.changeset_comment = self.changeset_comment
base_dto.osmcha_filter_id = self.osmcha_filter_id
base_dto.due_date = self.due_date
base_dto.imagery = self.imagery
base_dto.josm_preset = self.josm_preset
base_dto.id_presets = self.id_presets
base_dto.country_tag = self.country
base_dto.organisation_id = self.organisation_id
base_dto.license_id = self.license_id
base_dto.created = self.created
base_dto.last_updated = self.last_updated
base_dto.author = User().get_by_id(self.author_id).username
base_dto.active_mappers = Project.get_active_mappers(self.id)
base_dto.task_creation_mode = TaskCreationMode(
self.task_creation_mode).name
base_dto.percent_mapped = Project.calculate_tasks_percent(
"mapped",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
base_dto.percent_validated = Project.calculate_tasks_percent(
"validated",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
base_dto.percent_bad_imagery = Project.calculate_tasks_percent(
"bad_imagery",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
base_dto.project_teams = [
ProjectTeamDTO(
dict(
team_id=t.team.id,
team_name=t.team.name,
role=TeamRoles(t.role).name,
)) for t in self.teams
]
if self.custom_editor:
base_dto.custom_editor = self.custom_editor.as_dto()
if self.private:
# If project is private it should have a list of allowed users
allowed_usernames = []
for user in self.allowed_users:
allowed_usernames.append(user.username)
base_dto.allowed_usernames = allowed_usernames
if self.mapping_types:
mapping_types = []
for mapping_type in self.mapping_types:
mapping_types.append(MappingTypes(mapping_type).name)
base_dto.mapping_types = mapping_types
if self.campaign:
base_dto.campaigns = [i.as_dto() for i in self.campaign]
if self.mapping_editors:
mapping_editors = []
for mapping_editor in self.mapping_editors:
mapping_editors.append(Editors(mapping_editor).name)
base_dto.mapping_editors = mapping_editors
if self.validation_editors:
validation_editors = []
for validation_editor in self.validation_editors:
validation_editors.append(Editors(validation_editor).name)
base_dto.validation_editors = validation_editors
if self.priority_areas:
geojson_areas = []
for priority_area in self.priority_areas:
geojson_areas.append(priority_area.get_as_geojson())
base_dto.priority_areas = geojson_areas
base_dto.interests = [
InterestDTO(dict(id=i.id, name=i.name)) for i in self.interests
]
return self, base_dto
def as_dto_for_mapping(self,
authenticated_user_id: int = None,
locale: str = "en",
abbrev: bool = True) -> Optional[ProjectDTO]:
""" Creates a Project DTO suitable for transmitting to mapper users """
# Check for project visibility settings
is_allowed_user = True
if self.private:
is_allowed_user = False
if authenticated_user_id:
user = User().get_by_id(authenticated_user_id)
if (UserRole(user.role) == UserRole.ADMIN
or authenticated_user_id == self.author_id):
is_allowed_user = True
for user in self.allowed_users:
if user.id == authenticated_user_id:
is_allowed_user = True
break
if is_allowed_user:
project, project_dto = self._get_project_and_base_dto()
if abbrev is False:
project_dto.tasks = Task.get_tasks_as_geojson_feature_collection(
self.id, None)
else:
project_dto.tasks = Task.get_tasks_as_geojson_feature_collection_no_geom(
self.id)
project_dto.project_info = ProjectInfo.get_dto_for_locale(
self.id, locale, project.default_locale)
if project.organisation_id:
project_dto.organisation = project.organisation.id
project_dto.organisation_name = project.organisation.name
project_dto.organisation_logo = project.organisation.logo
project_dto.project_info_locales = ProjectInfo.get_dto_for_all_locales(
self.id)
return project_dto
def tasks_as_geojson(self,
task_ids_str: str,
order_by=None,
order_by_type="ASC",
status=None):
""" Creates a geojson of all areas """
project_tasks = Task.get_tasks_as_geojson_feature_collection(
self.id, task_ids_str, order_by, order_by_type, status)
return project_tasks
@staticmethod
def get_all_countries():
query = (db.session.query(
func.unnest(Project.country).label("country")).distinct().order_by(
"country"))
tags_dto = TagsDTO()
tags_dto.tags = [r[0] for r in query]
return tags_dto
@staticmethod
def get_all_organisations_tag(preferred_locale="en"):
query = (db.session.query(
Project.id, Project.organisation_tag, Project.private,
Project.status).join(ProjectInfo).filter(
ProjectInfo.locale.in_(
[preferred_locale,
"en"])).filter(Project.private is not True).filter(
Project.organisation_tag.isnot(None)).filter(
Project.organisation_tag != ""))
query = query.distinct(Project.organisation_tag)
query = query.order_by(Project.organisation_tag)
tags_dto = TagsDTO()
tags_dto.tags = [r[1] for r in query]
return tags_dto
@staticmethod
def get_all_campaign_tag(preferred_locale="en"):
query = (db.session.query(
Project.id, Project.campaign_tag, Project.private,
Project.status).join(ProjectInfo).filter(
ProjectInfo.locale.in_(
[preferred_locale,
"en"])).filter(Project.private is not True).filter(
Project.campaign_tag.isnot(None)).filter(
Project.campaign_tag != ""))
query = query.distinct(Project.campaign_tag)
query = query.order_by(Project.campaign_tag)
tags_dto = TagsDTO()
tags_dto.tags = [r[1] for r in query]
return tags_dto
@staticmethod
def calculate_tasks_percent(target, total_tasks, tasks_mapped,
tasks_validated, tasks_bad_imagery):
""" Calculates percentages of contributions """
if target == "mapped":
return int((tasks_mapped + tasks_validated) /
(total_tasks - tasks_bad_imagery) * 100)
elif target == "validated":
return int(tasks_validated / (total_tasks - tasks_bad_imagery) *
100)
elif target == "bad_imagery":
return int((tasks_bad_imagery / total_tasks) * 100)
def as_dto_for_admin(self, project_id):
""" Creates a Project DTO suitable for transmitting to project admins """
project, project_dto = self._get_project_and_base_dto()
if project is None:
return None
project_dto.project_info_locales = ProjectInfo.get_dto_for_all_locales(
project_id)
return project_dto
def create_or_update_interests(self, interests_ids):
self.interests = []
objs = [Interest.get_by_id(i) for i in interests_ids]
self.interests.extend(objs)
db.session.commit()
class FarmersMarket(db.Model):
""" Farmer's markets information. """
__tablename__ = 'farmersmarkets'
fm_id = db.Column(db.Integer, primary_key=True, nullable=False)
name = db.Column(db.String(200), nullable=False)
website = db.Column(db.String(2083), nullable=True)
facebook = db.Column(db.String(500), nullable=True)
twitter = db.Column(db.String(500), nullable=True)
youtube = db.Column(db.String(500), nullable=True)
other_media = db.Column(db.String(500), nullable=True)
street = db.Column(db.String(255), nullable=True)
city = db.Column(db.String(100), nullable=True)
county = db.Column(db.String(100), nullable=True)
state = db.Column(db.String(2), db.ForeignKey('states.state_id'), nullable=False)
zipcode = db.Column(db.String(15), nullable=True)
season_1_date = db.Column(db.String(200), nullable=True)
season_1_time = db.Column(db.String(200), nullable=True)
season_2_date = db.Column(db.String(200), nullable=True)
season_2_time = db.Column(db.String(200), nullable=True)
season_3_date = db.Column(db.String(200), nullable=True)
season_3_time = db.Column(db.String(200), nullable=True)
season_4_date = db.Column(db.String(200), nullable=True)
season_4_time = db.Column(db.String(200), nullable=True)
longitude = db.Column(db.Float, nullable=True)
latitude = db.Column(db.Float, nullable=True)
latlng = db.Column(Geometry(geometry_type='POINT'), nullable=True)
location = db.Column(db.String(100), nullable=True)
credit = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
wic = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
wic_cash = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
sfmnp = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
snap = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
organic = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
baked_goods = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
cheese = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
crafts = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
flowers = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
eggs = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
seafood = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
herbs = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
vegetables = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
honey = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
jams = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
maple = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
meat = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
nursery = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
nuts = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
plants = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
poultry = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
prepared = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
soap = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
trees = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
wine = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
coffee = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
beans = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
fruits = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
grains = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
juices = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
mushrooms = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
pet_food = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
tofu = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
wild_harvested = db.Column(db.String(10), db.ForeignKey('hasitems.has_item_id'), nullable=False)
date_updated = db.Column(db.String(50), nullable=False)
def __repr__(self):
""" Shows information about the farmer's market. """
return '<FarmersMarket id=%s name=%s>' % (self.fm_id, self.name)
class Mine(AuditMixin, Base):
__tablename__ = 'mine'
mine_guid = db.Column(UUID(as_uuid=True),
primary_key=True,
server_default=FetchedValue())
mine_no = db.Column(db.String(10))
mine_name = db.Column(db.String(60), nullable=False)
mine_note = db.Column(db.String(300), default='')
legacy_mms_mine_status = db.Column(db.String(50))
major_mine_ind = db.Column(db.Boolean, nullable=False, default=False)
deleted_ind = db.Column(db.Boolean,
nullable=False,
server_default=FetchedValue())
mine_region = db.Column(db.String(2),
db.ForeignKey('mine_region_code.mine_region_code'))
ohsc_ind = db.Column(db.Boolean,
nullable=False,
server_default=FetchedValue())
union_ind = db.Column(db.Boolean,
nullable=False,
server_default=FetchedValue())
latitude = db.Column(db.Numeric(9, 7))
longitude = db.Column(db.Numeric(11, 7))
geom = db.Column(Geometry('POINT', 3005))
mine_location_description = db.Column(db.String)
# Relationships
#Almost always used and 1:1, so these are joined
mine_status = db.relationship('MineStatus',
backref='mine',
order_by='desc(MineStatus.update_timestamp)',
lazy='joined')
mine_tailings_storage_facilities = db.relationship(
'MineTailingsStorageFacility',
backref='mine',
order_by=
'desc(MineTailingsStorageFacility.mine_tailings_storage_facility_name)',
lazy='joined')
#Almost always used, but faster to use selectin to load related data
mine_permit = db.relationship('Permit',
backref='mine',
order_by='desc(Permit.create_timestamp)',
lazy='selectin')
mine_type = db.relationship(
'MineType',
backref='mine',
order_by='desc(MineType.update_timestamp)',
primaryjoin=
"and_(MineType.mine_guid == Mine.mine_guid, MineType.active_ind==True)",
lazy='selectin')
mine_documents = db.relationship(
'MineDocument',
backref='mine',
primaryjoin=
"and_(MineDocument.mine_guid == Mine.mine_guid, MineDocument.active_ind==True)",
lazy='select')
mine_party_appt = db.relationship('MinePartyAppointment',
backref="mine",
lazy='select')
mine_incidents = db.relationship('MineIncident',
backref="mine",
lazy='select')
mine_reports = db.relationship('MineReport', backref="mine", lazy='select')
def __repr__(self):
return '<Mine %r>' % self.mine_guid
@reconstructor
def init_on_load(self):
if self.latitude and self.longitude:
try:
self.utm_values = utm.from_latlon(self.latitude,
self.longitude)
except utm.error.OutOfRangeError:
self.utm_values = ()
@hybrid_property
def utm_easting(self):
return self.utm_values[0] if self.utm_values else None
@hybrid_property
def utm_northing(self):
return self.utm_values[1] if self.utm_values else None
@hybrid_property
def utm_zone_number(self):
return self.utm_values[2] if self.utm_values else None
@hybrid_property
def utm_zone_letter(self):
return self.utm_values[3] if self.utm_values else None
@hybrid_property
def mine_location(self):
return {
'latitude': self.latitude,
'longitude': self.longitude,
'utm_easting': self.utm_easting,
'utm_northing': self.utm_northing,
'utm_zone_number': self.utm_zone_number,
'utm_zone_letter': self.utm_zone_letter,
'mine_location_description': self.mine_location_description
}
@staticmethod
def active(records):
return list(filter(lambda x: x.active_ind, records))
@classmethod
def find_by_mine_guid(cls, _id):
try:
uuid.UUID(_id, version=4)
return cls.query.filter_by(mine_guid=_id).filter_by(
deleted_ind=False).first()
except ValueError:
return None
@classmethod
def find_by_mine_no(cls, _id):
return cls.query.filter_by(mine_no=_id).filter_by(
deleted_ind=False).first()
@classmethod
def find_by_mine_name(cls, term=None):
MINE_LIST_RESULT_LIMIT = 50
if term:
name_filter = Mine.mine_name.ilike('%{}%'.format(term))
mines_q = Mine.query.filter(name_filter).filter_by(
deleted_ind=False)
mines = mines_q.limit(MINE_LIST_RESULT_LIMIT).all()
else:
mines = Mine.query.limit(MINE_LIST_RESULT_LIMIT).all()
return mines
@classmethod
def find_by_name_no_permit(cls, term=None):
MINE_LIST_RESULT_LIMIT = 50
if term:
name_filter = Mine.mine_name.ilike('%{}%'.format(term))
number_filter = Mine.mine_no.ilike('%{}%'.format(term))
permit_filter = Permit.permit_no.ilike('%{}%'.format(term))
mines_q = Mine.query.filter(name_filter | number_filter).filter_by(
deleted_ind=False)
permit_q = Mine.query.join(Permit).filter(permit_filter)
mines = mines_q.union(permit_q).limit(MINE_LIST_RESULT_LIMIT).all()
else:
mines = Mine.query.limit(MINE_LIST_RESULT_LIMIT).all()
return mines
@classmethod
def find_all_major_mines(cls):
return cls.query.filter_by(major_mine_ind=True).filter_by(
deleted_ind=False).all()
@classmethod
def find_by_mine_no_or_guid(cls, _id):
result = cls.find_by_mine_guid(_id)
if result is None:
result = cls.find_by_mine_no(_id)
return result
@validates('mine_name')
def validate_mine_name(self, key, mine_name):
if not mine_name:
raise AssertionError('No mine name provided.')
if len(mine_name) > 60:
raise AssertionError('Mine name must not exceed 60 characters.')
return mine_name
@validates('mine_note')
def validate_mine_note(self, key, mine_note):
mine_note = mine_note if mine_note else ''
if len(mine_note) > 300:
raise AssertionError('Mine note must not exceed 300 characters.')
return mine_note
@validates('mine_no')
def validate_mine_no(self, key, mine_no):
mine_no = mine_no if mine_no else ''
if mine_no and len(mine_no) > 10:
raise AssertionError('Mine number must not exceed 10 characters.')
return mine_no
@validates('mine_region')
def validate_mine_region(self, key, mine_region):
if not mine_region:
raise AssertionError('No mine region code provided.')
if len(mine_region) > 2:
raise AssertionError('Invalid region code')
return mine_region
import piecewise.config
from geoalchemy2 import Geometry
from sqlalchemy import MetaData, Table, Column, String, BigInteger, Integer, DateTime, Boolean, Float, create_engine, func, select, text
from sqlalchemy.sql.expression import label
import json
import datetime
aggregator = piecewise.config.read_config(
json.load(open("/etc/piecewise/config.json")))
engine = create_engine(aggregator.database_uri)
metadata = MetaData()
metadata.bind = engine
extra_data = Table('extra_data', metadata,
Column('id', Integer, primary_key=True),
Column('timestamp', DateTime, server_default=func.now()),
Column('verified', Boolean, server_default=text("True")),
Column('bigquery_key', String),
Column('bigquery_test_id', String),
Column('location', Geometry("Point", srid=4326)),
Column('isp', String), Column('connection_type', String),
Column('advertised_download', Integer),
Column('actual_download', Float),
Column('advertised_upload', Integer),
Column('actual_upload', Float), Column('min_rtt', Integer),
Column('location_type', String), Column('isp_user', String),
Column('client_ip', BigInteger),
Column('cost_of_service', String))
metadata.drop_all()
metadata.create_all()
def setup_method(self, _):
# Always see the diff
# https://docs.python.org/2/library/unittest.html#unittest.TestCase.maxDiff
self.maxDiff = None # pylint: disable=invalid-name
self._tables = []
from geoalchemy2 import Geometry
from sqlalchemy import Column, Table, func, types
from sqlalchemy.ext.declarative import declarative_base
from c2cgeoportal_commons.models import DBSession
from c2cgeoportal_commons.models.main import (
OGCSERVER_AUTH_GEOSERVER,
OGCSERVER_TYPE_GEOSERVER,
FullTextSearch,
Functionality,
Interface,
LayerGroup,
LayerWMS,
OGCServer,
RestrictionArea,
Role,
Theme,
)
from c2cgeoportal_commons.models.static import User
setup_db()
role1 = Role(name="__test_role1")
user1 = User(username="******",
password="******",
settings_role=role1,
roles=[role1])
user1.email = "*****@*****.**"
role2 = Role(name="__test_role2",
extent=WKTElement("POLYGON((1 2, 1 4, 3 4, 3 2, 1 2))",
srid=21781))
user2 = User(username="******",
password="******",
settings_role=role2,
roles=[role2])
main = Interface(name="desktop")
mobile = Interface(name="mobile")
engine = DBSession.c2c_rw_bind
engine.connect()
a_geo_table = Table(
"a_geo_table",
declarative_base(bind=engine).metadata,
Column("id", types.Integer, primary_key=True),
Column("geom", Geometry("POINT", srid=21781)),
schema="geodata",
)
self._tables = [a_geo_table]
a_geo_table.drop(checkfirst=True)
a_geo_table.create()
ogcserver = create_default_ogcserver()
private_layer_edit = LayerWMS(name="__test_private_layer_edit",
public=False)
private_layer_edit.layer = "__test_private_layer"
private_layer_edit.geo_table = "a_schema.a_geo_table"
private_layer_edit.interfaces = [main, mobile]
private_layer_edit.ogc_server = ogcserver
public_layer2 = LayerWMS(name="__test_public_layer",
layer="__test_public_layer_bis",
public=True)
public_layer2.interfaces = [main, mobile]
public_layer2.ogc_server = ogcserver
private_layer = LayerWMS(name="__test_private_layer",
layer="__test_private_layer_bis",
public=False)
private_layer.interfaces = [main, mobile]
private_layer.ogc_server = ogcserver
interface_not_in_mapfile = Interface(name="interface_not_in_mapfile")
public_layer_not_in_mapfile = LayerWMS(
name="__test_public_layer_not_in_mapfile",
layer="__test_public_layer_not_in_mapfile",
public=True)
public_layer_not_in_mapfile.interfaces = [interface_not_in_mapfile]
public_layer_not_in_mapfile.ogc_server = ogcserver
interface_notmapfile = Interface(name="interface_notmapfile")
ogcserver_notmapfile = OGCServer(name="__test_ogc_server_notmapfile")
ogcserver_notmapfile.url = mapserv_url + "?map=not_a_mapfile"
public_layer_not_mapfile = LayerWMS(
name="__test_public_layer_notmapfile",
layer="__test_public_layer_notmapfile",
public=True)
public_layer_not_mapfile.interfaces = [interface_notmapfile]
public_layer_not_mapfile.ogc_server = ogcserver_notmapfile
interface_geoserver = Interface(name="interface_geoserver")
ogcserver_geoserver = OGCServer(name="__test_ogc_server_geoserver")
ogcserver_geoserver.url = mapserv_url
ogcserver_geoserver.type = OGCSERVER_TYPE_GEOSERVER
ogcserver_geoserver.auth = OGCSERVER_AUTH_GEOSERVER
public_layer_geoserver = LayerWMS(name="__test_public_layer_geoserver",
layer="testpoint_unprotected",
public=True)
public_layer_geoserver.interfaces = [interface_geoserver]
public_layer_geoserver.ogc_server = ogcserver_geoserver
interface_no_layers = Interface(name="interface_no_layers")
public_layer_no_layers = LayerWMS(name="__test_public_layer_no_layers",
public=True)
public_layer_no_layers.interfaces = [interface_no_layers]
public_layer_no_layers.ogc_server = ogcserver
group = LayerGroup(name="__test_layer_group")
group.children = [
private_layer_edit,
public_layer2,
public_layer_not_in_mapfile,
public_layer_not_mapfile,
public_layer_geoserver,
public_layer_no_layers,
private_layer,
]
theme = Theme(name="__test_theme")
theme.children = [group]
theme.interfaces = [
main,
interface_not_in_mapfile,
interface_notmapfile,
interface_geoserver,
interface_no_layers,
]
functionality1 = Functionality(name="test_name", value="test_value_1")
functionality2 = Functionality(name="test_name", value="test_value_2")
theme.functionalities = [functionality1, functionality2]
poly = "POLYGON((-100 0, -100 20, 100 20, 100 0, -100 0))"
area = WKTElement(poly, srid=21781)
RestrictionArea(
name="__test_ra1",
description="",
layers=[private_layer_edit, private_layer],
roles=[role1],
area=area,
)
area = WKTElement(poly, srid=21781)
RestrictionArea(
name="__test_ra2",
description="",
layers=[private_layer_edit, private_layer],
roles=[role2],
area=area,
readwrite=True,
)
entry1 = FullTextSearch()
entry1.label = "label1"
entry1.layer_name = "layer1"
entry1.ts = func.to_tsvector("french", "soleil travail")
entry1.the_geom = WKTElement("POINT(-90 -45)", 21781)
entry1.public = True
entry2 = FullTextSearch()
entry2.label = "label1"
entry2.layer_name = "layer1"
entry2.ts = func.to_tsvector("french", "soleil travail")
entry2.the_geom = WKTElement("POINT(-90 -45)", 21781)
entry2.public = True
entry3 = FullTextSearch()
entry3.label = "label1"
entry3.layer_name = None
entry3.ts = func.to_tsvector("french", "soleil travail")
entry3.the_geom = WKTElement("POINT(-90 -45)", 21781)
entry3.public = True
DBSession.add_all([user1, user2, theme, entry1, entry2, entry3])
DBSession.flush()
self.role1_id = role1.id
transaction.commit()
def geodataframe_to_postgis(
geodataframe: gpd.GeoDataFrame,
output_table_name: str,
uri: str,
src_epsg: Union[bool, int] = None,
output_epsg: Union[bool, int] = None,
debug: bool = False
):
"""
Write a ``geopandas.GeoDataFrame`` to a PostGIS table in a SQL database.
Assumes that the geometry column has already been named 'geometry'
:param geodataframe: geopandas.GeoDataFrame
:param output_table_name: 'name_of_the_output_table'
:param src_epsg: if not None, will assign the geodataframe this EPSG in the format of {"init": "epsg:2227"}
:param output_epsg: if not None, will reproject data from input EPSG to specified EPSG
:param uri: connection string
:return: None
"""
start_time = time.time()
# Get the geometry type
# It's possible there are both MULTIPOLYGONS and POLYGONS. This grabs the MULTI variant
geom_types = list(geodataframe.geometry.geom_type.unique())
geom_typ = max(geom_types, key=len).upper()
if debug:
print(f'## PROCESSING {geom_typ} geodataframe to {output_table_name} in SQL')
# Manually set the EPSG if the user passes one
if src_epsg:
geodataframe.crs = f"epsg:{src_epsg}"
epsg_code = src_epsg
# Otherwise, try to get the EPSG value directly from the geodataframe
else:
try:
# Older geodataframes has CRS stored as a dict: {'init': 'epsg:4326'}
if type(geodataframe.crs) == dict:
epsg_code = int(geodataframe.crs['init'].split(" ")[0].split(':')[1])
# Now geopandas has a different approach:
else:
epsg_code = int(str(geodataframe.crs).split(':')[1])
except:
print('This geodataframe does not have a valid EPSG. Aborting.')
print(geodataframe.crs)
return
# Sanitize the columns before writing to the database
# Make all column names lower case
geodataframe.columns = [x.lower() for x in geodataframe.columns]
# Replace the 'geom' column with 'geometry'
if 'geom' in geodataframe.columns:
geodataframe['geometry'] = geodataframe['geom']
geodataframe.drop('geom', 1, inplace=True)
# Drop the 'gid' column
if 'gid' in geodataframe.columns:
geodataframe.drop('gid', 1, inplace=True)
# Rename 'uid' to 'old_uid'
if 'uid' in geodataframe.columns:
geodataframe['old_uid'] = geodataframe['uid']
geodataframe.drop('uid', 1, inplace=True)
# Build a 'geom' column using geoalchemy2 and drop the source 'geometry' column
geodataframe['geom'] = geodataframe['geometry'].apply(lambda x: WKTElement(x.wkt, srid=epsg_code))
geodataframe.drop('geometry', 1, inplace=True)
# write geodataframe to SQL database
if debug:
print(f'## -> WRITING TO {uri}')
engine = sqlalchemy.create_engine(uri)
geodataframe.to_sql(output_table_name, engine,
if_exists='replace', index=True, index_label='gid',
dtype={'geom': Geometry(geom_typ, srid=epsg_code)})
engine.dispose()
if debug:
runtime = round((time.time() - start_time), 2)
print(f'\t FINISHED IN {runtime} seconds')
log_activity("pGIS.geodataframe_to_postgis",
uri=uri,
query_text=f"Wrote geopandas.GeoDataFrame to {output_table_name}",
debug=debug)
# If provided an EPSG, alter whatever the native projection was to the output_epsg
if output_epsg:
project_spatial_table(output_table_name, geom_typ, epsg_code, output_epsg, uri=uri, debug=debug)
# Add a unique_id column and do a spatial index
prep_spatial_table(output_table_name, uri=uri, debug=debug)
class Feature(Base):
__tablename__ = 'feature'
__template__ = 'tooltips/category.mako'
id = Column(Integer, primary_key=True)
name = Column(Unicode(50))
description = Column(Unicode)
image = Column(Unicode(255))
thumbnail = Column(Unicode(255))
color = Column(Unicode(255))
stroke = Column(Integer, default=2)
is_label = Column(Boolean, default=False)
is_circle = Column(Boolean, default=False)
linestyle = Column(Integer, default=0)
show_orientation = Column(Boolean, default=False)
geometry = Column(Geometry(srid=2169))
map_id = Column(Unicode, ForeignKey('map.uuid'))
symbol_id = Column(Integer)
size = Column(Float, default=10)
angle = Column(Float, default=0)
font_size = Column(Integer, default=15)
opacity = Column(Float, default=0.5)
shape = Column(Unicode(255))
def __init__(self, feature=None):
if feature:
self.__update__(feature)
def __update__(self, feature):
self.name = feature.properties.get('name')
self.description = feature.properties.get('description')
self.thumbnail = feature.properties.get('thumbnail')
self.image = feature.properties.get('image')
self.color = feature.properties.get('color')
self.stroke = feature.properties.get('stroke')
self.is_label = feature.properties.get('isLabel')
self.is_circle = feature.properties.get('isCircle')
self.show_orientation = feature.properties.get('showOrientation')
linestyle = feature.properties.get('linestyle')
self.linestyle = 0 if linestyle == 'plain' else 1\
if linestyle == 'dashed' else 2
self.shape = feature.properties.get('shape')
size = feature.properties.get('size')
self.size = size if size is not None and unicode(size).isnumeric()\
else 10
angle = feature.properties.get('angle')
try:
self.angle = float(angle)
except TypeError:
self.angle = 0
font_size = feature.properties.get('fontSize')
self.font_size = font_size if font_size is not None and\
unicode(font_size).isnumeric() else 15
symbol_id = feature.properties.get('symbolId')
self.symbol_id = None if symbol_id is not None and\
len(unicode(symbol_id)) == 0\
else symbol_id
opacity = feature.properties.get('opacity')
self.opacity = opacity if opacity is not None and\
unicode(opacity).isnumeric() else 0.5
if hasattr(feature.geometry, "__geo_interface__"):
ob = feature.geometry.__geo_interface__
else:
ob = feature.geometry
geom_type = ob.get("type").lower()
if geom_type != 'geometrycollection':
# openlayers gpx writter creates a 4 dimension geometry and
# shapely does not allow if for linestring.
if geom_type == 'linestring':
feature.geometry.coordinates = \
[coordinate[0:2] for coordinate
in feature.geometry.coordinates]
elif geom_type == 'multilinestring':
multilinestring = feature.geometry.coordinates
feature.geometry.coordinates = \
[[coord[0:2] for coord in multilinestring[i]]
for i in range(len(multilinestring))]
shape = asShape(feature.geometry)
else:
geoms = []
is_transformable = True
types = None
for geom in feature.geometry.geometries:
if hasattr(geom, "__geo_interface__"):
ob = geom.__geo_interface__
else:
ob = geom
geom_type = ob.get("type").lower()
if types is None:
types = geom_type
else:
is_transformable = types == geom_type
if not is_transformable:
break
geoms.append(asShape(geom))
if is_transformable:
if types == "point":
shape = asMultiPoint(geoms)
elif types == "linestring":
shape = asMultiLineString(geoms)
elif types == "polygon":
shape = MultiPolygonAdapter(geoms, context_type='geojson')
else:
shape = None
# ST_FORCE2D is used because the db only allows geometry with
# 2 dimensions.
self.geometry = func.ST_Force2D(from_shape(shape, srid=2169))\
if shape is not None else None
@property
def __geo_interface__(self):
geometry = wkb.loads(str(self.geometry), True)
properties = dict(
name=self.name,
description=self.description,
thumbnail=self.thumbnail,
image=self.image,
color=self.color,
stroke=self.stroke,
isLabel=self.is_label,
isCircle=self.is_circle,
showOrientation=self.show_orientation,
linestyle='plain' if self.linestyle == 0 else
'dashed' if self.linestyle == 1 else 'dotted',
fid=self.id,
symbolId=self.symbol_id,
angle=self.angle if self.angle is not None else 0,
size=self.size if self.size is not None else 10,
fontSize=self.font_size if self.font_size is not None else 15,
opacity=self.opacity if self.opacity is not None else 0.5,
shape=self.shape)
return geojson.Feature(id=self.id,
geometry=geometry,
properties=properties)
@property
def geom(self):
if hasattr(self.geometry, "geom_wkb"):
return wkb.loads(str(self.geometry.geom_wkb))
else:
if hasattr(self, "_shape"):
return self._shape
else:
return None
class Task(Base):
__tablename__ = "task"
id = Column(Integer, default=task_id_factory)
x = Column(Integer)
y = Column(Integer)
zoom = Column(Integer)
project_id = Column(Integer, ForeignKey('project.id'), index=True)
geometry = Column(Geometry('MultiPolygon', srid=4326))
date = Column(DateTime, default=datetime.datetime.utcnow)
lock_date = Column(DateTime, default=None)
extra_properties = Column(Unicode)
assigned_to_id = Column(Integer, ForeignKey('users.id'))
assigned_to = relationship(User)
assigned_date = Column(DateTime)
difficulty_easy = 1
difficulty_medium = 2
difficulty_hard = 3
difficulty = Column(Integer)
cur_lock = relationship(
TaskLock,
primaryjoin=lambda: and_(
Task.id == TaskLock.task_id, Task.project_id == TaskLock.
project_id, TaskLock.date == select([func.max(
TaskLock.date)]).where(
and_(TaskLock.task_id == Task.id, TaskLock.project_id ==
Task.project_id)).correlate(Task.__table__)),
uselist=False)
cur_state = relationship(
TaskState,
primaryjoin=lambda: and_(
Task.id == TaskState.task_id, Task.project_id == TaskState.
project_id, TaskState.date == select([func.max(
TaskState.date)]).where(
and_(TaskState.task_id == Task.id, TaskState.project_id ==
Task.project_id)).correlate(Task.__table__)),
uselist=False)
locks = relationship(TaskLock,
order_by="desc(TaskLock.date)",
cascade="all, delete, delete-orphan",
backref="task")
states = relationship(TaskState,
order_by="desc(TaskState.date)",
cascade="all, delete, delete-orphan",
backref="task")
comments = relationship(TaskComment,
order_by="desc(TaskComment.date)",
cascade="all, delete, delete-orphan",
backref="task")
__table_args__ = (
PrimaryKeyConstraint('project_id', 'id'),
Index('task_lock_date_', date.desc()),
{},
)
def __init__(self, x, y, zoom, geometry=None, properties=None):
self.x = x
self.y = y
self.zoom = zoom
if properties is not None:
self.extra_properties = _dumps(properties)
if geometry is None:
geometry = self.to_polygon()
multipolygon = MultiPolygon([geometry])
geometry = ST_Transform(shape.from_shape(multipolygon, 3857), 4326)
self.geometry = geometry
self.states.append(TaskState())
self.locks.append(TaskLock())
def to_polygon(self):
# task size (in meters) at the required zoom level
step = max / (2**(self.zoom - 1))
tb = TileBuilder(step)
return tb.create_square(self.x, self.y)
def to_feature(self):
properties = {
'state': self.cur_state.state if self.cur_state else 0,
'locked': self.lock_date is not None
}
if self.difficulty:
properties['difficulty'] = self.difficulty
if self.x and self.y and self.zoom:
properties['x'] = self.x
properties['y'] = self.y
properties['zoom'] = self.zoom
return Feature(geometry=shape.to_shape(self.geometry),
id=self.id,
properties=properties)
def get_extra_instructions(self):
instructions = self.project.per_task_instructions
properties = {}
if self.x:
properties['x'] = str(self.x)
if self.y:
properties['y'] = str(self.y)
if self.zoom:
properties['z'] = str(self.zoom)
if self.extra_properties:
properties.update(_loads(self.extra_properties))
return instructions.format(**properties)
def add_geometry_column(cls):
if not hasattr(cls, 'geom'):
cls.geom = Column(Geometry(geometry_type='POINT',
srid=config.SRID))
def add_geometry_column(cls):
cls.geom = Column(Geometry(geometry_type='POINT', srid=config.SRID))
def import_geodataframe(
self,
gdf: gpd.GeoDataFrame,
table_name: str,
src_epsg: Union[int, bool] = False,
if_exists: str = "replace",
schema: str = None,
uid_col: str = "uid",
):
"""
Import an in-memory ``geopandas.GeoDataFrame`` to the SQL database.
:param gdf: geodataframe with data you want to save
:type gdf: gpd.GeoDataFrame
:param table_name: name of the table that will get created
:type table_name: str
:param src_epsg: The source EPSG code can be passed as an integer.
By default this function will try to read the EPSG
code directly, but some spatial data is funky and
requires that you explicitly declare its projection.
Defaults to False
:type src_epsg: Union[int, bool], optional
:param if_exists: pandas argument to handle overwriting data,
defaults to "replace"
:type if_exists: str, optional
"""
if not schema:
schema = self.ACTIVE_SCHEMA
# Read the geometry type. It's possible there are
# both MULTIPOLYGONS and POLYGONS. This grabs the MULTI variant
geom_types = list(gdf.geometry.geom_type.unique())
geom_typ = max(geom_types, key=len).upper()
print(f"\t -> SQL tablename: {schema}.{table_name}")
print(f"\t -> Geometry type: {geom_typ}")
print(f"\t -> Beginning DB import...")
start_time = datetime.now()
# Manually set the EPSG if the user passes one
if src_epsg:
gdf.crs = f"epsg:{src_epsg}"
epsg_code = src_epsg
# Otherwise, try to get the EPSG value directly from the geodataframe
else:
# Older gdfs have CRS stored as a dict: {'init': 'epsg:4326'}
if type(gdf.crs) == dict:
epsg_code = int(gdf.crs["init"].split(" ")[0].split(":")[1])
# Now geopandas has a different approach
else:
epsg_code = int(str(gdf.crs).split(":")[1])
# Sanitize the columns before writing to the database
# Make all column names lower case
gdf.columns = [x.lower() for x in gdf.columns]
# Replace the 'geom' column with 'geometry'
if "geom" in gdf.columns:
gdf["geometry"] = gdf["geom"]
gdf.drop("geom", 1, inplace=True)
# Drop the 'gid' column
if "gid" in gdf.columns:
gdf.drop("gid", 1, inplace=True)
# Rename 'uid' to 'old_uid'
if uid_col in gdf.columns:
gdf[f"old_{uid_col}"] = gdf[uid_col]
gdf.drop(uid_col, 1, inplace=True)
# Build a 'geom' column using geoalchemy2
# and drop the source 'geometry' column
gdf["geom"] = gdf["geometry"].apply(
lambda x: WKTElement(x.wkt, srid=epsg_code))
gdf.drop("geometry", 1, inplace=True)
# Write geodataframe to SQL database
self.add_schema(schema)
engine = sqlalchemy.create_engine(self.uri())
gdf.to_sql(
table_name,
engine,
if_exists=if_exists,
# index=True,
# index_label=uid_col,
schema=schema,
dtype={"geom": Geometry(geom_typ, srid=epsg_code)},
)
engine.dispose()
end_time = datetime.now()
runtime = end_time - start_time
print(f"\t -> ... import completed in {runtime}")
self.table_add_uid_column(table_name, schema=schema, uid_col=uid_col)
self.table_add_spatial_index(table_name, schema=schema)
class clientLogIpInfo(CommonColumns):
__tablename__ = 'clientLogIpInfo'
cid = Column(Integer, ForeignKey('clientProfile.id'), nullable=False)
ip = Column(Integer, nullable=False)
date = Column(Integer, nullable=False)
coordinates = Column(Geometry('POINT')) # SQLAlchemy has no point type
def _write_postgis(
gdf,
name,
con,
schema=None,
if_exists="fail",
index=False,
index_label=None,
chunksize=None,
dtype=None,
):
"""
Upload GeoDataFrame into PostGIS database.
This method requires SQLAlchemy and GeoAlchemy2, and a PostgreSQL
Python driver (e.g. psycopg2) to be installed.
Parameters
----------
name : str
Name of the target table.
con : sqlalchemy.engine.Engine
Active connection to the PostGIS database.
if_exists : {'fail', 'replace', 'append'}, default 'fail'
How to behave if the table already exists:
- fail: Raise a ValueError.
- replace: Drop the table before inserting new values.
- append: Insert new values to the existing table.
schema : string, optional
Specify the schema. If None, use default schema: 'public'.
index : bool, default True
Write DataFrame index as a column.
Uses *index_label* as the column name in the table.
index_label : string or sequence, default None
Column label for index column(s).
If None is given (default) and index is True,
then the index names are used.
chunksize : int, optional
Rows will be written in batches of this size at a time.
By default, all rows will be written at once.
dtype : dict of column name to SQL type, default None
Specifying the datatype for columns.
The keys should be the column names and the values
should be the SQLAlchemy types.
Examples
--------
>>> from sqlalchemy import create_engine
>>> engine = create_engine("postgres://myusername:mypassword@myhost:5432\
/mydatabase";)
>>> gdf.to_postgis("my_table", engine)
"""
try:
from geoalchemy2 import Geometry
except ImportError:
raise ImportError("'to_postgis()' requires geoalchemy2 package. ")
if not compat.SHAPELY_GE_17:
raise ImportError(
"'to_postgis()' requires newer version of Shapely "
"(>= '1.7.0').\nYou can update the library using "
"'pip install shapely --upgrade' or using "
"'conda update shapely' if using conda package manager."
)
gdf = gdf.copy()
geom_name = gdf.geometry.name
# Get srid
srid = _get_srid_from_crs(gdf)
# Get geometry type and info whether data contains LinearRing.
geometry_type, has_curve = _get_geometry_type(gdf)
# Build dtype with Geometry
if dtype is not None:
dtype[geom_name] = Geometry(geometry_type=geometry_type, srid=srid)
else:
dtype = {geom_name: Geometry(geometry_type=geometry_type, srid=srid)}
# Convert LinearRing geometries to LineString
if has_curve:
gdf = _convert_linearring_to_linestring(gdf, geom_name)
# Convert geometries to EWKB
gdf = _convert_to_ewkb(gdf, geom_name, srid)
if if_exists == "append":
# Check that the geometry srid matches with the current GeoDataFrame
with con.begin() as connection:
if schema is not None:
schema_name = schema
else:
schema_name = "public"
# Only check SRID if table exists
if connection.run_callable(connection.dialect.has_table, name, schema):
target_srid = connection.execute(
"SELECT Find_SRID('{schema}', '{table}', '{geom_col}');".format(
schema=schema_name, table=name, geom_col=geom_name
)
).fetchone()[0]
if target_srid != srid:
msg = (
"The CRS of the target table (EPSG:{epsg_t}) differs from the "
"CRS of current GeoDataFrame (EPSG:{epsg_src}).".format(
epsg_t=target_srid, epsg_src=srid
)
)
raise ValueError(msg)
with con.begin() as connection:
gdf.to_sql(
name,
connection,
schema=schema,
if_exists=if_exists,
index=index,
index_label=index_label,
chunksize=chunksize,
dtype=dtype,
method=_psql_insert_copy,
)
return
