def get_video_clips(t1, t2):
video_list = []
conn = None
try:
conn = Db.get_connection()
selectQuery = "SELECT id, camera_id, start_time, end_time, file_path FROM video_clips where id in(select video_id from intruder_in_action \
where start_time >= '{}' and end_time <= '{}') order by start_time".format(t1, t2)
cursor = conn.cursor()
cursor.execute(selectQuery)
results = cursor.fetchall()
for row in results:
video_list.append(VideoData(row[0], row[1], row[2], row[3], row[4]))
except Exception as e:
logging.exception("Error while retrieving videos from table")
Db.disconnect(conn)
return video_list
def get_intruder_video_clips(id, t1, t2):
conn = None
video_clips = []
try:
conn = Db.get_connection()
selectQuery = "select id, camera_id, start_time, end_time, file_path from video_clips where id in ( \
SELECT distinct(video_id) FROM intruder_in_action where common_id in \
(select common_id from intruder_in_action \
where intruder_id = {} and start_time >= '{}' and end_time <= '{}') \
union \
select distinct(video_id) FROM intruder_in_action \
where intruder_id={} and start_time >= '{}' and end_time <= '{}' and common_id is null \
order by start_time)".format(id, t1, t2, id, t1, t2)
cursor = conn.cursor()
cursor.execute(selectQuery)
results = cursor.fetchall()
for row in results:
video_clip = VideoData(row[0], row[1], row[2], row[3], row[4])
video_clips.append(video_clip)
except Exception as e:
logging.exception("Error while retrieving videos from table")
Db.disconnect(conn)
return video_clips
def get_by_id(id):
conn = None
intruder = None
try:
conn = Db.get_connection()
selectQuery = (
"SELECT id,frame,start_time FROM intruder_data where id = {}".
format(id))
cursor = conn.cursor()
cursor.execute(selectQuery)
row = cursor.fetchone()
if (row is not None):
intruder = IntruderData(row[0], row[1])
except Exception as e:
logging.exception("Error while loading intruder data from db: %s" %
id)
Db.disconnect(conn)
return intruder
def get_count_by_id(id):
conn = None
count = 0
try:
conn = Db.get_connection()
print "id in get count by id {}".format(id)
#selectQuery2 = "select count(distinct common_id) as count from intruder_in_action where {} >= {} and {} <= {}".format(start_time, t1,end_time,t2)
#selectQuery3 = "select count(intruder_id) as count from intruder_in_action where common_id is null and {} >= {} and {} <= {}".format(start_time, t1,end_time,t2)"
query = "select count(intruder_id) as count from intruder_in_action \
where camera_id = {}".format(id)
cursor = conn.cursor()
cursor.execute(query)
results = cursor.fetchall()
for row in results:
count += row[0]
except Exception as e:
logging.exception("Error while retrieving statistics from table")
Db.disconnect(conn)
return count
def load_by_id(id):
conn = None
camera = None
try:
if CameraData.camera_list is None:
CameraData.load_all()
conn = Db.get_connection()
selectQuery = "SELECT id, location, description FROM camera_data where id = {}".format(id)
cursor = conn.cursor()
cursor.execute(selectQuery)
results = cursor.fetchall()
for row in results:
camera = CameraData(row)
print "Loaded camera: %d" % camera.id
CameraData.camera_list[camera.id] = camera
except Exception as e:
logging.exception("Error while loading camera list from database.")
#print "Error while loading camera list from database. Error: %s" % str(e)
Db.disconnect(conn)
return camera
def day_wise_distribution(d1, d2):
conn = None
count = 0
try:
conn = Db.get_connection()
#selectQuery2 = "select count(distinct common_id) as count from intruder_in_action where {} >= {} and {} <= {}".format(start_time, t1,end_time,t2)
#selectQuery3 = "select count(intruder_id) as count from intruder_in_action where common_id is null and {} >= {} and {} <= {}".format(start_time, t1,end_time,t2)"
data = []
dtemp1 = d1
dtemp2 = dtemp1 + datetime.timedelta(days=1)
while (dtemp2 <= d2):
count = 0
query = "select count(distinct common_id) as count from intruder_in_action \
where start_time >= '{}' and end_time <= '{}'".format(
dtemp1, dtemp2, dtemp1, dtemp2)
query2 = "select count(distinct intruder_id) as count from intruder_in_action \
where common_id is null and start_time >= '{}' and end_time <= '{}'".format(
dtemp1, dtemp2, dtemp1, dtemp2)
cursor = conn.cursor()
cursor.execute(query)
results = cursor.fetchall()
for row in results:
count += row[0]
cursor.execute(query2)
results = cursor.fetchall()
for row in results:
count += row[0]
data.append({"date": str(dtemp1.date()), "count": count})
dtemp1 = dtemp2
dtemp2 = dtemp2 + datetime.timedelta(days=1)
except Exception as e:
logging.exception("Error while retrieving statistics from table")
Db.disconnect(conn)
return data
def delete(self):
conn = None
try:
conn = Db.get_connection()
deleteQuery = ("delete from intruder_data where id = {}".format(
self.id))
cursor = conn.cursor()
cursor.execute(deleteQuery)
conn.commit()
except Exception as e:
logging.exception(
"Error while deleting intruder data from db: %d" % self.id)
Db.disconnect(conn)
def update(self):
conn = None
try:
conn = Db.get_connection()
updateQuery = ("update video_clips set end_time='{}' where id={}".format(self.end_time, self.id))
cursor = conn.cursor()
cursor.execute(updateQuery)
conn.commit()
except Exception as e:
logging.exception("Error while updating video clip")
Db.disconnect(conn)
def select_recent_actions():
'''
for (intruder_id,camera_id, video_id,start_time,end_time,start_frame,end_frame) in cursor:
print("intruder_id: {}, camera_id: {}, video_id{}, start_time: {}, end_time{}, start_frame{}, end_frame{} ".format(
intruder_id,camera_id, video_id,start_time, end_time,start_frame,end_frame))
'''
conn = None
actions = {}
try:
conn = Db.get_connection()
selectQuery = "SELECT id,common_id,intruder_id,camera_id, video_id,start_time,end_time,start_frame,end_frame FROM intruder_in_action "
cursor = conn.cursor()
cursor.execute(selectQuery)
results = cursor.fetchall()
for row in results:
#print row
action = IntruderInActionData(row[0], row[1], row[2], row[3],
row[4], row[5], row[6], row[7],
row[8])
if actions.has_key(action.camera_id):
list = actions[action.camera_id]
list.append(action)
else:
list = []
list.append(action)
actions[action.camera_id] = list
except Exception as e:
logging.exception("Error while reading from database.")
return actions
def insert(self):
conn = None
try:
conn = Db.get_connection()
insertQuery = ("insert into video_clips(camera_id, start_time, file_path) values({},'{}','{}')".format(
self.camera_id, self.start_time, self.file_path))
cursor = conn.cursor()
cursor.execute(insertQuery)
self.id = cursor.lastrowid
conn.commit()
except Exception as e:
logging.exception("Error while saving video clip")
Db.disconnect(conn)
def insert(self):
conn = None
try:
conn = Db.get_connection()
insertQuery = "insert into intruder_in_action(intruder_id,camera_id,video_id,start_time,end_time,start_frame,end_frame) values( \
{},{},{},'{}','{}','{}','{}')" .format( \
self.intruder_id, self.camera_id, self.video_id, self.start_time, self.end_time, self.start_frame, self.end_frame)
print insertQuery
cursor = conn.cursor()
cursor.execute(insertQuery)
conn.commit()
self.id = cursor.lastrowid
except Exception as e:
logging.exception("Error while saving video clip")
Db.disconnect(conn)
def match(self):
#vidcap = cv2.VideoCapture(video)
img1 = cv2.imread(self.croppedImage,0) # queryImage
img2 = cv2.imread(self.fullImage,0) # trainImage
#success = True
#while success:
#success,img2 = vidcap.read()
# Initiate SIFT detector
sift = cv2.xfeatures2d.SURF_create()
# find the keypoints and descriptors with SIFT
kp1, des1 = sift.detectAndCompute(img1,None)
kp2, des2 = sift.detectAndCompute(img2,None)
# FLANN parameters
FLANN_INDEX_KDTREE = 1
index_params = dict(algorithm = FLANN_INDEX_KDTREE, trees = 5)
search_params = dict(checks=50) # or pass empty dictionary
flann = cv2.FlannBasedMatcher(index_params,search_params)
matches = flann.knnMatch(des1,des2,k=2)
# Need to draw only good matches, so create a mask
matchesMask = [[0,0] for i in xrange(len(matches))]
# ratio test as per Lowe's paper
for i,(m,n) in enumerate(matches):
if m.distance < 0.4*n.distance:
matchesMask[i]=[1,0]
flag = True
print "Matched! id {} with id {}".format(self.intruderId1,self.intruderId2)
try:
conn = Db.get_connection()
cursor = conn.cursor()
print "Linking actions: {}, {}".format(self.id1, self.id2)
cursor.callproc('project.LINK_INTRUDER_ACTIONS',(self.id1,self.id2))
conn.commit()
print "Results {}".format(cursor.fetchone())
except Exception as e:
print(e)
break
else:
flag = False
#print "Not a match id {} with {}".format(self.intruderId1,self.intruderId2)
'''
def insert(description):
camera = None
try:
conn = Db.get_connection()
print description
insertQuery = "insert into camera_data(description) values('{}')".format(description)
cursor = conn.cursor()
cursor.execute(insertQuery)
conn.commit()
id = cursor.lastrowid
camera = CameraData.load_by_id(id)
print "Id in insert: {}".format(id)
#print id
except Exception as e:
logging.exception("Error while inserting camera description")
Db.disconnect(conn)
return camera
def get_intruders(t1, t2):
conn = None
intruder_id_list = []
try:
conn = Db.get_connection()
selectQuery = "SELECT distinct(intruder_id) FROM intruder_in_action where start_time >= '{}' and end_time <= '{}'".format(t1, t2, t1, t2)
cursor = conn.cursor()
cursor.execute(selectQuery)
results = cursor.fetchall()
for row in results:
intruder_id_list.append(row[0])
except Exception as e:
logging.exception("Error while retrieving intruders from table")
Db.disconnect(conn)
return intruder_id_list
def insert(self):
conn = None
try:
conn = Db.get_connection()
frame_file = ""
if (self.frame is not None):
frame_file = self.frame
insertQuery = (
"insert into intruder_data(frame,camera_id) values('{}', {})".
format(frame_file, self.camera_id))
cursor = conn.cursor()
cursor.execute(insertQuery)
# Fetch the auto increment id
self.id = cursor.lastrowid # conn.insert_id()
print "Intruder ID: %d" % (self.id)
conn.commit()
except Exception as e:
logging.exception("Error while saving intruder data into db")
Db.disconnect(conn)
