def makeupposition(tp,pair1,pair2):
sql = '('
if tp == 2:
print 'position type is 2'
#0
maxup = callnglat.getdespoint(pair1[0],pair1[1],0,pair2[0])[1]
#90
maxright = callnglat.getdespoint(pair1[0],pair1[1],90,pair2[0])[0]
#180
maxdown = callnglat.getdespoint(pair1[0],pair1[1],180,pair2[0])[1]
#-90
maxleft = callnglat.getdespoint(pair1[0],pair1[1],-90,pair2[0])[0]
sql = MYSQL_COL_TABLE['latitude'] +' <= ' +mysqlQuery.revertdegree(maxup)+ ' AND '\
+ MYSQL_COL_TABLE['latitude'] +' >= ' +mysqlQuery.revertdegree(maxdown)+ ' AND '
if maxleft <= maxright:
sql+= MYSQL_COL_TABLE['longitude'] + ' >= ' + mysqlQuery.revertdegree(maxleft)+ ' AND '\
+ MYSQL_COL_TABLE['longitude'] + ' <= ' +mysqlQuery.revertdegree(maxright)
else:
sql += '(( ' + MYSQL_COL_TABLE['longitude'] + ' >= ' +mysqlQuery.revertdegree(maxleft) + ' AND '\
+MYSQL_COL_TABLE['longitude'] +' <= '+mysqlQuery.revertdegree(180)+' ) OR ( '\
+MYSQL_COL_TABLE['longitude'] + ' >= ' + mysqlQuery.revertdegree(-180) + ' AND '\
+MYSQL_COL_TABLE['longitude'] + ' <= ' + mysqlQuery.revertdegree(maxright)+'))'
sql += ' AND ('+ mysqlQuery.makeupdistance(str(math.radians(pair1[0])),str(math.radians(pair1[1]))) + ' <= '+ str(pair2[0])+")"
return sql
if tp == 1:
print 'position type is 1'
if tp == 0:
print 'position type is 0'
pair2 = callnglat.getlowerrightpoint(pair1[0],pair1[1],pair2[0],pair2[1])
sql = MYSQL_COL_TABLE['latitude'] +' <= ' +mysqlQuery.revertdegree(pair1[1])+ ' AND '\
+ MYSQL_COL_TABLE['latitude'] +' >= ' +mysqlQuery.revertdegree(pair2[1])+ ' AND '
if pair1[0] <= pair2[0]:
sql+= MYSQL_COL_TABLE['longitude'] + ' >= ' + mysqlQuery.revertdegree(pair1[0])+ ' AND '\
+ MYSQL_COL_TABLE['longitude'] + ' <= ' +mysqlQuery.revertdegree(pair2[0])
else:
sql += '( ( ' + MYSQL_COL_TABLE['longitude'] + ' >= ' +mysqlQuery.revertdegree(pair1[0]) + ' AND '\
+MYSQL_COL_TABLE['longitude'] +' <= '+mysqlQuery.revertdegree(180)+' ) OR ( '\
+MYSQL_COL_TABLE['longitude'] + ' >= ' + mysqlQuery.revertdegree(-180) + ' AND '\
+MYSQL_COL_TABLE['longitude'] + ' <= ' + mysqlQuery.revertdegree(pair2[0])+') )'
sql += ')'
return sql
def queryHive(self, timestart=0, timeend=0, tp=1, pt1= (0,0), pt2=(0,0), mmsi=['0'], output='csv', taskid=''):
'''
:param timestart:
:param timeend:
:param tp:
:param pt1:
:param pt2:
:param mmsi:
:param output:
:param taskid:
:return:
hivetableName
'''
print "in queryHive"
year = time.localtime(timestart).tm_year
sqlContext = HiveContext(self.sc)
# time filter
table = None
print 'mmsi',mmsi
if mmsi[0] != '0':
#for x mmsi:
table = sqlContext.sql("select * from aisdynamiclog_{0} where drterminalcode={1}".format(year, mmsi[0])).rdd
else:
table = sqlContext.sql("select * from aisdynamiclog_{0}".format(year)).rdd
#table.cache()
#print table.count()
# space filter
rltrdd = None
if tp == 0:
pt2 = getlowerrightpoint(pt1[0], pt1[1], pt2[0], pt2[1])
pt_times1 = (revertdegree_f(pt1[0]), revertdegree_f(pt1[1]))
pt_times2 = (revertdegree_f(pt2[0]), revertdegree_f(pt2[1]))
if tp == 1:
pt_times1 = (revertdegree_f(pt1[0]), revertdegree_f(pt1[1]))
pt_times2 = (revertdegree_f(pt2[0]), revertdegree_f(pt2[1]))
if tp == 2:
pt_times1 = (revertdegree_f(pt1[0]), revertdegree_f(pt1[1]))
if tp == 0 or tp == 1:
rltrdd = table.filter(lambda row: callnglat.inside0_1(row, pt_times1, pt_times2))
elif tp == 2:
rltrdd = table.filter(lambda row: callnglat.inside2(row, pt_times1, pt2[0]))
#empty rdd
if rltrdd.count() == 0:
return
newdf = sqlContext.createDataFrame(rltrdd)
# regist with a unique name
tablename = "tmptable"
newdf.registerAsTable(tablename)
#hivetablename = "testtable3"
hivetablename = "query_{0}_{1}".format(taskid, year)
sql_create = "CREATE TABLE IF NOT EXISTS {0} \
ROW FORMAT DELIMITED FIELDS TERMINATED BY \',\' LINES TERMINATED BY \'\\n\' \
AS SELECT * from ais_model where 1=0".format(hivetablename)
sql_insert = "INSERT INTO TABLE {0} select * from {1}".format(hivetablename, tablename)
sqlContext.sql(sql_create)
sqlContext.sql(sql_insert)
return hivetablename